Hi,
I am currently using Oracle 11gR2
I am developing parallel process using dbms_parallel_execute package. I tried to create chunks by using DBMS_PARALLEL_EXECUTE.CREATE_CHUNKS_BY_SQL('mytask', l_chunk_sql, false) but I am unsuccessful to create a required chunk_sql. As a work around I created a number column to get the start_id and end_id and using dbms_parallel_execute.create_chunks_by_number_col()
but to update this number(ID) column I using PL/SQl blockto generate start_id and end_id. If I can achieve it using SQL then it will improve performance little bit more.
Table A
Col1 col2 .... coln ID
123 100 1
123 100 1
123 100 1
456 101 2
456 101 2
456 101 2
789 102 3
789 102 3
789 102 3
As I mentioned above I need to get the same ID number for group of col1values and ID value cannot be reused for different group. Is there anyway to achieve this in SQL query?
To achevie this I am using below PL/SQL
Declare
cursor c1 is select * from tableA;
v_id number :=0;
begin
for i in c1 loop
v_id := v_id +1;
update tableA set id = v_id where col1 = i.col1;
end loop;
end;
Please advise
Thanks,
Umakanth