INDEX

Los índices son objetos de base de datos que puede crear para mejorar el rendimiento de algunas consultas. El servidor también puede crear índices automáticamente al crear una clave primaria o una restricción única. 

Un índice de Oracle Server es un objeto de esquema que puede acelerar la recuperación de filas mediante un puntero. Los índices se pueden crear explícita o automáticamente. Si no tiene un índice en la columna, se produce una exploración de tabla completa. 

Un índice proporciona acceso directo y rápido a las filas de una tabla. Su objetivo es reducir la necesidad de E/S de disco mediante una ruta de acceso indexada para localizar los datos rápidamente. Oracle Server utiliza y mantiene el índice automáticamente Después de la creación de un índice, no se requiere ninguna actividad directa por parte del usuario. 

Los índices son independientes lógica y físicamente de la tabla a la que indexan. Esto significa que se pueden crear y borrar en cualquier momento y que no afectan a las tablas base o a otros índices. 

Nota: Al borrar una tabla, se borran también los índices correspondientes. 

Para obtener más información, consulte “Schema Objects: Indexes” en Database Concepts. 

Un índice: 

• Es un objeto de esquema 
• Oracle Server lo utiliza para acelerar la recuperación de filas mediante un puntero 
• Puede reducir E/S de disco utilizando un método de acceso de ruta rápido para localizar los datos rápidamente 
• Es independiente de la tabla a la que indexa 
• Oracle Server lo utiliza y mantiene automáticamente 

¿Cómo se Crean los Índices? 

 Automáticamente: Se crea automáticamente un índice único cuando se define una restricción PRIMARY KEY o UNIQUE en una definición de tabla. 

Manualmente: Los usuarios pueden crear índices no únicos en columnas para acelerar el acceso a las filas. 

Tipos de Índices 

Se pueden crear dos tipos de índices. 

Índice único: Oracle Server crea automáticamente este índice cuando se define una columna en una tabla para tener una restricción PRIMARY KEY o UNIQUE. El nombre del índice es el nombre que se pone a la restricción. 

Índice no único: Es un índice que puede crear el usuario. Por ejemplo, puede crear un índice de columna FOREIGN KEY para una unión en una consulta para aumentar la velocidad de recuperación. 

Nota: Puede crear manualmente un índice único, pero se recomienda que cree una restricción única, lo que crea implícitamente un índice único. 

Creación de Índices 

Cree un índice en una o más columnas: 

Aumente la velocidad de acceso a consulta en la columna LAST_NAME de la tabla EMPLOYEES: 

CREATE INDEX emp_last_name_idx 

ON employees(last_name); 

Más No Siempre Es lo Mejor 

Tener más índices en una tabla no genera consultas más rápidas. Cada operación DML que se valida en una tabla con índices significa que los índices se deben actualizar. Cuantos más índices tenga asociados a una tabla, más esfuerzo tendrá que hacer Oracle Server para actualizarlos todos después de cada operación DML. 

Cuándo Crear Índices 

Por tanto, debe crear índices únicamente si: 

• La columna contiene un amplio rango de valores 
• La columna contiene un gran número de valores nulos 
• Se utilizan frecuentemente una o más columnas en una cláusula WHERE o en una condición de unión 
• La tabla es grande y se espera que la mayoría de las consultas recupere menos del 2 % al 4 % de las filas 

Recuerde que si desea forzar la unicidad, debe definir una restricción única en la definición de tabla. Un índice único se creará entonces automáticamente. 

Elimine un índice del diccionario de datos utilizando el comando DROP INDEX: 

Elimine el índice UPPER_LAST_NAME_IDX del diccionario de datos: 

DROP INDEX emp_last_name_idx; 

Para borrar un índice, debe ser el propietario del índice o tener el privilegio DROP ANY INDEX. 

Eliminación de Índices 

Los índices no se pueden modificar. Para cambiar un índice, debe borrarlo y después volverlo a crear. 

Elimine un índice del diccionario de datos utilizando el comando DROP INDEX. Para borrar un índice, debe ser el propietario del índice o tener el privilegio DROP ANY INDEX. 

En la sintaxis, index es el nombre del índice. 

Nota: Si borra una tabla, los índices y las restricciones se borran automáticamente, pero las vistas y las secuencias permanecen. 

Visión General de Índices 

Los índices se crean:

• Automáticamente 
• Creación de PRIMARY KEY 
• Creación de UNIQUE KEY 
• Manualmente 
• Sentencia CREATE INDEX 
• Sentencia CREATE TABLE

Se pueden crear dos tipos de índices. Un tipo es el índice único. Oracle Server crea automáticamente un índice único si se define una columna o un grupo de columnas en una tabla para tener una restricción PRIMARY KEY o UNIQUE. El nombre del índice es el nombre que se asigna a la restricción. 

