Alternativa más rápida en Oracle a SELECT COUNT (*) FROM sometable

Me he dado cuenta de que en Oracle, la consulta

SELECT COUNT(*) FROM sometable; 

es muy lento para tablas grandes Parece que la base de datos atraviesa cada fila e incrementa un contador a la vez. Yo pensaría que habría un contador en algún lugar de la tabla, cuántas filas tiene esa mesa.

Entonces, si quiero verificar el número de filas en una tabla en Oracle, ¿cuál es la forma más rápida de hacerlo?

Piénselo: la base de datos realmente tiene que ir a cada fila para hacer eso. En un entorno multiusuario, mi COUNT(*) puede ser diferente de su COUNT(*) . Sería impráctico tener un contador diferente para cada sesión, así que tienes que contar literalmente las filas. La mayoría de las veces, de todos modos, tendrías una cláusula WHERE o JOIN en tu consulta, por lo que tu contador hipotético sería de poco valor práctico.

Sin embargo, hay formas de acelerar las cosas: si tiene un INDICE en una columna NOT NULL, Oracle contará las filas del índice en lugar de la tabla. En un modelo relacional adecuado, todas las tablas tienen una clave principal, por lo que el COUNT(*) usará el índice de la clave principal.

El índice de bitmap tiene entradas para filas NULAS por lo que un COUNT (*) usará un índice de bitmap si hay uno disponible.

Si solo desea una estimación aproximada, puede extrapolar de una muestra:

SELECT COUNT(*) * 100 FROM sometable SAMPLE (1);

Para una mayor velocidad (pero menor precisión), puede reducir el tamaño de la muestra:

SELECT COUNT(*) * 1000 FROM sometable SAMPLE (0.1);

Para una velocidad aún mayor (pero con una precisión aún peor) puede usar un muestreo en bloque:

SELECT COUNT(*) * 100 FROM sometable SAMPLE BLOCK (1);

Esto funciona muy bien para tablas grandes.

 SELECT NUM_ROWS FROM ALL_TABLES WHERE TABLE_NAME = 'TABLE_NAME_IN_UPPERCASE'; 

Para tablas de tamaño pequeño a mediano, lo siguiente será correcto.

 SELECT COUNT(Primary_Key) FROM table_name; 

Aclamaciones,

Si la tabla tiene un índice en una columna NOT NULL, el COUNT (*) lo usará. De lo contrario, se ejecuta una exploración de tabla completa. Tenga en cuenta que el índice no tiene que ser ÚNICO, simplemente tiene que ser NO NULO.

Aquí hay una tabla …

 SQL> desc big23 Name Null? Type ----------------------------------------- -------- --------------------------- PK_COL NOT NULL NUMBER COL_1 VARCHAR2(30) COL_2 VARCHAR2(30) COL_3 NUMBER COL_4 DATE COL_5 NUMBER NAME VARCHAR2(10) SQL> 

Primero haremos un conteo sin índices …

 SQL> explain plan for 2 select count(*) from big23 3 / Explained. SQL> select * from table(dbms_xplan.display) 2 / select * from table)dbms_xplan.display) PLAN_TABLE_OUTPUT -------------------------------------------------------------------- Plan hash value: 983596667 -------------------------------------------------------------------- | Id | Operation | Name | Rows | Cost (%CPU)| Time | -------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 1618 (1)| 00:00:20 | | 1 | SORT AGGREGATE | | 1 | | | | 2 | TABLE ACCESS FULL| BIG23 | 472K| 1618 (1)| 00:00:20 | -------------------------------------------------------------------- Note PLAN_TABLE_OUTPUT -------------------------------------------------------------------- - dynamic sampling used for this statement 13 rows selected. SQL> 

No, creamos un índice en una columna que puede contener entradas NULL …

 SQL> create index i23 on big23(col_5) 2 / Index created. SQL> delete from plan_table 2 / 3 rows deleted. SQL> explain plan for 2 select count(*) from big23 3 / Explained. SQL> select * from table(dbms_xplan.display) 2 / PLAN_TABLE_OUTPUT -------------------------------------------------------------------- Plan hash value: 983596667 -------------------------------------------------------------------- | Id | Operation | Name | Rows | Cost (%CPU)| Time | -------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 1618 (1)| 00:00:20 | | 1 | SORT AGGREGATE | | 1 | | | | 2 | TABLE ACCESS FULL| BIG23 | 472K| 1618 (1)| 00:00:20 | -------------------------------------------------------------------- Note PLAN_TABLE_OUTPUT -------------------------------------------------------------------- - dynamic sampling used for this statement 13 rows selected. SQL> 

