CODE
Oracle Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
PSOUG Home Code Snippets Oracle Reference Oracle Functions PSOUG Forum Oracle Blogs Bookmark and Share
 
 Search the Reference Library pages:  

Free
Oracle Magazine
Subscriptions
and Oracle White Papers

Oracle Clustering Factor
Version 11.1
 

Data Distribution Demo

-- create demo table
CREATE TABLE t (
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;
BEGIN
  OPEN c;
  LOOP
    FETCH c BULK COLLECT INTO t_docname LIMIT 250;

    FORALL i IN 1..t_docname.COUNT
    INSERT INTO t
    (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 t
WHERE rownum < 301;

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

-- begin tracing
set autotrace traceonly

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

-- create a B*Tree index
CREATE INDEX ix_t_person_id
ON t(person_id);

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

SELECT clustering_factor
FROM user_indexes
WHERE table_name = 'T';

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

set linesize 121

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

set autotrace off
 
Highly Clustered Data Distribution Demo
-- empty the demo table
TRUNCATE TABLE t;

-- drop the index
DROP INDEX ix_t_person_id;

-- reload the table with each person's documents highly clustered
DECLARE
 x PLS_INTEGER;
BEGIN
  INSERT INTO t
  (doc_id, doc_name)
  SELECT rownum, object_name FROM dba_objects;

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

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

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

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

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

-- begin tracing
set autotrace traceonly

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

-- recreate a B*Tree index
CREATE INDEX ix_t_person_id
ON t(person_id);

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

SELECT clustering_factor
FROM user_indexes
WHERE table_name = 'T';

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

-- note that Oracle uses the index and the cost is much lower.
 
 
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us [276 users online]    © 2010 psoug.org