• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 626
  • Last Modified:

Difference between Bulk SQL and Loop SQL for Oracle

Hi all experts,

What is the differ between bulk SQL and Loop SQL for Oracle?

Any code example for them?

Thank you.
0
silterra
Asked:
silterra
2 Solutions
 
sdstuberCommented:
with bulk, you perform a single operation over many rows.

with loop, you perform many operations, each over a single row.
-- bulk example
--
DECLARE
    TYPE numlist IS TABLE OF NUMBER;
    num_tab      numlist   := numlist(10, 0, 11, 12, 30, 0, 20, 199, 2, 0, 9, 1);
BEGIN
    FORALL i IN num_tab.FIRST .. num_tab.LAST 
        DELETE FROM emp
              WHERE sal > 500000 / num_tab(i);
END;
 
 
-- loop example  note the FOR instead of FORALL and the loop structure
DECLARE
    TYPE numlist IS TABLE OF NUMBER;
    num_tab      numlist   := numlist(10, 0, 11, 12, 30, 0, 20, 199, 2, 0, 9, 1);
BEGIN
    FOR i IN num_tab.FIRST .. num_tab.LAST LOOP
        DELETE FROM emp
              WHERE sal > 500000 / num_tab(i);
    END LOOP;
END;

Open in new window

0
 
dportasCommented:
Use single, set-based statements where you can:

DELETE FROM emp WHERE sal > 500000 /199 ;
0
 
silterraAuthor Commented:
Hi all,
Maybe you want to try this. I had post other question how to change loop to bulk SQL.
http://www.experts-exchange.com/Database/Oracle/PL_SQL/Q_24167459.html
Change this from Loop SQL to Bulk SQL
Thank you.
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
schwertnerCommented:
Bulk SQL reads many rows at time and place them in a working area.
So the program avoids the content switches (client software - server software)
that consumes time.
So the advantage is that the program works faster, the disadvantage is that it needs more RAM to run.
Using a varray to ensure a limited number of rows :
 
DROP TABLE training_months 
/
CREATE TABLE training_months (month_name VARCHAR2(100))
/
 
BEGIN
   /* No trainings in the depths of summer and winter... */
   INSERT INTO training_months VALUES ('March');
   INSERT INTO training_months VALUES ('April');
   INSERT INTO training_months VALUES ('May');
   INSERT INTO training_months VALUES ('June');
   INSERT INTO training_months VALUES ('September');
   INSERT INTO training_months VALUES ('October');
   INSERT INTO training_months VALUES ('November');
   COMMIT;
END;
/
 
DECLARE
   TYPE at_most_twelve_t IS 
      VARRAY (12) OF VARCHAR2 (100);
 
   l_months   at_most_twelve_t;
BEGIN
   SELECT month_name
   BULK COLLECT INTO l_months
      FROM training_months;
 
   FOR indx IN 1 .. l_months.COUNT
   LOOP
      DBMS_OUTPUT.put_line (l_months (indx));
   END LOOP;
END;
/
 
 
BULK COLLECT with LIMIT when you dont know the upper limit. BULK COLLECT helps retrieve multiple rows of data quickly. Rather than retrieve one row of data at a time into a record or a set of individual variables, BULK COLLECT lets us retrieve hundreds, thousands, even tens of thousands of rows with a single context switch to the SQL engine and deposit all that data into a collection. The resulting performance improvement can be an order of magnitude or greater.
 
Using the LIMIT clause 
 
 
PROCEDURE bulk_with_limit (
   dept_id_in   IN   employees.department_id%TYPE
 , limit_in       IN   PLS_INTEGER DEFAULT 100
)
IS
   CURSOR employees_cur
   IS
      SELECT *
        FROM employees
      WHERE department_id = dept_id_in;
 
   TYPE employee_tt IS TABLE OF employees_cur%ROWTYPE
      INDEX BY PLS_INTEGER;
 
   l_employees   employee_tt;
BEGIN
   OPEN employees_cur;
 
   LOOP
      FETCH employees_cur
      BULK COLLECT INTO l_employees LIMIT limit_in;
 
      FOR indx IN 1 .. l_employees.COUNT
      LOOP
         process_each_employees (l_employees (indx));
      END LOOP;
 
      EXIT WHEN employees_cur%NOTFOUND;
   END LOOP;
 
   CLOSE employees_cur;
END bulk_with_limit;
 
 
The limit value can be a variable; in this case, I provide a default value of 100 (retrieve up to 100 rows with each fetch). As for the appropriate limit value, you will theoretically use the largest number that can be accommodated within the per-session memory your DBA feels can be allocated per connection

Open in new window

0
 
silterraAuthor Commented:
Hi sdstuber and  schwertner,
I will study them.
Very thankful for your sharing.
0
 
sdstuberCommented:
if you've received your answer, please close the question
if you need further assistance, please ask

thanks
0
 
silterraAuthor Commented:
Thank you.
0
 
silterraAuthor Commented:
Thank you schwertner & sdstuber.
0
 
sdstuberCommented:
if you needed more information you just needed to ask.

if you didn't need more, then what did you need to for an A?
0
 
silterraAuthor Commented:
Hi sdstuber,
Thank you for your sharing.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now