|
|
| |
 |
|
Oracle Tips
by Burleson Consulting
|
The Data Warehouse Development Life Cycle
Oracle Features for the Data Warehouse
How to create a clustered index
Listing 8.2 A SQL*Plus
routine to locate clustered indexes
rem
idx_bad1.sql, © 1997 by Donald K. Burleson
set pagesize 60;
set linesize 100;
column c0 heading 'Table' format a8;
column c1 heading 'Index' format a18;
column c2 heading 'Level' format 999;
column c3 heading 'Clust Ftr' format 9,999,999;
column c4 heading '# rows' format 99,999,999;
column c5 heading 'Clust Pct' format 999.9999;
column c6 heading 'dist. Keys' format 99,999,999;
spool idx_bad1.lst;
break on c0 skip 1;
select
Remote DBA_indexes.table_name c0,
index_name c1,
blevel c2,
clustering_factor c3,
num_rows c4,
decode(clustering_factor,0,1,clustering_factor)/
decode(num_rows,0,1,num_rows) c5,
distinct_keys c6
from Remote DBA_indexes, Remote DBA_tables
SEE CODE DEPOT FOR FULL SCRIPT
and c5 < .25
order by c0, c5 desc;
spool off;
Here is a sample listing from this report:
SQL> @idx_bad1
Table Index Level Clust Ftr # rows reorg
dist. Keys
-------- ------------ ----- ---------- ---------- -------
----------
INV_LINE INV_LINE_ITEM_PK 2 62,107 1,910,034 .0325
1,912,644
ILI_FK_INV 2 164,757 1,910,034 .0339
1,659,625
ILI_FK_ACT 2 283,343 1,910,034
.0436 47
ILI_EK_CCHS_ACCT 3 1,276,987 1,910,034
.1450 25,041
Now, let’s inspect this listing to see the clustering status of our
indexes. The indexes are listed within each table heading in
descending order of their clustering factor, with the most clustered
indexes at the bottom of the list. In the example above we see the
INV_LINE_ITEM_PK index with a clustering factor of 62,107,
indicating that the INV_LINE table has been loaded in nearly the
same physical order as this index.
If
the value for clustering factor approaches the number of blocks in
the base table, then the index is said to be clustered. If the
clustering factor is greater than the number of blocks in the base
table and approaches the number of rows in the base table, then the
index is un-clustered.
 |
Expert Remote DBA
BC is America's oldest and largest Remote DBA Oracle support
provider. Get real Remote DBA experts, call
BC Remote DBA today. |
 |
|
|
|
|
