Vistas, Secuencias, Índices y Sinónimos

Objetivos:

•Crear vistas simples y complejas.
•Recuperar datos de vistas.
•Crear, mantener y utilizar secuencias.
•Crear y mantener índices.
•Crear sinónimos privados y públicos.

NOTA: Usamos como ejemplo la base de datos: ORCL, la cual viene por defecto en cualquier versión de ORACLE.

_____________________________________________________________________________________
Objetos de Base de Datos.

Objeto	Descripción
Tabla	Unidad básica de almacenamiento; compuesta por filas.
Vista	Representa de forma lógica subjuegos de datos de una o más tablas.
Secuencia	Genera valores numéricos.
Índice	Mejora el rendimiento de algunas consultas.
Sinónimo	Proporciona nombres alternativos a objetos.

Además de las tablas, hay algunos otros objetos en una base de datos. En esta publicación, obtendrá información sobre vistas, secuencias, índices y sinónimos. 

Con las vistas, se pueden presentar y ocultar datos de las tablas. 

Muchas aplicaciones requieren el uso de números únicos como valores de clave primaria. Puede crear código en la aplicación para manejar este requisito o utilizar una secuencia para generar números únicos. 

Si desea mejorar el rendimiento de algunas consultas, se debería plantear la creación de un índice. También puede utilizar índices para forzar la unicidad en una columna o un conjunto de columnas. 

Puede proporcionar nombres alternativos para objetos mediante sinónimos.
_____________________________________________________________________________________
Las Vistas.
Puede presentar subjuegos lógicos o combinaciones de datos creando vistas o tablas. Una vista es una tabla lógica basada en una tabla u otra vista. Una vista no contiene datos de sí misma, sino que es como una ventana a través de la cual se pueden ver o cambiar datos de tablas. Las tablas en las que se basa una vista se denominan tablas base. La vista se almacena en una sentencia SELECT en el diccionario de datos.

Ventajas de las Vistas:

•Las vistas restringen el acceso a los datos porque pueden mostrar columnas seleccionadas de la tabla. 

•Las vistas se pueden utilizar para crear consultas simples que recuperen los resultados de consultas complejas. Por ejemplo, las vistas se pueden utilizar para consultar información de varias tablas sin necesidad de que el usuario sepa escribir una sentencia de unión. 

•Las vistas proporcionan independencia a los datos para usuarios ad hoc y programas de aplicaciones. Se puede utilizar una vista para recuperar datos de varias tablas. 

•Las vistas proporcionan a grupos de usuarios acceso a datos según los criterios particulares de cada uno.

Clasificando las Vistas:

Hay dos clasificaciones para las vistas: simples y complejas La diferencia básica tiene que ver con las operaciones DML (INSERT, UPDATE y DELETE). 

 •Una vista simple es la que:
-Deriva datos de una sola tabla.
-No contiene funciones ni grupos de datos.
-Puede realizar operaciones DML a través de la vista.
•Una vista compleja es la que:
-Deriva datos de muchas tablas.
-Contiene funciones o grupos de datos.
-No siempre permite operaciones DML a través de la vista.

Sintaxis:

CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW view_name 

 [(alias[, alias]...)]

 AS subquery

[WITH CHECK OPTION [CONSTRAINT constraint]]

[WITH READ ONLY [CONSTRAINT constraint]];

En la sintaxis:
OR REPLACE vuelve a crear la vista si ya existe.

FORCE crea la vista independientemente de si existe o no la tabla base.

NOFORCE crea la vista sólo si existe la tabla base (Es el valor por defecto).

view_name  es el nombre de la vista.

alias especifica nombres para las expresiones seleccionadas por la consulta de la vista (El número de alias debe corresponder con el número de expresiones seleccionadas por la vista).

subquery es una sentencia SELECT completa (Puede utilizar alias para las columnas de la lista SELECT).

WITH CHECK OPTION especifica que sólo se pueden insertar o actualizar las filas que estén accesibles para la vista.

constraint es el nombre asignado a la restricción CHECK OPTION.

WITH READ ONLY asegura que no se pueda realizar ninguna operación DML en esta vista.

Directrices:

•La subconsulta que define una vista puede contener una sintaxis SELECT compleja, incluidos uniones, grupos y subconsultas. 

•Si no especifica un nombre de restricción para una vista creada con WITH CHECK OPTION, el sistema asigna un nombre por defecto en el formato SYS_Cn. 

