Hi All,
I have couple of large (size > 30 GB) production tables (partitioned) on my 11.0.1.7 DB.
Table stores financial data and that are always inserted into. There is no DELETE/UPDATE on it.
PK values are populated with Oracle sequences.
All PK are global non-partitioned indexes. We do purge past data by truncating old partitions as and when required.
There are couple of PK which are highly fragmented. (Due to 90/10 Split and concurrent conventional path Inserts).
Most of the PKs are on single column.
Almost all queries on above PKs are using UNIQUE SCAN and PK columns are being used as join condition between tables.
Is there any solution to deal with such large PK considering
1) Minimizing Down time for large PK re-org due to partition truncation. (I have observed that ONLINE REBUILD is causing lot of locking issues due to HIGH DML on my tables)
2) Most of PKs have BLEVEL=3. Should high BLEVEL cause any performance issue where index access is UNIQUE SCAN ?
Since i know index access pattern (no RANGE SCAN ),I am considering converting indexes into REVERSE key indexes. But before that, i want to check other options available that can address my issue.
Most of my PKs have similar stats like this :
INDEX_NAME LAST_ANALYZED COLUMN_NAME COLUMN_POSITION TABLESPACE_NAME STATUS INDEX_TYPE NUM_ROWS BLEVEL CLUSTERING_FACTOR DESCEND
------------------------- -------------------- -------------------- --------------- ------------------------------ -------- ---------- ---------- ---------- ----------------- ----------
PK_EVENT_STATE_LOG 15-jun-2010:22:41:04 EVENT_STATE_LOG_ID 1 OTC_IDX VALID NORMAL 1443727263 3 1335891875 ASC
Index Size
SEGMENT_NAME MB
--------------------------------------------------------------------------------- ----------
PK_EVENT_STATE_LOG 38636
select STATISTIC_NAME,VALUE from V$SEGMENT_STATISTICS where OBJECT_NAME='PK_EVENT_STATE_LOG' and OBJECT_TYPE='INDEX';
STATISTIC_NAME VALUE
---------------------------------------------------------------- --------------------
logical reads 1420951184
buffer busy waits 42222626
gc buffer busy 0
db block changes 360275408
physical reads 1260176
physical writes 6556519
physical reads direct 2191
physical writes direct 2564233
gc cr blocks received 0
gc current blocks received 0
ITL waits 450
row lock waits 23886350
space used 34370621678
space allocated 40852520960
segment scans 0
My tables are RANGE partitioned. Should i consider GLOBAL PARTITIONED INDEXES (Hash Partition on PK column) as an other alternative ? I am not sure, what benefits its gonna give ;-)
(Index re-org is certainly a concern with this option as well.)