| |
 |
|
Oracle 8 Tips
by Burleson Consulting |
The Data Warehouse Development Life Cycle
Oracle Features for the Data Warehouse
Understanding Oracle indexes
If you are collecting index statistics (i.e. your
Oracle system utilizes the cost-based optimizer), you can see
additional internal details for an index by running the report shown
in Listing 8.1.
Listing 8.1 index.sql shows the details for indexes.
Rem ©
1997 by Donald K. Burleson
set
pagesize 999;
set
linesize 100;
column c1
heading 'Index' format a19;
column c3
heading 'S' format a1;
column c4
heading 'Level' format 999;
column c5
heading 'Leaf Blks' format 999,999;
column c6
heading 'dist. Keys' format 99,999,999;
column c7
heading 'Bks/Key' format 99,999;
column c8
heading 'Clust Ftr' format 9,999,999;
column c9
heading 'Lf/Key' format 99,999;
spool
index.lst;
select
owner||'.'||index_name c1,
substr(status,1,1) c3,
blevel
c4,
leaf_blocks c5,
distinct_keys c6,
avg_data_blocks_per_key c7,
clustering_factor c8,
avg_leaf_blocks_per_key c9
from
Remote DBA_indexes
SEE CODE DEPOT FOR FULL SCRIPT
spool
off;
Here is the output of index.sql.
Index S Level Leaf
Blks dist. Keys Bks/Key Clust Ftr Lf/Key
------------------- - -----
--------- ----------- ------- ---------- -------
DON.LOB_FACT1_PK V 2
25,816 3,511,938 1 455,343 1
DON.DON_EK_CUST_INV V 2
23,977 2,544,132 1 1,764,915 1
DON.DON_FK_GLO_DEST V 2
23,944 22,186 112 2,493,095 1
DON.JEN_FK_SHP V 2
22,650 1,661,576 1 339,031 1
DON.DON_FK_ORL_ORIG V 2
21,449 404 806 325,675 53
DON.PAT_FK_JEN V 2
21,181 2,347,812 1 996,641 1
DON.JEN_FK_LOB V 2
19,989 187 4,796 896,870 106
DON.FACT1_PK V 2
19,716 3,098,063 1 1,674,264 1
DON.DON_FK_CAR V 2
18,513 689 390 268,859 26
DON.DON_EK_ROLE V 2
17,847 10 24,613 246,134 1,784
DON.DON_FK_SPT V 2
16,442 4 46,872 187,489 4,110
DON.INV_EK_INV_NUM V 2
16,407 2,014,268 1 518,206 1
DON.DON_FK_ORL_DEST V 2
15,863 385 692 266,656 41
DON.DON_FK_SRC V 2
15,827 10 17,469 174,694 1,582
DON.INV_LINE_ITEM V 2 14,731 2,362,216 1
102,226 1
|
