oracle - resueltos - plan de ejecución y optimización de consultas sql server 2008 r2

¿Cómo elegir y optimizar los índices de Oracle? (6)

De la Guía de desarrollo de aplicaciones de base de datos Oracle 10g - Fundamentos, Capítulo 5:

En general, debe crear un índice en una columna en cualquiera de las siguientes situaciones:

La columna se consulta con frecuencia.
Existe una restricción de integridad referencial en la columna.
Existe una restricción de integridad de clave ÚNICA en la columna.

Use las siguientes pautas para determinar cuándo crear un índice:

Cree un índice si con frecuencia desea recuperar menos del 15% de las filas en una tabla grande. Sin embargo, este porcentaje de umbral varía mucho, de acuerdo con la velocidad relativa de una exploración de tabla y cómo se agrupan los datos de fila sobre la clave de índice. Cuanto más rápido es el escaneo de la tabla, menor es el porcentaje; cuanto más agrupados estén los datos de fila, mayor será el porcentaje.
Columnas de índice que se utilizan para uniones para mejorar el rendimiento de las uniones.
Las claves primarias y únicas tienen índices automáticamente, pero es posible que desee crear un índice en una clave externa; vea el Capítulo 6, "Mantenimiento de la integridad de datos en el desarrollo de aplicaciones" para más información.
Las tablas pequeñas no requieren índices; si una consulta tarda demasiado, entonces la tabla podría haber crecido de pequeña a grande.

Algunas columnas son buenos candidatos para la indexación. Las columnas con una o más de las siguientes características son buenas candidatas para la indexación:

Los valores son únicos en la columna, o hay pocos duplicados.
Hay una amplia gama de valores (bueno para índices regulares).
Hay un pequeño rango de valores (bueno para índices de mapa de bits).
La columna contiene muchos valores nulos, pero las consultas a menudo seleccionan todas las filas que tienen un valor. En este caso, una comparación que coincida con todos los valores no nulos, como por ejemplo:
DONDE COL_X> = -9.99 * potencia (10,125) es preferible a DONDE COL_X NO ES NULO
Esto se debe a que el primero usa un índice en COL_X (suponiendo que COL_X sea una columna numérica).

Las columnas con las siguientes características son menos adecuadas para la indexación:

Hay muchos nulos en la columna y no busca en los valores no nulos.

Me gustaría saber si hay reglas generales para crear un índice o no. ¿Cómo elijo qué campos debo incluir en este índice o cuándo no incluirlos?

Sé que siempre depende del entorno y la cantidad de datos, pero me preguntaba si podríamos hacer algunas reglas globalmente aceptadas sobre cómo hacer índices en Oracle.

Hay algunas cosas que siempre debe indexar:

Claves primarias: se les asigna un índice automáticamente (a menos que especifique un índice existente adecuado para que Oracle lo use)
Llaves únicas: reciben un índice automáticamente (ídem)
Claves externas: estas no se indexan automáticamente, pero debe agregar una para evitar problemas de rendimiento cuando se verifican las restricciones.

Después de eso, busque otras columnas que se utilizan con frecuencia para filtrar las consultas: un ejemplo típico son los apellidos de las personas.

La documentación de Oracle tiene un excelente conjunto de consideraciones para las opciones de indexación: http://download.oracle.com/docs/cd/B28359_01/server.111/b28274/data_acc.htm#PFGRF004

Citando:

