Hi,
I have the following table, in which a SUBS_ID is unique but INDEX_ID is not and REQUEST_ID is initially null for all records.
The table may be large (~1M rows) and although there might be multiple SUBS_ID with the same INDEX_ID - most records will have 1 to 1 relation.
CREATE GLOBAL TEMPORARY TABLE SUBS_HIERARCHY_TEMP
(
SUBS_ID NUMBER(10) NOT NULL,
INDEX_ID NUMBER(10) NOT NULL,
REQUEST_ID NUMBER(10)
)
ON COMMIT DELETE ROWS;
I have the following code which assigns a unique number (REQUEST_ID) to each distinct INDEX_ID using a sequence (this code may run in parallel and I REQUEST_ID must be unique globally):
...
cursor Request_id_cur is
select distinct INDEX_ID
from SUBS_HIERARCHY_TEMP;
...
begin
...
-- fill SUBS_HIERARCHY_TEMP with records, doesn't matter how, REQUEST_ID remains NULL
-- populate REQUEST_ID with a sequence
for SUB in Request_id_cur LOOP
v_UniqeIndex := SEQ_REQUEST_ID.nextval;
Update SUBS_HIERARCHY_TEMP
set REQUEST_ID = v_UniqeIndex
where INDEX_ID = SUB.INDEX_ID;
END LOOP;
...
end;
This works - but obviously not very efficient. If all records are 1 to 1 it's going to run 1M updates, yet I cannot use a simple update in case some SUBS_ID share the same INDEX_ID hence need to have the same REQUEST_ID.
I was trying to do it in one go using a merge statement but apparently Sequences are not allowed in the USING clause of the merge:
merge into SUBS_HIERARCHY_TEMP tab
using (select INDEX_ID, SEQ_REQUEST_ID.nextval as REQUEST_ID
from (select distinct INDEX_ID
from SUBS_HIERARCHY_TEMP)
)tmp
on (tab.INDEX_ID=tmp.INDEX_ID)
when matched then update
set REQUEST_ID=tmp.REQUEST_ID;
ORA-02287: sequence number not allowed here
Any ideas how to do it in one statement and avoid the loop?
I'm on 11.2.0.3 RHEL6 64bit.
Thanks in advance.