Some time ago we’ve seen how to create and maintain Oracle materialized views in order to improve query performance. But while materialized views are a valuable part of our toolbox, they definitely shouldn’t be our first attempt at improving a query performance. In this post we’re going to talk about something you’ve already heard about and used, but we will take it to the next level: indexes.
¿Por qué utilizar índices? Porque sin ellos usted tiene que realizar una lectura completa en cada tabla. Basta pensar en una guía telefónica: está indexado por su nombre, así que si te pido que encuentres todos los números de teléfono de personas cuyo nombre es Larrouturou, puedes hacerlo en menos de un minuto. Sin embargo, si te pido que encuentres a todas las personas que tienen un número de teléfono que comienza con 66903, no tendrás más remedio que leer el directorio telefónico completo. Espero que no tengas nada más planeado para los próximos dos meses o así.
Lo mismo ocurre con las tablas de la base de datos: si buscas algo en una tabla de hechos de filas multimillonarias no indexadas, la consulta correspondiente tomará mucho tiempo (y el usuario final típico no quiere sentarse 5 minutos Delante de su computadora esperando un informe). Si utilizó índices, podría haber encontrado el resultado en menos de 5 (o 1, o 0.1) segundos.
Voy a responder a las siguientes tres preguntas: ¿Qué tipo de índices podemos utilizar? ¿En qué tablas / campos los utilizaremos? ¿Cuáles son las consecuencias en términos de tiempo (tiempo de consulta, tiempo de construcción de índice) y almacenamiento?
¿Qué tipo de índices podemos usar?
Oracle tiene muchos tipos de índices disponibles (IOT, Cluster, etc.), pero sólo hablaré de los tres principales utilizados en data warehouses.
Índices de los árboles B
B-tree indexes are mostly used on unique or near-unique columns. They keep a good performance during update/insert/delete operations, and therefore are well adapted to operational environments using third normal form schemas. But they are less frequent in data warehouses, where columns often have a low cardinality. Note that B-tree is the default index type – if you have created an index without specifying anything, then it’s a B-tree index.
Índices de mapa de bits
Los índices de mapa de bits se utilizan mejor en columnas de cardinalidad baja y, a continuación, pueden ofrecer ahorros significativos en términos de espacio, así como un rendimiento de consulta muy bueno. Son más eficaces en las consultas que contienen varias condiciones en la cláusula WHERE.
Tenga en cuenta que los índices de mapa de bits son particularmente lentos para actualizar.
Índices de unión de mapa de bits
Un índice de unión de mapa de bits es un índice de mapa de bits para la unión entre tablas (2 o más). Almacena el resultado de las uniones, y por lo tanto puede ofrecer grandes prestaciones en las combinaciones predefinidas. Está especialmente adaptado a los entornos de esquema en estrella.
¿En qué tablas / campos usaremos qué índices?
¿Podemos poner índices en todas partes? No. Los índices vienen con costos (tiempo de creación, tiempo de actualización, almacenamiento) y deben crearse sólo cuando sea necesario.
Recuerde también que el objetivo es evitar lecturas completas de tabla - si la tabla es pequeña, entonces el optimizador de Oracle decidirá leer toda la tabla de todos modos. Por lo tanto, no es necesario crear índices en tablas pequeñas. Ya puedo oírte preguntar: "¿Qué es una mesa pequeña?" Una mesa de un millón de filas definitivamente no es pequeña. Una tabla 50 -row definitivamente es pequeña. ¿Una mesa de 4532 -crow? No estoy seguro. Vamos a ejecutar algunas pruebas y averiguar.
Antes de decidir sobre dónde usaremos los índices, analicemos nuestro esquema típico en estrella con una tabla de hechos y varias dimensiones.
Comencemos por ver la columna de cardinalidad. Tenemos un caso de unicidad: las claves primarias de las tablas de dimensiones. En ese caso, puede que desee utilizar un índice de árbol b para reforzar la unicidad. Sin embargo, si considera que el ETL que prepara las tablas de dimensiones ya se aseguró de que las claves de dimensión sean únicas, puede saltarse este índice (es todo acerca de su ETL y cuánto confía en él).
Entonces tenemos un caso de alta cardinalidad: las medidas en la tabla de hechos. Una de las principales preguntas que se deben hacer al decidir si aplicar o no un índice es: "¿Alguien va a buscar un valor específico en esta columna?" En este ejemplo he desarrollado asumo que nadie está interesado en saber qué cuenta Tiene un valor de 43453.12. Así que no hay necesidad de un índice aquí.
¿Qué pasa con los atributos de la dimensión? La respuesta es, depende". ¿Los usuarios van a hacer búsquedas en la columna X? Entonces usted quiere un índice. Elegirá el tipo basado en la cardinalidad: índice de mapa de bits para cardinalidad baja, árbol b para cardinalidad alta.
En cuanto a las claves de dimensión en la tabla de hechos, ¿va alguien a realizar una búsqueda en ellos? No directamente (sin filtros por las teclas de dimensión!), Pero indirectamente, sí. Cada consulta que une una tabla de hechos con una o más tablas de dimensiones busca claves de dimensión específicas en la tabla de hechos. Tenemos dos opciones para manejar esto: poner una clave de mapa de bits en cada columna o usar claves de combinación de mapa de bits.
Más consultas...
¿Son efectivos los índices? ¿Y qué hay del almacenamiento necesario? ¿Y el tiempo necesario para construir / refrescar los índices?
Vamos a hablar de eso la próxima semana en la segunda parte de mi puesto.