Oracle Live

11/04/2016

Indices – Clustering Factor

Filed under: Laboratorios — mogukiller @ 10:56 pm

Clustering factor es un indicador de las estadisticas de un indice que me indica lo ordenados que estan los valores de una tabla respecto a los del indice.
Si el CF es proximo al numero de bloques significa que los valores (columnas a las que apunta el indice) de la tabla estan ordenados favoreciendo el index scan.
Si el CF es proximo al numero de filas significa que los valores (columnas a las que apunta el indice) de la tabla estan desordenados favoreciendo el full table scan.

-- Creamos la tabla</pre>
CREATE TABLE CF_TABLE TABLESPACE DATOS AS SELECT dbms_random.random AS ID_VALOR,dbms_random.string('u',25) AS VALOR from dual connect by level <110000;

EXEC DBMS_STATS.GATHER_TABLE_STATS (OWNNAME => 'MOGU',TABNAME =>'CF_TABLE', CASCADE=>TRUE, NO_INVALIDATE=>FALSE);

COL TABLE_NAME FOR A30
SELECT TABLE_NAME, NUM_ROWS, BLOCKS FROM DBA_TABLES WHERE TABLE_NAME ='CF_TABLE';
/*
TABLE_NAME                       NUM_ROWS     BLOCKS
------------------------------ ---------- ----------
CF_TABLE                           109998        628
*/

-- Creamos un indice sobre el campo id_valor

CREATE INDEX IDX_ID_VALOR ON CF_TABLE(ID_VALOR);

COL INDEX_NAME FOR A30
SELECT TABLE_NAME, INDEX_NAME, BLEVEL, LEAF_BLOCKS, DISTINCT_KEYS,CLUSTERING_FACTOR FROM DBA_INDEXES WHERE INDEX_NAME='IDX_ID_VALOR';
/*
TABLE_NAME                     INDEX_NAME                         BLEVEL LEAF_BLOCKS DISTINCT_KEYS CLUSTERING_FACTOR
------------------------------ ------------------------------ ---------- ----------- ------------- -----------------
CF_TABLE                       IDX_ID_VALOR                            1         407        109998            109805 <<<<< Muy desordenado
*/

-- Probamos con una query para forzar un range scan
SELECT ID_VALOR, VALOR FROM CF_TABLE WHERE ID_VALOR >0 AND ID_VALOR <10000000;

/*
253 rows selected.

------------------------------------------------------------------------------
| Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |          |   256 |  8448 |   173   (1)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| CF_TABLE |   256 |  8448 |   173   (1)| 00:00:01 |
------------------------------------------------------------------------------
*/

-- Si forzasemos por HINT el uso del indice

SELECT /*+ INDEX(CF_TABLE IDX_ID_VALOR) */ ID_VALOR, VALOR FROM CF_TABLE WHERE ID_VALOR >0 AND ID_VALOR <10000000;
/*
----------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |              |   256 |  8448 |   258   (0)| 00:00:01 |    <<<<< El coste es mucho mayor que por full scan
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| CF_TABLE     |   256 |  8448 |   258   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | IDX_ID_VALOR |   256 |       |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------
*/
-- Ordenamos el valor de la tabla

CREATE TABLE CF_TABLE_AUX TABLESPACE DATOS AS SELECT * FROM CF_TABLE ORDER BY ID_VALOR;
CREATE INDEX IDX_ID_VALOR2 ON CF_TABLE_AUX(ID_VALOR);
EXEC DBMS_STATS.GATHER_TABLE_STATS (OWNNAME => 'MOGU',TABNAME =>'CF_TABLE_AUX', CASCADE=>TRUE, NO_INVALIDATE=>FALSE);

COL INDEX_NAME FOR A30
SELECT TABLE_NAME, INDEX_NAME, BLEVEL, LEAF_BLOCKS, DISTINCT_KEYS,CLUSTERING_FACTOR FROM DBA_INDEXES WHERE INDEX_NAME LIKE 'IDX_ID_VALOR%';
/*
TABLE_NAME                     INDEX_NAME                         BLEVEL LEAF_BLOCKS DISTINCT_KEYS CLUSTERING_FACTOR
------------------------------ ------------------------------ ---------- ----------- ------------- -----------------
CF_TABLE_AUX                   IDX_ID_VALOR2                           1         283        109998               587    <<<<< Proximo al numero de bloques
CF_TABLE                       IDX_ID_VALOR                            1         407        109998            109805
*/

SELECT ID_VALOR, VALOR FROM CF_TABLE_AUX WHERE ID_VALOR >0 AND ID_VALOR <10000000;

/*
253 rows selected.

-----------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |               |   256 |  8448 |     4   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| CF_TABLE_AUX  |   256 |  8448 |     4   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | IDX_ID_VALOR2 |   256 |       |     2   (0)| 00:00:01 |    <<<<<< Se observa un cambio de plan y una reduccion en el coste.
-----------------------------------------------------------------------------------------------------
*/
<pre>

Dejar un comentario »

Aún no hay comentarios.

RSS feed for comments on this post. TrackBack URI

Responder

Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Cerrar sesión / Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión / Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión / Cambiar )

Google+ photo

Estás comentando usando tu cuenta de Google+. Cerrar sesión / Cambiar )

Conectando a %s

Blog de WordPress.com.

A %d blogueros les gusta esto: