[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

BULK COLLECT 11 G - Get rid of the for loops and incorporate bulk collect

Posted on 2012-08-20
25
Medium Priority
?
717 Views
Last Modified: 2012-08-21
I have a stored procedure that pull data from multiple tables and the result set brings back +1,000,000 rows of data and will constantly grow.   I am a novice so excuse my ignorance.  I am using For Loops.  

Since I have to derive a value for a column in the table, before I insert the rows of data,
I am not sure on how to incorporate the BULK COLLECT

Below is a sample of the pl/sql stored procedure




vRecExists         BOOLEAN;
vTskProg           VARCHAR2(4000) := NULL;
snapshot_date    DATE;

CURSOR get_person_course IS  -- Cursor retrieves a million of more records
SELECT table_1.emp_id
       table_1.name,
       table_1.department,
       table_2.target_type,
       table_2.mtl_task_target_id,
       table_3.course_name      
FROM
    table_1
JOIN table_2 ON table_2.emp_id = table_1.emp_id
JOIN table_3 ON table_3.sid = table_2.sid


--Get qualification_status  -- use to derive the task progress for accreditations
CURSOR get_qual_status (p_id IN VARCHAR2, p_emp_id IN VARCHAR2) IS
SELECT COUNT(*) rec_cnt,
             granted_on_date,
             status_reason,
             accreditation_status
FROM    itl_accreditations,
             mtl_accred_versions
WHERE  itl_accreditations.mtl_accred_version_id = mtl_accred_versions.mtl_accred_version_id
   AND   mtl_accred_versions.mtl_accreditation_id = p_id
   AND   emp_id =  p_emp_id
   AND   (granted_on_date = (SELECT MAX(itla.granted_on_date)
                                               FROM itl_accreditations itla,
                                                         mtl_accred_versions mav
                                             WHERE itla.emp_id = itl_accreditations.emp_id                                        
                                                AND itla.mtl_accred_version_id = mav.mtl_accred_version_id
                                                AND mav.mtl_accreditation_id = mtl_accred_versions.mtl_accreditation_id) OR granted_on_date IS NULL)
GROUP BY granted_on_date, status_reason, accreditation_status;        

-- Get Task List Status - use to derive task progress for task lists
CURSOR get_tasklist_status(p_id IN VARCHAR2, p_emp_id IN VARCHAR2) IS
SELECT  MAX(completed_date),
             mtl_tasklist_versions.name,
             status
FROM    itl_tasklists,
            mtl_tasklist_versions,
            mtl_tasklists
WHERE mtl_tasklist_versions.mtl_tasklist_version_id = itl_tasklists.mtl_tasklist_version_id
   AND mtl_tasklists.mtl_tasklist_id = p_id
   AND mtl_tasklists.mtl_tasklist_id = mtl_tasklist_versions.mtl_tasklist_id
   AND itl_tasklists.emp_id  = p_emp_id
GROUP BY status,version_status, mtl_tasklist_versions.name,itl_tasklist_id;

BEGIN

FOR per_course_rec IN get_person_course -- Looping to get the data elements to get  the task progress
   
    LOOP
   
     snapshot_date :=  sysdate;
   
      IF per_course_rec.target_type  = 'ACCREDITATION' THEN
     
         vTskProg := NULL;
         vRecExists := FALSE;
         vPrevStatReason := NULL;

-- How can I eliminate the following for loops and use a bulk collection?

  FOR  acc_stat_rec IN get_qual_status(per_course_rec.mtl_task_target_id, per_course_rec.emp_id) -- pass value passed from main cursor to get status info            
            LOOP
           
                 vRecExists := TRUE;
                 IF acc_stat_rec.rec_cnt > 1 AND acc_stat_rec.status_reason = 'EXPIRED' THEN vPrevStatReason :=  'EXPIRED';  -- Store  in case the next record has a Pending Qualificaiton status for the mtl_accredition_id
                 ELSIF acc_stat_rec.rec_cnt  = 1 AND  acc_stat_rec.status_reason = 'EXPIRED' AND   per_course_rec.task_status <> 'NA'  THEN  vTskProg := 'EXPIRED';
                 ELSIF vPrevStatReason =  'EXPIRED' AND   acc_stat_rec.status_reason = 'PENDING_QUALIFICATION'   THEN vTskProg := 'EXPIRED PENDING_QUALIFICATION';
                 ELSIF acc_stat_rec.rec_cnt  > 1 AND acc_stat_rec.status_reason IS NOT NULL THEN vTskProg:= acc_stat_rec.status_reason;
                 ELSIF (acc_stat_rec.accreditation_status = 'ACTIVE' AND acc_stat_rec.status_reason is null)  OR   per_course_rec.task_status = 'COMPLETE' THEN  vTskProg:= 'COMPLETE';
                 ELSIF per_course_rec.task_status = 'NA' THEN vTskProg:= 'NA' ;
                 ELSIF acc_stat_rec.status_reason IS NOT NULL THEN  vTskProg:= acc_stat_rec.status_reason;
                 ELSE  vTskProg:= per_course_rec.task_status;
                 END IF;            
           
            END LOOP;    
           
                   
          vPrevStatReason := NULL;        
         
      END IF;
     
      --  Get Task List status
      IF per_course_rec.target_type  = 'TASKLIST' THEN
          FOR tasklist_rec IN get_tasklist_status(per_course_rec.mtl_task_target_id, per_course_rec.emp_id)
         
            LOOP
           
             vRecExists := TRUE;
             vTskProg := tasklist_rec.status;
             
            END LOOP;
      END IF;
     
      IF NOT vRecExists AND per_course_rec.task_status = 'NA' THEN  vTskProg := 'NA' ;
      ELSIF NOT vRecExists THEN vTskProg := 'NOT ASSIGNED';
      END IF;
           
     
     INSERT INTO  qpts_rev VALUES
        (per_course_rec.X,
         snapshot_date,
         per_course_rec.EMP_ID,
         per_course_rec.DEPARTMENT,
         per_course_rec.NAME,
         per_course_rec.COURSE_NAME,
         vTskProg
         );
     COMMIT;
     
     
  END LOOP;
0
Comment
Question by:cookiejar
  • 12
  • 12
25 Comments
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38313479
bulk collect will return all the rows and store them in an in-memory structure like a pl/sql table.  Are you sure this is what you want?
0
 

Author Comment

by:cookiejar
ID: 38313654
I am not sure.  I read that bulk collect would improve performance and is faster.  Now it takes 15 hours to insert  over 1 million rows. What's your advice?  If I use limit of 1000 rows for the bulk collect would this take up a lot of memory or should I use 100?  As I have said, I am a novice.  Please advise.
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38314158
It all depends on your system.  I would set up a small test that mimics your setup and test.

Bulk collect can be faster but not always.  The reason is because everything is in memory.  It might not make sense to have everything in memory and can degrade overall system performance.
0
Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

 

Author Comment

by:cookiejar
ID: 38314210
Will using limit relieved the load on the system?
0
 
LVL 78

Accepted Solution

by:
slightwv (䄆 Netminder) earned 1500 total points
ID: 38314238
Personally I'm not seeing the benefit.

It all depends on where most of the 15 hours is being taken up.

It looks like you are committing after every trip through the loop.  You might want to look into setting up a counter and committing after a 'batch' of say 1,000/10,000 or so.

I just took another quick scan of the code.  Do you really need to nested cursors?

For example:        --  Get Task List status

What is this loop for?  Seems like it should be a single SQL call to retrieve a single value.


Same for what you are wanting to remove:  -- How can I eliminate the following for loops and use a bulk collection?

What does the loop get you if all you are doing is setting a single variable value?
0
 

Author Comment

by:cookiejar
ID: 38314302
First recommendation:  You might want to look into setting up a counter and committing after a 'batch' of say 1,000/10,000.

Would I DO THE FOLLOWING
FOR per_course_rec IN get_person_course    
    LOOP

      reccnt := reccnt + 1;
      INSERT STATEMENT GOES HERE
      if reccnt >= 10,000 then
          COMMIT;
      END IF;
     How would I handle when reccnt < 10,000
  END LOOP;
0
 

Author Comment

by:cookiejar
ID: 38314327
I meant
if reccnt = 10,000 then
          COMMIT;
      END IF;
0
 

Author Comment

by:cookiejar
ID: 38314373
2nd Question:  Why the nested loops


I need to decipher what  vTskProg should be.  The emp_id and the mtl_task_target_id are contained in the first cursor.

If the target type is an 'Accreditation, emp_id and the mtl_task_target_id are passed to the get_qual_status cursor which will look at the last  granted accreditation status_reason and accreditation_status to determine what to set vTskProg to.  In some cases there may be two records returned from the get_qual_status cursor. If so, I determine if the previous was an expired accreditation and the current is a pending qualification then vTskProg is 'EXPIRED PENDING QUALIFICATION'.

Are you saying I do not need the
Get Task List status cursor?
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38314386
>>if reccnt = 10,000 then

If you do it this way, you need to also reset reccnt after the commit.  

I always use MOD:
if mod(reccnt,10000) then
   commit;
end if;

>>How would I handle when reccnt < 10,000

Commit at the end just to clean up and left-overs.

>>If so, I determine if the previous was an expired accreditation and the current is a pending qualification then vTskProg is 'EXPIRED PENDING QUALIFICATION'.

Can you not formulate a single select into statement to figure this out?

>.>Are you saying I do not need the Get Task List status cursor?

I don't know your requirements.  I cannot say for sure.  From the quick scan I did, I don't see the need for it.  There might be a need I don't know.


I just see both loops being similar to:

for in 1 in to 1000 loop
   some_single_value := 1;
end loop;

Taking out all the IF's, the value of some_single_value is 1.  I don't see the need for the loop to tell me that where a single select should do.
0
 

Author Comment

by:cookiejar
ID: 38314396
As mentioned before, I am not proficient in pl/sql

For example:
How would I incorporate the following evaluations into a single SQL
   IF acc_stat_rec.rec_cnt > 1 AND acc_stat_rec.status_reason = 'EXPIRED' THEN vPrevStatReason :=  'EXPIRED';  -- Store  in case the next record has a Pending Qualificaiton status for the mtl_accredition_id
 ELSIF acc_stat_rec.rec_cnt  = 1 AND  acc_stat_rec.status_reason = 'EXPIRED' AND   per_qpts_rec.task_status <> 'NA'  THEN  vTskProg := 'EXPIRED';
 ELSIF vPrevStatReason =  'EXPIRED' AND   acc_stat_rec.status_reason = 'PENDING_QUALIFICATION'   THEN vTskProg := 'EXPIRED PENDING_QUALIFICATION';
 ELSIF acc_stat_rec.rec_cnt  > 1 AND acc_stat_rec.status_reason IS NOT NULL THEN vTskProg:= acc_stat_rec.status_reason;
 ELSIF (acc_stat_rec.accreditation_status = 'ACTIVE' AND acc_stat_rec.status_reason is null)  OR   per_qpts_rec.task_status = 'COMPLETE' THEN  vTskProg:= 'COMPLETE';
 ELSIF per_qpts_rec.task_status = 'NA' THEN vTskProg:= 'NA' ;
 ELSIF acc_stat_rec.status_reason IS NOT NULL THEN  vTskProg:= acc_stat_rec.status_reason;
                 ELSE  vTskProg:= per_qpts_rec.task_status;
                 END IF;
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38314410
>>As mentioned before, I am not proficient in pl/sql

What I'm thinking is not PL/SQL.  It is straight SQL.  the 'PL' is an Oracle specific term for 'Procedural Language'.

>>How would I incorporate the following evaluations into a single SQL

Possibly a case statement?

Something like:

select case when (acc_stat_rec.rec_cnt > 1 AND acc_stat_rec.status_reason = 'EXPIRED') or (acc_stat_rec.rec_cnt  = 1 AND  acc_stat_rec.status_reason = 'EXPIRED' AND   per_qpts_rec.task_status <> 'NA'  THEN  vTskProg := 'EXPIRED') THEN 'EXIPIRED'
...
when ...
end into vTskProg
from ...
0
 

Author Comment

by:cookiejar
ID: 38314448
Okay this is the select statement that I using to get the values to evaluate:

SELECT COUNT(*) rec_cnt,
             granted_on_date,
             status_reason,
             accreditation_status,

           WILL I HAVE THE OPTION TO STORE A PREVIOUS VALUE INTO A VARIABLE

             case when count(*) > 2 and status_reason =  'EXPIRED' THEN vPrevStatReason = 'EXPIRED'
             case when vPrevStatReason =  'EXPIRED' AND   status_reason = 'PENDING_QUALIFICATION'   THEN 'EXPIRED PENDING_QUALIFICATION'
             
            FROM    itl_accreditations,
             mtl_accred_versions
WHERE  itl_accreditations.mtl_accred_version_id = mtl_accred_versions.mtl_accred_version_id
   AND   mtl_accred_versions.mtl_accreditation_id = p_mtl_accred_id
   AND   per_person_id =  p_person_id
   AND   (granted_on_date = (SELECT MAX(itla.granted_on_date)
                                               FROM itl_accreditations itla,
                                                         mtl_accred_versions mav
                                             WHERE itla.per_person_id = itl_accreditations.per_person_id                                        
                                                AND itla.mtl_accred_version_id = mav.mtl_accred_version_id
                                                AND mav.mtl_accreditation_id = mtl_accred_versions.mtl_accreditation_id) OR granted_on_date IS NULL)
GROUP BY granted_on_date, status_reason, accreditation_status;
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38314462
>>Okay this is the select statement that I using to get the values to evaluate:

Is there a question here?
0
 
LVL 5

Expert Comment

by:Sanjeev Labh
ID: 38316751
Trying to answer your original question. Bulk collect does help in certain situations when you are handling huge amount of data, but that depends upon your usage and approach as slightwv has already pointed out.

In your particular case a simple bulk collect would not help much as you have to go further and insert them, however you also have to perform certain operations in between. Bulk collect accompanied with bulk insert would help your case but the approach would need to be changed. Looking at it though a little tricky your operations can be clubbed into a single query. If you are able to achieve this it is relatively easy to incorporate bulk insert. However, in conjunction to this you will have to consider other factors such as query tuning, heavy operation on indexes etc.
0
 

Author Comment

by:cookiejar
ID: 38316936
We will opt out using bulk collect.  The question that I was asking is how could I incorporate this evaluation in the case statment or should I post this as a seperate question?

slightwv  - You recommended that i convert the deciphering of what should be assigned to  vTskProg to a case statement

How would I incorporate the following into a case statement to store a value into vPrevStatReason?  (See the above thread id: 38314396 to review the evaluations)

 IF acc_stat_rec.rec_cnt > 1 AND acc_stat_rec.status_reason = 'EXPIRED' THEN vPrevStatReason := 'EXPIRE'
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38317141
>>How would I incorporate the following into a case statement to store a value into vPrevStatReason?  (See the above thread id: 38314396 to review the evaluations)

I already commented on that in http:#a38314410

I'm afrad I probably cannot provide the exact working syntax since I don't have data to test with.  I can provide the conecpt or idea and hopefully you can integrate it into your code.

The other question is:  Will it even make a difrerence?  I asked before where the time is being taken up.  If most of the tim is taken up by the outer loop then making the inner loops faster won't do anything so why bother trying.

You need to get an idea of what parts to focus the tuning efforts on.

Right now you have 3 loops that take 15 hours to run.  How much time does just the outer loop take if it does no processing on either inner loop?

Then how much time does it take with the first two loops?

In other words:  Where is the main slow down?
0
 

Author Comment

by:cookiejar
ID: 38317267
EMP_ID     STATUS_REASON                   TARGET_ID         GRANTED_ON_DATE
00001        EXPIRED                                 56000                 01/21/2012
00001        EXPIRED                                 56000                 11/11/2011
00001        PENDING QUALIFICATION       56000                

In the select statement I'm  fetching the record with the maximum granted_on_date or granted on date is null.  The result set will return 2 records.  Is there a way to do this using the case statement in the select so that I do not need to use a  for loop?
           
    IF acc_stat_rec.rec_cnt > 1 AND acc_stat_rec.status_reason = 'EXPIRED' THEN vPrevStatReason :=  'EXPIRED';  
  ELSIF vPrevStatReason =  'EXPIRED' AND   acc_stat_rec.status_reason = 'PENDING_QUALIFICATION'   THEN vTskProg := 'EXPIRED PENDING_QUALIFICATION';
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38317380
You didn't answer the questions about there the 15 hours is being used.  If it isn't in this IF statement, then why even focus time here?

I'm sorry but I don't have the time to walk through your code to understand it enough to rewrite it for you.

I still think you should be able to write a select to return the correct vTskProg based on your rules but why even try if this isn't what is taking up a lot of time?
0
 

Author Comment

by:cookiejar
ID: 38317437
The main sql takes 3 minutes to return the results set.  The time is consumed in the for loop and  I was trying to think of a method to improve the processing time in the if then else.  

Thank you for your recommendations.
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38317462
>>The time is consumed in the for loop

So you are saying that you can comment out both inside loops and only keep the outer loop and it runs in 3 minutes?

This:
vRecExists         BOOLEAN;
vTskProg           VARCHAR2(4000) := NULL;
snapshot_date    DATE;

CURSOR get_person_course IS  -- Cursor retrieves a million of more records
SELECT table_1.emp_id
       table_1.name,
       table_1.department,
       table_2.target_type,
       table_2.mtl_task_target_id,
       table_3.course_name       
FROM 
    table_1
JOIN table_2 ON table_2.emp_id = table_1.emp_id
JOIN table_3 ON table_3.sid = table_2.sid


--Get qualification_status  -- use to derive the task progress for accreditations
CURSOR get_qual_status (p_id IN VARCHAR2, p_emp_id IN VARCHAR2) IS
SELECT COUNT(*) rec_cnt,
             granted_on_date,
             status_reason,
             accreditation_status
FROM    itl_accreditations,
             mtl_accred_versions
WHERE  itl_accreditations.mtl_accred_version_id = mtl_accred_versions.mtl_accred_version_id
   AND   mtl_accred_versions.mtl_accreditation_id = p_id
   AND   emp_id =  p_emp_id
   AND   (granted_on_date = (SELECT MAX(itla.granted_on_date)
                                               FROM itl_accreditations itla,
                                                         mtl_accred_versions mav
                                             WHERE itla.emp_id = itl_accreditations.emp_id                                        
                                                AND itla.mtl_accred_version_id = mav.mtl_accred_version_id
                                                AND mav.mtl_accreditation_id = mtl_accred_versions.mtl_accreditation_id) OR granted_on_date IS NULL)
GROUP BY granted_on_date, status_reason, accreditation_status;         

-- Get Task List Status - use to derive task progress for task lists
CURSOR get_tasklist_status(p_id IN VARCHAR2, p_emp_id IN VARCHAR2) IS
SELECT  MAX(completed_date),
             mtl_tasklist_versions.name,
             status
FROM    itl_tasklists,
            mtl_tasklist_versions,
            mtl_tasklists
WHERE mtl_tasklist_versions.mtl_tasklist_version_id = itl_tasklists.mtl_tasklist_version_id
   AND mtl_tasklists.mtl_tasklist_id = p_id
   AND mtl_tasklists.mtl_tasklist_id = mtl_tasklist_versions.mtl_tasklist_id
   AND itl_tasklists.emp_id  = p_emp_id
GROUP BY status,version_status, mtl_tasklist_versions.name,itl_tasklist_id;

BEGIN

FOR per_course_rec IN get_person_course -- Looping to get the data elements to get  the task progress
   
    LOOP
    
     snapshot_date :=  sysdate;
    
      IF per_course_rec.target_type  = 'ACCREDITATION' THEN
      
         vTskProg := NULL;
         vRecExists := FALSE;
         vPrevStatReason := NULL;

-- How can I eliminate the following for loops and use a bulk collection?

--  FOR  acc_stat_rec IN get_qual_status(per_course_rec.mtl_task_target_id, per_course_rec.emp_id) -- pass value passed from main cursor to get status info            
--            LOOP
--            
--                 vRecExists := TRUE;
--                 IF acc_stat_rec.rec_cnt > 1 AND acc_stat_rec.status_reason = 'EXPIRED' THEN vPrevStatReason :=  'EXPIRED';  -- Store  in case the next record has a Pending Qualificaiton status for the mtl_accredition_id
--                 ELSIF acc_stat_rec.rec_cnt  = 1 AND  acc_stat_rec.status_reason = 'EXPIRED' AND   per_course_rec.task_status <> 'NA'  THEN  vTskProg := 'EXPIRED';
--                 ELSIF vPrevStatReason =  'EXPIRED' AND   acc_stat_rec.status_reason = 'PENDING_QUALIFICATION'   THEN vTskProg := 'EXPIRED PENDING_QUALIFICATION';
--                 ELSIF acc_stat_rec.rec_cnt  > 1 AND acc_stat_rec.status_reason IS NOT NULL THEN vTskProg:= acc_stat_rec.status_reason;
--                 ELSIF (acc_stat_rec.accreditation_status = 'ACTIVE' AND acc_stat_rec.status_reason is null)  OR   per_course_rec.task_status = 'COMPLETE' THEN  vTskProg:= 'COMPLETE';
--                 ELSIF per_course_rec.task_status = 'NA' THEN vTskProg:= 'NA' ;
--                 ELSIF acc_stat_rec.status_reason IS NOT NULL THEN  vTskProg:= acc_stat_rec.status_reason;
--                 ELSE  vTskProg:= per_course_rec.task_status;
--                 END IF;             
--            
--            END LOOP;     
--            
--                    
--          vPrevStatReason := NULL;         
--          
--      END IF;
--      
--      --  Get Task List status
--      IF per_course_rec.target_type  = 'TASKLIST' THEN
--          FOR tasklist_rec IN get_tasklist_status(per_course_rec.mtl_task_target_id, per_course_rec.emp_id)
--          
--            LOOP
--            
--             vRecExists := TRUE;
--             vTskProg := tasklist_rec.status;
--             
--            END LOOP;
      END IF;
      
      IF NOT vRecExists AND per_course_rec.task_status = 'NA' THEN  vTskProg := 'NA' ;
      ELSIF NOT vRecExists THEN vTskProg := 'NOT ASSIGNED';
      END IF; 
           
     
     --INSERT INTO  qpts_rev VALUES
--        (per_course_rec.X,
--         snapshot_date,
--         per_course_rec.EMP_ID,
--         per_course_rec.DEPARTMENT,
--         per_course_rec.NAME,
--         per_course_rec.COURSE_NAME,
--         vTskProg
--         );
--     COMMIT;
      
      
  END LOOP; 

Open in new window

0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38317469
Why did you close this?  Did you get what you needed?
0
 

Author Comment

by:cookiejar
ID: 38317568
I didn't want to waste anymore of your time trying to figure out how I could improve or remove the inner loops because it's difficult when you aren't familiar with the database construct.  

No it takes 3 minutes to run the main query in TOAD.  The sample I sent you isn't the actual query statement. It has  multiple tables inner and  outer joins.
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38317591
>>No it takes 3 minutes to run the main query in TOAD.

Toad buffers the data.  It likely takes 3 minutes to display the first records.  You cannot use that as a timing metric.

You need 'actual' timings.

Take what I posted and run it.  See how long it takes.  If it still takes hours, does it make sense to try to tune the inner loops?

You need to know specifically what to tune so you don't spend time guessing at what might make something faster.

For example:
My car is slow.  I need a larger engine to make my car go faster!

That might make sense when looking at the big picture but you need to drill down and focus on the 'real' issues:

Replacing the square wheels with round ones will likely be a better choice and be cheaper to do.
0
 

Author Comment

by:cookiejar
ID: 38317705
I'll look online to see if I can determine how to  incorporate the case when into the select statements, so that I can do a select into vice for loop
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38317734
>>, so that I can do a select into vice for loop

If this isn't the cause of the perforance issue, why take the time?  You can spend days porting the loops to a single select only to find out it still takes 15 hours.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Via a live example, show how to take different types of Oracle backups using RMAN.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
Suggested Courses

830 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