# Fragmentación por splits en índices B-tree: análisis práctico en Oracle 19c Analizando un índice B-tree en una tabla que recibe únicamente INSERTs encuentro que la volumetría de éste es similar a la volumetría de la tabla. ```plaintext select sum(bytes/1024/1024/1024) Gb from dba_segments where segment_name='TABLA'; GB ---------- 94,0537109 select sum(bytes/1024/1024/1024) Gb from dba_segments where segment_name='INDICE_TABLA' GB ---------- 95,1396484 ``` La tabla está particionada y tiene volumetrías similares en cada partición. En entorno replicado comprobamos si recuperamos el espacio después de un rebuild: ```plaintext SET SERVEROUTPUT ON DECLARE v_sql VARCHAR2(1000); BEGIN FOR rec IN ( SELECT index_name, partition_name FROM dba_ind_partitions WHERE index_name = 'INDICE_TABLA' AND index_owner = 'PROPIETARIO' ORDER BY partition_name ) LOOP v_sql := 'ALTER INDEX PROPIETARIO.' || rec.index_name || ' REBUILD PARTITION ' || rec.partition_name || ' ONLINE'; DBMS_OUTPUT.PUT_LINE('Ejecutando: ' || v_sql); EXECUTE IMMEDIATE v_sql; END LOOP; END; / Ejecutando: ALTER INDEX PROPIETARIO.INDICE_TABLA REBUILD PARTITION SYS_P15950 ONLINE Ejecutando: ALTER INDEX PROPIETARIO.INDICE_TABLA REBUILD PARTITION SYS_P16003 ONLINE Ejecutando: ALTER INDEX PROPIETARIO.INDICE_TABLA REBUILD PARTITION SYS_P16051 ONLINE Ejecutando: ALTER INDEX PROPIETARIO.INDICE_TABLA REBUILD PARTITION SYS_P16091 ONLINE Ejecutando: ALTER INDEX PROPIETARIO.INDICE_TABLA REBUILD PARTITION SYS_P16160 ONLINE Ejecutando: ALTER INDEX PROPIETARIO.INDICE_TABLA REBUILD PARTITION SYS_P16173 ONLINE PL/SQL procedure successfully completed. Elapsed: 00:52:08.117 select sum(bytes/1024/1024/1024) Gb from dba_segments where segment_name='INDICE_TABLA' GB ---------- 57,2919922 ``` Vemos que recuperamos ~38 GB. **Investigo para ponerle cara**. ## 1\. Índice y estructura de la tabla Tabla con tipos de datos | Columna | Tipo | | --- | --- | | ID | NUMBER | | ITEM | NUMBER | | EVENT\_DATE | DATE | | TIMESTAMP | DATE | | ATRIBUTO1 | NUMBER | | ATRIBUTO2 | NUMBER | Índice B-tree particionado denominado INDICE\_TABLA sobre (ITEM, EVENT\_DATE). En primera instancia compruebo si hay bloques no ocupados. Nos vale con una partición, todas son similares. ```plaintext SET SERVEROUTPUT ON DECLARE v_total_blocks NUMBER; v_total_bytes NUMBER; v_unused_blocks NUMBER; v_unused_bytes NUMBER; v_last_file_id NUMBER; v_last_extent_block NUMBER; v_last_used_block NUMBER; BEGIN DBMS_SPACE.UNUSED_SPACE( segment_owner => 'PROPIETARIO', segment_name => 'INDICE_TABLA', segment_type => 'INDEX PARTITION', total_blocks => v_total_blocks, total_bytes => v_total_bytes, unused_blocks => v_unused_blocks, unused_bytes => v_unused_bytes, last_used_extent_file_id => v_last_file_id, last_used_extent_block_id => v_last_extent_block, last_used_block => v_last_used_block, partition_name => 'SYS_P16051' ); DBMS_OUTPUT.PUT_LINE('Partición SYS_P16003:'); DBMS_OUTPUT.PUT_LINE(' Total Blocks : ' || v_total_blocks); DBMS_OUTPUT.PUT_LINE(' Unused Blocks: ' || v_unused_blocks); DBMS_OUTPUT.PUT_LINE(' Unused Bytes : ' || ROUND(v_unused_bytes/1024/1024,2) || ' MB'); END; / Partición SYS_P16003: Total Blocks : 2247296 Unused Blocks: 0 Unused Bytes : 0 MB ``` ## Observaciones clave 1. **No hay espacio no utilizado:** * DBMS\_SPACE.UNUSED\_SPACE devuelve 0 unused * La fragmentación **no es PCTFREE ni filas borradas**. 2. **Splits de leaf nodes no se monitorizan directamente en Oracle:** * No hay vista que liste “splits ocurridos”. * Solo se puede estimar a partir de diferencias entre **bloques reales vs bloques teóricos**. 3. **Rebuild recupera el espacio:** * Compacta los leaf nodes y reduce los 50/50 y 99/1 parcialmente llenos. * Reducción observada en este ejemplo: ~38 GB. # Investigamos splits ## Tipos de split en Oracle 19c Antes de nada explico los distintos tipos de split en Oracle. ### 50/50 Split * La nueva clave no es la mayor del bloque → bloque viejo y bloque nuevo ~50 % llenos. * Ocurre cuando varias filas tienen el mismo ITEM y se insertan fechas intermedias (EVENT\_DATE). ```plaintext Bloque inicial (capacidad 5): I1-2026-02-10 I1-2026-02-11 I2-2026-02-09 I2-2026-02-10 I3-2026-02-08 Insertamos I2-2026-02-09.5 → split 50/50 Bloque viejo Bloque nuevo I1-2026-02-10 I2-2026-02-09.5 I1-2026-02-11 I2-2026-02-10 I2-2026-02-09 I3-2026-02-08 ``` ### 99/1 Split * La nueva clave **es la mayor** → bloque viejo ~99 % lleno, bloque nuevo casi vacío. * Minimiza desperdicio y ocurre con fechas crecientes. ```plaintext Bloque viejo inicial (ITEM I1): I1-2026-02-10 I1-2026-02-11 Insertamos I1-2026-02-12 → split 99/1 Bloque viejo Bloque nuevo I1-2026-02-10 I1-2026-02-12 I1-2026-02-11 ``` ## Conceptos clave de ocupación de bloques * **ROWID:** 6 bytes por fila, puntero físico. * **Overhead de entrada:** ~3 bytes (flags, punteros, longitud de clave). * **Tamaño promedio de entrada de índice:** `avg_index_entry_size = 20 bytes` * **Ajuste para ramas internas:** +10 % → `22 bytes/fila` * **Factor de ocupación máximo post-rebuild:** 95 % Para el tamaño promedio de entrada de índice calculo cúanto debería ocupar cada entrada: ```plaintext SELECT ROUND(AVG(vsize(ITEM) + vsize(EVENT_DATE) + 6 + 3)) AS avg_index_entry_size FROM PROPIETARIO.TABLA partition(SYS_P16160); avg_index_entry_size -------------------- 20 ``` Y con ésta fórmula calcularíamos los bloques teóricos: ```plaintext blocks_teoricos = num_rows * avg_index_entry_size / (db_block_size * block_occupancy_factor) ``` Comparando con bloques reales obtenemos el espacio extra generado por splits. ## Análisis de MB recuperables Tener en cuenta que es una tabla donde se inserta constantemente, los valores son superiores a la primera estimación de espacio puesto que se ha realizado posteriormente (pasamos de 95Gb de volumetría de tabla a 109Gb aprox) ```plaintext -- =============================================== -- Script para estimación de MB recuperables -- =============================================== -- Variables VAR block_size NUMBER VAR avg_index_entry_size NUMBER VAR block_occupancy_factor NUMBER EXEC :block_size := 8192; -- tamaño del bloque en bytes EXEC :avg_index_entry_size := 22; -- bytes por fila (clave + ROWID + overhead de rama) EXEC :block_occupancy_factor := 0.95; -- factor de ocupación realista post-rebuild -- Formato de salida COLUMN partition_name FORMAT A15 COLUMN num_rows FORMAT 999,999,999 COLUMN blocks FORMAT 999,999,999 COLUMN blocks_teoricos FORMAT 999,999,999 COLUMN diff_blocks FORMAT 999,999,999 COLUMN size_mb FORMAT 999,999.99 COLUMN est_mb_recover FORMAT 999,999.99 COLUMN occupancy_pct FORMAT 999.99 -- Fragmentación por partición ajustada SELECT i.partition_name, i.num_rows, s.blocks, ROUND(i.num_rows * :avg_index_entry_size / (:block_size * :block_occupancy_factor)) AS blocks_teoricos, s.blocks - ROUND(i.num_rows * :avg_index_entry_size / (:block_size * :block_occupancy_factor)) AS diff_blocks, ROUND(s.bytes/1024/1024,2) AS size_mb, ROUND((s.blocks - ROUND(i.num_rows * :avg_index_entry_size / (:block_size * :block_occupancy_factor))) * :block_size / 1024/1024,2) AS est_mb_recover, ROUND((i.num_rows * :avg_index_entry_size) / s.bytes * 100,2) AS occupancy_pct FROM dba_ind_partitions i JOIN dba_segments s ON i.index_name = s.segment_name AND i.partition_name = s.partition_name AND i.index_owner = s.owner WHERE i.index_owner = 'PROPIETARIO' AND i.index_name = 'INDICE_TABLA' ORDER BY i.partition_position; -- Total del índice ajustado SELECT ROUND(SUM(s.bytes)/1024/1024,2) AS total_size_mb, ROUND(SUM(i.num_rows * :avg_index_entry_size)/1024/1024,2) AS total_used_mb, ROUND(SUM((s.blocks - ROUND(i.num_rows * :avg_index_entry_size / (:block_size * :block_occupancy_factor))) * :block_size)/1024/1024,2) AS total_est_mb_recover, ROUND(SUM(i.num_rows * :avg_index_entry_size)/SUM(s.bytes)*100,2) AS total_occupancy_pct FROM dba_ind_partitions i JOIN dba_segments s ON i.index_name = s.segment_name AND i.partition_name = s.partition_name AND i.index_owner = s.owner WHERE i.index_owner = 'PROPIETARIO' AND i.index_name = 'INDICE_TABLA'; PARTITION_NAME NUM_ROWS BLOCKS BLOCKS_TEORICOS DIFF_BLOCKS SIZE_MB EST_MB_RECOVER OCCUPANCY_PCT --------------- ------------ ------------ --------------- ------------ ----------- -------------- ------------- SYS_P15950 435,119,799 2,328,320 1,230,036 1,098,284 18,190.00 8,580.34 50.19 SYS_P16003 418,392,746 2,390,144 1,182,751 1,207,393 18,673.00 9,432.76 47.01 SYS_P16051 409,281,521 2,247,296 1,156,994 1,090,302 17,557.00 8,517.98 48.91 SYS_P16091 414,177,258 2,487,936 1,170,834 1,317,102 19,437.00 10,289.86 44.71 SYS_P16160 435,751,783 2,511,744 1,231,823 1,279,921 19,623.00 9,999.38 46.59 SYS_P16173 330,935,423 2,032,512 935,519 1,096,993 15,879.00 8,570.26 43.73 TOTAL_SIZE_MB TOTAL_USED_MB TOTAL_EST_MB_RECOVER TOTAL_OCCUPANCY_PCT ------------- ------------- -------------------- ------------------- 109359 51270 55390,59 46,88 ``` Teniendo en consideración los conceptos clave detallados anteriormente: **Resultados resumidos por partición** | Partición | Filas | Bloques Reales | Bloques Teóricos | Diff Blocks | Tamaño Actual (MB) | Tamaño Recuperable (MB) | % Ocupación | | --- | --- | --- | --- | --- | --- | --- | --- | | P1 | 435,119,799 | 2,328,320 | 1,230,036 | 1,098,284 | 18,190 | 8,580 | 50.2 | | P2 | 418,392,746 | 2,390,144 | 1,182,751 | 1,207,393 | 18,673 | 9,433 | 47.0 | | P3 | 409,281,521 | 2,247,296 | 1,156,994 | 1,090,302 | 17,557 | 8,518 | 48.9 | | P4 | 414,177,258 | 2,487,936 | 1,170,834 | 1,317,102 | 19,437 | 10,290 | 44.7 | | P5 | 435,751,783 | 2,511,744 | 1,231,823 | 1,279,921 | 19,623 | 9,999 | 46.6 | | P6 | 330,935,423 | 2,032,512 | 935,519 | 1,096,993 | 15,879 | 8,570 | 43.7 | **Totales:** * Tamaño actual: ~109 GB * Tamaño usado: ~51 GB * Tamaño recuperable: ~55 GB * Ocupación global: 46 % ## Conclusión Tras analizar el comportamiento del índice B-tree sobre (ITEM NUMBER, EVENT\_DATE DATE), podemos relacionar todos los elementos observados y **entender claramente el origen de la fragmentación**. En primer lugar, descartamos causas clásicas: * No existen DELETEs ni actualizaciones que reduzcan cardinalidad. * DBMS\_SPACE.UNUSED\_SPACE devuelve 0 bloques sin uso por lo que no hay espacio sin formatear dentro del segmento. * El PCTFREE no explica una ocupación cercana al 46 %, ya que incluso tras un rebuild Oracle puede llenar los leaf nodes hoja hasta ~90–95 %. Por tanto, la diferencia entre: * ~109 GB de tamaño real * ~51 GB de tamaño teórico necesario * ~55 GB recuperables tras rebuild no puede atribuirse a espacio no utilizado, sino a leaf nodes parcialmente ocupados generados por splits. El análisis del patrón de inserción explica el fenómeno: * ITEM (NUMBER) no es consecutivo. * EVENT\_DATE (DATE) es creciente dentro de cada ITEM. * El índice está ordenado por (ITEM, EVENT\_DATE). Esto provoca una combinación de comportamientos: * Cuando se insertan claves intermedias dentro del rango de un mismo ITEM, se producen splits 50/50, dejando dos bloques aproximadamente al 50 % de ocupación. * Cuando la nueva clave es la mayor dentro del bloque (fecha creciente al final), se producen splits 99/1, que también introducen bloques parcialmente vacíos. A gran escala (cientos de millones de filas por partición), la acumulación de estos splits explica matemáticamente la ocupación observada (~44–50 %). Además, Oracle no proporciona una vista que permita monitorizar directamente cuántos splits han ocurrido. El único modo práctico de detectarlos es indirectamente, comparando: * Bloques reales del segmento * Bloques teóricos necesarios según el tamaño medio de entrada Cuando la desviación es estructural, homogénea entre particiones y coherente con el patrón de inserción, la explicación más sólida es el comportamiento interno del B-tree ante splits de leaf nodes. **En definitiva:** La fragmentación observada no es un síntoma de corrupción ni de mala configuración, sino una **consecuencia natural del algoritmo de mantenimiento del B-tree bajo un patrón específico de inserciones**. El rebuild no “arregla un problema lógico”, sino que simplemente recompone los leaf nodes eliminando el espacio generado por años de splits acumulados.