| |
 |
|
Oracle 8 Tips
by Burleson Consulting |
The Data Warehouse Development Life Cycle
Oracle Features for the Data Warehouse
Oracle Bitmapped Indexes
Other system that are using Oracle rule-based optimizer must examine
each index to determine the cardinality. Listing 8.12 will check all
of your b-tree indexes and provide you with a list of candidates in
ascending order of cardinality.
Listing 8.12 bitmap.sql identifies low cardinality indexes for
bitmapped indexes.
rem Written by Don Burleson
prompt Be patient. This can take awhile . . .
set pause off;
set echo off;
set termout off;
set linesize 300;
set pagesize 999;
set newpage 0;
set feedback off;
set heading off;
set verify off;
rem First create the syntax to determine the cardinality . . .
spool idx1.sql;
select 'set termout off;' from dual;
select 'spool idx2.lst;' from dual;
select 'column card format 9,999,999;' from dual;
select 'select distinct count(distinct '
||a.column_name
||') card, '
||''' is the cardinality of '
||'Index '
||a.index_name
||' on column '
||a.column_name
||' of table '
||a.table_owner
||'.'
||a.table_name
||''' from '
||index_owner||'.'||a.table_name
||';'
from Remote DBA_ind_columns a, Remote DBA_indexes b
SEE CODE DEPOT FOR FULL SCRIPT
;
|
