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

Al ejecutar esta consulta:

SELECT MIN(SDATE) FROM MYTABLE

da respuesta al instante. Lo mismo sucede para:

SELECT MAX(SDATE) FROM MYTABLE

Pero, si me consulta de los dos juntos:

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

se necesita mucho más tiempo para ejecutar. He analizado los planes y encuentra cuando uno de min o max se consulta, se utiliza el ÍNDICE de análisis COMPLETO(MIN/MAX), pero cuando ambos se consultan al mismo tiempo, se hace un análisis COMPLETO de la TABLA.

¿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
  );

De la 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:

La selección de MIN y MAX De la Tabla es más lento de lo esperado

Plan2:

La selección de MIN y MAX De la Tabla es más lento de lo esperado

  • Cuántas filas de la tabla? ¿Cómo fresco son las estadísticas?
  • Mi tabla tiene casi 100000 filas y las estadísticas son frescos; usted puede fácilmente volver a generar el problema mediante la creación de una tabla simple con sólo uno o dos columnas y ver los resultados por ti mismo.
  • ¿cuáles son los costos de las consultas? puedes publicar los planes? Creo que el índice está muy fragmentado.
  • espacio de tablas SYSTEM? por favor, pruebe con otro.
  • Este es mi pequeño entorno de prueba así que no me importa el SISTEMA de espacio de tabla. pero de todos modos el mismo que está sucediendo en la producción de la tabla.
  • Por favor no te lleves malos hábitos: (1) no se pueden crear objetos en el esquema SYS, nunca. (2) no comprometerse en un bucle.
  • Voy a tomar tu consejo! Gracias. 🙂

InformationsquelleAutor RGO | 2012-09-24

4 Comentarios

  1. 12

    El Índice análisis Completo puede visitar sólo uno de los lados del índice. Cuando usted está haciendo

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

    que usted está solicitando para la visita de los 2 lados. Por lo tanto, si usted quiere que el mínimo y el máximo valor de la columna, un Índice Completo de Escaneo no es viable.

    Una descripción más detallada de analizar puedes encontrar aquí.

    • El enlace que usted tiene, no ofrece ninguna explicación para este comportamiento. No responder por qué el índice no se puede (o no) se utiliza para encontrar el MIN y el MAX.
    • +1 para proporcionar la respuesta correcta y un enlace a Richard Foote excelente blog en torno a este tema.
    • Acerca de ¿cuál crees que es todo lo que se hable en ese enlace que me dio si no explica por qué el Índice Completo de Escaneo no funciona para esa consulta? y tnx Rob van Wijk
    • Dudo que esta respuesta es totalmente correcta. Esta consulta: SELECT MIN(SDATE), MIN(SDATE) FROM MYTABLE también se ejecuta de forma diferente! Por favor, ver mi post actualizado.
    • es posible que el inusual (y redundante) consulta SELECT MIN(SDATE), MIN(SDATE) FROM MYTABLE de alguna manera está causando Oracle para no detectar la optimización que hace para SELECT MIN(SDATE) FROM MYTABLE. Sin embargo, en mi prueba (11gR2) la optimización funciona bien incluso con redundantes cláusulas en el select.
    • Usted pensaría que podría optimizar la distancia en 2 índice de escaneos completos dado que no hay otras columnas en la consulta y no de agrupación por nada.

  2. 6

    El explicar los planes son diferentes: una sola MIN o MAX producirá un INDEX FULL SCAN (MIN/MAX) mientras que cuando los dos están presentes, usted recibirá una INDEX FULL SCAN o un AYUNO COMPLETO INDEX SCAN.

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

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

    En otras palabras, si el índice está en una VARCHAR2 campo, Oracle obtendrá el primer bloque del índice que podría contener, por ejemplo, todas las entradas que comienzan con la letra «A» y de lectura de bloque por bloque todas las entradas por orden alfabético hasta que la última entrada («a» a la «Z»). Oracle puede procesar de esta manera debido a que las entradas se ordenan en un árbol binario de índice.

    Cuando vea INDEX FULL SCAN (MIN/MAX) en un explain plan, que es el resultado de una optimización que utiliza el hecho de que, dado que las entradas se ordenan, se puede parar después de haber leído la primera, si usted está interesado sólo por el MIN. Si usted está interesado en la MAX sólo, Oracle puede utilizar la misma ruta de acceso, pero esta vez a partir de la última entrada y la lectura al revés de «Z» a «A».

    A partir de ahora, un FULL INDEX SCAN tiene una sola dirección (hacia adelante o hacia atrás) y no se puede iniciar desde ambos extremos simultáneamente, es por eso que cuando usted pide tanto el min y el max, se obtiene una menos eficiente método de acceso.

    Según lo sugerido por otras respuestas, si la consulta de las necesidades críticas de la eficiencia, usted puede ejecutar su propio optimización buscando el min y el max en dos distintas consultas.

    • Mi primer pensamiento fue ¿por qué un índice completo análisis? Por qué no buscar? Pero ya que se detiene después de que el primer valor de la lectura, a continuación, que tiene sentido, es solo un paso, frente a un índice de búsqueda que serían múltiples pasos a pie del b-tree. Gracias por explicar de explicar.
  3. 5

    Trate de no seleccionar ambos bordes del índice en una consulta ,
    El acceso a la consulta de una manera diferente, como este :

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

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

    La selección de MIN y MAX De la Tabla es más lento de lo esperado

    • Yo iba a proponer la misma solución pero su respuesta hace la magia, sorprendente la manera en que el motor de sql no es lo suficientemente inteligente como para entender esto de forma automática, lol
  4. 2

    Tengo que decir que no veo el mismo comportamiento en 11.2

    Si puedo configurar un caso de prueba de la siguiente manera y actualizado de 10k 1 millón de filas en respuesta a Vicente del comentario

    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.

    Entonces, la ejecución de las consultas puedo obtener casi idénticos en busca explicar los planes (observe los diferentes tipos de ÍNDICE 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.

    A la cita de una respuesta anterior de la mina:

    Las dos razones más comunes para una consulta no uso de índices son:

    1. Es más rápido que hacer un análisis completo de la tabla.
    2. La escasez de estadísticas.

    A menos que haya algo que no estás publicando en la pregunta, mi respuesta inmediata sería que no se han recogido en las estadísticas de esta tabla, usted no ha reunido con un nivel suficientemente alto de estimación por ciento o has utilizado analizar, que se no ayudar a que el Optimizador Basado en Costes, a diferencia de dbms_stats.gather_table_stats.

    A la cita de la documentación en analyze:

    Para la recopilación de la mayoría de las estadísticas, utilice el paquete DBMS_STATS,
    que le permite recopilar estadísticas en paralelo, recoger global
    las estadísticas de los objetos con particiones, y afinar sus estadísticas
    colección de otras maneras. Ver Base de datos de Oracle PL/SQL Paquetes y
    Los tipos de Referencia para obtener más información sobre el paquete DBMS_STATS.

    Uso a ANALIZAR la declaración (en lugar de DBMS_STATS) para las estadísticas
    la colección no relacionados con el optimizador basado en el costo:

    • He visto tu comentario, indicando 100k filas, pero la re-hacerlo con esa cantidad no hace ninguna diferencia.
    • Yo siempre los datos en mi post. Yo también lo hice de nuevo y obtuvo los mismos resultados con 11.1.
    • 10k es insignificante 🙂 intente con 1 millón de filas, usted debe ver una diferencia
    • También la diferencia en explicar el plan es muy grande: el INDEX FULL SCAN (MIN/MAX) se detendrá después de que la primera fila recopilada (filas de la columna=1), mientras que el INDEX FULL SCAN va a leer todos los bloques de índice (filas de la columna=10000) que es 10000 veces más trabajo ! (casi 🙂
    • parece que voy a ser puesta en duda toda mi vida :-). He actualizado a 1 millón de filas.
    • Yo estaba tratando de llamar la atención a lo que realmente fue escrito en lugar de la salida; he actualizado a «casi idéntica buscando«… @VincentMalgrat.
    • Análisis COMPLETO (MIN/MAX) no es realmente una todas index scan?
    • es una versión optimizada de la INDEX FULL SCAN que se detendrá el escaneo completo después de la primera entrada que se encuentra para responder a la condición where, en este caso la primera fila. Puedo ver cómo el nombre puede ser engañoso 🙂
    • se ven casi de la misma 🙂 y de hecho estoy de acuerdo en que la diferencia de tiempo se ve más bien pequeñas (0,01 a 0,04), pero la cantidad de trabajo que debe variar mucho sospecho (se puede ejecutar instrucciones con el trazado automático TRACEONLY?)

Dejar respuesta

Please enter your comment!
Please enter your name here