Listing 8.10  Locate Bitmapped candidates for cost-based optimizer
 

rem idx_bad2.sql -- Copyright (c) 1996 by Donald K. Burleson

set pagesize 60;

set linesize 100;

column c1 heading 'Index'      format a25;

column c2 heading 'Status'     format a10;

column c3 heading 'Level'      format 999;

column c4 heading 'dist. Keys' format 99,999,999;

spool ind_bad2.lst;

prompt Change to bittmapped index if distinct_keys < 50

select

  owner||'.'||index_name      c1,

  status                      c2,

  blevel                      c3,

  distinct_keys               c4

from Remote DBA_indexes

where

distinct_keys < 50 and leaf_blocks > 500

order by distinct_keys desc;

spool off;

Listing 8.11 shows the output from this script:

Listing 8.11  Bitmapped candidate report for cost-based optimizer
 

sql>  @idx_bad2

Index                     Status     Level  dist. Keys

------------------------- ---------- ----- -----------

DON.DON_FK_ACT            VALID          2          47

DON.CSB_EK_MONTH_YEAR     VALID          2          37

DON.DON_EK_ROLE_TY_DEST   VALID          2          10

DON.DON_FK_SRC            VALID          2          10

DON.SHL_FK_SRC            VALID          2           9

DON.DON_FK_PLT            VALID          2           4

DON.INF_EK_ABBR_CD_LOADED VALID          2           4

DON.DON_FK_SPT            VALID          2           4

DON.INV_FK_INT            VALID          2           3

DON.INV_FK_SRC            VALID          2           1

DON.DON_FK_RAT            VALID          2           1

11 rows selected.

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

|