Cálculo de costo de acceso a filas por el optimizador basado en costos (CBO) usando Clustering Factor

El costo de un acceso a filas usando INDEX SCAN lo calcula el CBO con:
blevel + FF*leaf_blocks + FF*clustering_factor
Donde: FF es el "Factor de Filtro" que indica la proporción de registros que se recupera al aplicar el predicado de la query al índice. Clustering_factor es una estadística calculada por Oracle para cada índice que que refleja la magnitud de orden de las filas basado en los valores del índice:
Si el valor es cercano al número de bloques, entonces la tabla está muy bien ordenada. En este caso las entradas del índice en el mismo bloque hoja tienden a apuntar a filas en el mismo bloque de datos.
Si el valor es cercano al numero de filas, entonces la tabla no está ordenada con respecto al índice. En este caso, es improbable que las entradas en un bloque hoja del índice apunten a los mismos bloques de datos.
Cada término de esta fórmula representa:

blevel: Bloques de recorrido del árbol del índice
FF*leaf_blocks: Cantidad de bloques hojas que se deben recorrer en el índice
FF*clustering_factor: Cantidad de bloques de la tabla que se deben leer

La forma en que el tomador de estadísticas calcula el valor de clustering factor es:

The index is scanned in order.
The block portion of the rowid pointed at by the current indexed value is compared with that pointed at by the previous indexed value. This is achieved by comparing adjacent rowids in the index leaf block (the blocks themselves do not need to be visited).
If these rowids are pointing at different blocks then a counter is incremented.
This is continued throughout the whole index.
The resultant count is then stored.

Esta forma de calcular no considera para nada el efecto del cache, salvo en que los registros contiguos en el índice apunten al mismo bloque de datos.

Si consideramos:
CF: es clustering factor calculado.
ncache: es la cantidad de bloques que se mantendrán en cache.

El algoritmo queda:
Mantendremos una lista de ncache bloques referenciados, que inicialmente parte vacía.
CF parte en 0.
Se Recorren los registros ordenados por el índice en orden ascendente.
Sumamos 1 a CF si aparece un bloque que no está en la lista.
Si la lista está completa, se quita de ella el bloque que indique técnica LRU.
Se agrega el bloque a la lista.

Como script SQL:
set serverout on size 1000000
set ver off

declare
  v_owner varchar2(30) := upper('&1');
  v_index varchar2(30) := upper('&2');
  max_block_cached NUMBER := &3;
  v_query varchar2(4000);
  v_table_name varchar2(30);
  v_table_owner varchar2(30);
  TYPE TypRefCursor IS REF CURSOR;
  cic TypRefCursor;
  idx_rowid rowid;
  ROWID_TYPE    NUMBER;
  OBJECT_NUMBER NUMBER;
  RELATIVE_FNO  NUMBER;
  BLOCK_NUMBER  NUMBER;
  ROW_NUMBER    NUMBER;
  block_ant varchar2(100);
  dist_blocks number := 0;
  dist_rows number := 0;
  TYPE type_tab_numbers
       IS TABLE OF number
       INDEX BY BINARY_INTEGER;
  tab_blocks   type_tab_numbers;
  tab_count    type_tab_numbers;
  countid      number := 0;
  block_id integer;
begin
  /* Obtener v_query */
  begin
    select table_owner, table_name
    into   v_table_owner, v_table_name
    from   all_indexes
    where  index_name = v_index
    and    owner      = v_owner;
  exception
    when no_data_found then
      raise_application_error(-20001,'No existe indice '||v_owner||'.'||v_index);
  end;
  for cols in ( select column_name
                from   all_ind_columns
                where  index_name = v_index
                and    index_owner = v_owner
                order by column_position ) loop
    if ( v_query is null ) then
      v_query := 'select /*+ index_ffs(t '||v_index||') */ rowid from '||v_table_owner||'.'||v_table_name||' t where '||cols.column_name||' is not null';
    else
      v_query := v_query || ' or '||cols.column_name||' is not null';
    end if;

  end loop;
  dbms_output.put_line('Query: '||v_query);
  /* verificar explain plan */
  savepoint pre_explain;
  delete plan_table where statement_id='clustering_factor';
  execute immediate
        'explain plan set statement_id=''clustering_factor'' for '||v_query;
  dbms_output.put_line('Explain:');
  for ep in ( select id, operation||' '||options operation, OBJECT_OWNER, OBJECT_NAME
              from plan_table where statement_id='clustering_factor'
              order by id ) loop
    dbms_output.put_line('   '||ep.id
                       ||': '||ep.operation||' on '
                       ||ep.OBJECT_OWNER||'.'||ep.OBJECT_NAME);
    if ( ep.id = 0 and ep.operation != 'SELECT STATEMENT ' ) then
      raise_application_error(-20004,'Esta operacion debe ser "SELECT STATEMENT"');
    elsif ( ep.id = 1 and ( ep.operation != 'INDEX FAST FULL SCAN' or ep.OBJECT_OWNER != v_owner or ep.OBJECT_NAME != v_index ) ) then
      raise_application_error(-20004
        ,'Esta operacion debe ser "FAST FULL SCAN on '
         ||v_owner||'.'||v_index||'"');
    elsif ( ep.id > 1 ) then
      raise_application_error(-20004
        ,'El plan de ejecucion tiene mas de dos pasos');
    end if;
  end loop;
  rollback to pre_explain;
  /* Obtener clustering_factor  */
  open cic for v_query;
  loop
    fetch cic into idx_rowid;
    exit when cic%NOTFOUND;
    dbms_rowid.rowid_info(
        idx_rowid
      , ROWID_TYPE
      , OBJECT_NUMBER
      , RELATIVE_FNO
      , BLOCK_NUMBER
      , ROW_NUMBER);
    block_id := RELATIVE_FNO*100000+BLOCK_NUMBER;
    if ( tab_blocks.exists(block_id) ) then
      tab_count.delete(tab_blocks(block_id));
    else
      if ( tab_blocks.count > max_block_cached ) then
        tab_blocks.delete(tab_count(tab_count.first));
        tab_count.delete(tab_count.first);
      end if;
      dist_blocks := dist_blocks + 1;
    end if;
    countid := countid + 1;
    tab_count(countid) := block_id;
    tab_blocks(block_id) := countid;
    dist_rows := dist_rows + 1;
  end loop;
  close cic;
  dbms_output.put_line('num_rows    : '||dist_rows);
  dbms_output.put_line('CLUSTERING_FACTOR: '||dist_blocks);
end;
/