sql - usar - uso de indices en oracle

¿Qué tan importante es el orden de las columnas en los índices?

Question

He escuchado que deberías poner columnas que serán las más selectivas al comienzo de la declaración del índice.

Por ejemplo:

CREATE NONCLUSTERED INDEX MyINDX on Table1
(
MostSelective,
SecondMost,
Least
)

Primero, ¿ese es el rumor correcto?

Si es así, ¿es probable que vea grandes diferencias en el rendimiento al reorganizar el orden de las columnas en mi índice, o es más una práctica "agradable de hacer"?

La razón por la que estoy preguntando es porque después de realizar una consulta a través del DTA, me recomendó que creara un índice que tuviera casi todas las mismas columnas como índice existente, simplemente en un orden diferente.

Estaba considerando simplemente agregar las columnas faltantes al índice existente y llamarlo bueno. ¿Pensamientos?

Answer 1

debe colocar columnas que serán las más selectivas al comienzo de la declaración del índice.

Correcto. Los índices pueden ser compuestos, compuestos de múltiples columnas, y el orden es importante debido al principio más a la izquierda. La razón es que la base de datos verifica la lista de izquierda a derecha y debe encontrar una referencia de columna correspondiente que coincida con el orden definido. Por ejemplo, tener un índice en una tabla de direcciones con columnas:

• Dirección
• Ciudad
• Estado

Cualquier consulta que use la columna de address puede utilizar el índice, pero si la consulta solo tiene referencias de city y / o state , no se puede usar el índice. Esto se debe a que no se hace referencia a la columna de la izquierda. El rendimiento de la consulta debería indicarle cuál es el óptimo: índices individuales o compuestos múltiples con diferentes órdenes. Buena lectura: The Tipping Point , por Kimberley Tripp

Answer 2

Como dice Remus, depende de tu carga de trabajo.

Sin embargo, quiero abordar un aspecto engañoso de la respuesta aceptada.

Para las consultas que realizan una búsqueda de igualdad en todas las columnas del índice, no hay una diferencia significativa.

A continuación, se crean dos tablas y las rellena con datos idénticos. La única diferencia es que uno tiene las claves ordenadas de más a menos selectivo y el otro al revés.

CREATE TABLE Table1(MostSelective char(800), SecondMost TINYINT, Least CHAR(1), Filler CHAR(4000) null);
CREATE TABLE Table2(MostSelective char(800), SecondMost TINYINT, Least CHAR(1), Filler CHAR(4000) null);
CREATE NONCLUSTERED INDEX MyINDX on Table1(MostSelective,SecondMost,Least);
CREATE NONCLUSTERED INDEX MyINDX2 on Table2(Least,SecondMost,MostSelective);
INSERT INTO Table1 (MostSelective, SecondMost, Least)
output inserted.* into Table2
SELECT TOP 26 REPLICATE(CHAR(number + 65),800), number/5, ''~''
FROM master..spt_values
WHERE type = ''P'' AND number >= 0
ORDER BY number;

Ahora haciendo una consulta en contra de ambas tablas ...

SELECT *
FROM Table1
WHERE MostSelective = REPLICATE(''P'', 800)
AND SecondMost = 3
AND Least = ''~'';
SELECT *
FROM Table2
WHERE MostSelective = REPLICATE(''P'', 800)
AND SecondMost = 3
AND Least = ''~'';

... Ambos usan una multa de índice y ambos reciben el mismo costo exacto.

El arte ASCII en la respuesta aceptada no es, de hecho, cómo se estructuran los índices. Las páginas de índice para Table1 se representan a continuación (haga clic en la imagen para abrir en tamaño completo).

Las páginas de índice contienen filas que contienen la clave completa (en este caso, hay una columna de clave adicional añadida para el identificador de fila ya que el índice no se declaró como único, pero se puede descartar que se pueda encontrar más información sobre esto aquí ).

Para la consulta anterior, SQL Server no se preocupa por la selectividad de las columnas. Realiza una búsqueda binaria en la página raíz y descubre que la clave (PPP...,3,~ ) es >=(JJJ...,1,~ ) y < (SSS...,3,~ ) por lo que debería leer la página 1:118 . Luego realiza una búsqueda binaria de las entradas clave en esa página y ubica la página de la hoja para viajar hacia abajo.

Alterar el índice en orden de selectividad no afecta el número esperado de comparaciones de clave de la búsqueda binaria o el número de páginas que se deben navegar para hacer una búsqueda de índice. En el mejor de los casos, podría acelerar marginalmente la comparación de claves en sí misma.

Sin embargo, en ocasiones, ordenar el índice más selectivo tendrá sentido para otras consultas en su carga de trabajo.

Por ejemplo, si la carga de trabajo contiene consultas de los dos formularios siguientes.

SELECT * ... WHERE MostSelective = ''P''
SELECT * ...WHERE Least = ''~''

Los índices anteriores no cubren ninguno de ellos. MostSelective es lo suficientemente selectivo como para hacer que un plan con una búsqueda y búsquedas valga la pena, pero la consulta contra Least no lo es.

