[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?

Bulk collect

Posted on 2017-10-12
9
Medium Priority
?
39 Views
Last Modified: 2017-10-16
due to performance reasons we need to use  bulk collect

1) need to select  data from table 1 (select col1,col2,col3,col4 from table 1 where col3=sysdate)

 fetch through bulk collect


2) need to get the  data from table 2  by passing  the values from above fetch bulk collect


select rowid , col4 from above fetch bulk collect
 as col99  from table2
where  table2.col1=fetch bulk from table1.col1
   and  table2.col2=fetch bulk from table1.col2
and table2.col3=fetch bulk from table1.col3


1  to  ... count     save  exceptions


update table2 set col4=col99  from above select
where  rowid=rowid from  above  select



exceptions
0
Comment
Question by:steve wa
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2
  • +1
9 Comments
 
LVL 60

Expert Comment

by:HainKurt
I could not get what is the issue here and what is not working...
do you have any code?

have a look at this

Oracle Bulk Collect tips
http://www.dba-oracle.com/t_oracle_bulk_collect.htm
0
 

Author Comment

by:steve wa
how to pass values from first  bulk fetch to the step 2) select statement or cursor?
0
 

Author Comment

by:steve wa
I need sample code  for the above scenario.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 60

Expert Comment

by:HainKurt
sample code is in the link

a_store string_array;
a_qty number_array;

cursor c1 is
  select store_key, sum(quantity)
    from sales
   group by store_key;
begin
  open c1;
  fetch c1 bulk collect into a_store, a_qty;
  close c1;
  for indx in a_store.first..a_store.last loop
    dbms_output.put_line(a_store(indx)||'....'||a_qty(indx));
  end loop;
end; /

Open in new window

0
 

Author Comment

by:steve wa
Open cursor and  fetch data using bulk collect is  normal scenario.My scenario is little different

step 1)  open cursor 1 and  fetch data using bulk collect from cursor 1 (  example  table A)
step 2 )  Pass the fetch data from the step 1)  to the   where condition of   cursor 2 or another select statement    and get rowid from the that   ( ex :rowid from table B based on the values from the  table A)
step 3) update Table B based on the rowid   pulled from step 2)
0
 
LVL 60

Expert Comment

by:HainKurt
logic should be:

1. cursor declaration and opening looping fetching closing is already in that sample...
2. select rowid into v_rowid from tableB where colX=a_ColX(indx)
3. update tableC where rowid=v_rowid
0
 
LVL 14

Expert Comment

by:Alexander Eßer [Alex140181]
declare

  cursor c1 is
    select *
      from table1 a
     where a.whatever = 'foo';

  cursor c2(ci_param1 in varchar2) is
    select *
      from table2 a
     where a.some_column = ci_param1;

begin

  for r1 in c1 loop
  
    for r2 in c2(c1.some_column) loop /* you can also do "open c2(c1.some_column)... and do some bulk collect processing... " */
    
      null; /* do some stuff... */
    
    end loop;
  
  end loop;

end;

Open in new window

0
 
LVL 35

Expert Comment

by:johnsone
Why bulk collect?  Seems like a pretty simple update statement.  Update a table based on a value in another table.  The column names and tables names in the original post are confusing, so I can't write a sample.  Seems like you want to update COL4 in TABLE2, but COL4 came from TABLE1.

This is a total guess, but you should get the idea.
UPDATE table1 t1 
SET    col4 = (SELECT col99 
               FROM   table2 t2 
               WHERE  t1.col1 = t2.col1 
                      AND t1.col2 = t2.col2 
                      AND t1.col3 = t2.col3) 
WHERE  t1.col3 = SYSDATE; 

Open in new window

Writing a single statement into a process where you start to involve PL/SQL probably isn't going to make it faster.
1
 
LVL 14

Expert Comment

by:Alexander Eßer [Alex140181]
Writing a single statement into a process where you start to involve PL/SQL probably isn't going to make it faster.
Yes! No need for PL/SQL if you can do it with pure SQL. The less context switches (PL/SQL engine to SQL engine and vice versa), the better ;-)

You may also use the MERGE for that purpose...
1

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Join & Write a Comment

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

656 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question