This discussion is archived
1 Reply Latest reply: Feb 6, 2013 3:28 PM by rp0428 RSS

updating Indexes

987518 Newbie
Currently Being Moderated
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.
  • 1. Re: updating Indexes
    rp0428 Guru
    Currently Being Moderated
    WRONG FORUM!

    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.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points