在 Oracle 中识别具有最大行数的表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/390945/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Identify a table with maximum rows in Oracle
提问by Preets
I have a set of tables in Oracle and I would like to identify the table that contains the maximum number of rows.
我在 Oracle 中有一组表，我想确定包含最大行数的表。
So if, A has 200 rows, B has 345 rows and C has 120 rows I want to be able to identify table B.
因此，如果 A 有 200 行，B 有 345 行，C 有 120 行，我希望能够识别表 B。
Is there a simple query I can run to achieve this?
有没有我可以运行的简单查询来实现这一点？
Edit: There are 100 + tables so I am looking for something generic.
编辑：有 100 多个表，所以我正在寻找通用的东西。
回答by Dan
Given that you said you were using Oracle I would just query the meta-data.
鉴于您说您使用的是 Oracle，我只会查询元数据。
select table_name, max(num_rows) from all_tables where table_name in ('A', 'B', 'C');
Just saw your edit. Just run the above without the where clause and it will return the largest table in the database. Only problem may be that you might get a SYS$ table or something. Alternately if you are just doing this for your own knowledge just do
刚看到你的编辑。只需在没有 where 子句的情况下运行上面的代码，它将返回数据库中最大的表。唯一的问题可能是您可能会得到一个 SYS$ 表或其他东西。或者，如果你只是为了你自己的知识这样做
select table_name, num_rows from all_tables order by num_rows;
and you'll see what the biggest are.
你会看到最大的是什么。
回答by tuinstoel
The table in your schema that has max rows:
架构中具有最大行数的表：
with data as
(
select table_name,
to_number(extractvalue(xmltype(
dbms_xmlgen.getxml (
' select count(*) c from ' || table_name)),
'/ROWSET/ROW/C')) countrows
from user_tables
)
select table_name, countrows
from data
where countrows = (select max(countrows)
from data);
dbms_xmlgen.getxml(' select .... ') is extremely flexible.
dbms_xmlgen.getxml(' select .... ') 非常灵活。
回答by Dave Costa
Here's another method, likely to be much slower than simply getting ALL_TABLES.NUM_ROWS, but it doesn't depend on statistics having been gathered and gives exact, current values -- although how current depends on how long it takes to run!
这是另一种方法，可能比简单地获取 ALL_TABLES.NUM_ROWS 慢得多，但它不依赖于已收集的统计信息并提供准确的当前值——尽管当前值取决于运行所需的时间！
-- For running in SQLPlus you need this to see the output.
-- If running in Toad or similar tool, output is enabled by default
SET SERVEROUTPUT ON SIZE 100000
DECLARE
l_rows INTEGER;
l_max_rows INTEGER := 0;
l_table_name all_tables.table_name%TYPE := NULL;
BEGIN
FOR table_record IN (SELECT table_name FROM all_tables) LOOP
EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM '||table_record.table_name
INTO l_rows;
IF l_rows > l_max_rows THEN
l_max_rows := l_rows;
l_table_name := table_record.table_name;
END IF;
END LOOP;
IF l_table_name IS NULL THEN
dbms_output.put_line( 'All tables are empty' );
ELSE
dbms_output.put_line( 'Table ' || table_record.table_name ||
' has ' || TO_CHAR(l_max_rows) || ' rows'
);
END IF;
END;
/
回答by Dave Costa
You could get the same result with one trawl of the data like so:
您可以通过一次拖网数据获得相同的结果，如下所示：
SELECT DISTINCT
FIRST_VALUE ( t.owner )
OVER ( ORDER BY t.num_rows DESC NULLS LAST )
owner,
FIRST_VALUE ( t.table_name )
OVER ( ORDER BY t.num_rows DESC NULLS LAST )
table_name,
FIRST_VALUE ( t.num_rows )
OVER ( ORDER BY t.num_rows DESC NULLS LAST )
num_rows
FROM all_tables t
回答by RussellH
David Aldridge correctly points out that querying all_tables could give incorrect results due to missing or stale table statistics. But there is also a problem with using user_segments; Deleted blocks beneath the high water mark would still be counted for the size of the table.
David Aldridge 正确地指出查询 all_tables 可能会由于缺少或陈旧的表统计信息而给出不正确的结果。但是使用user_segments也有问题；高水位线下方的已删除块仍将计入表的大小。
Example:
例子：
SQL>create table t as select * from all_objects
Table created.
SQL>select blocks, bytes from user_segments where segment_name = 'T';
BLOCKS BYTES
---------- ----------
768 6291456
SQL>delete from t
52676 rows deleted.
SQL>commit;
Commit complete.
SQL>select count(*) from t;
COUNT(*)
----------
0
SQL>select blocks, bytes from user_segments where segment_name = 'T';
BLOCKS BYTES
---------- ----------
768 6291456
SQL>truncate table t;
Table truncated.
SQL>select blocks, bytes from user_segments where segment_name = 'T';
BLOCKS BYTES
---------- ----------
8 65536
回答by Gabriele D'Antona
select max(select count(*) from A union select count(*) from B...)
should work.
应该管用。
edit: if you want something dynamic, you can build a string in PL/SQL with each "count(*)" subquery (for example, listing the table names from USER_TABLES), and then execute te main query with:
编辑：如果你想要一些动态的东西，你可以在 PL/SQL 中用每个“count(*)”子查询构建一个字符串（例如，列出 USER_TABLES 中的表名），然后执行主查询：
execute immediate 'select max('||subquery||')'
回答by Ranjan Jena
This is a query to get the maximum numbers of rows there in a database table..
这是获取数据库表中最大行数的查询。
select table_name, num_rows from USER_TABLES
where num_rows = (select max(num_rows) from
(select table_name, num_rows from USER_TABLES));
