avg - sql max group by

Seleccionar tanto MIN como MAX de la tabla es más lento de lo esperado

Question

Tengo una tabla MYTABLE con una columna de fecha SDATE que es la clave principal de la tabla y tiene un índice único en ella.

Cuando ejecuto esta consulta:

SELECT MIN(SDATE) FROM MYTABLE

Da respuesta al instante. Lo mismo sucede con:

SELECT MAX(SDATE) FROM MYTABLE

Pero, si me consulta los dos juntos:

SELECT MIN(SDATE), MAX(SDATE) FROM MYTABLE

se necesita mucho más tiempo para ejecutar. Analicé los planos y descubrí que cuando se consulta uno de los mínimos o los máximos, utiliza INDEX FULL SCAN (MIN / MAX), pero cuando se consultan ambos al mismo tiempo, se realiza un FULL TABLE SCAN.

¿por qué?

Datos de prueba:

versión 11g

create table MYTABLE
(
  SDATE  DATE not null,
  CELL   VARCHAR2(10),
  data NUMBER
)
tablespace CHIPS
  pctfree 10
  pctused 40
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );

alter table MYTABLE
  add constraint PK_SDATE primary key (SDATE)
  using index 
  tablespace SYSTEM
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );

Tabla de carga:

declare 
  i integer;
begin
  for i in 0 .. 100000 loop
     insert into MYTABLE(sdate, cell, data)
     values(sysdate - i/24, ''T'' || i, i);     
     commit;
  end loop;
end;

Recopilar estadísticas:

begin
  dbms_stats.gather_table_stats(tabname => ''MYTABLE'', ownname => ''SYS'');
end;

Plan1:

Plan2:

Answer 1

El Index Full Scan solo puede visitar un lado del índice. Cuando estas haciendo

SELECT MIN(SDATE), MAX(SDATE) FROM MYTABLE

Usted está solicitando visitar 2 lados. Por lo tanto, si desea el valor de columna mínimo y máximo, un Análisis completo de índice no es viable.

Un análisis más detallado que puedes encontrar here .

Answer 2

Intente no seleccionar ambos bordes del índice en una consulta, accediendo a la consulta de una manera diferente como esta:

select max_date, min_date
from (select max(sdate) max_date from mytable),
       (select min(sdate) min_date from mytable)

hará que el optimizador acceda al índice en INDEX_FULL_SCAN (MIN / MAX) en bucles anidados (en nuestro caso, dos veces).

Answer 3

Los planes explicativos son diferentes: un solo MIN o MAX producirá un INDEX FULL SCAN (MIN/MAX) mientras que cuando los dos estén presentes obtendrá un INDEX FULL SCAN o un FAST FULL INDEX SCAN .

Para entender la diferencia, tenemos que buscar una descripción de una FULL INDEX SCAN :

En una exploración de índice completa, la base de datos lee todo el índice en orden.

En otras palabras, si el índice está en un campo VARCHAR2 , Oracle buscará el primer bloque del índice que contendrá, por ejemplo, todas las entradas que comienzan con la letra "A" y leerá bloque por bloque todas las entradas alfabéticamente hasta la última entrada ("De la A a la Z"). Oracle puede procesar de esta manera porque las entradas se ordenan en un índice de árbol binario.

Cuando ve INDEX FULL SCAN (MIN/MAX) en un plan explicativo, es el resultado de una optimización que utiliza el hecho de que, dado que las entradas están ordenadas, puede detenerse después de haber leído la primera, si solo le interesa el MIN . Si solo está interesado en el MAX , Oracle puede usar la misma ruta de acceso, pero esta vez comenzando por la última entrada y leyendo hacia atrás de "Z" a "A".

A partir de ahora, una FULL INDEX SCAN tiene una sola dirección (ya sea hacia adelante o hacia atrás) y no puede comenzar desde ambos extremos simultáneamente, por eso, cuando solicita tanto el mínimo como el máximo, obtiene un método de acceso menos eficiente.

Como lo sugieren otras respuestas, si la consulta necesita una eficiencia crítica, puede ejecutar su propia optimización buscando el mínimo y el máximo en dos consultas distintas.

Answer 4

Tengo que decir que no veo el mismo comportamiento en 11.2

Si configuro un caso de prueba de la siguiente manera y lo actualizo de 10k a 1m filas en respuesta al comentario de Vincent

set linesize 130
set pagesize 0
create table mytable ( sdate date );

Table created.

insert into mytable
 select sysdate - level
   from dual
connect by level <= 1000000;
commit;

1000000 rows created.


Commit complete.

alter table mytable add constraint pk_mytable primary key ( sdate ) using index;

Table altered.

begin
dbms_stats.gather_table_stats( user, ''MYTABLE'' 
                             , estimate_percent => 100
                             , cascade => true
                               );
end;
/

PL/SQL procedure successfully completed.

Luego, ejecutando sus consultas obtengo planes de explicación casi idénticos (observe los diferentes tipos de INDEX FULL SCAN)

explain plan for select min(sdate) from mytable;

Explained.

select * from table(dbms_xplan.display);
Plan hash value: 3877058912

-----------------------------------------------------------------------------------------
| Id  | Operation          | Name   | Rows  | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |        |     1 |     8 |     1   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE        |        |     1 |     8 |        |      |
|   2 |   INDEX FULL SCAN (MIN/MAX)| PK_MYTABLE |     1 |     8 |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

9 rows selected.

explain plan for select min(sdate), max(sdate) from mytable;

Explained.

select * from table(dbms_xplan.display);
Plan hash value: 3812733167

-------------------------------------------------------------------------------
| Id  | Operation    | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |        |     1 |     8 |   252   (0)| 00:00:04 |
|   1 |  SORT AGGREGATE  |        |     1 |     8 |        |          |
|   2 |   INDEX FULL SCAN| PK_MYTABLE |  1000K|  7812K|   252   (0)| 00:00:04 |
-------------------------------------------------------------------------------

9 rows selected.

Para citar de una respuesta anterior mía:

Las dos razones más comunes para que una consulta no use índices son:

1. Es más rápido hacer un escaneo de tabla completa.
2. Estadísticas pobres.

A menos que haya algo que no esté publicando en la pregunta, mi respuesta inmediata sería que no ha recopilado estadísticas en esta tabla, no las ha recopilado con un porcentaje de estimación suficiente o no ha utilizado el analyze , lo que no ayudará a Optimizador basado en costos, a diferencia de dbms_stats.gather_table_stats .

Para citar de la documentación a analyze :

Para la recopilación de la mayoría de las estadísticas, use el paquete DBMS_STATS, que le permite recopilar estadísticas en paralelo, recopilar estadísticas globales para objetos particionados y afinar su colección de estadísticas de otras maneras. Consulte Oracle Database PL / SQL Packages and Types Reference para obtener más información sobre el paquete DBMS_STATS.

Use la declaración ANALYZE (en lugar de DBMS_STATS) para la recopilación de estadísticas no relacionadas con el optimizador basado en costos: