sql - unico - Índice incorrecto que se utiliza al seleccionar las filas superiores

restriccion check sql server

Question

Tengo una consulta simple, que selecciona las primeras 200 filas ordenadas por una de las columnas filtradas por otra columna indexada. La confusión es por qué el plan de consulta en PL / SQL Developer muestra que este índice se usa solo cuando estoy seleccionando todas las filas, por ejemplo:

SELECT * FROM
(
 SELECT *
 FROM cr_proposalsearch ps
 WHERE UPPER(ps.customerpostcode) like ''MK3%''
 ORDER BY ps.ProposalNumber DESC
)
WHERE ROWNUM <= 200

El plan muestra que usa el índice CR_PROPOSALSEARCH_I1, que es un índice en dos columnas: NÚMERO DE PROPUESTA Y SUPERIOR ( NOMBRE DE CLIENTE), esto requiere 0.985 segundos para ejecutarse:

Si me deshago de la condición ROWNUM, el plan es lo que espero y se ejecuta en 0.343s :

Donde el index XIF25CR_PROPOSALSEARCH is on CR_PROPOSALSEARCH (UPPER(CUSTOMERPOSTCODE));

¿Cómo?

EDITAR : He reunido estadísticas sobre la tabla cr_proposalsearch y ambos planes de consulta ahora muestran que usan el índice XIF25CR_PROPOSALSEARCH .

Answer 1

Esta condición:

WHERE UPPER(ps.customerpostcode) like ''MK3%''

no es continuo, es decir, no puede conservar un solo rango ordenado para él.

Entonces, hay dos formas de ejecutar esta consulta:

1. Ordene por número y luego filtre el código.
2. Filtra el código y luego ordena por número.

El Método 1 es capaz de utilizar un índice en el número que le da tiempo de ejecución lineal (las 100 filas superiores se seleccionarían 2 veces más rápido que las 200 principales, siempre que ese número y código no se correlacionen).

El método 2 puede usar un escaneo de rango para filtrado grueso en código (la condición de rango sería algo así como code >= ''MK3'' AND code < ''MK4'' ), sin embargo, requiere un orden ya que el orden del número no puede ser preservado en un índice compuesto.

El tiempo de ordenamiento depende del número de filas superiores que también esté seleccionando, pero esta dependencia, a diferencia de la del método 1 , no es lineal (siempre necesita al menos un escaneo de rango).

Sin embargo, la condición de filtrado en el método 2 es lo suficientemente selectiva para la RANGE SCAN con una clasificación posterior para ser más eficiente que una FULL SCAN para toda la tabla.

Esto significa que hay un punto de inflexión: para esta condición: ROWNUM <= X existe un valor de X modo que el método 2 vuelve más eficiente cuando se excede este valor.

Actualizar:

Si siempre está buscando al menos 3 primeros símbolos, puede crear un índice como este:

SUBSTRING(UPPER(customerpostcode), 1, 3), proposalnumber

y usarlo en esta consulta:

SELECT  *
FROM    (
        SELECT  *
        FROM    cr_proposalsearch ps
        WHERE   SUBSTRING(UPPER(customerpostcode, 1, 3)) = SUBSTRING(UPPER(:searchquery), 1, 3)
                AND UPPER(ps.customerpostcode) LIKE UPPER(:searchquery) || ''%''
        ORDER BY
                proposalNumber DESC
        )
WHERE   rownum <= 200

De esta forma, el orden de los números se conservará por separado para cada conjunto de códigos que comparta las primeras 3 letras, lo que le dará un escaneo de índice más denso.

Answer 2

La inclusión de ROWNUM modifica los cálculos del optimizador sobre cuál es la ruta más eficiente.

Cuando realiza una consulta top-n como esta, no significa necesariamente que Oracle obtendrá todas las filas, las clasificará por completo y luego devolverá las más importantes. La operación COUNT STOPKEY en el plan de ejecución indica que Oracle solo realizará las operaciones subyacentes hasta que haya encontrado el número de filas que solicitó.

El optimizador ha calculado que la consulta completa adquirirá y ordenará 77K filas. Si utilizara este plan para la consulta top-n, tendría que hacer un gran tipo de esas filas para encontrar las 200 principales (no necesariamente tendría que ordenarlas completamente, ya que no le importaría el orden exacto de filas más allá de la parte superior, pero tendría que mirar todas esas filas).

El plan para la consulta top-n utiliza el otro índice para evitar tener que ordenar en absoluto. Considera cada fila en orden, comprueba si coincide con el predicado y, de ser así, la devuelve. Cuando se devuelven 200 filas, ya está hecho. Sus cálculos indican que esto será más eficiente para obtener un pequeño número de filas. (Puede que no sea correcto, por supuesto, usted no ha dicho cuál es el rendimiento relativo de estas consultas).

Si el optimizador fuera a elegir este plan cuando solicite todas las filas, debería leer todo el índice en orden descendente, obteniendo cada fila de la tabla por ROWID a medida que va contra el predicado. Esto daría como resultado una gran cantidad de E / S adicionales e inspeccionaría muchas filas que no serían devueltas. Entonces, en este caso, decide que usar el índice en customerpostcode es más eficiente.

Si aumenta gradualmente el número de filas que se devolverán desde la consulta top-n, probablemente encontrará un punto de inflexión en el que el plan cambia de la primera a la segunda. Solo por los costos de los dos planes, supongo que esto podría ser alrededor de 1,200 filas.

Answer 3

Parece que no tienes un índice perfectamente adecuado. El índice CR_PROPOSALSEARCH_I1 se puede usar para recuperar las filas en orden descendente del atributo PROPOSALNUMBER. Probablemente se elija porque Oracle puede evitar recuperar todas las filas coincidentes, ordenarlas de acuerdo con la cláusula ORDER BY y luego descartar todas las filas, excepto las primeras.

Sin la condición ROWNUM, Oracle usa el índice XIF25CR_PROPOSALSEARCH (no proporcionó ningún detalle al respecto) porque probablemente sea bastante selectivo con respecto a la cláusula WHERE. Pero requerirá ordenar el resultado después. Este es probablemente el plan más eficiente basado en la suposición de que recuperará todas las filas.

Como cualquiera de los índices es una solución de compromiso (uno es mejor para la clasificación, el otro mejor para aplicar la cláusula WHERE), detalles como ROWNUM determinan qué plan de ejecución elige Oracle.

Answer 4

Si está seguro de que sus estadísticas están actualizadas y de que el índice es lo suficientemente selectivo, podría decirle a Oracle que use el índice

SELECT  *
FROM   (SELECT /*+ index(ps XIF25CR_PROPOSALSEARCH) */  *
        FROM     cr_proposalsearch ps
        WHERE    UPPER (ps.customerpostcode) LIKE ''MK3%''
        ORDER BY ps.proposalnumber DESC)
WHERE  ROWNUM <= 200

(Solo recomendaría este enfoque como último recurso)

Si estuviera haciendo esto, primero haría la consulta para ver realmente cuánto trabajo está haciendo,

por ejemplo: el costo de los escaneos de rango de índice podría estar muy lejos

olvidé mencionar ... Debes verificar la cardinalidad real:

SELECT count(*)  FROM cr_proposalsearch ps  WHERE UPPER(ps.customerpostcode) like ''MK3%'' 

y luego compararlo con la cardinalidad en el plan de consulta.