Guía Completa de SQL para Oracle
Consultas, optimización, administración y PL/SQL
Introducción
Oracle Database es el sistema de gestión de bases de datos relacionales más utilizado en entornos
empresariales de gran escala a nivel mundial. Su dialecto SQL incorpora extensiones propietarias que
van más allá del estándar ANSI SQL, incluyendo funciones analíticas avanzadas, hints de optimización,
manejo de secuencias, particionamiento de tablas y un lenguaje procedural propio llamado PL/SQL.
Esta guía cubre los conceptos más importantes para el trabajo diario con Oracle, desde consultas básicas
hasta optimización avanzada y administración de esquemas.
1. Estructura Básica de una Consulta
La sintaxis completa de una instrucción SELECT en Oracle incluye las siguientes cláusulas, en el orden
en que deben aparecer:
SELECT [DISTINCT | ALL] columnas
FROM [Link] [alias]
JOIN otra_tabla ON condicion
WHERE condicion_filtro
GROUP BY columnas_agrupacion
HAVING condicion_grupo
ORDER BY columna [ASC|DESC]
FETCH FIRST n ROWS ONLY; -- Oracle 12c+
1.1 Limitación de filas en Oracle 11g
En Oracle 11g no existe FETCH FIRST. La forma estándar es usar ROWNUM dentro de una subconsulta
para evitar que el filtro se aplique antes de ORDER BY:
SELECT * FROM (
SELECT t.*, ROWNUM rn
FROM (SELECT * FROM ventas ORDER BY fecha DESC) t
WHERE ROWNUM <= 20
) WHERE rn >= 11; -- Filas del 11 al 20
2. Tipos de JOIN en Oracle
Los JOINs permiten combinar filas de dos o más tablas basándose en una columna relacionada. Oracle
soporta todos los tipos estándar:
Tipo de JOIN Descripción Caso de uso típico
INNER JOIN Solo filas con coincidencia en ambas tablasRelaciones obligatorias
LEFT OUTER JOIN Todas las filas de la tabla izquierda Relaciones opcionales
RIGHT OUTER JOIN Todas las filas de la tabla derecha Menos común, preferir LEFT
Tipo de JOIN Descripción Caso de uso típico
FULL OUTER JOIN Todas las filas de ambas tablas Comparación de conjuntos
CROSS JOIN Producto cartesiano completo Generación de combinaciones
SELF JOIN Una tabla unida consigo misma Jerarquías, empleado-jefe
Ejemplo práctico con LEFT JOIN:
SELECT [Link], d.nombre_depto
FROM empleados e
LEFT JOIN departamentos d ON e.id_depto = d.id_depto
WHERE [Link] = 'S'
ORDER BY [Link];
3. Funciones Analíticas (Window Functions)
Las funciones analíticas son una de las características más poderosas de Oracle SQL. Permiten realizar
cálculos sobre un conjunto de filas relacionadas con la fila actual, sin necesidad de hacer GROUP BY.
Función Descripción
ROW_NUMBER() Número de fila único dentro de la partición
RANK() Ranking con saltos en caso de empate
DENSE_RANK() Ranking sin saltos en empates
LAG(col, n) Valor de la columna n filas antes
LEAD(col, n) Valor de la columna n filas adelante
SUM() OVER() Suma acumulada o por partición
FIRST_VALUE() Primer valor de la ventana
LAST_VALUE() Último valor de la ventana
NTILE(n) Divide filas en n grupos iguales (percentiles)
Ejemplo — Top ventas por región con ROW_NUMBER:
SELECT region, vendedor, total_ventas,
ROW_NUMBER() OVER (
PARTITION BY region
ORDER BY total_ventas DESC
) ranking
FROM resumen_ventas
ORDER BY region, ranking;
4. UNION, INTERSECT y MINUS
Los operadores de conjuntos permiten combinar los resultados de múltiples consultas SELECT. Todos
requieren que las columnas tengan el mismo número y tipos de datos compatibles.
Operador Comportamiento Elimina duplicados
UNION Combina todos los resultados Sí
UNION ALL Combina todos los resultados No (más rápido)
INTERSECT Solo filas en ambas consultas Sí
MINUS Filas en primera pero no en segunda Sí
SELECT id, 'PENDIENTE' estado FROM msg_pendientes WHERE fecha > SYSDATE-7
UNION ALL
SELECT id, 'PROCESADO' estado FROM msg_procesados WHERE fecha > SYSDATE-7
UNION ALL
SELECT id, 'DORMIDO' estado FROM msg_dormidos WHERE fecha > SYSDATE-7
ORDER BY 1;
5. Subconsultas y Expresiones WITH (CTE)
Las Common Table Expressions (CTE) con la cláusula WITH permiten definir subconsultas nombradas
que pueden reutilizarse en la misma instrucción SQL, mejorando la legibilidad y en ocasiones el
rendimiento:
WITH ventas_region AS (
SELECT region, SUM(monto) total
FROM ventas
WHERE EXTRACT(YEAR FROM fecha) = 2024
GROUP BY region
),
meta_region AS (
SELECT region, meta_anual
FROM presupuesto WHERE anio = 2024
)
SELECT [Link], [Link], m.meta_anual,
ROUND([Link]/m.meta_anual*100, 1) pct_cumplimiento
FROM ventas_region v
JOIN meta_region m ON [Link] = [Link]
ORDER BY pct_cumplimiento DESC;
6. Optimización de Consultas
6.1 Principios fundamentales
• Filtrar temprano: colocar las condiciones más restrictivas primero en el WHERE.
• Evitar funciones sobre columnas indexadas: WHERE TO_CHAR(fecha,'YYYY')='2024' no usa
índice; WHERE fecha BETWEEN DATE'2024-01-01' AND DATE'2024-12-31' sí lo usa.
• Usar EXISTS en lugar de IN para subconsultas correlacionadas con tablas grandes.
• Analizar el plan de ejecución antes de asumir que una query es óptima.
6.2 EXPLAIN PLAN
EXPLAIN PLAN FOR
SELECT * FROM sat_sad.msg_pendientes WHERE fecha > SYSDATE - 30;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
6.3 Hints de optimización
Hint Efecto
/*+ INDEX(t idx_nombre) */ Fuerza el uso de un índice específico
/*+ FULL(t) */ Fuerza un Full Table Scan
/*+ PARALLEL(t,4) */ Habilita ejecución paralela con 4 hilos
/*+ NO_MERGE */ Evita que Oracle fusione vistas o subconsultas
/*+ LEADING(a b) */ Establece el orden de los JOINs
7. PL/SQL: Procedimientos y Funciones
PL/SQL es el lenguaje procedural de Oracle que extiende SQL con estructuras de control, manejo de
excepciones, cursores y módulos reutilizables.
7.1 Estructura básica de un bloque PL/SQL
DECLARE
v_total NUMBER := 0;
v_nombre VARCHAR2(100);
BEGIN
SELECT nombre INTO v_nombre
FROM empleados WHERE id = 1001;
DBMS_OUTPUT.PUT_LINE('Empleado: ' || v_nombre);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('No encontrado.');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
END;
/
7.2 Cursores explícitos
DECLARE
CURSOR cur_ventas IS
SELECT id, monto FROM ventas WHERE fecha > SYSDATE - 7;
v_total NUMBER := 0;
BEGIN
FOR rec IN cur_ventas LOOP
v_total := v_total + [Link];
END LOOP;
DBMS_OUTPUT.PUT_LINE('Total: ' || v_total);
END;
/
8. Administración de Esquemas y Permisos
La gestión de permisos en Oracle sigue el principio de mínimo privilegio. Los roles y privilegios se asignan
a nivel de sistema o sobre objetos específicos:
-- Otorgar SELECT sobre todas las tablas de un esquema
BEGIN
FOR t IN (SELECT table_name FROM dba_tables WHERE owner='SAT_SAD') LOOP
EXECUTE IMMEDIATE 'GRANT SELECT ON SAT_SAD.'||t.table_name||' TO SAT_LECTOR';
END LOOP;
END;
/
-- Crear sinónimo para acceso transparente
CREATE OR REPLACE SYNONYM mi_tabla FOR sat_sad.msg_pendientes_consumidor;
-- Revocar permiso
REVOKE SELECT ON sat_sad.msg_procesados FROM usuario_externo;
Privilegio Nivel Descripción
SELECT Objeto Leer datos de una tabla o vista
INSERT / UPDATE / DELETE Objeto Modificar datos
EXECUTE Objeto Ejecutar procedimientos y funciones
CREATE SESSION Sistema Conectarse a la base de datos
CREATE TABLE Sistema Crear tablas en el propio esquema
DBA Rol Acceso total al sistema (solo administradores)
9. Manejo de Fechas en Oracle
El manejo de fechas es uno de los temas más frecuentes y con más errores potenciales en Oracle. Las
funciones clave son:
Función / Expresión Descripción Ejemplo
SYSDATE Fecha y hora actual del servidor WHERE fecha = SYSDATE
TRUNC(date) Trunca al inicio del día TRUNC(SYSDATE) = hoy a 00:00
ADD_MONTHS(d,n) Suma n meses a la fecha ADD_MONTHS(SYSDATE, -3)
MONTHS_BETWEEN(d1,d2) Meses entre dos fechas MONTHS_BETWEEN(hoy, inicio)
LAST_DAY(d) Último día del mes LAST_DAY(SYSDATE)
EXTRACT(part FROM d) Extrae parte de fecha EXTRACT(YEAR FROM fecha)
TO_DATE(str, fmt) Convierte texto a fecha TO_DATE('2024-01-15','YYYY-MM-DD')
TO_CHAR(d, fmt) Formatea fecha a texto TO_CHAR(SYSDATE,'DD/MM/YYYY')
Rangos de fecha frecuentes:
-- Hoy completo (00:00:00 a 23:59:59)
WHERE fecha >= TRUNC(SYSDATE) AND fecha < TRUNC(SYSDATE)+1
-- Últimos 30 días
WHERE fecha >= SYSDATE - 30
-- Mes actual
WHERE fecha >= TRUNC(SYSDATE,'MM')
AND fecha < ADD_MONTHS(TRUNC(SYSDATE,'MM'),1)
10. Vistas del Diccionario de Datos
El diccionario de datos de Oracle es la fuente de verdad sobre la estructura de la base de datos. Las
vistas con prefijo DBA_ requieren privilegios de DBA; las ALL_ muestran lo accesible al usuario actual; las
USER_ solo del esquema propio.
Vista Información
DBA_TABLES / USER_TABLES Tablas del sistema / esquema propio
DBA_INDEXES Índices y columnas asociadas
DBA_CONSTRAINTS Primary keys, foreign keys, checks
DBA_SEGMENTS Tamaño físico de tablas e índices en MB
V$SESSION Sesiones activas en tiempo real
V$SQL / V$SQLAREA Consultas en memoria y estadísticas de ejecución
V$LOCKED_OBJECT Objetos con bloqueos activos
DBA_AUDIT_TRAIL Registro de auditoría de accesos
DBA_JOBS / DBA_SCHEDULER_JOBS
Tareas programadas del sistema