1

We have a performance issue for a table which has indexes. It takes 1 hour to load, but if we drop the indexes and recreate them after each load it executes in 30 minutes.

Is it a feasible solution to drop and recreate the indexes for that table after each cycle?

APC
  • 144,005
  • 19
  • 170
  • 281
Mahesh Malpani
  • 1,782
  • 16
  • 27
  • Please tell me why you are dropping and recreating an index? – Ed Heal Feb 03 '18 at 09:00
  • because the DML performance is very slow and somehow when we drop and recreated same index it took less time. There is no logic behind drop and recreate but it helped so we did. can you please help if we can avoid it ? I dont thinkthis drop and recreate is a solution for performance – Mahesh Malpani Feb 03 '18 at 09:13
  • 2
    The short answer is maybe. However if you have so many indexes on the table that dropping and recreating them is quicker than maintaining them the problem might be the indexes. Perhaps you have too many of them, maybe they are badly structured.You should consider auditing your index usage. Perhaps you have some indexes where you would be better off just dropping them. – APC Feb 03 '18 at 09:30
  • 1
    do you drop the index before the load and recreate after the load? – miracle173 Feb 03 '18 at 09:32
  • Is it one big insert statement or many of them? Are they bitmap indexes? How many indexes does the table have? Did you monitor the index usage? – Wernfried Domscheit Feb 03 '18 at 13:45

1 Answers1

3

It sounds like what you're trying to do is a bulk data load into a table with several indexes, but you're finding that the load is unacceptably slow if the indexes are present

If that's your problem, Oracle allows you to disable and rebuild indexes, rather than having to drop and recreate them: https://docs.oracle.com/cd/E18283_01/server.112/e17120/indexes004.htm#CIHJCEAJ

Oracle also give this guidance on when to use this feature:

Use unusable or invisible indexes when you want to improve the performance of bulk loads, test the effects of removing an index before dropping it, or otherwise suspend the use of an index by the optimizer.

hugh
  • 2,237
  • 1
  • 12
  • 25