Sin embargo, este escenario (el índice que no cubre la búsqueda en el subconjunto de columnas principales de un índice compuesto) es solo una clase posible de consulta que puede ser ayudado por un índice. Si en realidad nunca busca por MostSelective solo o una combinación de MostSelective, SecondMost y siempre busca por una combinación de las tres columnas, entonces esta ventaja teórica es inútil para usted.

Por el contrario, consultas como

SELECT MostSelective,
SecondMost,
Least
FROM Table2
WHERE Least = ''~''
ORDER BY SecondMost,
MostSelective

Sería útil tener el orden inverso al comúnmente prescrito, ya que cubre la consulta, puede admitir una búsqueda y devuelve filas en el orden deseado para arrancar.

Por lo tanto, este es un consejo que a menudo se repite, pero a lo sumo es una heurística sobre el beneficio potencial para otras consultas, y no es un sustituto para ver realmente su carga de trabajo.

Answer 3

El orden de las columnas es crítico. Ahora, el orden correcto depende de cómo lo va a consultar. Un índice se puede usar para hacer una búsqueda exacta o un escaneo de rango. Una búsqueda exacta es cuando se especifican los valores para todas las columnas en el índice y la consulta aterriza exactamente en la fila que le interesa. Para las búsquedas, el orden de las columnas es irrelevante. Un escaneo de rango es cuando solo se especifican algunas columnas, y en este caso cuando el orden se vuelve importante. SQL Server puede usar un índice para un escaneo de rango solo si se especifica la columna más a la izquierda, y luego solo si se especifica la siguiente columna más a la izquierda, y así sucesivamente. Si tiene un índice en (A, B, C) se puede usar para escanear el rango para A=@a , para A=@a AND B=@b pero no para B=@b , para C=@c ni B=@b AND C=@c . El caso A=@a AND C=@c es mixto, ya que en la porción A=@a usará el índice, pero C=@c no (la consulta escaneará todos los valores B para A=@a , no ''saltar'' a C=@c ). Otros sistemas de bases de datos tienen el llamado operador ''skip scan'' que puede aprovechar algunas ventajas de las columnas internas en un índice cuando las columnas externas no están especificadas.

Con ese conocimiento en la mano, puede ver las definiciones del índice nuevamente. Un índice en (MostSelective, SecondMost, Least) será efectivo solo cuando se especifique la columna MostSelective . Pero como es el más selectivo, la relevancia de las columnas internas se degradará rápidamente. Muy a menudo encontrará que un mejor índice está en (MostSelective) include (SecondMost, Least) o on (MostSelective, SecondMost) include (Least) . Debido a que las columnas internas son menos relevantes, colocar columnas de baja selectividad en tales posiciones correctas en el índice no hace más que ruido para una búsqueda, por lo que tiene sentido sacarlas de las páginas intermedias y guardarlas solo en las páginas de las hojas, para propósitos de cobertura de consulta En otras palabras, moverlos a INCLUDE. Esto se vuelve más importante a medida que aumenta el tamaño de la columna Least . La idea es que este índice solo puede beneficiar consultas que especifican MostSelective como un valor exacto o un rango, y esa columna es la más selectiva ya que restringe las filas candidatas en gran medida.

Por otro lado, un índice en (Least, SecondMost, MostSelective) puede parecer un error, pero en realidad es un índice bastante poderoso. Debido a que tiene la columna Least como su consulta más externa, se puede usar para consultas que tienen que agregar resultados en columnas de baja selectividad. Dichas consultas prevalecen en OLAP y almacenes de datos de análisis, y es aquí exactamente donde dichos índices tienen un muy buen caso. Tales índices en realidad son excelentes índices agrupados , exactamente porque organizan el diseño físico en grandes porciones de filas relacionadas (el mismo valor Least , que generalmente indica algún tipo de categoría o tipo) y facilitan las consultas de análisis.

Entonces, desafortunadamente, no hay un orden "correcto". No debe seguir ninguna receta de cortador de galletas, sino analizar el patrón de consulta que va a utilizar contra esas tablas y decidir qué orden de columna de índice es la correcta.

Answer 4

Mira un índice como este:

Cols
1 2 3
-------------
| | 1 | |
| A |---| |
| | 2 | |
|---|---| |
| | | |
| | 1 | 9 |
| B | | |
| |---| |
| | 2 | |
| |---| |
| | 3 | |
|---|---| |

Vea cómo restringir primero a A, ya que su primera columna elimina más resultados que restringir primero a su segunda columna. Es más fácil si te imaginas cómo debe atravesar el índice, columna 1, columna 2, etc. ... ves que cortar la mayoría de los resultados en el primer paso hace que el segundo paso sea mucho más rápido.

Otro caso, si consultaste en la columna 3, el optimizador ni siquiera usaría el índice, porque no ayuda en absoluto a reducir los conjuntos de resultados. Cada vez que está en una consulta, reducir el número de resultados a tratar antes del siguiente paso significa un mejor rendimiento.

Como el índice también se almacena de esta manera, no hay retroceso en el índice para encontrar la primera columna cuando se realiza la consulta.

En resumen: No, no es para mostrar, hay beneficios reales de rendimiento.