•Puede utilizar la opción OR REPLACE para cambiar la definición de la vista sin borrarla y volverla a crear ni volver a otorgar privilegios de objeto que se le hayan otorgado previamente.

•Puede controlar los nombres de columna mediante la inclusión de alias de columna en la subconsulta. Como alternativa, puede utilizar un alias después de la sentencia CREATE OR REPLACE VIEW view_name y antes de la subconsulta SELECT. El número de alias que se muestre debe corresponder con el número de expresiones seleccionadas en la subconsulta.

•Puede recuperar datos de una vista del mismo modo que de una tabla. Puede mostrar el contenido de toda la vista o sólo filas y columnas específicas.

Ejemplos:
-- Eje part 1

CREATE VIEW v_emp_dept_30 
AS SELECT employee_id, last_name, department_id, email, hire_date
FROM employees
WHERE department_id = 30;
-- Eje part 2
DESC v_emp_dept_30; 
-- Eje part 3
CREATE OR REPLACE VIEW v_emp_dept_30 
(codigo, apellido, departamento,correo, contrato)
AS SELECT employee_id, last_name, department_id, email, hire_date
FROM employees
WHERE department_id = 30;
-- Eje part 4
DESC  v_emp_dept_30; 
---
SELECT *
FROM  v_emp_dept_30;
/*En Eje 1 creamos una vista simple con algunos campos de la tabla employees, dicha vista solo muestra los empleados con department_id = 30; en Eje part 2 vemos la estructura de la vista, notar que los campos tienen los mismo nombres que los del SELECT y que luce exactamente como una tabla; en Eje part 3 re-creamos la misma vista pero ahora estableciendo nombres distintos antes de la sentencia SELECT, cabe destacar que es posible crear una vista desde cero con la sentencia CREATE OR REPLACE VIEW; luego en Eje 4 notamos la diferencia de la misma vista*/
---

CREATE OR REPLACE VIEW v_max_avg_min_sal

AS SELECT D.department_name AS DEPARTAMENTO, MAX(E.salary) AS MAYOR, ROUND(AVG(E.salary),2) AS PROMEDIO, MIN(E.salary) AS MENOR

FROM employees E, departments D

WHERE E.department_id = D.department_id

GROUP BY D.department_name;

---

DESC v_max_avg_min_sal;

---

SELECT *

FROM v_max_avg_min_sal;

_____________________________________________________________________________________
Operaciones DML en Vistas.
Es prudente recalcar que las vistas no contienen datos, las vistas son solo un SELECT almacenado en la base de datos el cual nos ayuda en la mayoría de los casos a agilizar el trabajo cuando se trata de crear consultas complejas y que se usan con frecuencia, así como también sirven como espejo para algunos usuario que por su desempeño o posición en la empresa/negocio no deben o no necesitan ver ciertos campos de X tabla.

Ahora bien, aun entendiendo que las vistas no contienen datos, es posible realizar operaciones DML en datos a través de dichas vistas si estas siguen ciertas reglas.

Se puede eliminar una fila de una vista a no ser que contenga alguno de los siguientes elementos:

•Funciones de grupo.

•Una cláusula GROUP BY.

•La palabra clave DISTINCT.

•La palabra clave ROWNUM de pseudocolumna.

No se pueden modificar los datos de una vista si contiene alguno de los elemento ya mencionados o si:
•Contiene columnas definidas por expresiones.

No se pueden agregar datos a través de una vista si la vista incluye alguno se los elementos ya expuesto o si contiene:
•Columnas NOT NULL sin valores por defecto(DEFAULT) en las tablas base que no están seleccionadas por la vista.

Ejemplos:
INSERT INTO v_emp_dept_30 
VALUES(800,'Papolo',30,'papo@',SYSDATE);
---
INSERT INTO v_emp_dept_30 
VALUES(801,'Julio',80,'jul@',SYSDATE);
---
UPDATE v_emp_dept_30
SET apellido= 'Jose'
WHERE codigo = 800;
---
DELETE FROM v_emp_dept_30
WHERE codigo = 800;

/*Como vemos la vista: v_emp_dept_30 creada en un ejemplo anterior, puede ser usada para insertar/eliminar/actualizar en la tabla employees, esto debido a que contiene todas las columnas NOT NULL de la tabla base, si en dicha vista faltara algún campo NOT NULL de la tabla base no seria posible insertar pero si seria posible eliminar un registro o modificar algún campo de la misma.*/

---

/*En la vista: v_max_avg_min_sal no es posible realizar ninguna operación DML debido a las funciones de grupo que en ella se usan.*/
_____________________________________________________________________________________

