BD2 · S11 · Particionamiento y escalabilidad¶
SGE-JD · JD Refrigeración S.A.C. · Equipo CherryTree · Yoel Wagner Chambi Sirena · 10/11/2026
Trabajo autónomo de la sesión 11. El objetivo es decidir qué particionar según cómo se usan los datos,
comprobar con Pstart/Pstop si Oracle de verdad descarta particiones, provocar a propósito el error que lo
impide y medir DROP PARTITION frente a DELETE. Se hizo en la base de práctica, sobre las ~27 000 ventas en orden
de fecha de S10. La edición Free incluye la opción (V$OPTION: Partitioning = TRUE).
| Orden | Script | Qué hace |
|---|---|---|
| 1 | S11_01_ventas_particionada.sql | VENTAS_PART: copia de VENTAS por mes (INTERVAL); pruning con rango, TRUNC y TO_CHAR |
| 2 | S11_02_variantes.sql | Hash, List y Composite con los datos reales, en el diccionario |
| 3 | S11_03_drop_frente_a_delete.sql | Purga de 12 meses de auditoría: DELETE frente a DROP PARTITION |
| 4 | S11_04_drop_con_indices.sql | El DROP correcto, con UPDATE GLOBAL INDEXES |
Todo se construye sobre copias (VENTAS_PART, AUDITORIA_PART…), igual que S4 y S5. Las tablas que usa el
backend no se tocan.
1. Tablas candidatas y estrategia, según el patrón de acceso¶
| Tabla | ¿Cómo crece? | ¿Cómo se consulta? | ¿Se borra? | Estrategia |
|---|---|---|---|---|
VENTAS (y DETALLE_VENTA) |
Unas 30 por día laborable; nunca se reduce | Por periodo: el día (Inicio), el mes (Rentabilidad, S4) y el año (reportes); S4, S5 y S10 lo mostraron | No. SUNAT exige conservar ventas y comprobantes por años | RANGE mensual por FECHA_VENTA, con INTERVAL: el pruning sirve; el DROP no aplica |
VENTA_AUDITORIA (S2) |
La que más crece: una fila por cada INSERT, UPDATE y DELETE de una venta (S5 la vio con 19 779 filas para 237 ventas, por la cobranza) | Por venta, y "qué pasó en tal periodo" | Sí, por antigüedad: se conserva un tiempo y después se purga | RANGE mensual por FECHA_EVENTO, con INTERVAL: la purga es un DROP PARTITION |
CLIENTES, PRODUCTOS |
37 y 39 filas | Por ID | — | Ninguna: particionar tablas chicas solo agrega costo |
Con INTERVAL, Oracle crea la partición del mes siguiente cuando llega la primera venta de ese mes. Nadie tiene que acordarse de crearla. En una PC sin DBA es la diferencia entre que funcione o que en enero falle con ORA-14400 ("inserted partition key does not map to any partition").
2. VENTAS_PART: una partición por mes¶
PARTICIONES FILAS_MINIMO FILAS_MAXIMO FILAS_TOTAL
36 0 877 27200
PARTITION_NAME POSITION NUM_ROWS HIGH_VALUE
P_ANTES_2024 1 0 TO_DATE(' 2024-01-01 …')
SYS_P800 2 790 TO_DATE(' 2024-02-01 …') ← creada sola por INTERVAL
…
SYS_P790 35 793 TO_DATE(' 2026-11-01 …') ← octubre de 2026
3. Pruning: la misma pregunta escrita de cuatro formas¶
-- Octubre 2026 con rango explícito
| 2 | PARTITION RANGE SINGLE | | 793 | 9 | 35 | 35 |
-- Octubre 2026 con TRUNC(fecha_venta, 'MM') = DATE '2026-10-01'
| 2 | PARTITION RANGE ALL | | 272 | 234 | 1 |1048575|
-- Año 2025 con TO_CHAR(fecha_venta, 'YYYY') = '2025'
| 2 | PARTITION RANGE ALL | | 272 | 234 | 1 |1048575|
-- Año 2025 con rango explícito
| 2 | PARTITION RANGE ITERATOR| | 9540 | 80 | 14 | 25 |
| Consulta | Operación | Pstart / Pstop | Particiones leídas | Costo |
|---|---|---|---|---|
| Octubre, rango | PARTITION RANGE SINGLE |
35 / 35 | 1 de 36 | 10 |
Octubre, TRUNC(…,'MM') |
PARTITION RANGE ALL |
1 / 1048575 | todas | 235, 23 veces más |
2025, TO_CHAR(…,'YYYY') |
PARTITION RANGE ALL |
1 / 1048575 | todas | 235 |
| 2025, rango | PARTITION RANGE ITERATOR |
14 / 25 | 12 (enero a diciembre de 2025) | 81 |
Cómo leer Pstart/Pstop:
SINGLEcon 35/35 significa que, antes de ejecutar, Oracle supo que la respuesta solo podía estar en la partición 35.ITERATORcon 14 a 25 significa que recorre esas 12 particiones y descarta las demás.ALLsignifica que las lee todas, porque ya no puede saber dónde está la respuesta.
Con TRUNC o TO_CHAR sobre la clave de partición, Oracle no da ningún error ni aviso: el plan corre igual,
solo que 23 veces más caro, y además estima mal (272 filas frente a 793).
4. Hash, List y Composite¶
HASH (cliente_id), 4 particiones LIST (estado) COMPOSITE: RANGE (año) + LIST (estado)
SYS_P822 5182 P_REGISTRADA 26379 'REGISTRADA' P_2024 SP_ANULADA 289 · SP_OTROS 0 · SP_REGISTRADA 9224
SYS_P823 7322 P_ANULADA 821 'ANULADA' P_2025 SP_ANULADA 303 · SP_OTROS 0 · SP_REGISTRADA 9079
SYS_P824 7393 P_OTROS 0 DEFAULT P_2026 SP_ANULADA 229 · SP_OTROS 0 · SP_REGISTRADA 8076
SYS_P825 7303
"Anuladas de 2025" en la Composite:
| 2 | PARTITION RANGE SINGLE | 303 | 7 | 2 | 2 |
| 3 | PARTITION LIST SINGLE | 303 | 7 | KEY | KEY |
| 4 | TABLE ACCESS FULL | 303 | 7 | 5 | 5 | ← 1 subpartición de 9
TABLE_NAME PARTITIONING SUBPARTITIONING PARTITION_COUNT INTERVAL
VENTAS_COMP RANGE LIST 3
VENTAS_HASH HASH NONE 4
VENTAS_LIST LIST NONE 3
VENTAS_PART RANGE NONE 1048575 NUMTOYMINTERVAL(1, 'MONTH')
| Estrategia | ¿Sirve para SGE-JD? |
|---|---|
| Hash por cliente | No. Reparte para equilibrar la carga, y ninguna consulta de JD pide un grupo de clientes. Además quedó desparejo (5 182 frente a ~7 300): con solo 37 clientes, el hash no tiene suficientes valores para repartir bien. Hash sirve con claves de muchos valores distintos |
| List por estado | Aísla el 3 % de anuladas, pero ninguna consulta pide "todas las anuladas" sin un periodo (S5) |
| Composite año + estado | Hace pruning doble ("anuladas de 2025" lee 1 subpartición de 9). Correcta, pero para el volumen de JD el RANGE mensual solo ya alcanza |
| Range mensual (INTERVAL) | Sí: es el patrón real, consultar por periodo |
5. DROP PARTITION frente a DELETE: purgar un año de auditoría¶
Dos gemelas con los mismos 240 000 eventos (diciembre de 2024 a noviembre de 2026, 10 000 por mes), cada una con clave primaria y un índice por venta. Se purgan los 12 meses más viejos, 120 067 filas:
A) DELETE FROM auditoria_plana WHERE fecha_evento < TIMESTAMP '2025-12-01 00:00:00';
120067 rows deleted. Elapsed: 00:00:01.17
B) ALTER TABLE auditoria_part DROP PARTITION … (13 particiones)
Particiones eliminadas: 13 en 290 ms
INDEX_NAME PAR STATUS
IX_AUDITORIA_PART_VENTA YES N/A ← LOCAL: sus particiones se fueron con las de la tabla
PK_AUDITORIA_PART NO UNUSABLE ← GLOBAL: apunta a filas que ya no existen
INSERT INTO auditoria_part VALUES (…)
ORA-01502: index 'JDREFRIGERACION.PK_AUDITORIA_PART' or partition of such index is in unusable state
C) Igual, con UPDATE GLOBAL INDEXES
Particiones eliminadas: 13 en 240 ms (con UPDATE GLOBAL INDEXES)
PK_AUDITORIA_PART NO VALID
FILAS_QUE_QUEDAN 119933 · INSERT → 1 row created.
| Forma | Tiempo | Índices después | ¿Se puede seguir escribiendo? |
|---|---|---|---|
DELETE |
1 170 ms (en la primera corrida, 670 ms) | Válidos | Sí, y deja 120 000 filas de undo y redo |
DROP PARTITION |
290 ms (primera corrida: 350 ms) | La PK global UNUSABLE | No: ORA-01502 hasta reconstruirla |
DROP PARTITION … UPDATE GLOBAL INDEXES |
240 ms | Todos válidos | Sí |
Decisión: la purga de la auditoría se hace con DROP PARTITION … UPDATE GLOBAL INDEXES. Es unas 4 a 5 veces
más rápida que el DELETE, casi no genera undo ni redo, y deja los índices válidos. Desde Oracle 12c, el
mantenimiento del índice global se difiere, por eso no fue más lento que el DROP simple. La diferencia con la
guía (allí más de 30 veces) viene del volumen y la máquina. Lo que se repite en cualquier caso es la tendencia: el
DELETE crece con las filas, el DROP no.
6. Relación con ADS y LP2¶
| BD2 | ADS | LP2 |
|---|---|---|
| RANGE mensual en ventas | Rendimiento y escalabilidad (S4: una PC durante años); retención legal de SUNAT | Sin cambios: JPA no sabe de particiones. Pero los reportes deben filtrar con rangos (S4) y nunca con TRUNC o TO_CHAR sobre la fecha |
DROP PARTITION … UPDATE GLOBAL INDEXES en auditoría |
Auditabilidad (S1) con un costo acotado | El trigger de S2 sigue insertando durante la purga, sin ORA-01502 |
| Hash descartado | Decisión medida, no por catálogo | — |
7. Error o hallazgo¶
Hallazgo 1: el DROP PARTITION simple habría detenido las ventas. El DROP de la purga dejó la clave primaria
global en UNUSABLE, y el siguiente INSERT falló con ORA-01502. En VENTA_AUDITORIA real, el que inserta es el
trigger de S2, dentro de la transacción de cada venta. Una purga mal escrita haría fallar todas las ventas de
Lucy hasta que alguien reconstruya el índice. Es el mismo efecto que el MOVE de S9, pero aquí lo provoca una
tarea de mantenimiento de rutina. La forma correcta (UPDATE GLOBAL INDEXES) no solo evita el problema: además
fue un poco más rápida.
Hallazgo 2: Hash no reparte con pocos valores. Se esperaban 4 particiones de unas 6 800 filas. Salió una con 5 182 y tres con ~7 300, porque la clave tiene solo 37 valores (clientes), y el hash los reparte de a grupos.
Hallazgo 3: con INTERVAL, Pstop y PARTITION_COUNT dicen 1048575. En PARTITION RANGE ALL, el plan mostró
1 / 1048575, y USER_PART_TABLES también, aunque la tabla tiene 36 particiones. Es el máximo teórico que Oracle
reserva para las particiones que INTERVAL podría crear. Leído rápido, parece un error. Para saber cuántas existen
hay que contar en USER_TAB_PARTITIONS.
Error propio: la primera corrida de S11_03 no creó las claves primarias. Había un comentario -- índice GLOBAL
después del ; en la misma línea. Para SQLPlus ese ; ya no termina la sentencia: pegó las dos ALTER TABLE en
una y dio ORA-03405. Sin la PK no había índice global, y la primera medición no mostraba el hallazgo 1. Es el tercer
tropiezo de SQLPlus con la puntuación, después del guion final de PROMPT (S1, S5) y del DELETE …; COMMIT; en una
línea (S10).
8. Reflexión¶
¿Por qué una función sobre la clave de partición puede apagar el pruning sin ningún error, y qué cuesta eso a medida que la tabla crece?
Porque el pruning se decide antes de ejecutar, comparando el filtro con los límites de cada partición. Con
fecha_venta >= … AND < …, Oracle compara directamente. Con TRUNC(fecha_venta, 'MM'), el valor recién existe al
leer cada fila, así que no puede descartar ninguna. Como la respuesta sigue siendo correcta, no hay nada que
reportar. Hoy, con 36 meses, eso cuesta 23 veces más (235 frente a 10). Cada mes nuevo agrega una partición que la
consulta mala lee y la buena no: en diez años serían 120 particiones leídas para responder sobre un mes. Pasa lo
mismo que con DELETE frente a DROP PARTITION: lo que cuesta proporcional al total se nota cuando ya es tarde.
El DELETE de un año tardó 1,17 s con 240 000 eventos, y con el ritmo real de la auditoría de JD crecería cada
año. El DROP sigue costando lo mismo.