convertir - Identificar una tabla con filas máximas en Oracle

pivot oracle columns to rows

Question

Tengo un conjunto de tablas en Oracle y me gustaría identificar la tabla que contiene el número máximo de filas.

Entonces, si A tiene 200 filas, B tiene 345 filas y C tiene 120 filas, quiero poder identificar la tabla B.

¿Hay una consulta simple que pueda ejecutar para lograr esto?

Editar: Hay más de 100 tablas, entonces estoy buscando algo genérico.

Answer 1

Aquí hay otro método, que probablemente sea mucho más lento que simplemente obtener ALL_TABLES.NUM_ROWS, pero no depende de que se hayan recopilado las estadísticas y proporciona valores actuales exactos, ¡aunque la forma actual depende del tiempo que lleva correr!

-- 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 2

Dado que dijiste que estabas usando Oracle, solo consultaría los metadatos.

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

Acabo de ver tu edición. Simplemente ejecute lo anterior sin la cláusula where y devolverá la tabla más grande en la base de datos. El único problema es que puede obtener una tabla SYS $ o algo así. Alternativamente, si solo hace esto por su propio conocimiento solo haga

select table_name, num_rows from all_tables order by num_rows; 

y verás cuáles son los más grandes.

Answer 3

La tabla en su esquema que tiene filas máximas:

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 ....'') es extremadamente flexible.

Answer 4

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

Deberia trabajar.

editar: si quieres algo dinámico, puedes construir una cadena en PL / SQL con cada subconsulta "count (*)" (por ejemplo, enumerando los nombres de tabla de USER_TABLES), y luego ejecutar la consulta principal con:

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

Answer 5

Puede obtener el mismo resultado con una red de búsqueda de datos como esta:

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 señala correctamente que consultar all_tables podría dar resultados incorrectos debido a estadísticas faltantes o obsoletas. Pero también hay un problema con el uso de user_segments; Los bloques eliminados debajo de la marca de agua alta aún se contarán para el tamaño de la tabla.

Ejemplo:

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

Esta es una consulta para obtener el número máximo de filas allí en una tabla de base de datos.

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