Uso de la Cláusula WITH CHECK OPTION

Es posible realizar comprobaciones de integridad referencial a través de las vistas. Puede forzar restricciones en el nivel de base de datos. La vista se puede utilizar para proteger la integridad de los datos, pero el uso está muy limitado. 

La cláusula WITH CHECK OPTION especifica que las inserciones (INSERT) y las actualizaciones (UPDATE) realizadas a través de la vista no se pueden seleccionar, con lo que permite que las restricciones de integridad y las comprobaciones de validación de datos se fuercen en los datos que se estén insertando o actualizando. Si intenta realizar operaciones DML en filas que no haya seleccionado la vista, se mostrará un error, junto con el nombre de restricción si se ha especificado. 

Nota: No se actualiza ninguna fila porque, si el número de departamento se tuviera que cambiar a 10, la vista ya no sería capaz de ver ese empleado. Con la cláusula WITH CHECK OPTION, por lo tanto, la vista puede ver únicamente a los empleados del departamento 20 y no permite que se cambie el número de departamento para esos empleados a través de la vista.

Ejemplos:

UPDATE v_emp_dept_30

SET departamento = 80;

/*Aquí cambiamos el departamento de 30 a 80 a todos los empleados del departamento 30, si luego consultamos la tabla notaran que no habrá ni un solo registro*/

---

ROLLBACK;

/*Aquí deshacemos los cambios para tener todos los empleados de vuelta en el departamento 30*/

---

CREATE OR REPLACE VIEW v_emp_dept_30 

(codigo, apellido, departamento,correo, contrato)

AS SELECT employee_id, last_name, department_id, email, hire_date

FROM employees

WHERE department_id = 30

WITH CHECK OPTION CONSTRAINT ck_v_dept_30;

/*Modificamos o recreamos la vista para así incluir el CONSTRAINT WITH CHECK OPTION y así no permitir modificaciones o inserciones fuera del alcance de la vista; luego de modificar la vista de esta manera, el último UPDATE realizado a través de la vista retornaría un error.*/
_____________________________________________________________________________________
Denegación de Operaciones DML.
Si no se quiere permitir ningún tipo de operaciones DML a través de la vista, entonces debe crearse con la opción WITH READ ONLY.

Ejemplo:

CREATE OR REPLACE VIEW v_emp_dept_30 

(codigo, apellido, departamento,correo, contrato)

AS SELECT employee_id, last_name, department_id, email, hire_date

FROM employees

WHERE department_id = 30

WITH READ ONLY CONSTRAINT read_v_dept_30;
/*Luego de este ejemplo no seria posible hacer ningún tipo de modificación de datos a través de esta vista.*/
_____________________________________________________________________________________
Eliminación de Vistas.
Puede utilizar la sentencia DROP VIEW para eliminar una vista. La sentencia elimina la definición de vista de la base de datos. Borrar vistas no afecta a las tablas en las que se base la vista. Las vistas u otras aplicaciones basadas en vistas suprimidas se convierten en no válidas. Sólo puede eliminar una vista su creador o un usuario con el privilegio DROP ANY VIEW.

Sintaxis:

DROP VIEW view; 

Ejemplo:

DROP VIEW v_emp_dept_30;

_____________________________________________________________________________________

Secuencias.
Una secuencia es un objeto de base de datos creado por el usuario que pueden compartir varios usuarios para generar enteros.

Puede definir una secuencia para generar valores únicos o para reciclar y volver a utilizar los números.

Un uso habitual para las secuencias es la creación de un valor de clave primario, que debe ser único para cada fila. La secuencia se genera y se incrementa (o disminuye) en una rutina interna de Oracle. Puede servir para ahorrar tiempo, ya que reduce la cantidad de código de aplicación necesario para escribir una secuencia de generación de secuencias.

Los números de secuencias se almacenan y se generan independientemente de las tablas. Por lo tanto, se puede utilizar la misma secuencia para varias tablas.

Una secuencia:
•Puede generar automáticamente números únicos.
•Es un objeto compartible.
•Se puede utilizar para crear un valor de clave primaria.
•Sustituye al código de la aplicación.
•Acelera la eficacia de acceso a valores de secuencia cuando se almacenan en caché en la memoria.

Sintaxis:

CREATE SEQUENCE sequence 

    [INCREMENT BY n] 

    [START WITH n] 

    [{MAXVALUE n | NOMAXVALUE}] 

    [{MINVALUE n | NOMINVALUE}] 

    [{CYCLE | NOCYCLE}] 

    [{CACHE n | NOCACHE}]; 

