Oracle Database Discussions

Hey all,

I am currently redesigning a weekly process (weekly coz we pre determined the rate of index fragmentation) for specific indexes that get massive updates. The old process has proved to be able to fix and maintain reports performance.

In this process we rebuild specific indexes using the below command:

Alter index index_name rebuild online;

This command takes around 10 min for selected indexes.

Testing the below took 2 min for 6 or 7 indexes.
Drop Index Index_Name;
Create Index Index_Name on Table_name (Col1, col, ..);


I know that indexes might not be used, and the application performance would be degraded with stale or non-existent stats. But our production and all our test DBs have procedures that daily gather stats on them.

I tested the below script to make sure that execution plan does not change:
SELECT ProductID, ProductName, MfrID FROM PRODUCT WHERE MFRID = 'Mfr1';


-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 37 | 3737 | 13 (0)|
| 1 | TABLE ACCESS BY INDEX ROWID| PRODUCT | 37 | 3737 | 13 (0)|
| 2 | INDEX RANGE SCAN | PRODUCT_X1 | 37 | | 3 (0)|
-------------------------------------------------------------------------------

dropping PRODUCT_X1 and recreating it only changed the cost to 12.
Gathering the stats again took the cost to 14.
No performance issues were faced and index was still used.


My question is: Is there any oracle recommendation that requires rebuilding the index instead of dropping and recreating it?
Is there any side effect to my approach that I did not consider?


Thank you