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.
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);
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);
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;