Definición:
sequence es el nombre del generador de secuencias.
INCREMENT BY n especifica el intervalo entre números de secuencia, donde n es un entero (Si se omite esta cláusula, la secuencia se incrementa en 1).
START WITH n especifica el primer número de secuencia que se generará (Si se omite esta cláusula, la secuencia empieza en 1).
MAXVALUE n especifica el valor máximo que puede generar la secuencia.
NOMAXVALUE especifica un valor máximo de 10^27 para una secuencia ascendente y de –1 para una descendente (Ésta es la opción por defecto).
MINVALUE n especifica el valor mínimo de secuencia.
NOMINVALUE especifica un valor mínimo de 1 para una secuencia ascendente y de –(10^26) para una descendente (Ésta es la opción por defecto).
CYCLE | NOCYCLE especifica si la secuencia continúa generando valores después de alcanzar sus valores máximo o mínimo (NOCYCLE es la opción por defecto).
CACHE n | NOCACHE especifica cuántos valores preasigna y mantiene en memoria Oracle Server. El CACHE hace que la generación de números sea mas rápida.(Por defecto, Oracle Server almacena en caché 20 valores).

Ejemplo:
CREATE SEQUENCE random_seq
START WITH 1500
INCREMENT BY 1
CACHE 10;

/*Acá creamos la secuencia random_seq la cual inicia en 1500, incrementa de 1 en 1, no tiene valor máximo, por lo tanto no es circular y tiene un CACHE de 10 en memoria.*/

---
CREATE SEQUENCE round_seq
INCREMENT BY 1
MAXVALUE 5
CYCLE
NOCACHE;

/*Acá creamos la secuencia round_seq la cual inicia en 1, incrementa de 1 en 1, tiene un valor máximo de 5, es circular y no tiene CACHE en memoria.*/

---

Pseudocolumnas NEXTVAL y CURRVAL.

Después de crear la secuencia, ésta genera números secuenciales para su uso en las tablas o en cualquier otra tarea que requiera de estas. Haga referencia a los valores de secuencia mediante las pseudocolumnas NEXTVAL y CURRVAL. 

•NEXTVAL devuelve el siguiente valor de secuencia disponible. Devuelve un valor único cada vez que se hace referencia al mismo, incluso para usuarios distintos. Debe cualificar NEXTVAL con el nombre de secuencia. Al hacer referencia a sequence.NEXTVAL, se genera un nuevo número de secuencia y el número de secuencia actual se coloca en CURRVAL. 

La pseudocolumna CURRVAL se utiliza para hacer referencia a un número de secuencia que el usuario actual acaba de generar. Se debe utilizar NEXTVAL para generar un número de secuencia en la sesión del usuario actual antes de que se pueda hacer referencia a CURRVAL. Debe cualificar CURRVAL con el nombre de secuencia. Al hacer referencia a sequence.CURRVAL, se muestra el último valor devuelto al proceso de ese usuario.

Reglas para Utilizar NEXTVAL y CURRVAL.
Puede utilizar NEXTVAL y CURRVAL en los siguientes contextos:
•Lista SELECT de una sentencia SELECT que no forma parte de una subconsulta.
•Lista SELECT de una subconsulta en una sentencia INSERT.
•Cláusula VALUES de una sentencia INSERT.
•Cláusula SET de una sentencia UPDATE.

No puede utilizar NEXTVAL y CURRVAL en los siguientes contextos:
•Lista SELECT de una vista.
•Sentencia SELECT con la palabra clave DISTINCT.
•Sentencia SELECT con las cláusulas GROUP BY, HAVING u ORDER BY.
•En la cláusula WHERE de cualquier Sentencia SQL.
•Subconsulta en una sentencia SELECT, DELETE o UPDATE.
•Expresión DEFAULT en una sentencia CREATE TABLE o ALTER TABLE.

Ejemplos:
INSERT INTO departments(department_id,department_name,manager_id)
VALUES(round_seq.NEXTVAL, 'Department Round_seq', 100);
/*Acá insertamos un nuevo departamento con el código generado por la secuencia: round_seq.*/

---

INSERT INTO employees(employee_id, last_name, email, hire_date)
VALUES(random_seq.NEXTVAL,'Lalo', 'lalo@', SYSDATE);
/*Acá insertamos un nuevo empleado con el código generado por la secuencia: random_seq.*/
---
UPDATE employees
SET department_id = round_seq.CURRVAL 
WHERE employee_id = 1500;

