Oracle Clustering Factor
Version 12.1.0.1

General Information
Library Note Morgan's Library Page Footer
The Library is currently in the process of being upgraded from Oracle Database Version 11.2.0.3 to 12.1.0.1. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.
 
Weakly Clustered Data Distribution Demo
conn uwclass/uwclass@pdbdev

-- create demo table
CREATE TABLE t1 (
doc_id    VARCHAR2(10),
person_id NUMBER(7),
doc_name  VARCHAR2(45));

-- create document id sequence
CREATE SEQUENCE seq_t;

-- load demo table
DECLARE
 t_docname dbms_sql.VARCHAR2_TABLE;

 CURSOR c IS
 SELECT object_name
 FROM dba_objects_ae;
BEGIN
  OPEN c;
  LOOP
    FETCH c BULK COLLECT INTO t_docname LIMIT 250;

    FORALL i IN 1..t_docname.COUNT
    INSERT INTO t1
    (doc_id, person_id, doc_name)
    VALUES
    (seq_t.NEXTVAL, MOD(seq_t.CURRVAL, 233), t_docname(i));

    EXIT WHEN c%NOTFOUND;
  END LOOP;
  COMMIT;
  CLOSE c;
END;
/

-- look at the first 300 rows of data
SELECT *
FROM t1
WHERE rownum < 301;

-- verify the data for each person is equivalent
SELECT person_id, COUNT(*)
FROM t1
GROUP BY person_id
ORDER BY 1;

-- begin tracing
set autotrace traceonly

-- select all documents belonging to person 221
SELECT doc_name
FROM t1
WHERE person_id = 221;
-- save the autotrace / note Oracle does an FTS (full table scan)

-- create a normal B*Tree index
CREATE INDEX ix_t1_person_id
ON t1(person_id);

exec dbms_stats.gather_table_stats(USER, 'T1', CASCADE=>TRUE);

set autotrace off

SELECT clustering_factor
FROM user_indexes
WHERE table_name = 'T1';

set autotrace traceonly

-- repeat the select ... note Oracle still does an FTS
SELECT doc_name
FROM t1
WHERE person_id = 221;

set linesize 121

-- force Oracle to use the index
SELECT /*+ INDEX(t1 ix_t1_person_id) */ doc_name
FROM t1
WHERE person_id = 221;
-- note that the cost went up

set autotrace off
 
Highly Clustered Data Distribution Demo
-- recreate sequence object
DROP SEQUENCE seq_t;
CREATE SEQUENCE seq_t;

-- create an new identical table and load it with each person's documents highly clustered
CREATE TABLE t2 AS
SELECT * FROM t1
WHERE 1=2;

DECLARE
 x PLS_INTEGER;
BEGIN
  INSERT INTO t2
  (doc_id, doc_name)
  SELECT rownum, object_name FROM dba_objects_ae;

  FOR i IN 1..235
  LOOP
    UPDATE t2
    SET person_id = i
    WHERE person_id IS NULL
    AND rownum < 234;

    SELECT COUNT(*)
    INTO x
    FROM t2
    WHERE person_id IS NULL;

    EXIT WHEN x = 0;
  END LOOP;
  COMMIT;
END;
/

-- look at the first 300 rows of data
SELECT *
FROM t2
WHERE rownum < 301;

-- verify the data for each person is equivalent
SELECT person_id, COUNT(*)
FROM t2
GROUP BY person_id
ORDER BY 1;

-- begin tracing
set autotrace traceonly

-- select all documents belonging to person 221
SELECT doc_name
FROM t2
WHERE person_id = 221;
-- save the autotrace / note Oracle does an FTS (full table scan)

-- recreate a B*Tree index
CREATE INDEX ix_t2_person_id
ON t2(person_id);

exec dbms_stats.gather_table_stats(USER, 'T2', CASCADE=>TRUE);

SELECT clustering_factor
FROM user_indexes
WHERE table_name = 'T2';

-- repeat the query without a hint
SELECT doc_name
FROM t2
WHERE person_id = 221;

-- note that Oracle uses the index and the cost is much lower
 
Related Queries
Another Index Quality Query SELECT dt.table_name, di.index_name, blocks, clustering_factor, (blocks/clustering_factor) CF_RATIO
FROM dba_tables dt, dba_indexes di
WHERE dt.owner NOT LIKE '%SYS%'
AND dt.owner = di.owner
AND dt.table_name = di.table_name
AND dt.blocks > 0
AND di.clustering_factor > (blocks*5)
ORDER BY 5;

Related Topics
Indexes
SYS_OP_LBID
SYS_OP_RPB

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2014 Daniel A. Morgan All Rights Reserved