Определить таблицу с максимальными строками в Oracle

У меня есть набор таблиц в Oracle, и я бы хотел определить таблицу, содержащую максимальное количество строк.

Итак, если A имеет 200 строк, B имеет 345 строк, а C имеет 120 строк, которые я хочу, чтобы идентифицировать таблицу B.

Есть ли простой запрос, который я могу выполнить для этого?

Изменить: Есть 100 + таблицы, поэтому я ищу что-то общее.

7 ответов

Учитывая, что вы сказали, что используете Oracle, я бы просто запросил метаданные.

select table_name, max(num_rows) from all_tables where table_name in ('A', 'B', 'C');

Просто увидел ваше редактирование. Просто запустите выше без предложения where, и он вернет самую большую таблицу в базе данных. Только проблема может заключаться в том, что вы можете получить таблицу SYS $или что-то еще. Если вы просто делаете это для своих собственных знаний, просто делайте

select table_name, num_rows from all_tables order by num_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....') чрезвычайно гибкий.


Здесь другой метод, вероятно, будет намного медленнее, чем просто получение 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;
 /


Вы можете получить тот же результат с одним тралом таких данных:

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


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

должен работать.

edit: если вы хотите что-то динамическое, вы можете построить строку в PL/SQL с каждым подзапросом count (*) "(например, перечисляя имена таблиц из USER_TABLES), а затем выполнить основной запрос с помощью:

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


Дэвид Олдридж правильно указывает, что запрос all_tables может приводить к неправильным результатам из-за отсутствия или устаревшей статистики таблицы. Но есть также проблема с использованием user_segments; Удаленные блоки под отметкой высокой воды будут по-прежнему учитываться для размера таблицы.

Пример:

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


Это запрос для получения максимального количества строк в таблице базы данных.

select table_name, num_rows from USER_TABLES
where num_rows = (select max(num_rows) from
(select table_name, num_rows from USER_TABLES));

licensed under cc by-sa 3.0 with attribution.