/*Acá usamos la pseudocolumna CURRVAL de las sentencias de los ejemplos anteriores; Recordar que CURRVAL o NEXTVAL no pueden ser usados en la cláusula WHERE.*/

---

SELECT round_seq.CURRVAL, random_seq.CURRVAL

FROM dual;

/*Acá vemos los números secuenciales usados en los SCRIPT anteriores.*/

---

SELECT round_seq.NEXTVAL, random_seq.NEXTVAL

FROM dual;

/*Ejecutar esta consulta mas de 5 veces para así ver como se comportan debido al CYCLE de una y el NOCYCLE de la otra.*/

---

Caché de Valores de Secuencia.

Puede almacenar en caché secuencias de la memoria para proporcionar un acceso más rápido a esos valores de secuencia. El caché se rellena la primera ver que hace referencia a la secuencia. Cada solicitud del siguiente valor de secuencia se recupera de la secuencia almacenada en caché. Una vez utilizado el último valor de secuencia, la siguiente solicitud de secuencia obtendrá otra caché de secuencias en la memoria. 

Intervalos en la Secuencia.

•Se pueden producir intervalos en los valores de secuencia cuando:
–Se produce un rollback.
–Se produce un error en el sistema.
–Se utiliza una secuencia en mas de una tabla.

Aunque los generadores de secuencias emiten números secuenciales sin intervalos, esta acción se produce independientemente de una validación o un rollback. Por lo tanto, si hace rollback de una sentencia que contiene una sentencia, se perderá el número. 

Otro evento que puede provocar intervalos en la secuencia es un error del sistema. Si la secuencia almacena en caché valores en la memoria, esos valores se pierden si hay un error del sistema. 

Como las secuencias no están vinculadas directamente a tablas, se puede utilizar la misma secuencia para varias tablas. Si lo hace, cada tabla puede contener intervalos en los números secuenciales.

Modificación de Secuencias.
Si la secuencia alcanza el límite(MAXVALUE) y es una secuencia NOCYCLE, no se asigna ningún valor adicional y recibirá un error que indicará que la secuencia excede el valor MAXVALUE. Para continuar utilizando la secuencia, la puede modificar mediante la sentencia ALTER SEQUENCE.

Sintaxis:

ALTER SEQUENCE sequence 

    [INCREMENT BY n]

    [{MAXVALUE n | NOMAXVALUE}]

    [{MINVALUE n | NOMINVALUE}]

    [{CYCLE | NOCYCLE}]

    [{CACHE n | NOCACHE}];

Ejemplo:
ALTER SEQUENCE round_seq
INCREMENT BY 1
MAXVALUE 35
NOCYCLE
CACHE 15;

/*Ahora modificamos la secuencia: round_seq para poner un limite máximo de 35, que no sea circular y que tenga un CACHE de 15, luego esta modificación si tratamos de generar un numero mas después de 35 recibiríamos un error.*/

---

Instrucciones para Modificar una Secuencia.

•Debe ser el propietario de la secuencia o tener el privilegio ALTER para modificarla.

•Sólo los números de secuencia futuros se ven afectados por la sentencia ALTER SEQUENCE.

•La opción START WITH no se puede cambiar mediante ALTER SEQUENCE. Se debe borrar la secuencia y volver a crearla para reiniciarla en un número diferente. 

•Se llevan a cabo algunas validaciones. Por ejemplo, no se puede imponer un nuevo valor MAXVALUE que sea menor que el número actual de secuencia.

•Debe ser el propietario de la secuencia o tener el privilegio DROP ANY SEQUENCE para eliminarla. Para eliminar una secuencia, utilice la sentencia DROP SEQUENCE:

Ejemplo: DROP SEQUENCE round_seq;

_____________________________________________________________________________________

Indices.

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 Indice:

•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.

Detalles:

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.

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. 

Sintaxis:

CREATE [UNIQUE] INDEX index_name

ON table_name (column[, column] ...)

[ COMPUTE STATISTICS ];

En la sintaxis: 

UNIQUE indica que la combinación de valores en las columnas indexadas debe ser única.
index_name  es el nombre del índice.
table_name  es el nombre de la tabla.
column es el nombre de la columna de la tabla que se va a indexar.

COMPUTE STATISTICS hace que Oracle Server colecte estadísticas durante la creación del index. Luego el optimizador usa estas estadísticas para escoger el mejor plan de ejecución cuando se ejecuten sentencias SQL. 

Ejemplo:

CREATE INDEX emp_apell_indx 

