Oracle PL/SQL FOR Loop Optimisation

Question

Please can you assist me if possible, i need to optimize the below block of code if order to iterate the values quicker. see below statement :

for CONSULTANT_RECORD IN CONSULTANT_CURSOR LOOP /*Loop 2*/
VData := VData||crlf2||CONSULTANT_RECORD.USER_FIRSTNAME||'   '||CONSULTANT_RECORD.USER_SURNAME;

vTOT_LOG_CLOSED4USER := 0;
vAVERAGE_DAY := 0;
vTOT_DAYS := 0;
for x in 1..vlastday loop /*Loop 3*/
  select COUNT(DISTINCT LOG_NO) as "LOG_COUNT" INTO vLOG_COUNTER
    FROM DMS_CRM.TBL_DMS_SERVICE_REQUESTS_HIST
      WHERE LOG_STATUS = 'Resolved'
       AND TO_CHAR(HIST_DATE_TIME_STAMP, 'MONTH,YYYY') = TO_CHAR(SYSDATE, 'MONTH,YYYY')
       AND TO_CHAR(HIST_DATE_TIME_STAMP, 'DD') = lpad(to_char(x),2,'0')
       AND OWNER_USER_ID IS NOT NULL
       AND OWNER_USER_ID = CONSULTANT_RECORD.OWNER_USER_ID;

    end loop;
END LOOP;

The problem starts happening in the Comment Loop 3, bare in mind im developing over oracle app portal, so i cant run query optimization... my tables are indexed correctly as well (if anyone wants to comment on it).

Regards,

PS. No one has gotten back to me with their version of the code..........i am still struggling with this :-(

a1ex07 · Accepted Answer · 2012-08-28 13:35:24Z

1

I think the best loop is no loop at all (surely, if it's can be avoided). Assuming vlastday is a collection, you can do something like

select COUNT(DISTINCT LOG_NO) as "LOG_COUNT" 
FROM DMS_CRM.TBL_DMS_SERVICE_REQUESTS_HIST
INNER JOIN 
  (
    SELECT lpad(to_char(t.COLUMN_VALUE ),2,'0') as x 
    from table(vlastday)t
  )a ON (TO_CHAR(HIST_DATE_TIME_STAMP, 'DD') = a.x)
  WHERE LOG_STATUS = 'Resolved'
   AND TO_CHAR(HIST_DATE_TIME_STAMP, 'MONTH,YYYY') = TO_CHAR(SYSDATE, 'MONTH,YYYY')       
   AND OWNER_USER_ID IS NOT NULL
   AND OWNER_USER_ID = CONSULTANT_RECORD.OWNER_USER_ID
   GROUP BY a.x;

Then depends on your task, you need to either get rid of GROUP BY so you can select into, or declare a collection of numbers and BULK COLLECT into this variable.

Also, I from what I see in the question, you can do pretty much the same for Loop2 so you won't have loops ...

answered Aug 28, 2012 at 13:35

a1ex07

37.4k12 gold badges93 silver badges103 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user1630342 Over a year ago

Unfortunately the vlastday is not a collection rather a method of retrieving the current day ie.28 /*vlastday := to_number(to_char(LAST_DAY(TO_DATE(sysdate)), 'DD'));*/

a1ex07 Over a year ago

@user1630342: Ok, I see. This way you can create a collection of numbers, populate it with values (1,2,... etc), and do SELECT joining this collection. Instead of having up to 31 selects, you will have just one which should dramatically improve performance.

user1630342 Over a year ago

How do i go about handling one select instead of multiple selects (via the loop)..issue is that i have iterate of multiple days for multiple consultants...their values are different across the days, i am also incrementing the day values in the select statement to accommodate for the different days....ie./*AND TO_CHAR(HIST_DATE_TIME_STAMP, 'DD') = lpad(to_char(x),2,'0')*/

Collectives™ on Stack Overflow

Oracle PL/SQL FOR Loop Optimisation

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related