Crear indice usando CREATE INDEX Oracle

En este artículo veremos como crear indice usando CREATE INDEX en PostgreSQL para mejorar la velocidad de la consultas y obtener los resultados más rápido. Si no conoces lo que es un índice puedes leer este otro artículo.

Sintaxis

La sintaxis para crear un índice es la siguiente:

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 indice por el campo nombre de la tabla empleados con el resto de opciones por defecto:

CREATE INDEX index_emp_nombre 
ON empleados(nombre);

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.

-- www.MiguelTroyano.com
-- Borramos la tabla si existe
drop table empleados;

-- Creamos la tabla
create table empleados
(
    id_empleado numeric(2,0),
    id_departamento numeric(2,0),
    nombre character varying(50),
    puesto character varying(50),
    fecha_alta date,
    sueldo integer
);

-- Insertamos valores
insert into empleados values (1,1,'Miguel Troyano','Analista',to_date('26/09/1986','DD/MM/YYYY'),60000);
insert into empleados values (2,1,'Ismael Troyano','Analista',to_date('01/01/2001','DD/MM/YYYY'),60000);
insert into empleados values (3,1,'Jose Troyano','Alta Dirección',to_date('01/01/2001','DD/MM/YYYY'),80000);
insert into empleados values (4,1,'Pilar Redondo','Alta Dirección',to_date('02/02/2002','DD/MM/YYYY'),80000);

-- Se crea un indice llamado index_emp_nombre
--por el campo nombre de la tabla empleados
CREATE INDEX index_emp_nombre 
ON empleados(nombre);

-- Mostramos si la consulta utiliza algún indice
EXPLAIN 
select * 
from empleados
where nombre = 'Miguel Troyano';

-- Se crea un indice llamado index_emp_upper_nombre
--por el campo nombre de la tabla empleados cuando se
--utiliza la función UPPER
CREATE INDEX index_emp_upper_nombre
ON empleados(LOWER(nombre));

-- Mostramos si la consulta utiliza algún indice
EXPLAIN 
select * 
from empleados
where lower(nombre) = 'miguel troyano';