Oracle PLSQL

Oracle :show explain plan

select * from table(dbms_xplan.display);

EXPLAIN PLAN FOR statements

 

In fact, in a typical data warehouse environments, a bitmap index can be considered for any non-unique column.

   

In a data warehouse, B-tree indexes should be used only for unique columns or other columns with very high cardinalities (that is, columns that are almost unique). The majority of indexes in a data warehouse should be bitmap indexes.

 

One stage table, is a heap table, need always insert and delete, and we found it waste a lot of blocks.

How to fix this issue?

Use online –redefine.

In sqlserver, we can just create a cluster index on this table, then drop this index, it will be ok.

 

The following plsql is used to check the table's block used status: 

Check table's used space percent:

SELECT TABLE_NAME,HWM,AVG_USED_BLOCKS,

GREATEST(ROUND(100 * (NVL(HWM - AVG_USED_BLOCKS,0)/GREATEST(NVL(HWM,1),1) ), 2), 0) WASTE_PER,sysdate analyze_dt

from

(SELECT A.BLOCKS - B.EMPTY_BLOCKS - 1 HWM,table_name,

DECODE( ROUND((B.AVG_ROW_LEN * NUM_ROWS * (1 + (PCT_FREE/100)))/8192, 0),

0, 1,

ROUND((B.AVG_ROW_LEN * NUM_ROWS * (1 + (PCT_FREE/100)))/8192, 0)

) + 2 AVG_USED_BLOCKS

FROM USER_SEGMENTS A,

USER_TABLES B

WHERE SEGMENT_NAME = TABLE_NAME

and TABLE_NAME in ( SELECT TABLE_NAME FROM USER_TABLES WHERE TABLE_NAME LIKE 'XXX_%')

and SEGMENT_TYPE = 'TABLE'

)ORDER BY WASTE_PER DESC;