HomeArticlesSlideshowsAsk BobLogin or Register
NewsCustom SearchContactLinks
Quick check of index condition
ORACLE STUFFS
11-Jan-2011
One of task when checking Data Warehouse schema condition could be following quick index check. We look for:
  • Global indexes (suspicious in general, tere must be good reason to use them in data warehouse)
  • B-TREE indexes with low cardinality
  • B-TREE with high number of AVG_DATA_BLOCKS_PER_KEY (over 300) - such are reasonable to be made unusable before load process and rebuilt after

WITH XC AS(
 Select
  XC.INDEX_OWNER
 ,XC.INDEX_NAME
 ,RTRIM(XMLAGG(XMLELEMENT(C,case when TC.NULLABLE='Y' then '('||XC.COLUMN_NAME||')'else XC.COLUMN_NAME end||',')).EXTRACT('//text()'),',') AS COLS 
 FROM ALL_IND_COLUMNS XC 
 JOIN ALL_TAB_COLUMNS TC ON TC.OWNER=XC.TABLE_OWNER and TC.TABLE_NAME=XC.TABLE_NAME AND TC.COLUMN_NAME=XC.COLUMN_NAME
 Group by XC.INDEX_OWNER,XC.INDEX_NAME
)
SELECT 
 X.TABLE_NAME
,X.INDEX_NAME
,XC.COLS
,X.INDEX_TYPE
,X.NUM_ROWS
,X.DISTINCT_KEYS
,X.BLEVEL+1 as HEIGHT
,cast(X.DISTINCT_KEYS*100/nullif(X.NUM_ROWS,0) as NUMBER(4,1)) as CARDINALITY
,cast(X.NUM_ROWS/nullif(X.DISTINCT_KEYS,0) as NUMBER(10,1)) as ROWS_PER_KEY
,X.AVG_DATA_BLOCKS_PER_KEY
,X.AVG_LEAF_BLOCKS_PER_KEY
,CASE WHEN X.PARTITIONED='NO' AND T.PARTITIONING_TYPE IS NOT NULL THEN 'GLOBAL'
   WHEN T.PARTITIONING_TYPE IS NOT NULL THEN 'LOCAL' ELSE 'N/A' end as GLOBALITY
,NVL(T.PARTITIONING_TYPE, 'NONE') as PARTITIONING
FROM ALL_INDEXES X
LEFT JOIN ALL_PART_TABLES T on T.TABLE_NAME=X.TABLE_NAME and T.TABLE_NAME=X.TABLE_NAME and T.OWNER=X.TABLE_OWNER
JOIN XC ON X.INDEX_NAME=XC.INDEX_NAME and X.OWNER=XC.INDEX_OWNER
where X.OWNER = '&SCHEMA'

[Download]
Ludek Bob Jankovsky
All Right Reserved © 2007, Designed by Bob Jankovsky