Considere la posibilidad de indexar las claves que se utilizan con frecuencia en las cláusulas WHERE.
Considere la posibilidad de indexar claves que se utilizan con frecuencia para unir tablas en sentencias de SQL. Para obtener más información sobre la optimización de combinaciones, consulte la sección "Uso de clústeres Hash para el rendimiento".
Elija claves de índice que tengan alta selectividad. La selectividad de un índice es el porcentaje de filas en una tabla que tiene el mismo valor para la clave indexada. La selectividad de un índice es óptima si algunas filas tienen el mismo valor. Nota: Oracle crea automáticamente índices, o usa índices existentes, en las claves y expresiones de claves únicas y primarias que usted define con restricciones de integridad. Las columnas de baja selectividad de indexación pueden ser útiles si la distribución de datos está sesgada, de modo que uno o dos valores ocurren con mucha menos frecuencia que otros valores.
No utilice índices B-tree estándar en claves o expresiones con pocos valores distintos. Dichas claves o expresiones generalmente tienen una baja selectividad y, por lo tanto, no optimizan el rendimiento a menos que los valores clave frecuentemente seleccionados aparezcan con menos frecuencia que los otros valores clave. Puede utilizar índices de mapa de bits de forma efectiva en tales casos, a menos que el índice se modifique con frecuencia, como en una aplicación OLTP de concurrencia alta.
No indexe las columnas que se modifican con frecuencia. Las instrucciones UPDATE que modifican las columnas indexadas y las instrucciones INSERT y DELETE que modifican las tablas indexadas tardan más que si no hubiera ningún índice. Dichas sentencias de SQL deben modificar los datos en índices así como los datos en tablas. También generan deshacer y rehacer adicionales.
No indexe las claves que aparecen solo en las cláusulas WHERE con funciones u operadores. Una cláusula WHERE que utiliza una función, distinta de MIN o MAX, o un operador con una clave indexada no pone a disposición la ruta de acceso que utiliza el índice, excepto con índices basados en funciones.
Considere la posibilidad de indexar claves externas de restricciones de integridad referencial en casos en los que un gran número de instrucciones INSERT, UPDATE y DELETE concurrentes acceden a las tablas padre e hijo. Tal índice permite UPDATE y DELETE en la tabla padre sin compartir bloqueando la tabla hija.
Al elegir indexar una clave, considere si la ganancia de rendimiento para las consultas vale la pérdida de rendimiento para INSERT, UPDATE y DELETE y el uso del espacio requerido para almacenar el índice. Es posible que desee experimentar comparando los tiempos de procesamiento de las sentencias SQL con y sin índices. Puede medir el tiempo de procesamiento con la función de rastreo de SQL.

Observe la normalización de la base de datos: encontrará muchas reglas buenas y estándares de la industria sobre qué claves deberían existir, cómo deben relacionarse las bases de datos y consejos sobre los índices.

-Adán

Por lo general, uno pone las columnas de ID por adelantado y generalmente identifican las filas de manera única. Una combinación de columnas también puede hacer lo mismo. Como ejemplo de uso de automóviles ... Etiquetas o placas son únicas y califican para un índice. Ellos (la columna de etiquetas) pueden calificar para la clave principal. El nombre del propietario puede calificar para un índice si va a buscar por nombre. La marca de automóviles realmente no debería tener un índice al principio, ya que no va a variar demasiado. Los índices no ayudan si los datos en la columna no varían demasiado.

Eche un vistazo al SQL: ¿cuáles son las cláusulas where? Esos pueden necesitar un índice.

Medida. ¿Cuál es el problema - las páginas / consultas tardan demasiado? qué se está utilizando para las consultas. Crea un índice en esas columnas.

Advertencias: los índices necesitan tiempo para las actualizaciones y el espacio.

y a veces los escaneos completos de tablas son más rápidos que un índice. las tablas pequeñas se pueden escanear más rápido que obtener el índice y luego golpear la mesa. Mira tus uniones.

Wow, ese es un tema tan grande, es difícil responder en este formato. Recomiendo este book .

Diseño de índice de base de datos relacional y optimizadores por Tapio Lahdenmaki

No solo utiliza índices para hacer que el acceso a la tabla sea más rápido, a veces crea índices para evitar el acceso a la tabla por completo. Algo no mencionado aún pero vital.

Hay una ciencia completa para esto si realmente quieres que tu base de datos rinda al máximo.

Ah, una optimización específica para Oracle está construyendo índices clave inversos. Si tiene un índice PK de un valor que aumenta de manera monoatómica, como una secuencia, y tiene inserciones altamente concurrentes y no planea escanear el rango de esa columna, entonces conviértalo en un índice de clave inversa.

¿Ves cuán específicas pueden ser estas optimizaciones?