Finalmente construyamos el índice en la columna NOT NULL …

 SQL> drop index i23 2 / Index dropped. SQL> create index i23 on big23(pk_col) 2 / Index created. SQL> delete from plan_table 2 / 3 rows deleted. SQL> explain plan for 2 select count(*) from big23 3 / Explained. SQL> select * from table(dbms_xplan.display) 2 / PLAN_TABLE_OUTPUT --------------------------------------------------------------------- Plan hash value: 1352920814 ---------------------------------------------------------------------- | Id | Operation | Name | Rows | Cost (%CPU)| Time | ---------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 326 (1)| 00:00:04 | | 1 | SORT AGGREGATE | | 1 | | | | 2 | INDEX FAST FULL SCAN| I23 | 472K| 326 (1)| 00:00:04 | ---------------------------------------------------------------------- Note PLAN_TABLE_OUTPUT ---------------------------------------------------------------------- - dynamic sampling used for this statement 13 rows selected. SQL> 

Opción 1: Tener un índice en una columna no nula presente que pueda usarse para el escaneo. O crea un índice basado en funciones como:

 create index idx on t(0); 

esto puede escanearse para dar el conteo.

Opción 2: si tiene activada la monitorización, compruebe la visualización de supervisión USER_TAB_MODIFICATIONS y agregue / reste los valores relevantes a las estadísticas de la tabla.

Opción 3: para una estimación rápida en tablas grandes, invoque la cláusula SAMPLE … por ejemplo …

 SELECT 1000*COUNT(*) FROM sometable SAMPLE(0.1); 

Opción 4: use una vista materializada para mantener el recuento (*). Poderosa medicina sin embargo.

um …

Puede crear una vista materializada de actualización rápida para almacenar el recuento.

Ejemplo:

 create table sometable ( id number(10) not null primary key , name varchar2(100) not null); create materialized view log on sometable with rowid including new values; create materialized view sometable_count refresh on commit as select count(*) count from sometable; insert into sometable values (1,'Raymond'); insert into sometable values (2,'Hans'); commit; select count from sometable_count; 

Disminuirá un poco las mutaciones en la tabla, pero el recuento será mucho más rápido.

La forma más rápida de obtener un conteo de una mesa es exactamente lo que hizo. No hay trucos que pueda hacer que Oracle aún no conozca.

Hay algunas cosas que no nos has contado. A saber, ¿por qué crees que esto debería ser más rápido?

Por ejemplo:

  1. ¿Al menos has hecho un plan de explicación para ver qué está haciendo Oracle?
  2. ¿Cuántas filas hay en esta tabla?
  3. ¿Qué versión de Oracle estás usando? 8,9,10,11 … 7?
  4. ¿Alguna vez ha ejecutado las estadísticas de la base de datos en esta tabla?
  5. ¿Se carga una tabla o lote cargado con frecuencia o solo datos estáticos?
  6. ¿Es este el único COUNT lento (*) que tienes?
  7. ¿Cuánto tiempo SELECCIONA CUENTA (*) DESDE Dual Take?

Admitiré que no sería feliz con 41 segundos, pero realmente ¿POR QUÉ crees que debería ser más rápido? Si nos dice que la tabla tiene 18 mil millones de filas y se está ejecutando en la computadora portátil que compró en una venta de garaje en 2001, 41 segundos probablemente no quede tan afuera como sea posible, a menos que obtenga un mejor hardware. Sin embargo, si dice que está en Oracle 9 y realizó estadísticas el verano pasado, es probable que obtenga una sugerencia diferente.

Hubo una respuesta relevante de Ask Tom publicada en abril de 2016.

Si tiene suficiente potencia de servidor, puede hacer

 select /*+ parallel */ count(*) from sometable 

Si está justo después de una aproximación, puede hacer:

 select 5 * count(*) from sometable sample block (10); 

Además, si hay

  1. una columna que no contiene nulos, pero no está definida como NOT NULL, y
  2. hay un índice en esa columna

tu podrías intentar:

 select /*+ index_ffs(t) */ count(*) from sometable t where indexed_col is not null 

Puede tener un mejor rendimiento utilizando el siguiente método:

 SELECT COUNT(1) FROM (SELECT /*+FIRST_ROWS*/ column_name FROM table_name WHERE column_name = 'xxxxx' AND ROWNUM = 1); 

Podría usar COUNT (1) en su lugar