BC remote Oracle DBA - Call (800) 766-1884  
Oracle Consulting Oracle Training Development

Remote DBA

Remote DBA Plans  

Remote DBA Service

Remote DBA RAC

   
Remote DBA Oracle Home
Remote DBA Oracle Training
Remote DBA SQL Tuning Consulting
Remote DBA Oracle Tuning Consulting
Remote DBA Data Warehouse Consulting
Remote DBA Oracle Project Management
Remote DBA Oracle Security Assessment
Remote DBA Unix Consulting
Burleson Books
Burleson Articles
Burleson Web Courses
Burleson Qualifications
Oracle Links
Remote DBA Oracle Monitoring
Remote DBA Support Benefits
Remote DBA Plans & Prices
Our Automation Strategy
What We Monitor
Oracle Apps Support
Print Our Brochure
Contact Us (e-mail)
Oracle Job Opportunities
Oracle Consulting Prices





   

 

 

 

Remote DBA services

Remote DBA Support

Remote DBA RAC

Remote DBA Reasons

Remote Oracle Tuning

Remote DBA Links

Oracle DBA Support

Oracle DBA Forum

Oracle Disaster

Oracle Training

Oracle Tuning

Oracle Training

 Remote DBA SQL Server

Remote MSSQL Consulting

Oracle DBA Hosting

Oracle License Negotiation

 

 


 

 

 

 

   
 

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
 


This is an excerpt from "High Performance Data Warehousing", copyright 1997. To learn more about Oracle, try "Oracle Tuning: The Definitive Reference", by Donald K. Burleson.  You can buy it direct from the publisher at 30% off here:
http://www.rampant-books.com/book_1002_oracle_tuning_definitive_reference_2nd_ed.htm

|

 

Remote DBA Service
 

Oracle Tuning Book

 

Advance SQL Tuning Book 

BC Oracle support

Oracle books by Rampant

Oracle monitoring software

 

 

 

 

 

 

BC Remote Oracle Support

Remote DBA

Remote DBA Services

Copyright © 1996 -  2013 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.