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.

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:

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

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

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

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.

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:

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:

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)

-- ===============================================
-- 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

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.