ON employees(last_name);

/*En este ejemplo creamos un indice para la columna last_name de la tabla employees.*/

---

CREATE TABLE test_tab

(   c_1 NUMBER(3),

    c_2 NUMBER(3),

    c_3 NUMBER(3)

);

/*Acá creamos una tabla de prueba.*/

---

CREATE UNIQUE INDEX tab_test_indx

ON test_tab(c_1,c_2,c_3)

COMPUTE STATISTICS;

/*Creamos un indice compuesto de las 3 columnas de nuestra tabla de prueba; dicho INDEX no permite la misma combinación(en el mismo orden) de valores en las 3 columnas(UNIQUE); También especificamos el COMPUTE STATISTICS para que Oracle Server desarrolle una estrategia de ejecución.*/

---

INSERT INTO test_tab

VALUES(1,1,0);

/*Debido al indice: tab_test_indx anteriormente creado, no seria posible insertar la combinación (1,1,0) otra vez, pero si podemos insertar (0,1,1) o (1,0,1), claro: solo una vez.*/

---

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

Directrices para la Creación de Índices.

Cree un índice si:
R	Una columna contiene un amplio rango de valores.
R	Una columna contiene un gran número de valores nulos.
R	Se utilizan frecuentemente una o más columnas en una cláusula WHERE o en una condición de unión.
R	La tabla es grande y se espera que la mayoría de las consultas recupere menos del 2 % al 4 % de las filas de la tabla.
No cree un índice si:
S	Las columnas no se utilizan a menudo como condición en la consulta.
S	La tabla es pequeña y se espera que la mayoría de las consultas recupere más del 2 % al 4 % de las filas de la tabla.
S	La tabla se actualiza con frecuencia.
S	Se hace referencia a las columnas indexadas como parte de una expresión.

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. 

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.

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.

Sintaxis:

DROP INDEX index;

Ejemplo:

DROP INDEX tab_test_indx;

/*Eliminamos nuestro indice.*/

---

DROP TABLE test_tab;

/*Eliminamos la tabla; Si eliminábamos la tabla primero, el indice también se removía.*/

---

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

_____________________________________________________________________________________

Sinónimos.

Los sinónimos son objetos de base de datos que le permiten denominar un objeto X con otro nombre. Puede crear sinónimos para poner un nombre alternativo a una tabla, vista, secuencias, procedimientos, funciones y otros objectos.

Para hacer referencia a una tabla propiedad de otro usuario, debe poner como prefijo al nombre de tabla el nombre del usuario que la creó, seguido de un punto. Al crear un sinónimo, se elimina la necesidad de cualificar el nombre de objeto con el esquema y le proporciona un nombre alternativo para una tabla, una vista, una secuencia, un procedimiento u otros objetos. Este método puede resultar especialmente útil con nombres de objeto largos, como las vistas. 

Sintaxis: 

CREATE [OR REPLACE] [PUBLIC] SYNONYM [schema.]synonym_name

FOR [schema.]object_name[@dblink];

En la sintaxis:

OR REPLACE Crea nuevamente el sinónimo (si ya existe) sin tener que eliminarlo.

schema es el esquema al cual te refieres, si se omite, Oracle Server asume tu propio esquema.

PUBLIC crea un sinónimo que es accesible para todos los usuarios.

synonym_name es el nombre del sinónimo que se va a crear.

object_name identifica el objeto para el que se crea el sinónimo.

@dblink es un enlace con otra base de datos.

Directrices:

•El objeto no se puede contener en un paquete. 

•Un sinónimo privado debe ser distinto a todos los demás objetos propiedad del mismo usuario.

Ejemplo:
CREATE SYNONYM dept 
FOR departments;
---
SELECT *
FROM dept;
/*Acá creamos y luego usamos un sinónimo para la tabla: departments.*/
---
CREATE OR REPLACE SYNONYM add_jh

    FOR add_job_history;
---
DESC add_jh;
/*Acá creamos un sinónimo para el procedimiento: add_job_history y luego mostramos su definición(definición del procedimiento en si).*/
---
Eliminación de Sinónimos.

Para eliminar un sinónimo, utilice la sentencia DROP SYNONYM. 

Ejemplo:
DROP SYNONYM dept;

Nota: El administrador de la base de datos es el único que puede borrar un sinónimo público. 

_____________________________________________________________________________________

Link con un Archivo de ejercicios de práctica!!!

_____________________________________________________________________________________

Fuente: Base de Datos Oracle 10g: Conceptos Fundamentales de SQL 1