Define table with maximum rows in Oracle

Question

I have a set of tables in Oracle, and I would like to define a table containing the maximum number of rows.

So, if A has 200 rows, B has 345 rows, and C has 120 rows, which I want to identify table B.

Is there a simple query I can execute for this?

Edit: There are 100+ tables, so I'm looking for something in common.

Answer 1

Given that you said you were using Oracle, I would just request metadata.

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 can be that you can get the SYS $ table or something else. If you just do it for your own knowledge, just do it

 select table_name, num_rows from all_tables order by num_rows; 

and you will see which are the largest.

Answer 2

The table in your schema with 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.

Answer 3

Here, another method is likely to be much slower than just getting ALL_TABLES.NUM_ROWS, but it does not depend on the statistics collected and gives accurate current values ​​- although the time depends on how long it takes to start!

 -- 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; / 

Answer 4

 select max(select count(*) from A union select count(*) from B...) 

must work.

edit: if you want something dynamic, you can build a line in PL / SQL with each subquery count (*) "(for example, listing the table names from USER_TABLES), and then run the main query with:

 execute immediate 'select max('||subquery||')' 

Answer 5

You can get the same result with one trawl of such data:

 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 

Answer 6

David Aldridge correctly points out that the all_tables query may produce incorrect results due to missing or outdated table statistics. But there is also a problem with using user_segments; Deleted blocks under the high water mark will still be counted for the size of the table.

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 

Answer 7

This is a query to get the maximum number of rows 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));