Home » SQL & PL/SQL » SQL & PL/SQL » Merge on Table with UNUSABLE unique index (10g)
Merge on Table with UNUSABLE unique index [message #419167] Thu, 20 August 2009 04:08 Go to next message
Messages: 17
Registered: June 2009
Location: Manchester, UK
Junior Member
I have a procedure that does the following:
MERGE STMT ... (with large no of records)

Although droping the index before the merge helps, the index creation time is also significant so i tried to the following

SET INDEXES unusable (alter index indexname unusable)
MERGE STMT ... (with large no of records)
rebuild index (alter index indexname rebuild)

My problem: i cannot do the Merge Now :
ERROR at line 2:
ORA-26026: unique index IDS_WAREHOUSE.BTN_IDX_00_1 initially in unusable state

Apparently I cannot have a unusable unique index while doing a DML ?

Am I doing this right? and can anyone please suggest a better way to do this that drop/create index in this case.

[Updated on: Thu, 20 August 2009 04:15]

Report message to a moderator

Re: Merge on Table with UNUSABLE unique index [message #419168 is a reply to message #419167] Thu, 20 August 2009 04:26 Go to previous message
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
If it wasn't an unique index then you'd get away with it, but unique indexes are treated slightly differently.


I wouldn't have thought that the index rebuild time would be noticably different to the time it took to create the index from scratch.
Previous Topic: sql Query Taking To much time to be execute (merged 4)
Next Topic: Error while executing procedure
Goto Forum:

Current Time: Fri Oct 28 19:15:43 CDT 2016

Total time taken to generate the page: 0.11454 seconds