Crear indice usando CREATE INDEX Oracle

Crear índice usando CREATE INDEX en ORACLE es una de las técnicas más efectivas para optimizar el rendimiento de las consultas en una base de datos. Un índice actúa como un «atajo» que permite acceder a los datos de manera más rápida, mejorando significativamente los tiempos de respuesta en consultas complejas o sobre tablas con grandes volúmenes de información.

En este artículo, primero explicaré la sintaxis de la instrucción CREATE INDEX y, posteriormente, mostraré ejemplos prácticos que te ayudarán a entender cómo y cuándo usar esta poderosa herramienta. Es importante señalar que los índices son especialmente útiles en operaciones como búsquedas, ordenamientos o en la ejecución de cláusulas como WHERE, JOIN y ORDER BY.

Aunque muchos de los conceptos relacionados con índices son comunes a otras bases de datos relacionales, Oracle ofrece características avanzadas para personalizar la creación de índices, como índices únicos, compuestos o basados en funciones, que pueden adaptarse a las necesidades específicas de tu aplicación.

Si no estás familiarizado con lo que es un índice, te recomiendo revisar este artículo introductorio sobre índices. Si ya sabes lo básico, aquí aprenderás cómo implementarlos correctamente en Oracle para garantizar un rendimiento óptimo.

Para más información, puedes consultar la documentación oficial de ORACLE.

Sintaxis

Utiliza esta sintaxis para crear indice usando CREATE INDEX Oracle:

CREATE INDEX nombre_indice 
ON nombreTabla(nombreColumna1,nombreColumna2,...);

En la sintaxis anterior:

  • Después de CREATE INDEX se especifica el nombre del índice (se aconseja que sea fácil de recordar)
  • Después de ON se especifica en qué tabla se va crear el índice.
  • En tercer lugar se indica el método que se va utilizar para crear el índice. Tienes disponibles los tipos BTREE, HASH, GIST, SPGIST, GIN y BRIN. Esto es opcional, sino se especifica se usará BTREE.
  • En cuarto lugar se escriben una o más columnas de la tabla que se utilizaran en el índice. Puedes utilizar ASC o DESC de forma opcional para ordenar siendo ASC por defecto si no lo especificas. También puedes utilizar opcionalmente si los nulos irán primero o al final. Ten en cuenta que si no especificas el tratamiento de nulos se podrán al principio cuando se utilice el orden descendente e irán al final cuando no especifiques el orden.

Cuando se crea una nueva tabla con clave primaria, Oracle crea automáticamente un nuevo índice para las columnas de clave primaria.

Ver índices de una tabla

Para ver todos los índices de una tabla, se consulta desde la vista all_indexes:

SELECT 
    index_name, 
    index_type, 
    visibility, 
    status 
FROM 
    all_indexes
WHERE 
    table_name = 'empleados';

Crear un índice

En el siguiente ejemplo creamos un índice por el campo nombre de la tabla empleados con el resto de opciones por defecto:

CREATE INDEX index_emp_nombre 
ON empleados(nombre);
Crear indice usando CREATE INDEX Oracle

Si quieres comprobar si se está utilizando el índice puedes ejecutar la siguiente consulta:

EXPLAIN PLAN FOR
SELECT * FROM empleados
WHERE nombre = 'Miguel Troyano';

Índices basados en funciones

Cuando ejecutas una consulta y utilizas una función en un campo, a pesar de que ese campo tenga un índice no lo utilizará. Para resolver este problema se crean índices basados en funciones.

CREATE INDEX nombreIndice
ON nombreTabla(función(columna));

Por ejemplo, imagina que quieres un índice para la columna nombre, pero sabes que se va utilizar junto a la función LOWER para convertirlo en minúsculas. Utilizamos la siguiente sentencia para crear el índice.

CREATE INDEX index_emp_upper_nombre
ON empleados(LOWER(nombre));

Ejemplo completo

Copia y pega el siguiente código en tu consola de Oracle y realiza paso a paso leyendo los comentarios.

Para visualizar el código de ejemplo es necesario iniciar sesión desde desde la barra lateral derecha, desde los comentarios o desde el pie de página. Puedes utilizar tu cuenta de Microsoft o de Google. Gracias.

Escribir un comentario