ver - uso de indices en oracle

Oracle: ¿importa el orden de las columnas en un índice?

Question

Se puede crear un índice en dos columnas con cualquiera de las declaraciones

create index foo_ix on foo(a,b);
create index foo_ix on foo(b,a);

1. ¿Cómo afecta esto a las características operativas (tiempo de ejecución) del uso del índice?

2. ¿Cómo afecta esto las características de diseño (físicas) del índice?

3. ¿Están (1) o (2) afectados por los tipos / tamaños de las columnas?

4. ¿Cuáles son las mejores prácticas para crear índices de varias columnas?

En resumen, ¿importa qué columna coloco primero?

Answer 1

1. Si a y b tienen 1000 valores diferentes y siempre se consultan juntos, el orden de las columnas en el índice realmente no importa. Pero si a tiene solo 10 valores distintos o si tiene consultas que usan solo una de las columnas, entonces sí importa; en estos escenarios, el índice no se puede usar si el orden de las columnas no se ajusta a la consulta.
2. La columna con los valores menos definidos debe ser la primera y la columna con los valores más distintos duran. Esto no solo maximiza la utilidad del índice sino que también aumenta las ganancias potenciales de la compresión del índice.
3. El tipo de datos y la longitud de la columna tienen un impacto en el retorno que podemos obtener de la compresión del índice, pero no en el mejor orden de columnas en un índice.
4. Organice las columnas con la columna menos selectiva primero y la columna más selectiva al final. En el caso de una ventaja de lazo con la columna que es más probable que se use por sí mismo.

La única excepción posible para 2. y 3. es con columnas de FECHA. Debido a que las columnas Oracle DATE incluyen un elemento de tiempo, pueden tener 86400 valores distintos por día . Sin embargo, la mayoría de las consultas en una columna de datos generalmente solo están interesadas en el elemento día, por lo que es posible que desee considerar solo el número de días distintos en sus cálculos. Aunque sospecho que no afectará la selectividad relativa en un puñado de casos.

editar (en respuesta al comentario de Nick Pierpoint)

Las dos razones principales para liderar con la columna menos selectiva son

1. Compresión de índice
2. Index Skip lee

Ambos hacen su magia sabiendo que el valor en la ranura actual es el mismo que en la ranura anterior. En consecuencia, podemos maximizar el rendimiento de estas técnicas minimizando el número de veces que cambia el valor. En el siguiente ejemplo, A tiene cuatro valores distintos y B tiene seis. Los dittos representan un valor comprimible o un bloque de índice omitible.

Least selective column leads ...

A          B
---------  -
AARDVARK   1
"          2
"          3
"          4
"          5
"          6
DIFFVAL    1
"          2
"          3
"          4
"          5
"          6
OTHERVAL   1
"          2
"          3
"          4
"          5
"          6
WHATEVER   1
"          2
"          3
"          4
"          5
"          6

La mayoría de los leads de columna selectivos ...

B  A
-  --------
1  AARDVARK
"  DIFFVAL
"  OTHERVAL
"  WHATEVER
2  AARDVARK
"  DIFFVAL
"  OTHERVAL
"  WHATEVER
3  AARDVARK
"  DIFFVAL
"  OTHERVAL
"  WHATEVER
4  AARDVARK
"  DIFFVAL
"  OTHERVAL
"  WHATEVER
5  AARDVARK
"  DIFFVAL
"  OTHERVAL
"  WHATEVER
6  AARDVARK
"  DIFFVAL
"  OTHERVAL
"  WHATEVER

Incluso en este ejemplo trival, (A, B) tiene 20 ranuras saltables en comparación con las 18 de (B, A) . Una disparidad más amplia generaría un mayor retorno de la inversión en la compresión del índice o una mejor utilidad de las lecturas de Indice de omisión.

Como es el caso con la mayoría de las heurísticas de ajuste, debemos compararlas con valores reales y volúmenes realistas. Este es definitivamente un escenario en el que la distorsión de los datos podría tener un impacto dramático en la efectividad de los diferentes enfoques.

"Creo que si tienes un primer índice altamente selectivo, entonces, desde una perspectiva de rendimiento, harás bien en ponerlo primero".

Si tenemos una columna altamente selectiva, deberíamos construir un índice propio. Los beneficios adicionales de evitar una operación de FILTRO en un puñado de filas es poco probable que se vean superados por la sobrecarga de mantener un índice compuesto.

Los índices de varias columnas son más útiles cuando tenemos:

• dos o más columnas de selectividad media,
• que se utilizan con frecuencia en la misma consulta.

Answer 2

1. si una columna se usa sola en una consulta, Oracle utilizará una ruta de acceso de índice de exploración de omisión menos eficiente si no es la columna principal en el índice
2. depende de la selectividad respectiva de las columnas
3. no
4. Me gustaría ver las consultas que involucran las columnas en el índice y clasificar su posición en el índice por los más consultados

Answer 3

Pero según el propio Oracle, es mejor poner primero la columna con la cardinalidad más alta:

http://docs.oracle.com/cd/B10500_01/server.920/a96533/data_acc.htm#2174

Ordenar claves para índices compuestos

Si todas las claves se usan en las cláusulas WHERE con la misma frecuencia, ordenar estas claves de la más selectiva a la menos selectiva en la sentencia CREATE INDEX mejora el rendimiento de la consulta.

Answer 4

Puede encontrar respuestas a algunas de sus preguntas aquí: Exploración de salto de índice: ¿importa más el orden de la columna de índice? (Señal de advertencia)