Módulo 30: Vistas, índices y otros objetos
Objetivos del módulo
- Comprender qué es una vista, para qué sirve (seguridad, simplificación, independencia lógica) y cómo se crea, modifica y borra
- Distinguir vistas simples y complejas, y conocer las reglas que hacen que una vista sea actualizable
- Usar
WITH CHECK OPTIONyWITH READ ONLY, y consultarUSER_UPDATABLE_COLUMNS - Crear vistas materializadas y conocer sus modos de refresco
- Entender cómo funciona un índice B-tree y cuándo conviene (y cuándo no) crear índices
- Crear índices únicos, compuestos, basados en función, bitmap e invisibles
- Leer un plan de ejecución con
EXPLAIN PLANyDBMS_XPLAN.DISPLAY - Crear y usar secuencias con todas sus opciones, y compararlas con las columnas identidad
- Crear sinónimos privados y públicos
- Explorar el diccionario de datos (
USER_,ALL_,DBA_,V$) y escribir consultas para documentar un esquema
1. Los objetos de un esquema
Hasta ahora el único objeto que hemos creado son las tablas, que guardan los datos. Pero un esquema de Oracle contiene muchos otros tipos de objetos que no almacenan datos nuevos, sino que ayudan a consultarlos, protegerlos, acelerarlos o generarlos:
| Objeto | Qué es | Analogía |
|---|---|---|
| Tabla | Almacena las filas | El archivador con las fichas |
| Vista | Consulta guardada con nombre que se usa como una tabla | Una ventana que solo deja ver parte del archivador |
| Vista materializada | Consulta cuyo resultado se guarda físicamente y se refresca | Una fotocopia del informe que se actualiza cada cierto tiempo |
| Índice | Estructura auxiliar para encontrar filas rápidamente | El índice alfabético al final de un libro |
| Secuencia | Generador de números únicos | La máquina de “coja su número” de la carnicería |
| Sinónimo | Nombre alternativo para otro objeto | Un acceso directo del escritorio |
⚠️ Importante: Igual que en el módulo 25, las salidas se han obtenido en Oracle AI Database 26ai Free con SQLPlus. Los mensajes de SQLPlus se muestran en español y los errores
ORA-en inglés (la edición Free solo trae ese idioma). Omitimos la líneaHelp: https://docs.oracle.com/...que 23ai añade tras cada error.CURSOes el usuario en el que hemos cargado EMPRESA para las pruebas. La sesión usaNLS_LANGUAGE = 'SPANISH',NLS_TERRITORY = 'SPAIN'yNLS_DATE_FORMAT = 'DD/MM/YYYY', y los nulos se muestran como(nulo)conSET NULL '(nulo)'.
2. Vistas: concepto y usos
📘 Concepto: Una vista (view) es una tabla virtual definida mediante una consulta
SELECT. La vista no almacena filas: Oracle guarda en el diccionario solo el texto de la consulta, y cada vez que alguien consulta la vista, ejecuta esa consulta sobre las tablas base (base tables). En la terminología de la arquitectura ANSI/SPARC (módulo 03), las vistas implementan el nivel externo: cada usuario o aplicación ve los datos como le conviene.
¿Para qué sirven?
- Seguridad: mostrar solo algunas columnas (ocultar el salario) o algunas filas (solo los empleados de mi departamento). Se concede permiso sobre la vista y no sobre la tabla (módulo 37).
- Simplificar consultas: una combinación de cinco tablas con agrupaciones se escribe una vez y se consulta como
SELECT * FROM v_informe. - Independencia lógica: si la estructura de las tablas cambia (por ejemplo, se divide una tabla en dos), se redefine la vista y las aplicaciones que la usan no se enteran.
- Coherencia: todos los informes calculan “el importe de una línea” de la misma forma, porque la fórmula está en un único sitio.
- Nombres más claros: renombrar columnas con alias significativos para usuarios finales.
2.1. Sintaxis
CREATE [OR REPLACE] [FORCE | NOFORCE] VIEW [esquema.]nombre_vista
[(alias_columna1, alias_columna2, ...)]
AS
consulta_select
[WITH CHECK OPTION [CONSTRAINT nombre]]
[WITH READ ONLY [CONSTRAINT nombre]];
| Cláusula | Significado |
|---|---|
OR REPLACE | Si la vista existe, la sustituye (conservando los privilegios concedidos sobre ella) |
FORCE | Crea la vista aunque las tablas base no existan o haya errores (queda inválida) |
NOFORCE | Por defecto: solo la crea si la consulta es correcta |
(alias...) | Nombres de las columnas de la vista |
WITH CHECK OPTION | Impide insertar o modificar filas que luego no serían visibles en la vista |
WITH READ ONLY | La vista no admite INSERT, UPDATE ni DELETE |
2.2. Vista simple: seguridad por columnas
Una vista simple se basa en una sola tabla, no tiene funciones de grupo ni DISTINCT, y sus columnas son columnas de la tabla. Esta oculta el salario, la comisión y la jerarquía:
CREATE VIEW v_empleados_publico AS
SELECT emp_id, nombre, apellidos, email, telefono, dept_id
FROM empleados;
SELECT * FROM v_empleados_publico WHERE dept_id = 40;
Vista creada.
EMP_ID NOMBRE APELLIDOS EMAIL TELEFONO DEPT_ID
---------- ---------- ------------------ ---------------------------- --------------- ----------
103 Pedro Gómez Díaz pedro.gomez@empresa.es 600100103 40
130 Isabel Domínguez Vega isabel.dominguez@empresa.es 600100130 40
131 Jorge Vázquez Ramos jorge.vazquez@empresa.es (nulo) 40
3 filas seleccionadas.
La vista se usa exactamente igual que una tabla: admite WHERE, ORDER BY, combinaciones con otras tablas y vistas, y DESC v_empleados_publico muestra sus columnas.
2.3. Vista compleja con alias de columnas
Una vista compleja usa varias tablas, funciones de grupo, GROUP BY, DISTINCT o expresiones. Los nombres de las columnas pueden darse en la cabecera de la vista:
CREATE VIEW v_resumen_dept
(dept_id, departamento, localidad, num_empleados, masa_salarial, salario_medio) AS
SELECT d.dept_id, d.nombre, d.localidad,
COUNT(e.emp_id), NVL(SUM(e.salario), 0), ROUND(AVG(e.salario), 2)
FROM departamentos d
LEFT JOIN empleados e ON e.dept_id = d.dept_id
GROUP BY d.dept_id, d.nombre, d.localidad;
SELECT * FROM v_resumen_dept ORDER BY dept_id;
Vista creada.
DEPT_ID DEPARTAMENTO LOCALIDAD NUM_EMPLEADOS MASA_SALARIAL SALARIO_MEDIO
---------- -------------- ---------- ------------- ------------- -------------
10 DIRECCIÓN Madrid 1 6500 6500
20 VENTAS Madrid 6 15000 2500
30 INFORMÁTICA Valencia 6 18800 3133,33
40 CONTABILIDAD Sevilla 3 8450 2816,67
50 MARKETING Barcelona 3 9300 3100
60 LOGÍSTICA Zaragoza 4 8950 2237,5
70 INVESTIGACIÓN Bilbao 0 0 (nulo)
7 filas seleccionadas.
El departamento 70 aparece con 0 empleados gracias al LEFT JOIN (módulo 30); su salario medio es NULL porque AVG de ningún valor es NULL.
Toda columna de la vista necesita un nombre. Si una expresión no tiene alias (ni en la cabecera ni en el SELECT), Oracle se niega:
CREATE VIEW v_mal AS SELECT emp_id, salario * 14 FROM empleados;
CREATE VIEW v_salario_anual AS SELECT emp_id, nombre, salario * 14 AS salario_anual FROM empleados;
CREATE VIEW v_mal AS SELECT emp_id, salario * 14 FROM empleados
*
ERROR en línea 1:
ORA-00998: must name this expression with a column alias
Vista creada.
2.4. Las vistas en el diccionario
El texto de cada vista se guarda en USER_VIEWS:
SELECT view_name, text_length FROM user_views ORDER BY view_name;
SET LONG 400
SELECT text FROM user_views WHERE view_name = 'V_SALARIO_ANUAL';
VIEW_NAME TEXT_LENGTH
---------------------- -----------
V_EMPLEADOS_PUBLICO 75
V_RESUMEN_DEPT 232
V_SALARIO_ANUAL 67
3 filas seleccionadas.
TEXT
------------------------------------------------------------
SELECT emp_id, nombre, salario * 14 AS salario_anual FROM em
pleados
1 fila seleccionada.
💡 Consejo: La columna
TEXTes de tipoLONG; en SQL*Plus,SET LONG nindica cuántos caracteres mostrar (por defecto 80).
2.5. CREATE OR REPLACE
Para cambiar una vista no existe ALTER VIEW ... AS: se vuelve a crear. Sin OR REPLACE, Oracle protesta porque el nombre ya existe:
CREATE VIEW v_salario_anual AS
SELECT emp_id, nombre, oficio, salario * 14 AS salario_anual FROM empleados;
CREATE OR REPLACE VIEW v_salario_anual AS
SELECT emp_id, nombre, oficio, salario * 14 AS salario_anual FROM empleados;
CREATE VIEW v_salario_anual AS SELECT emp_id, nombre, oficio, salario * 14 AS salario_anual FROM empleados
*
ERROR en línea 1:
ORA-00955: name is already used by an existing object
Vista creada.
📘 Concepto:
CREATE OR REPLACEes preferible aDROP+CREATEporque conserva los privilegios concedidos sobre la vista y no invalida innecesariamente los objetos que dependen de ella.ALTER VIEWexiste, pero solo para recompilar (COMPILE) o gestionar restricciones de la vista.
2.6. FORCE: vistas sobre tablas que aún no existen
CREATE VIEW v_bonus AS SELECT emp_id, importe FROM bonus;
CREATE FORCE VIEW v_bonus AS SELECT emp_id, importe FROM bonus;
SELECT object_name, object_type, status FROM user_objects WHERE object_name = 'V_BONUS';
SELECT * FROM v_bonus;
CREATE VIEW v_bonus AS SELECT emp_id, importe FROM bonus
*
ERROR en línea 1:
ORA-00942: table or view "CURSO"."BONUS" does not exist
Advertencia: Vista creada con errores de compilación.
OBJECT_NAME OBJECT_TYP STATUS
---------------------- ---------- -------
V_BONUS VIEW INVALID
1 fila seleccionada.
SELECT * FROM v_bonus
*
ERROR en línea 1:
ORA-04063: view "CURSO.V_BONUS" has errors
Cuando la tabla aparece, la vista se recompila automáticamente la primera vez que se usa:
CREATE TABLE bonus (emp_id NUMBER(6), importe NUMBER(8,2));
INSERT INTO bonus VALUES (110, 300);
COMMIT;
SELECT * FROM v_bonus;
SELECT object_name, object_type, status FROM user_objects WHERE object_name = 'V_BONUS';
Tabla creada.
1 fila creada.
Confirmación terminada.
EMP_ID IMPORTE
---------- ----------
110 300
1 fila seleccionada.
OBJECT_NAME OBJECT_TYP STATUS
---------------------- ---------- -------
V_BONUS VIEW VALID
1 fila seleccionada.
FORCE es útil en scripts de instalación en los que el orden de creación no está garantizado.
2.7. Dependencias: cuando la tabla base cambia
Una vista depende de sus tablas base. Si se modifica una tabla de forma que la consulta deja de ser válida (aquí renombramos la columna importe), la vista queda inválida:
ALTER TABLE bonus RENAME COLUMN importe TO cantidad;
SELECT object_name, object_type, status FROM user_objects WHERE object_name = 'V_BONUS';
SELECT * FROM v_bonus;
ALTER VIEW v_bonus COMPILE;
Tabla modificada.
OBJECT_NAME OBJECT_TYP STATUS
---------------------- ---------- -------
V_BONUS VIEW INVALID
1 fila seleccionada.
SELECT * FROM v_bonus
*
ERROR en línea 1:
ORA-04063: view "CURSO.V_BONUS" has errors
Advertencia: Vista modificada con errores de compilación.
La solución es redefinir la vista con CREATE OR REPLACE usando el nuevo nombre de columna. Borramos los objetos de prueba:
DROP TABLE bonus PURGE;
DROP VIEW v_bonus;
Tabla borrada.
Vista borrada.
⚠️ Importante: Una vista definida con
SELECT *“congela” la lista de columnas en el momento de crearla: si después se añade una columna a la tabla, la vista no la muestra hasta que se vuelve a crear. Escribe siempre la lista explícita de columnas en las vistas.
3. Vistas actualizables
¿Se pueden hacer INSERT, UPDATE y DELETE sobre una vista? A veces sí: Oracle traduce la operación a la tabla base. La condición general es que cada fila de la vista se corresponda sin ambigüedad con una fila de una tabla base.
3.1. Operaciones a través de una vista simple
CREATE VIEW v_emp_ventas AS
SELECT emp_id, nombre, apellidos, email, fecha_alta, oficio, salario, dept_id
FROM empleados
WHERE dept_id = 20;
UPDATE v_emp_ventas SET salario = salario * 1.05 WHERE emp_id = 113;
Vista creada.
1 fila actualizada.
El UPDATE modifica la tabla empleados. Ahora fíjate en esto:
UPDATE v_emp_ventas SET dept_id = 30 WHERE emp_id = 114;
SELECT emp_id, nombre FROM v_emp_ventas WHERE emp_id = 114;
SELECT emp_id, nombre, dept_id FROM empleados WHERE emp_id = 114;
INSERT INTO v_emp_ventas
VALUES (170, 'Olga', 'Rey Sanz', 'olga.rey@empresa.es', DATE '2026-09-01', 'VENDEDOR', 2000, 50);
SELECT COUNT(*) FROM v_emp_ventas WHERE emp_id = 170;
ROLLBACK;
1 fila actualizada.
ninguna fila seleccionada
EMP_ID NOMBRE DEPT_ID
---------- ---------- ----------
114 Lucía 30
1 fila seleccionada.
1 fila creada.
COUNT(*)
----------
0
1 fila seleccionada.
Rollback terminado.
Lucía ha “desaparecido” de la vista (ahora es del 30) y Olga se ha insertado a través de la vista de Ventas… en el departamento de Marketing, así que tampoco la vemos. Son filas migratorias: un usuario que solo tuviera acceso a la vista podría sacar filas de su ámbito o meter filas en ámbitos ajenos.
3.2. WITH CHECK OPTION
WITH CHECK OPTION impide cualquier INSERT o UPDATE que produzca una fila que no cumpla el WHERE de la vista:
CREATE OR REPLACE VIEW v_emp_ventas AS
SELECT emp_id, nombre, apellidos, email, fecha_alta, oficio, salario, dept_id
FROM empleados
WHERE dept_id = 20
WITH CHECK OPTION CONSTRAINT ck_v_emp_ventas;
UPDATE v_emp_ventas SET dept_id = 30 WHERE emp_id = 114;
INSERT INTO v_emp_ventas
VALUES (170, 'Olga', 'Rey Sanz', 'olga.rey@empresa.es', DATE '2026-09-01', 'VENDEDOR', 2000, 50);
INSERT INTO v_emp_ventas
VALUES (170, 'Olga', 'Rey Sanz', 'olga.rey@empresa.es', DATE '2026-09-01', 'VENDEDOR', 2000, 20);
ROLLBACK;
Vista creada.
UPDATE v_emp_ventas SET dept_id = 30 WHERE emp_id = 114
*
ERROR en línea 1:
ORA-01402: view WITH CHECK OPTION where-clause violation
INSERT INTO v_emp_ventas VALUES (170, 'Olga', 'Rey Sanz', 'olga.rey@empresa.es', DATE '2026-09-01', 'VENDEDOR', 2000, 50)
*
ERROR en línea 1:
ORA-01402: view WITH CHECK OPTION where-clause violation
1 fila creada.
Rollback terminado.
La opción se registra como una restricción de tipo V asociada a la vista:
SELECT constraint_name, constraint_type, table_name
FROM user_constraints
WHERE table_name = 'V_EMP_VENTAS';
CONSTRAINT_NAME C TABLE_NAME
---------------------- - ----------------------
CK_V_EMP_VENTAS V V_EMP_VENTAS
1 fila seleccionada.
3.3. WITH READ ONLY
CREATE VIEW v_departamentos_ro AS
SELECT dept_id, nombre, localidad FROM departamentos
WITH READ ONLY;
UPDATE v_departamentos_ro SET localidad = 'Getafe' WHERE dept_id = 10;
SELECT constraint_name, constraint_type, table_name
FROM user_constraints
WHERE table_name = 'V_DEPARTAMENTOS_RO';
Vista creada.
UPDATE v_departamentos_ro SET localidad = 'Getafe' WHERE dept_id = 10
*
ERROR en línea 1:
ORA-42399: cannot perform a DML operation on a read-only view
CONSTRAINT_NAME C TABLE_NAME
---------------------- - ----------------------
SYS_C0010632 O V_DEPARTAMENTOS_RO
1 fila seleccionada.
Se registra como restricción de tipo O (read Only). Es la opción recomendada para vistas de consulta o de informes: deja clara la intención y evita sorpresas.
3.4. Qué hace que una vista NO sea actualizable
Columnas calculadas. Una columna que es una expresión no corresponde a ninguna columna de la tabla:
UPDATE v_salario_anual SET salario_anual = 50000 WHERE emp_id = 100;
UPDATE v_salario_anual SET nombre = 'Ana María' WHERE emp_id = 100;
ROLLBACK;
UPDATE v_salario_anual SET salario_anual = 50000 WHERE emp_id = 100
*
ERROR en línea 1:
ORA-01733: virtual column not allowed here
1 fila actualizada.
Rollback terminado.
Las demás columnas de la vista sí son modificables.
Agrupaciones y DISTINCT. Una fila de v_resumen_dept resume varias filas de empleados; una fila de una vista con DISTINCT puede proceder de muchas:
UPDATE v_resumen_dept SET num_empleados = 10 WHERE dept_id = 10;
CREATE VIEW v_oficios AS SELECT DISTINCT oficio FROM empleados;
DELETE FROM v_oficios WHERE oficio = 'BECARIO';
UPDATE v_resumen_dept SET num_empleados = 10 WHERE dept_id = 10
*
ERROR en línea 1:
ORA-01732: data manipulation operation not legal on this view
Vista creada.
DELETE FROM v_oficios WHERE oficio = 'BECARIO'
*
ERROR en línea 1:
ORA-01732: data manipulation operation not legal on this view
Columnas obligatorias que la vista no incluye. Una vista puede ser actualizable y aun así no admitir INSERT, porque la fila nueva tendría NULL en columnas NOT NULL de la tabla:
CREATE VIEW v_emp_mini AS SELECT emp_id, nombre, apellidos FROM empleados;
INSERT INTO v_emp_mini VALUES (171, 'Pepe', 'Ruiz');
Vista creada.
INSERT INTO v_emp_mini VALUES (171, 'Pepe', 'Ruiz')
*
ERROR en línea 1:
ORA-01400: cannot insert NULL into ("CURSO"."EMPLEADOS"."EMAIL")
Resumen de las reglas: una vista no admite DML si su consulta contiene:
| Elemento | DELETE | UPDATE | INSERT |
|---|---|---|---|
Operadores de conjuntos (UNION, MINUS…) | No | No | No |
Funciones de grupo, GROUP BY, HAVING, DISTINCT | No | No | No |
Consultas jerárquicas, ROWNUM, funciones analíticas | No | No | No |
Subconsultas en la lista del SELECT | No | No | No |
| Columnas definidas por expresiones | Sí | No en esas columnas | No en esas columnas |
Columnas NOT NULL sin DEFAULT que la vista no incluye | Sí | Sí | No |
WITH READ ONLY | No | No | No |
3.5. Vistas de combinación y tablas con clave preservada
📘 Concepto: En una vista que combina varias tablas, una tabla tiene la clave preservada (key-preserved table) si su clave primaria (o única) sigue siendo clave en el resultado de la vista; es decir, si cada fila de esa tabla aparece como mucho una vez en la vista. Oracle solo garantiza el DML sobre columnas de tablas con clave preservada, y cada sentencia solo puede modificar una tabla base.
CREATE VIEW v_emp_dept AS
SELECT e.emp_id, e.nombre, e.apellidos, e.salario, e.dept_id,
d.nombre AS departamento, d.localidad
FROM empleados e
JOIN departamentos d ON d.dept_id = e.dept_id;
En esta vista, empleados tiene la clave preservada (cada empleado aparece una vez), pero departamentos no (el departamento 20 aparece seis veces, una por empleado). La vista USER_UPDATABLE_COLUMNS lo confirma:
SELECT column_name, updatable, insertable, deletable
FROM user_updatable_columns
WHERE table_name = 'V_EMP_DEPT';
COLUMN_NAME UPD INS DEL
-------------- --- --- ---
EMP_ID YES YES YES
NOMBRE YES YES YES
APELLIDOS YES YES YES
SALARIO YES YES YES
DEPT_ID YES YES YES
DEPARTAMENTO NO NO NO
LOCALIDAD NO NO NO
7 filas seleccionadas.
UPDATE v_emp_dept SET salario = salario + 100 WHERE emp_id = 131;
DELETE FROM v_emp_dept WHERE emp_id = 131;
SELECT COUNT(*) FROM empleados WHERE emp_id = 131;
SELECT COUNT(*) FROM departamentos WHERE dept_id = 40;
ROLLBACK;
1 fila actualizada.
1 fila suprimida.
COUNT(*)
----------
0
1 fila seleccionada.
COUNT(*)
----------
1
1 fila seleccionada.
Rollback terminado.
El DELETE sobre una vista de combinación borra la fila de la tabla con clave preservada (empleados), nunca la del departamento.
Con las columnas de la tabla no preservada, el comportamiento depende de la versión:
UPDATE v_emp_dept SET departamento = 'FINANZAS' WHERE emp_id = 131;
UPDATE v_emp_dept SET localidad = 'Cádiz' WHERE dept_id = 40;
ROLLBACK;
1 fila actualizada.
UPDATE v_emp_dept SET localidad = 'Cádiz' WHERE dept_id = 40
*
ERROR en línea 1:
ORA-30926: The operation attempted to update the same row (rowid: 'AAASGBAAAAAAAR8AAD') twice.
Rollback terminado.
⚠️ Importante: En Oracle 19c (y en la regla clásica que encontrarás en toda la bibliografía), las dos sentencias anteriores fallan con
ORA-01779: cannot modify a column which maps to a non key-preserved table. En nuestras pruebas con Oracle 26ai, la primera se aceptó porque solo afectaba a una fila dedepartamentos, y la segunda falló conORA-30926porque habría modificado tres veces la misma fila del departamento 40 (tiene tres empleados). Observa queUSER_UPDATABLE_COLUMNSsigue marcando esas columnas como no actualizables. Diseña tus vistas siguiendo la regla clásica: modifica a través de una vista de combinación solo las columnas de la tabla con clave preservada.
💡 Consejo: Antes de hacer DML sobre una vista compleja, consulta
USER_UPDATABLE_COLUMNS. Si necesitas que una vista no actualizable admita DML con una lógica propia, la solución es un triggerINSTEAD OF, que pertenece a PL/SQL (Curso de PL/SQL).
3.6. Vistas en línea y vistas sobre vistas
Una vista en línea (inline view) es una subconsulta en la cláusula FROM: una vista sin nombre que solo existe durante la consulta. Se estudian a fondo en el módulo 31; aquí basta con reconocerlas:
SELECT r.departamento, r.salario_medio
FROM (SELECT d.nombre AS departamento, ROUND(AVG(e.salario), 2) AS salario_medio
FROM empleados e
JOIN departamentos d ON d.dept_id = e.dept_id
GROUP BY d.nombre) r
WHERE r.salario_medio > 2800
ORDER BY r.salario_medio DESC;
DEPARTAMENTO SALARIO_MEDIO
-------------- -------------
DIRECCIÓN 6500
INFORMÁTICA 3133,33
MARKETING 3100
CONTABILIDAD 2816,67
4 filas seleccionadas.
Una vista también puede definirse sobre otra vista:
CREATE VIEW v_dept_grandes AS
SELECT * FROM v_resumen_dept WHERE num_empleados >= 4;
SELECT dept_id, departamento, num_empleados FROM v_dept_grandes;
Vista creada.
DEPT_ID DEPARTAMENTO NUM_EMPLEADOS
---------- -------------- -------------
20 VENTAS 6
30 INFORMÁTICA 6
60 LOGÍSTICA 4
3 filas seleccionadas.
💡 Consejo: No abuses de las vistas anidadas en muchos niveles: el optimizador las “desenrolla” (view merging), pero los planes de ejecución se complican y el mantenimiento se vuelve difícil.
3.7. Borrar vistas: DROP VIEW
DROP VIEW v_oficios;
DROP VIEW v_emp_mini;
DROP VIEW v_dept_grandes;
DROP VIEW v_mal;
DROP VIEW IF EXISTS v_mal;
Vista borrada.
Vista borrada.
Vista borrada.
DROP VIEW v_mal
*
ERROR en línea 1:
ORA-00942: table or view "CURSO"."V_MAL" does not exist
Vista borrada.
Borrar una vista no borra datos (no los tiene) y no pasa por la papelera. Las vistas que dependían de ella quedan inválidas. DROP VIEW IF EXISTS está disponible desde Oracle 23ai.
4. Vistas materializadas
📘 Concepto: Una vista materializada (materialized view, antiguamente snapshot) es una vista cuyo resultado se almacena físicamente en una tabla. Consultarla es tan rápido como leer una tabla, porque no se ejecuta la consulta original; a cambio, sus datos pueden quedar desactualizados y hay que refrescarla. Se usan en almacenes de datos (data warehouses), informes pesados y réplicas de datos remotos.
| Vista | Vista materializada | |
|---|---|---|
| Almacena filas | No | Sí (ocupa espacio) |
| Datos | Siempre actuales | Actuales hasta el último refresco |
| Coste de consulta | El de ejecutar la consulta base | El de leer una tabla |
| Admite índices propios | No | Sí |
| DML directo | Según las reglas de la sección 3 | No (salvo las de tipo updatable, para replicación) |
4.1. Creación y refresco completo bajo demanda
CREATE MATERIALIZED VIEW mv_ventas_producto
BUILD IMMEDIATE
REFRESH COMPLETE ON DEMAND
AS
SELECT p.producto_id, p.nombre,
SUM(l.cantidad) AS unidades,
SUM(l.cantidad * l.precio_unitario * (1 - l.descuento / 100)) AS facturado
FROM productos p
JOIN lineas_pedido l ON l.producto_id = p.producto_id
GROUP BY p.producto_id, p.nombre;
SELECT * FROM mv_ventas_producto WHERE producto_id IN (1, 2, 3) ORDER BY producto_id;
Vista materializada creada.
PRODUCTO_ID NOMBRE UNIDADES FACTURADO
----------- ------------------------ ---------- ----------
1 Portátil UltraBook 14 15 12361,25
2 Portátil Gaming 16 3 4497
3 Portátil Básico 15 24 10479
3 filas seleccionadas.
| Opción | Significado |
|---|---|
BUILD IMMEDIATE | Se rellena al crearla (por defecto). BUILD DEFERRED: se rellena en el primer refresco |
REFRESH COMPLETE | Cada refresco vuelve a ejecutar la consulta entera |
REFRESH FAST | Refresco incremental: solo aplica los cambios registrados en los logs de las tablas base |
REFRESH FORCE | Intenta FAST y, si no es posible, hace COMPLETE (por defecto) |
ON DEMAND | Se refresca cuando alguien lo pide (por defecto) |
ON COMMIT | Se refresca automáticamente al confirmar cada transacción que modifica las tablas base |
START WITH ... NEXT ... | Refresco programado periódicamente |
ENABLE QUERY REWRITE | Permite al optimizador usar la vista materializada para responder consultas sobre las tablas base |
El diccionario muestra su estado en USER_MVIEWS. Internamente, Oracle crea una tabla con el mismo nombre:
SELECT mview_name, refresh_mode, refresh_method, staleness, last_refresh_date
FROM user_mviews;
SELECT object_name, object_type
FROM user_objects
WHERE object_name = 'MV_VENTAS_PRODUCTO'
ORDER BY 2;
MVIEW_NAME REFRESH_ REFRESH_ STALENESS LAST_REFRESH_DATE
---------------------- -------- -------- -------------- -------------------
MV_VENTAS_PRODUCTO DEMAND COMPLETE FRESH 03/10/2026 23:19:31
1 fila seleccionada.
OBJECT_NAME OBJECT_TYPE
---------------------- ------------------
MV_VENTAS_PRODUCTO MATERIALIZED VIEW
MV_VENTAS_PRODUCTO TABLE
2 filas seleccionadas.
Si se venden dos portátiles más, la vista materializada no se entera:
INSERT INTO lineas_pedido VALUES (5024, 2, 1, 2, 899, 0);
COMMIT;
SELECT mview_name, staleness FROM user_mviews;
SELECT producto_id, unidades FROM mv_ventas_producto WHERE producto_id = 1;
1 fila creada.
Confirmación terminada.
MVIEW_NAME STALENESS
---------------------- --------------
MV_VENTAS_PRODUCTO NEEDS_COMPILE
1 fila seleccionada.
PRODUCTO_ID UNIDADES
----------- ----------
1 15
1 fila seleccionada.
El refresco bajo demanda se solicita llamando al procedimiento REFRESH del paquete del sistema DBMS_MVIEW (el comando EXEC de SQL*Plus permite invocarlo en una línea; la programación con paquetes es materia del Curso de PL/SQL). En SQL Developer también puedes refrescarla desde el menú contextual de la vista materializada.
EXEC DBMS_MVIEW.REFRESH('MV_VENTAS_PRODUCTO', 'C')
SELECT producto_id, unidades FROM mv_ventas_producto WHERE producto_id = 1;
Procedimiento PL/SQL terminado correctamente.
PRODUCTO_ID UNIDADES
----------- ----------
1 17
1 fila seleccionada.
Las vistas materializadas de este tipo son de solo lectura:
UPDATE mv_ventas_producto SET unidades = 0;
UPDATE mv_ventas_producto SET unidades = 0
*
ERROR en línea 1:
ORA-01732: data manipulation operation not legal on this view
4.2. Refresco rápido al confirmar: logs de vista materializada
Para el refresco incremental (FAST), Oracle necesita saber qué filas han cambiado en las tablas base desde el último refresco. Eso se registra en un log de vista materializada (materialized view log), una tabla auxiliar MLOG$_tabla:
CREATE MATERIALIZED VIEW LOG ON lineas_pedido
WITH ROWID, SEQUENCE (producto_id, cantidad)
INCLUDING NEW VALUES;
CREATE MATERIALIZED VIEW mv_unidades_producto
REFRESH FAST ON COMMIT
AS
SELECT producto_id,
SUM(cantidad) AS unidades,
COUNT(cantidad) AS n_cant,
COUNT(*) AS n_lineas
FROM lineas_pedido
GROUP BY producto_id;
Log de vista materializada creado.
Vista materializada creada.
💡 Consejo: Las vistas materializadas de agregación con refresco rápido tienen requisitos: el log debe incluir las columnas usadas y
INCLUDING NEW VALUES, y la consulta debe incluirCOUNT(*)yCOUNT(columna)de cada columna sumada, para que Oracle pueda recalcular los agregados de forma incremental.
El producto 14 nunca se ha vendido, así que no aparece. Vendemos 5 unidades y observamos el efecto antes y después del COMMIT:
SELECT producto_id, unidades, n_lineas FROM mv_unidades_producto WHERE producto_id = 14;
INSERT INTO lineas_pedido VALUES (5024, 3, 14, 5, 49, 0);
SELECT producto_id, unidades, n_lineas FROM mv_unidades_producto WHERE producto_id = 14;
COMMIT;
SELECT producto_id, unidades, n_lineas FROM mv_unidades_producto WHERE producto_id = 14;
ninguna fila seleccionada
1 fila creada.
ninguna fila seleccionada
Confirmación terminada.
PRODUCTO_ID UNIDADES N_LINEAS
----------- ---------- ----------
14 5 1
1 fila seleccionada.
La vista materializada se ha actualizado sola en el COMMIT. Al deshacer la venta, también:
DELETE FROM lineas_pedido WHERE pedido_id = 5024 AND num_linea IN (2, 3);
COMMIT;
SELECT producto_id, unidades, n_lineas FROM mv_unidades_producto WHERE producto_id = 14;
DROP MATERIALIZED VIEW mv_unidades_producto;
DROP MATERIALIZED VIEW LOG ON lineas_pedido;
DROP MATERIALIZED VIEW mv_ventas_producto;
2 filas suprimidas.
Confirmación terminada.
ninguna fila seleccionada
Vista materializada borrada.
Log de vista materializada borrado.
Vista materializada borrada.
⚠️ Importante:
ON COMMIThace que cada transacción que modifica las tablas base pague el coste de refrescar la vista materializada. Es razonable para agregados sencillos sobre tablas con pocas escrituras; en sistemas con muchas transacciones se prefiereON DEMANDprogramado (por ejemplo, cada noche). Crear vistas materializadasON COMMITrequiere el privilegioON COMMIT REFRESH.
5. Índices: qué son y cómo funcionan
5.1. El problema: buscar sin índice
Imagina un libro de 800 páginas sin índice alfabético. Para encontrar dónde se habla de “normalización” tendrías que leerlo entero. Oracle tiene el mismo problema: para encontrar las ventas del cliente 500 en una tabla de 200.000 filas, sin índice debe leer todos los bloques de la tabla. Es el acceso completo a la tabla (full table scan).
📘 Concepto: Un índice (index) es una estructura de datos auxiliar, separada de la tabla, que almacena los valores de una o varias columnas ordenados junto con el ROWID (dirección física) de la fila donde está cada valor. Permite localizar las filas que cumplen una condición sin recorrer toda la tabla. Los índices son transparentes: las consultas no cambian, es el optimizador quien decide si los usa.
5.2. El árbol B (B-tree)
El tipo de índice por defecto en Oracle es el árbol B+ (B-tree index): un árbol equilibrado en el que todas las hojas están a la misma profundidad.
flowchart TB
R["Bloque raíz<br/>< 400 | 400-799 | ≥ 800"]
B1["Rama<br/>< 200 | 200-399"]
B2["Rama<br/>400-599 | 600-799"]
B3["Rama<br/>800-899 | ≥ 900"]
H1["Hoja<br/>1 → ROWID<br/>1 → ROWID<br/>2 → ROWID ..."]
H2["Hoja<br/>200 → ROWID<br/>201 → ROWID ..."]
H3["Hoja<br/>499 → ROWID<br/>500 → ROWID<br/>500 → ROWID ..."]
H4["Hoja<br/>600 → ROWID ..."]
H5["Hoja<br/>800 → ROWID ..."]
H6["Hoja<br/>900 → ROWID ..."]
R --> B1
R --> B2
R --> B3
B1 --> H1
B1 --> H2
B2 --> H3
B2 --> H4
B3 --> H5
B3 --> H6
H1 <-.-> H2
H2 <-.-> H3
H3 <-.-> H4
H4 <-.-> H5
H5 <-.-> H6
H3 == "ROWID" ==> T[("Bloque de la tabla<br/>con la fila")]
- Los bloques raíz y rama contienen rangos de valores y punteros a los bloques del nivel inferior.
- Los bloques hoja contienen cada valor indexado con el ROWID de su fila, ordenados, y están enlazados entre sí (en ambos sentidos) para recorrer rangos.
- Para buscar el valor 500: raíz → rama “400-599” → hoja → ROWID → bloque de la tabla. Con una altura de 3 niveles, se encuentra cualquier fila entre millones leyendo unos 4 bloques.
- Para buscar un rango (
BETWEEN 100 AND 110), se baja hasta la primera hoja y se recorren las hojas enlazadas.
📘 Concepto: La selectividad de una condición es la fracción de filas que la cumplen. Un índice es útil cuando la condición es muy selectiva (devuelve pocas filas). Si la consulta devuelve una gran parte de la tabla, leer la tabla entera es más barato que ir y venir del índice a la tabla fila a fila.
5.3. Índices creados automáticamente
Oracle crea automáticamente un índice único para cada PRIMARY KEY y cada UNIQUE (si no existe ya uno adecuado), con el mismo nombre que la restricción:
SELECT index_name, table_name, uniqueness
FROM user_indexes
ORDER BY table_name, index_name;
INDEX_NAME TABLE_NAME UNIQUENESS
---------------------- -------------- ----------
PK_ASIGNACIONES ASIGNACIONES UNIQUE
PK_CATEGORIAS CATEGORIAS UNIQUE
UQ_CAT_NOMBRE CATEGORIAS UNIQUE
PK_CLIENTES CLIENTES UNIQUE
UQ_CLI_NIF CLIENTES UNIQUE
PK_DEPARTAMENTOS DEPARTAMENTOS UNIQUE
UQ_DEP_NOMBRE DEPARTAMENTOS UNIQUE
PK_EMPLEADOS EMPLEADOS UNIQUE
...
14 filas seleccionadas.
Las columnas de cada índice, y su orden, están en USER_IND_COLUMNS:
SELECT index_name, column_name, column_position
FROM user_ind_columns
WHERE table_name IN ('LINEAS_PEDIDO', 'ASIGNACIONES')
ORDER BY index_name, column_position;
INDEX_NAME COLUMN_NAME COLUMN_POSITION
---------------------- -------------- ---------------
PK_ASIGNACIONES EMP_ID 1
PK_ASIGNACIONES PROYECTO_ID 2
PK_LINEAS_PEDIDO PEDIDO_ID 1
PK_LINEAS_PEDIDO NUM_LINEA 2
4 filas seleccionadas.
⚠️ Importante: Oracle no crea índices automáticamente sobre las columnas de clave ajena. Ninguna FK de EMPRESA tiene índice propio (lo comprobaremos en la sección 10.3). En tablas grandes conviene indexarlas: aceleran las combinaciones y evitan bloqueos de la tabla hija al borrar o modificar la clave de la tabla padre.
6. Planes de ejecución: EXPLAIN PLAN y DBMS_XPLAN
📘 Concepto: El optimizador (cost-based optimizer, CBO) es el componente de Oracle que decide cómo ejecutar cada sentencia: qué índices usar, en qué orden combinar las tablas, qué algoritmo de combinación aplicar. Para ello estima el coste de cada alternativa con las estadísticas de las tablas e índices (número de filas, de bloques, de valores distintos…). El resultado es el plan de ejecución.
Para ver el plan que elegiría el optimizador sin ejecutar la consulta:
EXPLAIN PLAN FOR
sentencia;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
EXPLAIN PLAN guarda el plan en la tabla PLAN_TABLE y DBMS_XPLAN.DISPLAY es una función de un paquete del sistema que lo devuelve formateado; se invoca desde SQL con TABLE(...), sin escribir PL/SQL. Admite como tercer parámetro el nivel de detalle: 'TYPICAL' (por defecto), 'BASIC', 'BASIC +ROWS +COST', 'ALL'…
6.1. Una tabla de pruebas grande
Con las 24 filas de empleados cualquier consulta es instantánea y el optimizador casi siempre lee la tabla entera. Para ver el efecto de los índices necesitamos volumen. Creamos una tabla de 200.000 ventas (la técnica CONNECT BY LEVEL se verá en el módulo 33):
CREATE TABLE ventas_demo AS
SELECT LEVEL AS venta_id,
MOD(LEVEL, 1000) + 1 AS cliente_id,
DATE '2025-01-01' + MOD(LEVEL, 365) AS fecha,
ROUND(MOD(LEVEL * 7919, 100000) / 100, 2) AS importe,
CASE MOD(LEVEL, 4) WHEN 0 THEN 'PENDIENTE' WHEN 1 THEN 'ENVIADO'
WHEN 2 THEN 'ENTREGADO' ELSE 'CANCELADO' END AS estado,
RPAD('x', 100, 'x') AS relleno
FROM dual
CONNECT BY LEVEL <= 200000;
ALTER TABLE ventas_demo ADD CONSTRAINT pk_ventas_demo PRIMARY KEY (venta_id);
SELECT table_name, num_rows, blocks, last_analyzed
FROM user_tables
WHERE table_name = 'VENTAS_DEMO';
Tabla creada.
Tabla modificada.
TABLE_NAME NUM_ROWS BLOCKS LAST_ANALY
-------------- ---------- ---------- ----------
VENTAS_DEMO 200000 3909 03/10/2026
1 fila seleccionada.
Hay 1000 clientes distintos, cada uno con 200 ventas, y 4 estados con 50.000 ventas cada uno. Las estadísticas (NUM_ROWS, BLOCKS) ya están calculadas: desde 12c, CREATE TABLE ... AS SELECT las recoge automáticamente, y CREATE INDEX calcula las del índice.
6.2. Acceso completo frente a acceso por índice
Sin índice sobre cliente_id:
EXPLAIN PLAN FOR
SELECT * FROM ventas_demo WHERE cliente_id = 500;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
Explicado.
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 4056408792
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 200 | 26600 | 1061 (1)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| VENTAS_DEMO | 200 | 26600 | 1061 (1)| 00:00:01 |
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("CLIENTE_ID"=500)
13 filas seleccionadas.
Cómo se lee un plan:
| Columna | Significado |
|---|---|
Id | Número de operación. Las operaciones se ejecutan de la más sangrada (interior) a la menos sangrada |
Operation | Qué hace: TABLE ACCESS FULL (leer toda la tabla), INDEX RANGE SCAN, TABLE ACCESS BY INDEX ROWID… |
Name | Objeto sobre el que actúa |
Rows | Filas que el optimizador estima que producirá esa operación (200 = 200.000 / 1000 clientes) |
Cost | Coste estimado (unidad interna, proporcional a lecturas de bloques y CPU) |
* en el Id | Esa operación tiene un predicado, detallado en Predicate Information |
filter | Condición que se aplica fila a fila tras leerla |
access | Condición que se usa para llegar a las filas (típicamente a través de un índice) |
Con índice:
CREATE INDEX ix_ventas_cliente ON ventas_demo (cliente_id);
EXPLAIN PLAN FOR
SELECT * FROM ventas_demo WHERE cliente_id = 500;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
Índice creado.
Explicado.
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2116813224
---------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 200 | 26600 | 201 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| VENTAS_DEMO | 200 | 26600 | 201 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IX_VENTAS_CLIENTE | 200 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("CLIENTE_ID"=500)
14 filas seleccionadas.
Ahora el plan se lee así: (2) recorrer el índice por el rango cliente_id = 500 obteniendo 200 ROWID, y (1) ir a la tabla a buscar cada fila por su ROWID (BATCHED: agrupando los accesos por bloque). El coste baja de 1061 a 201. Si medimos el tiempo real con SET TIMING ON:
-- Sin índice
COUNT(*) SUM(IMPORTE)
---------- ------------
200 100162
Transcurrido: 00:00:00.05
-- Con índice
COUNT(*) SUM(IMPORTE)
---------- ------------
200 100162
Transcurrido: 00:00:00.01
Con 200.000 filas en memoria la diferencia son centésimas; con 200 millones de filas en disco, sería la diferencia entre segundos y minutos.
6.3. Otros tipos de acceso
Por clave primaria: INDEX UNIQUE SCAN, como mucho una fila:
EXPLAIN PLAN FOR
SELECT * FROM ventas_demo WHERE venta_id = 12345;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(NULL, NULL, 'BASIC'));
------------------------------------------------------
| Id | Operation | Name |
------------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | TABLE ACCESS BY INDEX ROWID| VENTAS_DEMO |
| 2 | INDEX UNIQUE SCAN | PK_VENTAS_DEMO |
------------------------------------------------------
Condición poco selectiva: aunque existe índice, el optimizador prefiere leer la tabla entera, porque la condición devuelve casi todas las filas (198.000 estimadas):
EXPLAIN PLAN FOR
SELECT * FROM ventas_demo WHERE cliente_id > 10;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(NULL, NULL, 'BASIC +ROWS +COST'));
--------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)|
--------------------------------------------------------------
| 0 | SELECT STATEMENT | | 198K| 1062 (1)|
| 1 | TABLE ACCESS FULL| VENTAS_DEMO | 198K| 1062 (1)|
--------------------------------------------------------------
Solo índice: si todas las columnas que necesita la consulta están en el índice, Oracle ni siquiera visita la tabla:
EXPLAIN PLAN FOR
SELECT COUNT(*) FROM ventas_demo WHERE cliente_id BETWEEN 100 AND 110;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(NULL, NULL, 'BASIC +ROWS +COST'));
--------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)|
--------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 6 (0)|
| 1 | SORT AGGREGATE | | 1 | |
| 2 | INDEX RANGE SCAN| IX_VENTAS_CLIENTE | 2402 | 6 (0)|
--------------------------------------------------------------------
Una función o expresión sobre la columna anula el índice: el índice guarda cliente_id, no cliente_id + 0:
EXPLAIN PLAN FOR
SELECT * FROM ventas_demo WHERE cliente_id + 0 = 500;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(NULL, NULL, 'BASIC +ROWS +COST'));
--------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)|
--------------------------------------------------------------
| 0 | SELECT STATEMENT | | 200 | 1062 (1)|
| 1 | TABLE ACCESS FULL| VENTAS_DEMO | 200 | 1062 (1)|
--------------------------------------------------------------
⚠️ Importante: Este es uno de los errores de rendimiento más frecuentes:
WHERE UPPER(apellidos) = ...,WHERE TRUNC(fecha) = ...,WHERE TO_CHAR(fecha, 'YYYY') = '2025'o una conversión implícita de tipos impiden usar el índice normal de la columna. La solución es reescribir la condición sobre la columna “limpia” (fecha >= DATE '2025-01-01' AND fecha < DATE '2026-01-01') o crear un índice basado en función (sección 7.4).
7. Creación y tipos de índices
7.1. Sintaxis
CREATE [UNIQUE | BITMAP] INDEX [esquema.]nombre_indice
ON tabla (columna1 [ASC | DESC], columna2 ..., | expresión)
[INVISIBLE]
[TABLESPACE ts];
Por convención usamos el prefijo ix_ (índices normales), ux_ (únicos) y bix_ (bitmap).
7.2. Índices únicos
Un índice UNIQUE impide valores duplicados, igual que una restricción UNIQUE:
CREATE UNIQUE INDEX ux_per_nombre ON personas_demo (nombre);
INSERT INTO personas_demo VALUES (100001, 'Nombre1', 'X');
Índice creado.
INSERT INTO personas_demo VALUES (100001, 'Nombre1', 'X')
*
ERROR en línea 1:
ORA-00001: unique constraint (CURSO.UX_PER_NOMBRE) violated on table CURSO.PERSONAS_DEMO columns (NOMBRE)
ORA-03301: (ORA-00001 details) row with column values (NOMBRE:'Nombre1') already exists
(La tabla personas_demo se crea en la sección 7.4.) Observa el mensaje detallado de 23ai, que indica la columna y el valor duplicado.
💡 Consejo: Para garantizar unicidad, usa una restricción
UNIQUE(módulo 24) en lugar de un índice único: la restricción documenta la regla de negocio, aparece enUSER_CONSTRAINTS, puede ser referenciada por claves ajenas y puede deshabilitarse. El índice es un detalle de implementación que Oracle crea por ti.
7.3. Índices compuestos: el orden de las columnas importa
Un índice compuesto (composite o concatenated index) incluye varias columnas. Las entradas se ordenan por la primera columna; dentro de cada valor de la primera, por la segunda, y así sucesivamente (como una guía telefónica ordenada por apellido y luego por nombre).
DROP INDEX ix_ventas_cliente;
CREATE INDEX ix_ventas_cli_fecha ON ventas_demo (cliente_id, fecha);
Consulta por las dos columnas: el índice es perfecto (3 filas estimadas):
EXPLAIN PLAN FOR
SELECT * FROM ventas_demo WHERE cliente_id = 500 AND fecha = DATE '2025-06-01';
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(NULL, NULL, 'BASIC +ROWS +COST +PREDICATE'));
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)|
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 6 (0)|
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| VENTAS_DEMO | 3 | 6 (0)|
|* 2 | INDEX RANGE SCAN | IX_VENTAS_CLI_FECHA | 3 | 3 (0)|
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("CLIENTE_ID"=500 AND "FECHA"=TO_DATE(' 2025-06-01 00:00:00',
'syyyy-mm-dd hh24:mi:ss'))
Consulta solo por la primera columna (prefijo del índice): también lo usa:
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)|
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 200 | 203 (0)|
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| VENTAS_DEMO | 200 | 203 (0)|
| 2 | INDEX RANGE SCAN | IX_VENTAS_CLI_FECHA | 200 | 3 (0)|
----------------------------------------------------------------------------------------
Consulta solo por la segunda columna: no hay forma eficiente de usar el índice (las fechas están “desperdigadas” dentro de cada cliente), y el optimizador lee la tabla:
EXPLAIN PLAN FOR
SELECT * FROM ventas_demo WHERE fecha = DATE '2025-06-01';
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(NULL, NULL, 'BASIC +ROWS +COST'));
--------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)|
--------------------------------------------------------------
| 0 | SELECT STATEMENT | | 548 | 1062 (1)|
| 1 | TABLE ACCESS FULL| VENTAS_DEMO | 548 | 1062 (1)|
--------------------------------------------------------------
Consulta que solo necesita columnas del índice: ni siquiera toca la tabla:
EXPLAIN PLAN FOR
SELECT cliente_id, fecha FROM ventas_demo WHERE cliente_id = 500;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(NULL, NULL, 'BASIC +ROWS +COST'));
---------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)|
---------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 200 | 3 (0)|
| 1 | INDEX RANGE SCAN| IX_VENTAS_CLI_FECHA | 200 | 3 (0)|
---------------------------------------------------------------------
💡 Consejo: Reglas para elegir el orden de las columnas de un índice compuesto:
- Pon primero las columnas que aparecen en más consultas con igualdad (un índice
(a, b)sirve paraWHERE a = ...y paraWHERE a = ... AND b = ..., pero no bien paraWHERE b = ...).- Las columnas comparadas con igualdad antes que las comparadas por rango.
- Un índice
(a, b)hace innecesario otro índice solo sobre(a).- Oracle puede a veces usar la segunda columna con un index skip scan, pero solo es eficiente si la primera columna tiene muy pocos valores distintos.
7.4. Índices basados en función
Creamos una tabla de 100.000 personas con un índice normal sobre apellidos:
CREATE TABLE personas_demo AS
SELECT LEVEL AS persona_id,
'Nombre' || LEVEL AS nombre,
CASE MOD(LEVEL, 3) WHEN 0 THEN 'García ' WHEN 1 THEN 'López ' ELSE 'Martín ' END
|| 'Apellido' || LEVEL AS apellidos
FROM dual
CONNECT BY LEVEL <= 100000;
CREATE INDEX ix_per_apellidos ON personas_demo (apellidos);
La búsqueda exacta usa el índice, pero la búsqueda sin distinguir mayúsculas no:
EXPLAIN PLAN FOR
SELECT * FROM personas_demo WHERE apellidos = 'López Apellido4000';
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(NULL, NULL, 'BASIC +ROWS +COST'));
EXPLAIN PLAN FOR
SELECT * FROM personas_demo WHERE UPPER(apellidos) = 'LÓPEZ APELLIDO4000';
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(NULL, NULL, 'BASIC +ROWS +COST +PREDICATE'));
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)|
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4 (0)|
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| PERSONAS_DEMO | 1 | 4 (0)|
| 2 | INDEX RANGE SCAN | IX_PER_APELLIDOS | 1 | 3 (0)|
-------------------------------------------------------------------------------------
----------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)|
----------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1000 | 171 (1)|
|* 1 | TABLE ACCESS FULL| PERSONAS_DEMO | 1000 | 171 (1)|
----------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(UPPER("APELLIDOS")='LÓPEZ APELLIDO4000')
📘 Concepto: Un índice basado en función (function-based index) indexa el resultado de una expresión en lugar del valor de la columna. El optimizador lo usa cuando la consulta contiene exactamente la misma expresión.
CREATE INDEX ix_per_apellidos_upper ON personas_demo (UPPER(apellidos));
EXPLAIN PLAN FOR
SELECT * FROM personas_demo WHERE UPPER(apellidos) = 'LÓPEZ APELLIDO4000';
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(NULL, NULL, 'BASIC +ROWS +COST +PREDICATE'));
Índice creado.
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)|
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4 (0)|
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| PERSONAS_DEMO | 1 | 4 (0)|
|* 2 | INDEX RANGE SCAN | IX_PER_APELLIDOS_UPPER | 1 | 3 (0)|
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access(UPPER("APELLIDOS")='LÓPEZ APELLIDO4000')
La expresión indexada se consulta en USER_IND_EXPRESSIONS:
SELECT index_name, index_type FROM user_indexes WHERE table_name = 'PERSONAS_DEMO';
SELECT index_name, column_expression FROM user_ind_expressions WHERE table_name = 'PERSONAS_DEMO';
INDEX_NAME INDEX_TYPE
---------------------- ----------------------
IX_PER_APELLIDOS NORMAL
IX_PER_APELLIDOS_UPPER FUNCTION-BASED NORMAL
2 filas seleccionadas.
INDEX_NAME COLUMN_EXPRESSION
---------------------- ----------------------
IX_PER_APELLIDOS_UPPER UPPER("APELLIDOS")
1 fila seleccionada.
El caso de LIKE: un patrón con prefijo fijo puede usar el índice (es un rango); un patrón que empieza por % no:
EXPLAIN PLAN FOR
SELECT * FROM personas_demo WHERE apellidos LIKE 'López Apellido40%';
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(NULL, NULL, 'BASIC +ROWS +COST'));
EXPLAIN PLAN FOR
SELECT * FROM personas_demo WHERE apellidos LIKE '%Apellido4000';
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(NULL, NULL, 'BASIC +ROWS +COST'));
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)|
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4 (0)|
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| PERSONAS_DEMO | 1 | 4 (0)|
| 2 | INDEX RANGE SCAN | IX_PER_APELLIDOS | 1 | 3 (0)|
-------------------------------------------------------------------------------------
----------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)|
----------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5000 | 171 (1)|
| 1 | TABLE ACCESS FULL| PERSONAS_DEMO | 5000 | 171 (1)|
----------------------------------------------------------------
7.5. Índices bitmap
📘 Concepto: Un índice bitmap guarda, para cada valor distinto de la columna, un mapa de bits con un bit por fila (1 si la fila tiene ese valor). Es muy compacto cuando la columna tiene pocos valores distintos (baja cardinalidad: sexo, estado, provincia) y permite combinar condiciones con operaciones lógicas de bits (
AND,OR) rapidísimas.
CREATE BITMAP INDEX bix_ventas_estado ON ventas_demo (estado);
SELECT index_name, index_type FROM user_indexes WHERE table_name = 'VENTAS_DEMO';
EXPLAIN PLAN FOR
SELECT COUNT(*) FROM ventas_demo WHERE estado = 'CANCELADO';
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(NULL, NULL, 'BASIC +ROWS +COST'));
SELECT segment_name, blocks
FROM user_segments
WHERE segment_name IN ('BIX_VENTAS_ESTADO', 'IX_VENTAS_CLI_FECHA', 'PK_VENTAS_DEMO')
ORDER BY blocks;
Índice creado.
INDEX_NAME INDEX_TYPE
---------------------- ----------------------
PK_VENTAS_DEMO NORMAL
IX_VENTAS_CLI_FECHA NORMAL
BIX_VENTAS_ESTADO BITMAP
3 filas seleccionadas.
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)|
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5 (0)|
| 1 | SORT AGGREGATE | | 1 | |
| 2 | BITMAP CONVERSION COUNT | | 50000 | 5 (0)|
| 3 | BITMAP INDEX SINGLE VALUE| BIX_VENTAS_ESTADO | | |
------------------------------------------------------------------------------
SEGMENT_NAME BLOCKS
-------------------- ----------
BIX_VENTAS_ESTADO 32
PK_VENTAS_DEMO 512
IX_VENTAS_CLI_FECHA 768
3 filas seleccionadas.
El índice bitmap ocupa 32 bloques frente a los 512 del índice B-tree de la clave primaria, y cuenta las 50.000 ventas canceladas contando bits, sin tocar la tabla.
⚠️ Importante: Los índices bitmap son para almacenes de datos y tablas de solo lectura o de carga por lotes. En tablas con muchas modificaciones concurrentes (sistemas transaccionales, OLTP) son desaconsejables: una sola fila modificada bloquea una porción del mapa de bits que afecta a muchas filas, y las sesiones se bloquean unas a otras. En el sistema de pedidos de EMPRESA en producción, nunca pondrías un bitmap sobre
pedidos.estado.
| B-tree | Bitmap | |
|---|---|---|
| Cardinalidad ideal | Alta (muchos valores distintos) | Baja (pocos valores distintos) |
| Tamaño | Mayor | Muy compacto |
| Combinación de condiciones | Una a una | Muy eficiente con AND/OR de bits |
| DML concurrente | Bueno | Malo (bloqueos amplios) |
| Entorno típico | OLTP | Data warehouse |
Indexa NULL | No (si todas las columnas son nulas) | Sí |
7.6. Índices invisibles
Un índice invisible (11g) se mantiene con cada INSERT/UPDATE/DELETE, pero el optimizador lo ignora. Sirve para probar el efecto de quitar un índice sin borrarlo (recrearlo en una tabla enorme puede llevar horas), o para crear un índice y probarlo antes de “activarlo” para todos.
ALTER INDEX ix_ventas_cli_fecha INVISIBLE;
SELECT index_name, visibility FROM user_indexes WHERE table_name = 'VENTAS_DEMO';
EXPLAIN PLAN FOR
SELECT * FROM ventas_demo WHERE cliente_id = 500;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(NULL, NULL, 'BASIC +ROWS +COST'));
Índice modificado.
INDEX_NAME VISIBILITY
---------------------- ----------
PK_VENTAS_DEMO VISIBLE
IX_VENTAS_CLI_FECHA INVISIBLE
BIX_VENTAS_ESTADO VISIBLE
3 filas seleccionadas.
--------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)|
--------------------------------------------------------------
| 0 | SELECT STATEMENT | | 200 | 1061 (1)|
| 1 | TABLE ACCESS FULL| VENTAS_DEMO | 200 | 1061 (1)|
--------------------------------------------------------------
Una sesión concreta puede pedir que se tengan en cuenta los índices invisibles:
ALTER SESSION SET optimizer_use_invisible_indexes = TRUE;
EXPLAIN PLAN FOR
SELECT * FROM ventas_demo WHERE cliente_id = 500;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(NULL, NULL, 'BASIC +ROWS +COST'));
ALTER SESSION SET optimizer_use_invisible_indexes = FALSE;
ALTER INDEX ix_ventas_cli_fecha VISIBLE;
Sesión modificada.
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)|
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 200 | 203 (0)|
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| VENTAS_DEMO | 200 | 203 (0)|
| 2 | INDEX RANGE SCAN | IX_VENTAS_CLI_FECHA | 200 | 3 (0)|
----------------------------------------------------------------------------------------
Sesión modificada.
Índice modificado.
7.7. Mantenimiento y borrado de índices
DROP INDEX pk_ventas_demo; -- índice de una PK
CREATE INDEX ix_dup ON personas_demo (apellidos); -- ya indexada
ALTER INDEX ix_per_apellidos RENAME TO ix_personas_apellidos;
ALTER INDEX ix_personas_apellidos REBUILD; -- reconstruir
DROP INDEX ix_personas_apellidos;
DROP INDEX IF EXISTS ix_no_existe; -- 23ai
DROP INDEX pk_ventas_demo
*
ERROR en línea 1:
ORA-02429: cannot drop index used for enforcement of unique/primary key
CREATE INDEX ix_dup ON personas_demo (apellidos)
*
ERROR en línea 1:
ORA-01408: such column list already indexed
Índice modificado.
Índice modificado.
Índice borrado.
Índice borrado.
- El índice de una PK o UNIQUE no se borra directamente: se borra (o deshabilita) la restricción (módulo 25).
- Oracle no permite dos índices B-tree normales sobre exactamente la misma lista de columnas.
- Borrar un índice no afecta a los datos: solo puede hacer que algunas consultas vayan más lentas.
7.8. Cuándo crear índices y cuándo NO
Crea un índice cuando:
- La columna se usa a menudo en
WHEREcon condiciones selectivas (devuelven un pequeño porcentaje de filas, típicamente menos del 5-10 %). - La columna se usa en combinaciones (
JOIN ... ON): especialmente las claves ajenas. - La columna tiene muchos valores distintos (alta cardinalidad) y la tabla es grande.
- Se ordena (
ORDER BY) o agrupa frecuentemente por ella y se necesita solo una parte de las filas. - Hay que garantizar unicidad (aunque mejor con una restricción).
NO crees un índice cuando:
- La tabla es pequeña (unos pocos bloques): leerla entera es más barato.
- Las consultas devuelven una gran parte de la tabla.
- La columna tiene muy pocos valores distintos y la tabla se modifica mucho (en OLTP, ni B-tree ni bitmap).
- La tabla sufre muchas inserciones, modificaciones y borrados y se consulta poco: cada índice ralentiza el DML, porque hay que mantenerlo en cada operación, y ocupa espacio.
- La columna solo se usa dentro de expresiones (salvo que crees el índice basado en función correspondiente).
- Ya existe un índice compuesto que empieza por esa columna.
💡 Consejo: Los índices no se crean “por si acaso”. Se crean para consultas concretas y se comprueba su efecto con el plan de ejecución. Un buen punto de partida en cualquier esquema: índices en todas las claves ajenas de las tablas grandes y en las columnas de búsqueda habituales (NIF, email, fecha de pedido…).
📘 Concepto: El factor de agrupamiento (clustering factor,
USER_INDEXES.CLUSTERING_FACTOR) mide lo ordenada que está la tabla respecto al índice. Si las filas con valores consecutivos del índice están en los mismos bloques, el factor es bajo (cercano al número de bloques) y el índice es muy eficiente en rangos. Enventas_demo, la PK tiene un factor de 3839 (filas guardadas en orden deventa_id) y el índice por cliente, 200.000 (cada venta de un cliente está en un bloque distinto). Por eso el optimizador a veces descarta un índice aunque la condición parezca selectiva.
8. Secuencias
8.1. Concepto y sintaxis
📘 Concepto: Una secuencia (sequence) es un objeto que genera números enteros únicos de forma eficiente y segura en concurrencia: aunque cien sesiones pidan un número a la vez, cada una recibe uno distinto. Se usan sobre todo para generar claves primarias artificiales (surrogate keys). La secuencia es independiente de las tablas: la misma secuencia puede usarse en varias tablas, o en ninguna.
CREATE SEQUENCE [esquema.]nombre_secuencia
[START WITH n]
[INCREMENT BY n]
[MINVALUE n | NOMINVALUE]
[MAXVALUE n | NOMAXVALUE]
[CYCLE | NOCYCLE]
[CACHE n | NOCACHE]
[ORDER | NOORDER];
| Opción | Significado | Por defecto |
|---|---|---|
START WITH | Primer valor generado | MINVALUE (ascendente) o MAXVALUE (descendente) |
INCREMENT BY | Salto entre valores; negativo para secuencias descendentes; nunca 0 | 1 |
MINVALUE | Valor mínimo | 1 (ascendente) |
MAXVALUE | Valor máximo | 10^28 − 1 (ascendente) |
CYCLE | Al llegar al límite, vuelve a empezar desde MINVALUE (o MAXVALUE si es descendente) | NOCYCLE: da error al agotarse |
CACHE n | Oracle reserva en memoria los n siguientes valores para servirlos más rápido | CACHE 20 |
NOCACHE | Cada valor se obtiene actualizando el diccionario (más lento) | — |
ORDER | Garantiza que los valores se entregan en orden de petición (solo relevante en Oracle RAC, con varias instancias) | NOORDER |
8.2. NEXTVAL y CURRVAL
Las secuencias se usan a través de dos pseudocolumnas:
secuencia.NEXTVAL: incrementa la secuencia y devuelve el nuevo valor.secuencia.CURRVAL: devuelve el último valor obtenido con NEXTVAL en tu sesión.
CREATE SEQUENCE seq_pedidos
START WITH 5025
INCREMENT BY 1
NOCACHE;
SELECT seq_pedidos.CURRVAL FROM dual;
SELECT seq_pedidos.NEXTVAL FROM dual;
SELECT seq_pedidos.CURRVAL FROM dual;
Secuencia creada.
SELECT seq_pedidos.CURRVAL FROM dual
*
ERROR en línea 1:
ORA-08002: Sequence SEQ_PEDIDOS.CURRVAL is not yet defined in this session.
NEXTVAL
----------
5025
1 fila seleccionada.
CURRVAL
----------
5025
1 fila seleccionada.
CURRVAL no se puede usar en una sesión hasta que se ha hecho al menos un NEXTVAL: no es “el último valor de la secuencia” (que otra sesión podría haber cambiado), sino el tuyo. Dentro de una misma fila, todos los NEXTVAL devuelven el mismo valor:
SELECT seq_pedidos.NEXTVAL AS a, seq_pedidos.NEXTVAL AS b, seq_pedidos.CURRVAL AS c FROM dual;
A B C
---------- ---------- ----------
5026 5026 5026
1 fila seleccionada.
El uso típico: NEXTVAL para la clave de la fila padre y CURRVAL para las hijas:
INSERT INTO pedidos (pedido_id, cliente_id, empleado_id, fecha_pedido, estado)
VALUES (seq_pedidos.NEXTVAL, 1013, 110, DATE '2026-09-15', 'PENDIENTE');
INSERT INTO lineas_pedido (pedido_id, num_linea, producto_id, cantidad, precio_unitario)
VALUES (seq_pedidos.CURRVAL, 1, 6, 3, 19.90);
SELECT p.pedido_id, p.cliente_id, l.num_linea, l.producto_id
FROM pedidos p
JOIN lineas_pedido l ON l.pedido_id = p.pedido_id
WHERE p.pedido_id > 5024;
1 fila creada.
1 fila creada.
PEDIDO_ID CLIENTE_ID NUM_LINEA PRODUCTO_ID
---------- ---------- ---------- -----------
5027 1013 1 6
1 fila seleccionada.
8.3. Las secuencias tienen huecos
ROLLBACK;
SELECT seq_pedidos.NEXTVAL FROM dual;
SELECT sequence_name, min_value, max_value, increment_by, cycle_flag, cache_size, last_number
FROM user_sequences;
Rollback terminado.
NEXTVAL
----------
5028
1 fila seleccionada.
SEQUENCE_NAME MIN_VALUE MAX_VALUE INCREMENT_BY C CACHE_SIZE LAST_NUMBER
---------------- ---------- ---------- ------------ - ---------- -----------
SEQ_PEDIDOS 1 1,0000E+28 1 N 0 5029
1 fila seleccionada.
El ROLLBACK deshizo el pedido 5027, pero la secuencia no retrocede: el siguiente número es 5028. El 5027 (y el 5026, que pedimos con un SELECT) se han perdido.
⚠️ Importante: Las secuencias no garantizan números consecutivos. Habrá huecos por transacciones deshechas, por valores pedidos y no usados, y por los valores en caché que se pierden al reiniciar la instancia (con
CACHE 20, hasta 20 números). Esto es correcto: una clave primaria solo debe ser única, no consecutiva. Si la ley exige numeración correlativa sin huecos (por ejemplo, el número de factura), no uses una secuencia: hay que generar el número con otros mecanismos y serializando las transacciones.
8.4. Límites, ciclos y secuencias descendentes
CREATE SEQUENCE seq_turno START WITH 1 INCREMENT BY 1 MINVALUE 1 MAXVALUE 3 CYCLE NOCACHE;
SELECT emp_id, seq_turno.NEXTVAL AS turno FROM empleados WHERE emp_id <= 104;
Secuencia creada.
EMP_ID TURNO
---------- ----------
100 1
101 2
102 3
103 1
104 2
5 filas seleccionadas.
Una secuencia con CYCLE sirve para asignar turnos rotatorios, pero nunca para claves primarias (repetiría valores).
CREATE SEQUENCE seq_limitada START WITH 1 MAXVALUE 2 NOCYCLE NOCACHE;
SELECT seq_limitada.NEXTVAL FROM dual;
SELECT seq_limitada.NEXTVAL FROM dual;
SELECT seq_limitada.NEXTVAL FROM dual;
Secuencia creada.
NEXTVAL
----------
1
1 fila seleccionada.
NEXTVAL
----------
2
1 fila seleccionada.
SELECT seq_limitada.NEXTVAL FROM dual
*
ERROR en línea 1:
ORA-08004: Sequence SEQ_LIMITADA.NEXTVAL exceeds MAXVALUE and cannot be instantiated.
CREATE SEQUENCE seq_cuenta_atras START WITH 10 INCREMENT BY -3 MINVALUE 1 MAXVALUE 10 NOCACHE;
SELECT seq_cuenta_atras.NEXTVAL FROM empleados WHERE emp_id <= 103;
CREATE SEQUENCE seq_mal START WITH 1 MAXVALUE 10 CYCLE CACHE 20;
Secuencia creada.
NEXTVAL
----------
10
7
4
1
4 filas seleccionadas.
CREATE SEQUENCE seq_mal START WITH 1 MAXVALUE 10 CYCLE CACHE 20
*
ERROR en línea 1:
ORA-04013: The CACHE value may not exceed the CYCLE value.
Una secuencia cíclica no puede tener en caché más valores de los que tiene un ciclo.
8.5. Modificar y borrar secuencias
ALTER SEQUENCE admite cambiar cualquier opción salvo START WITH:
ALTER SEQUENCE seq_pedidos INCREMENT BY 10;
SELECT seq_pedidos.NEXTVAL FROM dual;
ALTER SEQUENCE seq_pedidos START WITH 6000;
ALTER SEQUENCE seq_pedidos RESTART START WITH 6000 INCREMENT BY 1;
SELECT seq_pedidos.NEXTVAL FROM dual;
ALTER SEQUENCE seq_pedidos MAXVALUE 5000;
ALTER SEQUENCE seq_pedidos CACHE 20;
SELECT sequence_name, increment_by, cache_size, last_number
FROM user_sequences
WHERE sequence_name = 'SEQ_PEDIDOS';
Secuencia modificada.
NEXTVAL
----------
5038
1 fila seleccionada.
ALTER SEQUENCE seq_pedidos START WITH 6000
*
ERROR en línea 1:
ORA-02283: cannot alter starting sequence number
Secuencia modificada.
NEXTVAL
----------
6000
1 fila seleccionada.
ALTER SEQUENCE seq_pedidos MAXVALUE 5000
*
ERROR en línea 1:
ORA-04009: MAXVALUE cannot be made to be less than the current value
Secuencia modificada.
SEQUENCE_NAME INCREMENT_BY CACHE_SIZE LAST_NUMBER
---------------- ------------ ---------- -----------
SEQ_PEDIDOS 1 20 6001
1 fila seleccionada.
RESTART(documentada desde Oracle 18c) reinicia la secuencia en un valor. En versiones anteriores, la forma clásica era borrar y volver a crear la secuencia (perdiendo los privilegios concedidos).LAST_NUMBERenUSER_SEQUENCESes el último número guardado en el diccionario; con caché, puede ir por delante del último valor realmente entregado.
Sin opciones, una secuencia empieza en 1, incrementa de 1 en 1, no cicla y tiene una caché de 20:
CREATE SEQUENCE seq_defecto;
SELECT sequence_name, min_value, max_value, increment_by, cycle_flag, order_flag, cache_size
FROM user_sequences
WHERE sequence_name = 'SEQ_DEFECTO';
Secuencia creada.
SEQUENCE_NAME MIN_VALUE MAX_VALUE INCREMENT_BY C O CACHE_SIZE
---------------- ---------- ---------- ------------ - - ----------
SEQ_DEFECTO 1 1,0000E+28 1 N N 20
1 fila seleccionada.
Para borrarla: DROP SEQUENCE seq_defecto; (o DROP SEQUENCE IF EXISTS ... en 23ai).
8.6. Secuencia como valor por defecto de una columna
Desde Oracle 12c, una columna puede tener DEFAULT secuencia.NEXTVAL:
CREATE SEQUENCE seq_incidencias;
CREATE TABLE incidencias (
incidencia_id NUMBER(8) DEFAULT seq_incidencias.NEXTVAL
CONSTRAINT pk_incidencias PRIMARY KEY,
descripcion VARCHAR2(100) CONSTRAINT nn_inc_descripcion NOT NULL
);
INSERT INTO incidencias (descripcion) VALUES ('Impresora sin tóner');
INSERT INTO incidencias (descripcion) VALUES ('Portátil no arranca');
INSERT INTO incidencias (incidencia_id, descripcion) VALUES (100, 'Alta manual');
SELECT * FROM incidencias;
SELECT column_name, data_default
FROM user_tab_columns
WHERE table_name = 'INCIDENCIAS' AND column_name = 'INCIDENCIA_ID';
Secuencia creada.
Tabla creada.
1 fila creada.
1 fila creada.
1 fila creada.
INCIDENCIA_ID DESCRIPCION
------------- --------------------------
1 Impresora sin tóner
2 Portátil no arranca
100 Alta manual
3 filas seleccionadas.
COLUMN_NAME DATA_DEFAULT
-------------- ------------------------------------
INCIDENCIA_ID "CURSO"."SEQ_INCIDENCIAS"."NEXTVAL"
1 fila seleccionada.
El valor por defecto solo actúa si se omite la columna: se puede indicar un valor manualmente (100), lo que en el futuro puede provocar un choque cuando la secuencia llegue a 100. Y la tabla depende de una secuencia que alguien puede borrar:
DROP SEQUENCE seq_incidencias;
INSERT INTO incidencias (descripcion) VALUES ('Tras borrar la secuencia');
Secuencia borrada.
INSERT INTO incidencias (descripcion) VALUES ('Tras borrar la secuencia')
*
ERROR en línea 1:
ORA-02289: sequence does not exist
8.7. Comparación con las columnas identidad
Las columnas identidad (GENERATED ... AS IDENTITY, Oracle 12c, módulo 23) resuelven estos problemas: Oracle crea y gestiona una secuencia interna ligada a la columna.
CREATE TABLE incidencias_id (
incidencia_id NUMBER(8) GENERATED ALWAYS AS IDENTITY
CONSTRAINT pk_incidencias_id PRIMARY KEY,
descripcion VARCHAR2(100) CONSTRAINT nn_incid_descripcion NOT NULL
);
INSERT INTO incidencias_id (descripcion) VALUES ('Impresora sin tóner');
INSERT INTO incidencias_id (incidencia_id, descripcion) VALUES (100, 'Alta manual');
SELECT * FROM incidencias_id;
SELECT table_name, column_name, generation_type, sequence_name FROM user_tab_identity_cols;
Tabla creada.
1 fila creada.
INSERT INTO incidencias_id (incidencia_id, descripcion) VALUES (100, 'Alta manual')
*
ERROR en línea 1:
ORA-32795: cannot insert into a generated always identity column
INCIDENCIA_ID DESCRIPCION
------------- --------------------------
1 Impresora sin tóner
1 fila seleccionada.
TABLE_NAME COLUMN_NAME GENERATION SEQUENCE_NAME
---------------- -------------- ---------- ----------------
INCIDENCIAS_ID INCIDENCIA_ID ALWAYS ISEQ$$_74169
1 fila seleccionada.
La secuencia interna ISEQ$$_nnnnn aparece en USER_SEQUENCES, pero no se puede borrar directamente (ORA-32794: cannot drop a system-generated sequence): desaparece al borrar la tabla o la propiedad identidad.
Secuencia + DEFAULT seq.NEXTVAL | Columna identidad | |
|---|---|---|
| Versión | 12c (secuencias desde siempre) | 12c |
| Objeto independiente | Sí, se gestiona aparte | No, va ligada a la columna |
| Compartible entre tablas | Sí | No |
| Se puede borrar por error | Sí (rompe los INSERT) | No |
| Impedir valores manuales | No | Sí, con GENERATED ALWAYS |
| Valores manuales permitidos | Siempre | Con BY DEFAULT o BY DEFAULT ON NULL |
| Acceso al valor generado | CURRVAL | RETURNING en el INSERT (desde aplicaciones o PL/SQL) |
| Huecos | Sí | Sí (usa una secuencia por debajo) |
| Recomendación actual | Numeraciones compartidas o con lógica especial | Claves primarias artificiales de tablas nuevas |
9. Sinónimos
📘 Concepto: Un sinónimo (synonym) es un alias de otro objeto (tabla, vista, secuencia, procedimiento, otro sinónimo…). Sirve para abreviar nombres largos, para ocultar el propietario o la ubicación real de un objeto (incluso en otra base de datos, mediante un database link) y para dar independencia a las aplicaciones: si el objeto cambia de esquema, basta con redefinir el sinónimo.
- Sinónimo privado: pertenece a un esquema y lo usa su propietario (u otros usuarios cualificándolo:
curso.emp). - Sinónimo público (
PUBLIC): pertenece al grupo especialPUBLICy lo ve todo el mundo sin cualificar. Requiere el privilegioCREATE PUBLIC SYNONYM, propio de administradores.
CREATE [OR REPLACE] [PUBLIC] SYNONYM nombre FOR [esquema.]objeto[@dblink];
DROP [PUBLIC] SYNONYM nombre;
CREATE SYNONYM emp FOR empleados;
CREATE SYNONYM lp FOR lineas_pedido;
SELECT emp_id, nombre FROM emp WHERE dept_id = 50;
SELECT COUNT(*) FROM lp;
SELECT synonym_name, table_owner, table_name, db_link FROM user_synonyms;
Sinónimo creado.
Sinónimo creado.
EMP_ID NOMBRE
---------- --------------------
104 Laura
140 Nuria
141 Álvaro
3 filas seleccionadas.
COUNT(*)
----------
51
1 fila seleccionada.
SYNONYM_NAME TABLE_OWNE TABLE_NAME DB_LINK
-------------- ---------- ---------------- --------
EMP CURSO EMPLEADOS (nulo)
LP CURSO LINEAS_PEDIDO (nulo)
2 filas seleccionadas.
Un usuario normal no puede crear sinónimos públicos; un sinónimo puede apuntar a un objeto que no existe (y fallará al usarse); y OR REPLACE permite redirigirlo:
CREATE PUBLIC SYNONYM empleados_pub FOR curso.empleados;
CREATE SYNONYM fantasma FOR tabla_inexistente;
SELECT * FROM fantasma;
CREATE OR REPLACE SYNONYM emp FOR departamentos;
SELECT dept_id, nombre FROM emp WHERE dept_id = 10;
DROP SYNONYM emp;
DROP SYNONYM lp;
DROP SYNONYM fantasma;
CREATE PUBLIC SYNONYM empleados_pub FOR curso.empleados
*
ERROR en línea 1:
ORA-01031: insufficient privileges
Sinónimo creado.
SELECT * FROM fantasma
*
ERROR en línea 1:
ORA-00980: synonym translation is no longer valid
Sinónimo creado.
DEPT_ID NOMBRE
---------- --------------------
10 DIRECCIÓN
1 fila seleccionada.
Sinónimo borrado.
Sinónimo borrado.
Sinónimo borrado.
Usas sinónimos públicos constantemente sin saberlo: DUAL, USER_TABLES o DBMS_XPLAN son sinónimos públicos de objetos del usuario SYS:
SELECT owner, synonym_name, table_owner, table_name
FROM all_synonyms
WHERE synonym_name IN ('DUAL', 'DBMS_XPLAN', 'USER_TABLES');
OWNER SYNONYM_NAME TABLE_OWNE TABLE_NAME
-------- -------------- ---------- ----------------
PUBLIC DUAL SYS DUAL
PUBLIC USER_TABLES SYS USER_TABLES
PUBLIC DBMS_XPLAN SYS DBMS_XPLAN
3 filas seleccionadas.
⚠️ Importante: Un sinónimo no concede permisos. Si creas un sinónimo hacia una tabla de otro usuario, necesitas además el privilegio de objeto correspondiente (
SELECT,INSERT…) sobre la tabla. Lo verás en el módulo 37. Cuando Oracle resuelve un nombre, busca primero un objeto o sinónimo privado en tu esquema y, si no lo encuentra, un sinónimo público.
10. El diccionario de datos
📘 Concepto: El diccionario de datos (data dictionary) es el conjunto de tablas y vistas de solo lectura, propiedad de
SYS, en las que Oracle guarda los metadatos: qué tablas, columnas, restricciones, índices, usuarios, privilegios… existen. Es el catálogo del modelo relacional (la regla 4 de Codd, módulo 16, exige que la descripción de la base de datos se pueda consultar con el mismo lenguaje que los datos). Cada sentencia DDL actualiza el diccionario; nunca se modifica directamente.
10.1. Las cuatro familias de vistas
| Prefijo | Contenido | Quién puede consultarlas |
|---|---|---|
USER_ | Objetos de tu esquema (no tienen columna OWNER) | Todos los usuarios |
ALL_ | Objetos a los que tienes acceso (tuyos y de otros con privilegios); tienen columna OWNER | Todos los usuarios |
DBA_ | Todos los objetos de la base de datos | Administradores (privilegio SELECT ANY DICTIONARY o rol SELECT_CATALOG_ROLE) |
V$ | Vistas dinámicas de rendimiento: estado de la instancia en memoria (sesiones, bloqueos, SQL en ejecución, parámetros…) | Administradores, salvo algunas como V$VERSION |
La vista DICTIONARY (sinónimo DICT) es el índice del diccionario: lista todas las vistas con una descripción.
SELECT COUNT(*) FROM dictionary;
SELECT CASE
WHEN table_name LIKE 'USER\_%' ESCAPE '\' THEN 'USER_'
WHEN table_name LIKE 'ALL\_%' ESCAPE '\' THEN 'ALL_'
WHEN table_name LIKE 'DBA\_%' ESCAPE '\' THEN 'DBA_'
WHEN table_name LIKE 'V$%' THEN 'V$'
WHEN table_name LIKE 'GV$%' THEN 'GV$'
ELSE 'otras'
END AS familia,
COUNT(*) AS vistas
FROM dictionary
GROUP BY CASE
WHEN table_name LIKE 'USER\_%' ESCAPE '\' THEN 'USER_'
WHEN table_name LIKE 'ALL\_%' ESCAPE '\' THEN 'ALL_'
WHEN table_name LIKE 'DBA\_%' ESCAPE '\' THEN 'DBA_'
WHEN table_name LIKE 'V$%' THEN 'V$'
WHEN table_name LIKE 'GV$%' THEN 'GV$'
ELSE 'otras'
END
ORDER BY vistas DESC;
COUNT(*)
----------
5752
1 fila seleccionada.
FAMILIA VISTAS
-------- ----------
otras 1385
DBA_ 1273
V$ 1022
GV$ 874
USER_ 618
ALL_ 580
6 filas seleccionadas.
GV$ son las versiones “globales” de las V$ para entornos con varias instancias (RAC). En “otras” están, entre otras, las vistas CDB_ (todo el contenedor multitenant) y sinónimos como DICT, TABS o COLS.
Como usuario sin privilegios de administración, las vistas DBA_ y la mayoría de las V$ no son accesibles:
SELECT COUNT(*) FROM dba_tables;
SELECT COUNT(*) FROM v$session;
SELECT banner_full FROM v$version;
SELECT COUNT(*) FROM dba_tables
*
ERROR en línea 1:
ORA-00942: table or view "SYS"."DBA_TABLES" does not exist
SELECT COUNT(*) FROM v$session
*
ERROR en línea 1:
ORA-00942: table or view "SYS"."V_$SESSION" does not exist
BANNER_FULL
------------------------------------------------------------------------------------------------------------------------
Oracle AI Database 26ai Free Release 23.26.3.0.0 - Develop, Learn, and Run for Free
Version 23.26.3.0.0
1 fila seleccionada.
💡 Consejo: Fíjate en que el error dice “does not exist”, no “permiso denegado”: Oracle no revela la existencia de objetos a quien no tiene privilegios sobre ellos.
V$SESSIONes en realidad un sinónimo público de la vistaSYS.V_$SESSION.
ALL_TABLES muestra también tablas de otros esquemas a las que tenemos acceso:
SELECT owner, COUNT(*) AS tablas
FROM all_tables
GROUP BY owner
ORDER BY 2 DESC
FETCH FIRST 5 ROWS ONLY;
OWNER TABLAS
-------- ----------
MDSYS 58
SYS 46
CURSO 11
CTXSYS 8
XDB 6
5 filas seleccionadas.
(En ese momento CURSO tenía las 9 tablas de EMPRESA más ventas_demo y personas_demo.)
10.2. Las vistas más útiles
SELECT table_name, comments
FROM dictionary
WHERE table_name IN ('USER_TABLES', 'USER_TAB_COLUMNS', 'USER_CONSTRAINTS', 'USER_CONS_COLUMNS',
'USER_INDEXES', 'USER_IND_COLUMNS', 'USER_VIEWS', 'USER_SEQUENCES',
'USER_OBJECTS', 'DICTIONARY')
ORDER BY table_name;
TABLE_NAME COMMENTS
-------------------- --------------------------------------------------------------
DICTIONARY Description of data dictionary tables and views
USER_CONS_COLUMNS Information about accessible columns in constraint definitions
USER_CONSTRAINTS Constraint definitions on user's own tables
USER_IND_COLUMNS COLUMNs comprising user's INDEXes and INDEXes on user's TABLES
USER_INDEXES Description of the user's own indexes
USER_OBJECTS Objects owned by the user
USER_SEQUENCES Description of the user's own SEQUENCEs
USER_TAB_COLUMNS Columns of user's tables, views and clusters
USER_TABLES Description of the user's own relational tables
USER_VIEWS Description of the user's own views
10 filas seleccionadas.
| Vista | Contenido | Columnas más útiles |
|---|---|---|
DICTIONARY | Todas las vistas del diccionario | TABLE_NAME, COMMENTS |
USER_OBJECTS | Todos tus objetos | OBJECT_NAME, OBJECT_TYPE, STATUS, CREATED, LAST_DDL_TIME |
USER_TABLES (TABS) | Tus tablas | TABLE_NAME, TABLESPACE_NAME, NUM_ROWS, BLOCKS, LAST_ANALYZED, READ_ONLY |
USER_TAB_COLUMNS (COLS) | Columnas de tus tablas y vistas | COLUMN_NAME, DATA_TYPE, DATA_LENGTH, DATA_PRECISION, DATA_SCALE, NULLABLE, DATA_DEFAULT, COLUMN_ID |
USER_CONSTRAINTS | Restricciones | CONSTRAINT_NAME, CONSTRAINT_TYPE (P, U, R, C, V, O), TABLE_NAME, SEARCH_CONDITION, R_CONSTRAINT_NAME, DELETE_RULE, STATUS, VALIDATED |
USER_CONS_COLUMNS | Columnas de cada restricción | CONSTRAINT_NAME, TABLE_NAME, COLUMN_NAME, POSITION |
USER_INDEXES | Índices | INDEX_NAME, TABLE_NAME, INDEX_TYPE, UNIQUENESS, STATUS, VISIBILITY |
USER_IND_COLUMNS | Columnas de cada índice | INDEX_NAME, COLUMN_NAME, COLUMN_POSITION, DESCEND |
USER_IND_EXPRESSIONS | Expresiones de los índices basados en función | INDEX_NAME, COLUMN_EXPRESSION |
USER_VIEWS | Vistas | VIEW_NAME, TEXT |
USER_UPDATABLE_COLUMNS | Qué columnas de las vistas admiten DML | UPDATABLE, INSERTABLE, DELETABLE |
USER_MVIEWS | Vistas materializadas | MVIEW_NAME, REFRESH_MODE, REFRESH_METHOD, STALENESS, LAST_REFRESH_DATE |
USER_SEQUENCES | Secuencias | SEQUENCE_NAME, MIN_VALUE, MAX_VALUE, INCREMENT_BY, CYCLE_FLAG, CACHE_SIZE, LAST_NUMBER |
USER_SYNONYMS | Sinónimos | SYNONYM_NAME, TABLE_OWNER, TABLE_NAME, DB_LINK |
USER_TAB_COMMENTS / USER_COL_COMMENTS | Comentarios (módulo 25) | COMMENTS |
USER_TAB_IDENTITY_COLS | Columnas identidad | GENERATION_TYPE, SEQUENCE_NAME |
USER_RECYCLEBIN (RECYCLEBIN) | Papelera (módulo 25) | OBJECT_NAME, ORIGINAL_NAME, DROPTIME |
USER_USERS | Tu usuario | USERNAME, DEFAULT_TABLESPACE, TEMPORARY_TABLESPACE |
Las vistas de privilegios (USER_SYS_PRIVS, USER_TAB_PRIVS, SESSION_PRIVS…) se estudian en el módulo 37.
10.3. Consultas útiles para documentar un esquema
Inventario de restricciones por tabla (separando las NOT NULL, que también son de tipo C, gracias al convenio de nombres nn_):
SELECT table_name AS tabla,
COUNT(CASE constraint_type WHEN 'P' THEN 1 END) AS n_pk,
COUNT(CASE constraint_type WHEN 'U' THEN 1 END) AS n_uq,
COUNT(CASE constraint_type WHEN 'R' THEN 1 END) AS n_fk,
COUNT(CASE WHEN constraint_type = 'C'
AND constraint_name NOT LIKE 'NN\_%' ESCAPE '\' THEN 1 END) AS n_ck,
COUNT(CASE WHEN constraint_type = 'C'
AND constraint_name LIKE 'NN\_%' ESCAPE '\' THEN 1 END) AS n_nn
FROM user_constraints
WHERE table_name IN (SELECT table_name FROM user_tables)
GROUP BY table_name
ORDER BY table_name;
TABLA N_PK N_UQ N_FK N_CK N_NN
-------------- ---------- ---------- ---------- ---------- ----------
ASIGNACIONES 1 0 2 1 1
CATEGORIAS 1 1 1 0 1
CLIENTES 1 1 1 1 3
DEPARTAMENTOS 1 1 0 1 1
EMPLEADOS 1 1 2 2 6
LINEAS_PEDIDO 1 0 2 3 4
PEDIDOS 1 0 2 2 3
PRODUCTOS 1 0 1 3 5
PROYECTOS 1 1 1 2 2
VENTAS_DEMO 1 0 0 0 0
10 filas seleccionadas.
Ficha de columnas de una tabla, marcando las de la clave primaria:
SELECT c.column_name AS columna,
c.data_type ||
CASE
WHEN c.data_type IN ('VARCHAR2', 'CHAR') THEN '(' || c.char_length || ')'
WHEN c.data_type = 'NUMBER' AND c.data_scale > 0
THEN '(' || c.data_precision || ',' || c.data_scale || ')'
WHEN c.data_type = 'NUMBER' THEN '(' || c.data_precision || ')'
END AS tipo,
c.nullable AS n,
(SELECT 'PK'
FROM user_constraints k
JOIN user_cons_columns kc ON kc.constraint_name = k.constraint_name
WHERE k.table_name = c.table_name AND k.constraint_type = 'P'
AND kc.column_name = c.column_name) AS clave
FROM user_tab_columns c
WHERE c.table_name = 'LINEAS_PEDIDO'
ORDER BY c.column_id;
COLUMNA TIPO N CLAVE
------------------ -------------- - ------
PEDIDO_ID NUMBER(8) N PK
NUM_LINEA NUMBER(3) N PK
PRODUCTO_ID NUMBER(6) N (nulo)
CANTIDAD NUMBER(5) N (nulo)
PRECIO_UNITARIO NUMBER(8,2) N (nulo)
DESCUENTO NUMBER(4,2) N (nulo)
6 filas seleccionadas.
Reglas de negocio (restricciones CHECK que no son NOT NULL):
SELECT constraint_name, search_condition
FROM user_constraints
WHERE table_name = 'PEDIDOS' AND constraint_type = 'C'
AND constraint_name NOT LIKE 'NN\_%' ESCAPE '\'
ORDER BY constraint_name;
CONSTRAINT_NAME SEARCH_CONDITION
---------------- ----------------------------------------------------------
CK_PED_ESTADO estado IN ('PENDIENTE', 'ENVIADO', 'ENTREGADO', 'CANCELADO
')
CK_PED_FECHAS fecha_envio >= fecha_pedido
2 filas seleccionadas.
Grafo de claves ajenas (hija → padre): la consulta clave para reconstruir el esquema relacional a partir del diccionario. Une cada FK con la PK a la que apunta (R_CONSTRAINT_NAME) y empareja sus columnas por posición:
SELECT fk.table_name AS tabla,
fc.column_name AS columna,
pk.table_name AS tabla_padre,
pc.column_name AS col_padre,
fk.delete_rule AS regla
FROM user_constraints fk
JOIN user_cons_columns fc ON fc.constraint_name = fk.constraint_name
JOIN user_constraints pk ON pk.constraint_name = fk.r_constraint_name
JOIN user_cons_columns pc ON pc.constraint_name = pk.constraint_name
AND pc.position = fc.position
WHERE fk.constraint_type = 'R'
ORDER BY fk.table_name, fc.column_name;
TABLA COLUMNA TABLA_PADRE COL_PADRE REGLA
-------------- ------------------ -------------- -------------- ---------
ASIGNACIONES EMP_ID EMPLEADOS EMP_ID NO ACTION
ASIGNACIONES PROYECTO_ID PROYECTOS PROYECTO_ID CASCADE
CATEGORIAS CATEGORIA_PADRE_ID CATEGORIAS CATEGORIA_ID NO ACTION
CLIENTES COMERCIAL_ID EMPLEADOS EMP_ID NO ACTION
EMPLEADOS DEPT_ID DEPARTAMENTOS DEPT_ID NO ACTION
EMPLEADOS JEFE_ID EMPLEADOS EMP_ID NO ACTION
LINEAS_PEDIDO PEDIDO_ID PEDIDOS PEDIDO_ID CASCADE
LINEAS_PEDIDO PRODUCTO_ID PRODUCTOS PRODUCTO_ID NO ACTION
PEDIDOS CLIENTE_ID CLIENTES CLIENTE_ID NO ACTION
PEDIDOS EMPLEADO_ID EMPLEADOS EMP_ID NO ACTION
PRODUCTOS CATEGORIA_ID CATEGORIAS CATEGORIA_ID NO ACTION
PROYECTOS DEPT_ID DEPARTAMENTOS DEPT_ID NO ACTION
12 filas seleccionadas.
Con este resultado puedes dibujar el grafo relacional de EMPRESA:
flowchart LR
ASIGNACIONES --> EMPLEADOS
ASIGNACIONES -- "CASCADE" --> PROYECTOS
CATEGORIAS --> CATEGORIAS
CLIENTES --> EMPLEADOS
EMPLEADOS --> DEPARTAMENTOS
EMPLEADOS --> EMPLEADOS
LINEAS_PEDIDO -- "CASCADE" --> PEDIDOS
LINEAS_PEDIDO --> PRODUCTOS
PEDIDOS --> CLIENTES
PEDIDOS --> EMPLEADOS
PRODUCTOS --> CATEGORIAS
PROYECTOS --> DEPARTAMENTOS
Claves ajenas sin índice (candidatas a indexar en tablas grandes):
SELECT fk.table_name AS tabla, fk.constraint_name AS restriccion, fc.column_name AS columna
FROM user_constraints fk
JOIN user_cons_columns fc ON fc.constraint_name = fk.constraint_name
WHERE fk.constraint_type = 'R'
AND NOT EXISTS (SELECT 1
FROM user_ind_columns ic
WHERE ic.table_name = fc.table_name
AND ic.column_name = fc.column_name
AND ic.column_position = fc.position)
ORDER BY fk.table_name, fc.column_name;
TABLA RESTRICCION COLUMNA
-------------- ------------------ ------------------
ASIGNACIONES FK_ASI_PROYECTO PROYECTO_ID
CATEGORIAS FK_CAT_PADRE CATEGORIA_PADRE_ID
CLIENTES FK_CLI_COMERCIAL COMERCIAL_ID
EMPLEADOS FK_EMP_DEPT DEPT_ID
EMPLEADOS FK_EMP_JEFE JEFE_ID
LINEAS_PEDIDO FK_LIN_PRODUCTO PRODUCTO_ID
PEDIDOS FK_PED_CLIENTE CLIENTE_ID
PEDIDOS FK_PED_EMPLEADO EMPLEADO_ID
PRODUCTOS FK_PRO_CATEGORIA CATEGORIA_ID
PROYECTOS FK_PRY_DEPT DEPT_ID
10 filas seleccionadas.
Faltan 2 de las 12 FK: asignaciones.emp_id y lineas_pedido.pedido_id están cubiertas porque son la primera columna de la PK compuesta de su tabla (pk_asignaciones, pk_lineas_pedido).
Inventario de objetos y objetos inválidos:
SELECT object_type, status, COUNT(*) AS total
FROM user_objects
GROUP BY object_type, status
ORDER BY object_type;
OBJECT_TYPE STATUS TOTAL
------------------ -------- ----------
INDEX VALID 19
TABLE VALID 11
VIEW VALID 6
3 filas seleccionadas.
💡 Consejo: Tras desplegar una migración (módulo 25), lanza
SELECT object_name, object_type FROM user_objects WHERE status = 'INVALID';. Una vista inválida es el síntoma típico de haber renombrado o borrado una columna que alguien usaba.
11. Ejercicios
Al terminar, borra los objetos que hayas creado (DROP VIEW, DROP INDEX, DROP SEQUENCE, DROP SYNONYM, DROP TABLE ... PURGE) o recarga EMPRESA con sus scripts. Los ejercicios 5 y 10 usan las tablas ventas_demo y personas_demo de las secciones 6 y 7.
Ejercicios resueltos
Ejercicio 1: Vista con datos de varias tablas
Crea la vista v_empleados_jefe con el código del empleado, su nombre completo, su oficio, el nombre de su departamento, el nombre completo de su jefe y su salario. Deben aparecer todos los empleados (incluidos la directora general, que no tiene jefe, y el becario, que no tiene departamento). ¿Qué columnas son actualizables?
Ver solución
CREATE OR REPLACE VIEW v_empleados_jefe AS
SELECT e.emp_id,
e.nombre || ' ' || e.apellidos AS empleado,
e.oficio,
d.nombre AS departamento,
j.nombre || ' ' || j.apellidos AS jefe,
e.salario
FROM empleados e
LEFT JOIN departamentos d ON d.dept_id = e.dept_id
LEFT JOIN empleados j ON j.emp_id = e.jefe_id;
SELECT emp_id, empleado, departamento, jefe
FROM v_empleados_jefe
WHERE emp_id IN (100, 121, 160)
ORDER BY emp_id;
SELECT column_name, updatable
FROM user_updatable_columns
WHERE table_name = 'V_EMPLEADOS_JEFE';
UPDATE v_empleados_jefe SET salario = salario + 50 WHERE emp_id = 160;
UPDATE v_empleados_jefe SET empleado = 'X' WHERE emp_id = 160;
ROLLBACK;
Vista creada.
EMP_ID EMPLEADO DEPARTAMENTO JEFE
---------- ------------------------ -------------- --------------------
100 Ana García López DIRECCIÓN
121 Raquel Molina Prieto INFORMÁTICA Pablo Moreno Herrera
160 Tomás Reyes Fuentes (nulo) Marta Sánchez Gil
3 filas seleccionadas.
COLUMN_NAME UPD
-------------- ---
EMP_ID YES
EMPLEADO NO
OFICIO YES
DEPARTAMENTO NO
JEFE NO
SALARIO YES
6 filas seleccionadas.
1 fila actualizada.
UPDATE v_empleados_jefe SET empleado = 'X' WHERE emp_id = 160
*
ERROR en línea 1:
ORA-01733: virtual column not allowed here
Rollback terminado.
Los LEFT JOIN conservan a los empleados sin departamento o sin jefe. Observa un detalle: el departamento del becario aparece como (nulo), pero el jefe de Ana aparece en blanco y no como (nulo). La expresión j.nombre || ' ' || j.apellidos con nombre y apellidos nulos no da NULL, sino un espacio: en Oracle, concatenar con NULL equivale a concatenar con la cadena vacía (módulo 28). Solo son actualizables las columnas simples de la tabla con clave preservada (e): emp_id, oficio y salario. empleado y jefe son expresiones (concatenaciones), y departamento pertenece a una tabla sin clave preservada.
Ejercicio 2: Seguridad por filas con WITH CHECK OPTION
La comercial Carmen (empleada 110) solo debe ver y gestionar sus clientes. Crea la vista v_clientes_carmen y comprueba que permite dar de alta un cliente suyo, pero no uno de otro comercial ni “regalar” un cliente suyo a otro.
Ver solución
CREATE OR REPLACE VIEW v_clientes_carmen AS
SELECT cliente_id, nombre, nif, ciudad, provincia, limite_credito, comercial_id
FROM clientes
WHERE comercial_id = 110
WITH CHECK OPTION CONSTRAINT ck_v_cli_carmen;
SELECT cliente_id, nombre, ciudad FROM v_clientes_carmen ORDER BY cliente_id;
INSERT INTO v_clientes_carmen
VALUES (1015, 'Ferretería Sol', 'B11223344', 'Valencia', 'Valencia', 3000, 110);
INSERT INTO v_clientes_carmen
VALUES (1016, 'Óptica Luna', 'B55667788', 'Valencia', 'Valencia', 3000, 111);
UPDATE v_clientes_carmen SET comercial_id = 112 WHERE cliente_id = 1001;
SELECT cliente_id, nombre, fecha_alta FROM clientes WHERE cliente_id = 1015;
ROLLBACK;
Vista creada.
CLIENTE_ID NOMBRE CIUDAD
---------- ---------------------------- ----------
1001 Talleres Martín S.L. Valencia
1002 Construcciones Levante S.A. Alicante
1012 Diseños Creativos S.L. Barcelona
3 filas seleccionadas.
1 fila creada.
INSERT INTO v_clientes_carmen VALUES (1016, 'Óptica Luna', 'B55667788', 'Valencia', 'Valencia', 3000, 111)
*
ERROR en línea 1:
ORA-01402: view WITH CHECK OPTION where-clause violation
UPDATE v_clientes_carmen SET comercial_id = 112 WHERE cliente_id = 1001
*
ERROR en línea 1:
ORA-01402: view WITH CHECK OPTION where-clause violation
CLIENTE_ID NOMBRE FECHA_ALTA
---------- ---------------------------- ----------
1015 Ferretería Sol 03/10/2026
1 fila seleccionada.
Rollback terminado.
El INSERT válido funciona aunque la vista no incluye fecha_alta, porque esa columna es NOT NULL pero tiene DEFAULT SYSDATE. En el módulo 37 verás cómo conceder a Carmen permisos solo sobre esta vista y no sobre la tabla.
Ejercicio 3: Vista de informe de solo lectura
Crea la vista de solo lectura v_facturacion_cliente con el número de pedidos no cancelados y el importe facturado (con descuentos) de cada cliente. Muestra los tres clientes que más han facturado y comprueba que la vista no admite borrados.
Ver solución
CREATE OR REPLACE VIEW v_facturacion_cliente AS
SELECT c.cliente_id,
c.nombre AS cliente,
COUNT(DISTINCT p.pedido_id) AS num_pedidos,
ROUND(SUM(l.cantidad * l.precio_unitario * (1 - l.descuento / 100)), 2) AS facturado
FROM clientes c
JOIN pedidos p ON p.cliente_id = c.cliente_id
JOIN lineas_pedido l ON l.pedido_id = p.pedido_id
WHERE p.estado <> 'CANCELADO'
GROUP BY c.cliente_id, c.nombre
WITH READ ONLY;
SELECT * FROM v_facturacion_cliente ORDER BY facturado DESC FETCH FIRST 3 ROWS ONLY;
DELETE FROM v_facturacion_cliente WHERE cliente_id = 1001;
Vista creada.
CLIENTE_ID CLIENTE NUM_PEDIDOS FACTURADO
---------- ---------------------------- ----------- ----------
1010 Instituto Tecnológico Sur 2 20602,55
1002 Construcciones Levante S.A. 3 14260,19
1004 Academia Nova 2 7891,3
3 filas seleccionadas.
DELETE FROM v_facturacion_cliente WHERE cliente_id = 1001
*
ERROR en línea 1:
ORA-01732: data manipulation operation not legal on this view
Hace falta COUNT(DISTINCT p.pedido_id) porque, tras combinar con las líneas, cada pedido aparece una vez por línea. El DELETE falla con ORA-01732 (y no con ORA-42399) porque Oracle detecta primero que la vista tiene GROUP BY: aunque no tuviera WITH READ ONLY, no sería actualizable. Aun así, WITH READ ONLY documenta la intención.
Ejercicio 4: Catálogo público
Crea la vista de solo lectura v_catalogo para la web: código, nombre del producto, nombre de su categoría y precio de los productos no descatalogados, sin mostrar el stock ni la fecha de alta. Comprueba que el stock no es accesible a través de la vista.
Ver solución
CREATE OR REPLACE VIEW v_catalogo AS
SELECT p.producto_id, p.nombre, c.nombre AS categoria, p.precio
FROM productos p
JOIN categorias c ON c.categoria_id = p.categoria_id
WHERE p.descatalogado = 'N'
WITH READ ONLY;
SELECT * FROM v_catalogo WHERE precio > 900 ORDER BY precio;
SELECT COUNT(*) FROM v_catalogo;
SELECT stock FROM v_catalogo;
Vista creada.
PRODUCTO_ID NOMBRE CATEGORIA PRECIO
----------- ---------------------------- ---------------------- ----------
12 Smartphone Gama Alta Telefonía 999
2 Portátil Gaming 16 Portátiles 1499
5 Torre Workstation Ordenadores sobremesa 1899
3 filas seleccionadas.
COUNT(*)
----------
19
1 fila seleccionada.
SELECT stock FROM v_catalogo
*
ERROR en línea 1:
ORA-00904: "STOCK": invalid identifier
La vista combina seguridad por columnas (oculta stock, descripcion, fecha_alta) y por filas (excluye el producto 14, descatalogado: quedan 19 de 20).
Ejercicio 5: El índice adecuado para una consulta
En ventas_demo (con el índice bitmap bix_ventas_estado de la sección 7.5) se lanza a menudo esta consulta: ventas enviadas de un día concreto. Comprueba su plan, quita el índice bitmap (en una tabla transaccional no debería estar), crea el índice B-tree adecuado y compara. Comprueba después si el índice sirve también para todas las ventas de un mes expresado con TRUNC o con un rango.
Ver solución
EXPLAIN PLAN FOR
SELECT venta_id, importe FROM ventas_demo
WHERE fecha = DATE '2025-03-15' AND estado = 'ENVIADO';
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(NULL, NULL, 'BASIC +ROWS +COST'));
DROP INDEX bix_ventas_estado;
CREATE INDEX ix_ventas_fecha_estado ON ventas_demo (fecha, estado);
EXPLAIN PLAN FOR
SELECT venta_id, importe FROM ventas_demo
WHERE fecha = DATE '2025-03-15' AND estado = 'ENVIADO';
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(NULL, NULL, 'BASIC +ROWS +COST +PREDICATE'));
--------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)|
--------------------------------------------------------------
| 0 | SELECT STATEMENT | | 137 | 1062 (1)|
| 1 | TABLE ACCESS FULL| VENTAS_DEMO | 137 | 1062 (1)|
--------------------------------------------------------------
Índice borrado.
Índice creado.
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)|
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 137 | 140 (0)|
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| VENTAS_DEMO | 137 | 140 (0)|
|* 2 | INDEX RANGE SCAN | IX_VENTAS_FECHA_ESTADO | 137 | 3 (0)|
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("FECHA"=TO_DATE(' 2025-03-15 00:00:00', 'syyyy-mm-dd hh24:mi:ss')
AND "ESTADO"='ENVIADO')
Con el bitmap solo sobre estado (25 % de las filas), el optimizador prefería leer la tabla. El índice compuesto (fecha, estado) resuelve las dos condiciones de igualdad en el propio índice: coste de 1062 a 140.
EXPLAIN PLAN FOR
SELECT venta_id, importe FROM ventas_demo WHERE TRUNC(fecha, 'MM') = DATE '2025-03-01';
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(NULL, NULL, 'BASIC +ROWS +COST'));
EXPLAIN PLAN FOR
SELECT venta_id, importe FROM ventas_demo
WHERE fecha >= DATE '2025-03-01' AND fecha < DATE '2025-03-04';
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(NULL, NULL, 'BASIC +ROWS +COST'));
--------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)|
--------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2000 | 1065 (1)|
| 1 | TABLE ACCESS FULL| VENTAS_DEMO | 2000 | 1065 (1)|
--------------------------------------------------------------
--------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)|
--------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2196 | 1062 (1)|
| 1 | TABLE ACCESS FULL| VENTAS_DEMO | 2196 | 1062 (1)|
--------------------------------------------------------------
- Con
TRUNC(fecha, 'MM')el índice no se puede usar (función sobre la columna). - Con el rango de tres días, el índice se podría usar, pero el optimizador prefiere leer la tabla: son unas 2200 filas repartidas por toda la tabla (el clustering factor del índice es 200.000, el máximo posible), y visitar 2200 bloques distintos sale más caro que leer secuencialmente los 3909 de la tabla. ¡Un índice no siempre es la respuesta!
Ejercicio 6: Secuencia para los clientes nuevos
Crea una secuencia seq_clientes que continúe la numeración de clientes, úsala para dar de alta dos clientes, deshaz la transacción y da de alta uno de nuevo. ¿Qué código recibe? ¿Por qué?
Ver solución
SELECT MAX(cliente_id) FROM clientes;
CREATE SEQUENCE seq_clientes START WITH 1015 INCREMENT BY 1 NOCACHE;
INSERT INTO clientes (cliente_id, nombre, nif, ciudad, provincia)
VALUES (seq_clientes.NEXTVAL, 'Librería Papiro', 'B12121212', 'Toledo', 'Toledo');
INSERT INTO clientes (cliente_id, nombre, nif, ciudad, provincia)
VALUES (seq_clientes.NEXTVAL, 'Bar El Puerto', 'B34343434', 'Cádiz', 'Cádiz');
SELECT cliente_id, nombre, ciudad FROM clientes WHERE cliente_id >= 1015;
ROLLBACK;
INSERT INTO clientes (cliente_id, nombre, nif, ciudad, provincia)
VALUES (seq_clientes.NEXTVAL, 'Librería Papiro', 'B12121212', 'Toledo', 'Toledo');
SELECT cliente_id, nombre FROM clientes WHERE cliente_id >= 1015;
ROLLBACK;
DROP SEQUENCE seq_clientes;
MAX(CLIENTE_ID)
---------------
1014
1 fila seleccionada.
Secuencia creada.
1 fila creada.
1 fila creada.
CLIENTE_ID NOMBRE CIUDAD
---------- ------------------------ ----------
1015 Librería Papiro Toledo
1016 Bar El Puerto Cádiz
2 filas seleccionadas.
Rollback terminado.
1 fila creada.
CLIENTE_ID NOMBRE
---------- ------------------------
1017 Librería Papiro
1 fila seleccionada.
Rollback terminado.
Secuencia borrada.
El cliente recibe el 1017: el ROLLBACK deshace los INSERT, pero no la secuencia. Los números 1015 y 1016 quedan como hueco. START WITH debe calcularse a partir de MAX(cliente_id) + 1 en el momento de crear la secuencia (en un script, consulta antes el máximo).
Ejercicio 7: Sinónimos para abreviar
Crea los sinónimos cli y ped para clientes y pedidos y úsalos para obtener los clientes con 3 o más pedidos. Comprueba los sinónimos en el diccionario y bórralos.
Ver solución
CREATE SYNONYM cli FOR clientes;
CREATE SYNONYM ped FOR pedidos;
SELECT c.nombre AS cliente, COUNT(*) AS pedidos
FROM cli c
JOIN ped p ON p.cliente_id = c.cliente_id
GROUP BY c.nombre
HAVING COUNT(*) >= 3
ORDER BY pedidos DESC;
SELECT synonym_name, table_name FROM user_synonyms ORDER BY 1;
DROP SYNONYM cli;
DROP SYNONYM ped;
Sinónimo creado.
Sinónimo creado.
CLIENTE PEDIDOS
---------------------------- ----------
Construcciones Levante S.A. 3
Talleres Martín S.L. 3
2 filas seleccionadas.
SYNONYM_NA TABLE_NAME
---------- --------------
CLI CLIENTES
PED PEDIDOS
2 filas seleccionadas.
Sinónimo borrado.
Sinónimo borrado.
Los sinónimos se usan como las tablas, incluso con alias de tabla (cli c). Ojo: abusar de sinónimos crípticos hace el código menos legible; úsalos sobre todo para ocultar propietarios o ubicaciones.
Ejercicio 8: Reconstruir el esquema relacional desde el diccionario
Sin mirar empresa_crear.sql, obtén del diccionario el esquema relacional de ASIGNACIONES en la notación del curso: clave primaria, claves ajenas y tabla a la que apunta cada una.
Ver solución
-- Columnas
SELECT column_name, nullable FROM user_tab_columns
WHERE table_name = 'ASIGNACIONES' ORDER BY column_id;
-- PK y FK con sus columnas
SELECT k.constraint_type, k.constraint_name, kc.column_name, kc.position,
(SELECT r.table_name FROM user_constraints r
WHERE r.constraint_name = k.r_constraint_name) AS tabla_padre
FROM user_constraints k
JOIN user_cons_columns kc ON kc.constraint_name = k.constraint_name
WHERE k.table_name = 'ASIGNACIONES' AND k.constraint_type IN ('P', 'R')
ORDER BY k.constraint_type, k.constraint_name, kc.position;
COLUMN_NAME N
------------------ -
EMP_ID N
PROYECTO_ID N
FECHA_ASIGNACION N
HORAS Y
FUNCION Y
5 filas seleccionadas.
C CONSTRAINT_NAME COLUMN_NAME POSITION TABLA_PADRE
- ---------------- ------------------ ---------- --------------
P PK_ASIGNACIONES EMP_ID 1 (nulo)
P PK_ASIGNACIONES PROYECTO_ID 2 (nulo)
R FK_ASI_EMP EMP_ID 1 EMPLEADOS
R FK_ASI_PROYECTO PROYECTO_ID 1 PROYECTOS
4 filas seleccionadas.
Resultado en la notación del curso:
ASIGNACIONES (*emp_id*, *proyecto_id*, fecha_asignacion, horas (N), funcion (N))
emp_id → EMPLEADOS(emp_id)proyecto_id → PROYECTOS(proyecto_id)(ON DELETE CASCADE)
Las dos columnas de la PK son a la vez claves ajenas: es la transformación típica de una relación N:M con atributos (módulo 17).
Ejercicio 9: Índices para las claves ajenas
Con la consulta de “claves ajenas sin índice” de la sección 10.3 has visto que pedidos.cliente_id no tiene índice. Créalo y vuelve a ejecutar la consulta para comprobar que ya no aparece. ¿Por qué no aparecía lineas_pedido.pedido_id?
Ver solución
CREATE INDEX ix_ped_cliente ON pedidos (cliente_id);
SELECT fk.table_name AS tabla, fk.constraint_name AS restriccion, fc.column_name AS columna
FROM user_constraints fk
JOIN user_cons_columns fc ON fc.constraint_name = fk.constraint_name
WHERE fk.constraint_type = 'R'
AND fk.table_name = 'PEDIDOS'
AND NOT EXISTS (SELECT 1
FROM user_ind_columns ic
WHERE ic.table_name = fc.table_name
AND ic.column_name = fc.column_name
AND ic.column_position = fc.position);
DROP INDEX ix_ped_cliente;
Índice creado.
TABLA RESTRICCION COLUMNA
-------------- ------------------ ------------------
PEDIDOS FK_PED_EMPLEADO EMPLEADO_ID
1 fila seleccionada.
Índice borrado.
fk_ped_cliente ha desaparecido de la lista; solo queda fk_ped_empleado. lineas_pedido.pedido_id no aparecía porque es la primera columna de pk_lineas_pedido: un índice cuya primera columna es la de la FK sirve también para ella. En cambio, proyecto_id en asignaciones es la segunda columna de su PK y por eso sí aparece como no indexada.
Ejercicios propuestos
Ejercicio 10: Índice basado en función para búsquedas por email
En una tabla de 100.000 filas creada como personas_demo, añade una columna email con valores tipo 'Nombre123@Correo.es'. Escribe una búsqueda que no distinga mayúsculas de minúsculas, comprueba su plan, crea el índice basado en función adecuado y comprueba el plan de nuevo. ¿Usa el índice la consulta WHERE LOWER(email) = ... si el índice es sobre UPPER(email)?
Ejercicio 11: Vista de proyectos
Crea la vista v_proyectos_resumen con cada proyecto, el nombre de su departamento, el número de empleados asignados, el total de horas y el presupuesto. Deben aparecer también los proyectos sin asignaciones (con 0 empleados y 0 horas). Hazla de solo lectura y consulta USER_UPDATABLE_COLUMNS para justificar si sería actualizable sin WITH READ ONLY.
Ejercicio 12: Filas migratorias
Crea la vista v_productos_baratos (productos de menos de 100 €) sin WITH CHECK OPTION. A través de ella, sube el precio del ratón inalámbrico a 150 € y comprueba que “desaparece” de la vista. Recréala con WITH CHECK OPTION y repite el experimento. Deshaz los cambios.
Ejercicio 13: Vista materializada de ventas mensuales
Crea una vista materializada mv_ventas_mes con el año-mes (TO_CHAR(fecha_pedido, 'YYYY-MM')) y el importe facturado de los pedidos no cancelados, con refresco completo bajo demanda. Inserta una línea en un pedido existente, comprueba en USER_MVIEWS que la vista materializada está desactualizada, refréscala y verifica el cambio. Bórrala al terminar.
Ejercicio 14: Numeración de facturas
Diseña una secuencia seq_facturas que empiece en 20260001, avance de uno en uno y no admita valores superiores a 20269999. Explica por qué no es adecuada si la normativa exige una numeración correlativa sin huecos, provocando tú mismo un hueco.
Ejercicio 15: Identidad frente a secuencia
Crea la tabla avisos_demo con una columna identidad GENERATED BY DEFAULT ON NULL AS IDENTITY (módulo 23) y otra avisos_demo2 con DEFAULT seq.NEXTVAL. Inserta en ambas una fila sin clave, otra con clave NULL explícita y otra con clave manual. Compara los resultados y explica las diferencias.
Ejercicio 16: Documentación automática
Escribe una sola consulta sobre el diccionario que, para cada tabla de tu esquema, muestre: nombre, número de columnas, número de columnas obligatorias, número de restricciones CHECK (sin contar las NOT NULL), número de claves ajenas y número de índices. Ordena por nombre de tabla.
Ejercicio 17: Índices que sobran
En ventas_demo, crea los índices ix_a (cliente_id), ix_b (cliente_id, fecha) y ix_c (fecha). Razona cuál es redundante. Usa ALTER INDEX ... INVISIBLE sobre el que creas redundante y comprueba con EXPLAIN PLAN que las consultas por cliente_id siguen usando un índice. Mide con USER_SEGMENTS cuánto espacio liberarías al borrarlo.
Resumen
| Concepto | Sintaxis / idea clave |
|---|---|
| Vista | CREATE [OR REPLACE] [FORCE] VIEW v [(alias)] AS SELECT ...; |
| Restricciones de vista | WITH CHECK OPTION [CONSTRAINT c] (tipo V, ORA-01402), WITH READ ONLY (tipo O, ORA-42399) |
| Vista no actualizable | Agregados, GROUP BY, DISTINCT, conjuntos (ORA-01732); expresiones (ORA-01733) |
| Clave preservada | En vistas de combinación solo se modifican columnas de la tabla cuya clave se conserva; ver USER_UPDATABLE_COLUMNS |
| Diccionario de vistas | USER_VIEWS (texto), USER_OBJECTS.STATUS (INVALID), ALTER VIEW v COMPILE |
| Vista materializada | CREATE MATERIALIZED VIEW mv BUILD IMMEDIATE REFRESH COMPLETE/FAST/FORCE ON DEMAND/ON COMMIT AS ... |
| Log de vista materializada | CREATE MATERIALIZED VIEW LOG ON t WITH ROWID, SEQUENCE (...) INCLUDING NEW VALUES; |
| Índice | CREATE [UNIQUE / BITMAP] INDEX ix ON t (c1, c2 / expresión) [INVISIBLE]; |
| B-tree | Árbol equilibrado; hojas ordenadas con ROWID; ideal para alta cardinalidad y consultas selectivas |
| Índice compuesto | Sirve para consultas sobre su prefijo (primeras columnas) |
| Basado en función | ON t (UPPER(col)): la consulta debe usar la misma expresión |
| Bitmap | Baja cardinalidad, data warehouse; desaconsejado con DML concurrente |
| Invisible | ALTER INDEX ix INVISIBLE / VISIBLE; optimizer_use_invisible_indexes |
| Plan de ejecución | EXPLAIN PLAN FOR ...; + SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY); |
| Accesos | TABLE ACCESS FULL, INDEX UNIQUE SCAN, INDEX RANGE SCAN, TABLE ACCESS BY INDEX ROWID |
| Secuencia | CREATE SEQUENCE s START WITH n INCREMENT BY n MINVALUE/MAXVALUE CYCLE CACHE ORDER; |
| Uso | s.NEXTVAL, s.CURRVAL (ORA-08002); huecos inevitables; DEFAULT s.NEXTVAL (12c) |
| Modificar secuencia | ALTER SEQUENCE (no START WITH: ORA-02283; sí RESTART) |
| Identidad | GENERATED ALWAYS / BY DEFAULT [ON NULL] AS IDENTITY (secuencia ISEQ$$_) |
| Sinónimo | CREATE [PUBLIC] SYNONYM s FOR esquema.objeto; no concede permisos |
| Diccionario | USER_ (míos), ALL_ (accesibles), DBA_ (todos), V$ (rendimiento), DICTIONARY |