I have a table of about 30 columns and 10 global unpartitioned indexes on it. The table is partitioned using Interval partitioning. Partitionig is done on a daily basis.
Data in the table is huge and is online(Insertions and extraction of data keeps on happening round the clock). I run a script on a daily baisis to delete 10 days old parition. After deletion the indexes becomes unusable, which approach should i follow to rebuild the indexes.
1. EXECUTE IMMEDIATE 'DELETE FROM DEMO PARTITION ( '|| x.partition_name|| ')';
EXECUTE IMMEDIATE 'ALTER TABLE DEMO DROP PARTITION '|| x.partition_name|| ;
i.e first deleting all the data in the partition and then dropping the parititon.
2. EXECUTE IMMEDIATE 'ALTER TABLE DEMO DROP PARTITION '|| x.partition_name|| ' UPDATE GLOBAL INDEXES';
i.e updating the index every time.
Which of these two approach will give me a faster performance?
If there is any other approcah please suggest.
I don't want the insertions to stop or extraction of data to stop.
As the forum title says this forum is ONLY for SQL Developer questions.
Please mark this thread ANSWERED and repost it in the SQL and PL/SQL forum SQL and PL/SQL
When you repost provide your 4 digit Oracle version.
Use option #2 - drop the partition and let Oracle update the global indexes. Option #1 only adds the substantial overhead of a DELETE that unnecessarily uses both REDO and UNDO and can take time.
Better would be to use partitioned indexes and avoid the index updating altogether.
No other support will be provided in this forum. If you need more help than the above repost the question.