| Data Distribution Demo |
-- 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;
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 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;
-- reload the table with each person's documents highly clustered
DECLARE
x PLS_INTEGER;
BEGIN
INSERT INTO t2
(doc_id, doc_name)
SELECT rownum, object_name FROM dba_objects;
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 |
| |
Index Quality Query |
SELECT
t.table_name, i.index_name, t.blocks TABLE_BLOCKS, i.clustering_factor
FROM user_tables t, user_indexes i
WHERE t.table_name = i.table_name
ORDER BY i.clustering_factor/t.blocks; |
|
