Index rebuild, coalesce or reserve key

I know it's been discussed to death on this newsgroup recently, but I still cannot find best solution which suits me.

On my productions, I have five largest indexes which keeps growing for more than three monthes, while the tables don't. Those indexes consist of sequence number and/or time stamp. The tables have been purged everyday, but small number of unfinished transactions left, which causes the index leaf block cannot be reused. After cloning database, I analyze indexes those indexes.
SELECT index_name,height,
del_lf_rows,
lf_rows - del_lf_rows lf_rows_used,
to_char(del_lf_rows / (lf_rows)*100,'999.99999') ratio, pct_used
FROM index_details
WHERE lf_rows != 0 and lf_rows > 1000000 order by lf_rows - del_lf_rows
/

                                  Deleted      Used % Deleted
Index Name               HEIGHT Leaf Rows Leaf Rows Leaf Rows   

-------------------- ---------- --------- --------- ----------
----------
IDX_GAMEDEALER                4   2009397   6178845   24.54003        
39
PK_GAMEDEALER                 4         2   6178845     .00003        
66
IDX_GAMEPLAYER                4     19826   6178862     .31984        
11
PK_GAMEPLAYER                 4     18322   6178862     .29565        
20
IDX_GAMEPLAYERSID             4    542545   6178862    8.07190        

  I don't mind the indexes waste some space as long as there is no performance degrade. However, they cannot grow forever.

  I suppose I have three options.
1. Rebuild the indexes: which is quickest way, only take 30 minutes. I have two issues.
Firstly, most posts I read in this group are against regualarly rebuild indexes, so do articles in asktom.oracle.com -- indexes, like people, have a certain "weight" they like to be at. Secondly, we are 24x7 shop. I cannot get the downtime appoval from the manager, just because the indexes need rebuild. I know it has online option. But like most oracle feature, there are always bugs with it. I search in bug database, there are so many bugs about rebuild index online.The one I meet this time is ORA-600 [KDIC_CREATE_JOURNAL_TABLE_1] WHEN REBUILD INDEX ONLINE. 2. coalesce: I make a test case. It does what I want to achieve, and I also test it in the clone database. It takes about an hour for each index, which much longer than rebuild. It's ok, since it doesn't block other transactions.However, when I run it in production, it runs forever more than 24 hours for one index so far. I'm not sure what happens. It looks like a bug. I make a TAR. EVENT of v$session_wait is "null event", which indeed is another bug.

3. Use reserve key for the pure sequence number index. I see disadvantages of reserve key are

• cannot participate in range-scan.
  • After checking v$sql, I cannot see it is an issue. Anyway, they are sequence number.
• the index efficiency for a reverse key is low compared with nonreverse.
  • But it should be higher than the one I have.
• the clustering factor of the index is change which causes the CBO to change its access path. -I don't know how serious this can be.

    BTW, I use RAC, which is the reserve key designed for.

Any other issue with reserve key?

Thank in advance,
Bin Received on Tue Dec 09 2003 - 23:43:48 CST