Oracle | Index Clustering Factor

Oracle Clustering Factor Version 21c

General Information

Library Note

Morgan's Library Page Header

ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.

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;