El otro tipo de índice es el no único y lo puede crear un usuario. Por ejemplo, puede crear un índice de columna FOREIGN KEY que se utilizará en uniones para mejorar la velocidad de recuperación. 

Puede crear un índice en una o más columnas mediante la emisión de la sentencia CREATE INDEX. 

Para obtener más información, consulte Oracle Database 10g SQL Reference. 

Nota: Puede crear manualmente un índice único, pero se recomienda que cree una restricción única, lo que crea implícitamente un índice único.

CREATE INDEX con Sentencia CREATE TABLE

CREATE TABLE NEW_EMP

(employee_id NUMBER(6) 

                    PRIMARY KEY USING INDEX

   (CREATE INDEX emp_id_idx ON 

NEW_EMP(employee_id)),

first_name  VARCHAR2(20),

last_name   VARCHAR2(25));

SELECT INDEX_NAME, TABLE_NAME 

  FROM   USER_INDEXES

 WHERE TABLE_NAME = 'NEW_EMP';

 

En el ejemplo de la diapositiva, se utiliza la cláusula CREATE INDEX con la sentencia CREATE TABLE para crear un índice de clave primaria explícitamente. Puede asignar nombres a los índices en el momento de la creación de la clave primaria para que sean diferentes al nombre de la restricción PRIMARY KEY. El siguiente ejemplo muestra el comportamiento de la base de datos si no se asigna un nombre al índice explícitamente.

CREATE TABLE EMP_UNNAMED_INDEX  

                                                              (employee_id NUMBER(6) PRIMARY KEY ,                                                                                        first_name VARCHAR2(20),last_name VARCHAR2(25));

Table created.

   SELECT INDEX_NAME, TABLE_NAME 

      FROM USER_INDEXES   

  WHERE TABLE_NAME = 'EMP_UNNAMED_INDEX';

Observe que Oracle Server asigna un nombre genérico al índice que se crea para la columna PRIMARY KEY. 

También puede utilizar un índice existente para la columna PRIMARY KEY, por ejemplo cuando espera una carga grande de datos y desea acelerar la operación. Le conviene desactivar las restricciones durante la carga y activarlas después, en cuyo caso el hecho de tener un índice único en la clave primaria seguirá haciendo que se verifiquen los datos durante la carga. Así pues, puede crear primero un índice no único en la columna designada como PRIMARY KEY, crear a continuación la columna PRIMARY KEY y especificar que debería utilizar el índice existente. Los siguientes ejemplos ilustran el proceso: 

Paso 1: Creación de la Tabla 

 CREATE TABLE NEW_EMP2 

(  employee_id NUMBER(6)first_name  VARCHAR2(20),last_name   VARCHAR2(25) 

 ); 

Paso 2: Creación del Índice 

CREATE INDEX emp_id_idx2 ON            new_emp2(employee_id); 

Paso 3: Creación de la Clave Primaria 

ALTER TABLE new_emp2 ADD PRIMARY KEY  (employee_id) USING INDEX emp_id_idx2;

Índices Basados en Funciones

• Un índice basado en funciones se basa en expresiones.  
• La expresión del índice se genera a partir de columnas de tablas, constantes, funciones SQL y funciones definidas por el usuario. 

Los índices basados en funciones definidos con las palabras clave UPPER(column_name) o LOWER(column_name) permiten búsquedas no sensibles a mayúsculas/minúsculas. Por ejemplo, el siguiente índice:

CREATE INDEX upper_last_name_idx ON emp2 (UPPER(last_name));

facilita el procesamiento de consultas como:

SELECT * FROM emp2 WHERE UPPER(last_name) = 'KING';

Oracle Server utiliza el índice únicamente si esa función en particular se utiliza en una consulta. Por ejemplo, la siguiente sentencia puede utilizar el índice, pero sin la cláusula WHERE Oracle Server puede realizar una exploración de tablas completas:

SELECT   *  

FROM     employees  

WHERE    UPPER (last_name) IS NOT NULL

ORDER BY UPPER (last_name);

Nota: Se debe definir el parámetro de inicialización QUERY_REWRITE_ENABLED en TRUE para que se utilice un índice basado en funciones.

Oracle Server trata los índices con columnas marcadas como DESC como índices basados en funciones. Las columnas marcadas como DESC se clasifican en orden descendente.

Eliminación de un Índice 

Los índices no se pueden modificar. Para cambiar un índice, debe borrarlo y volverlo a crear. Para eliminar una definición de índice del diccionario de datos, emita la sentencia DROP INDEX. Para borrar un índice, debe ser el propietario del índice o tener el privilegio DROP ANY INDEX. 

En la sintaxis: 

index es el nombre del índice 

Nota: Si borra una tabla, los índices y las restricciones se borran automáticamente, pero las vistas y las secuencias permanecen.