0

I have created an Oracle query, later I need to integrate is in a procedure.

In my SQL block I have two nested loops like this (Select statements are working fine):

DECLARE
  m_card_no NUMBER;
BEGIN
  FOR i IN (SELECT DISTINCT VISA_NUMBER FROM LC.WEEKLY_ROP_CARD_A WHERE NOT EXISTS (SELECT 1 FROM LC.LCT_MOMP_ROP WHERE VISA_NO = VISA_NUMBER))
  LOOP
    FOR n IN (
      SELECT DISTINCT l.new_clearance_no, l.occupation_code, l.sex_code, rtrim(ltrim(a.civil_number)) civil_number, a.name_e, a.name_a, a.date_of_birth, a.passport_number, a.passport_issue_country_code, a.passort_issue_date, a.passort_expiry_date, a.nationality_code, lpad(rtrim(ltrim(a.visa_number)),8,0) visa_number, a.visa_issue_date, a.visa_expiry_date, v.lct_occupation_clear_id, DECODE(V.LCM_VISA_APPL_TYPE_ID,7,'LOCAL ENDORSEMENT','ARRIVAL') TRAN
      FROM WEEKLY_ROP_CARD_A A
      JOIN LCT_OCCUPATION_CLEAR L ON L.OCCUPATION_CODE = A.OCCUPATION_CODE
      JOIN LCT_APPL_VISA V ON V.LCT_OCCUPATION_CLEAR_ID = L.ID
      where lpad(v.visa_no,8,0)=lpad(rtrim(ltrim(a.visa_number)),8,0) and v.lct_occupation_clear_id=l.id and a.last_mov_type='IN' AND ( LCM_VISA_APPL_TYPE_ID <> 6 or LCM_VISA_APPL_TYPE_ID is null) and lpad(v.visa_no,8,0)=i.VISA_NUMBER
    )
    LOOP
        --INSERT QUERY TO ANOTHER TABLE HERE
    END LOOP;
  END LOOP;
END;

The query in the first FOR loop has over 1.6 Million records and Second loop has over 600,000 records. When I run both queries separately, it provide result so there is no any error in the query. But when I run the above block, it is not even reaching insert statement. Is there a way to insert records without any issue?

Improve this question
2
  • 1
    Don't use a loop. Use a single insert into .. select .. statement Commented Mar 17, 2019 at 10:12
  • Can you please create an example and show since I am very new to oracle Commented Mar 17, 2019 at 10:37

2 Answers 2

Reset to default
1

First and foremost a set-based INSERT INTO ... SELECT FROM statement will be much faster than a single-row INSERT INTO ... VALUES statement inside a loop.

Secondly, nested loops are slower than an efficient join. Because the table you use in the outer loop is also in the inner loop it is easy to eliminate the two loops.

Putting this advice together....

insert into your_table 
      SELECT DISTINCT l.new_clearance_no, 
             l.occupation_code, 
             l.sex_code, 
             rtrim(ltrim(a.civil_number)) civil_number,
             a.name_e, 
             a.name_a, 
             a.date_of_birth,
             a.passport_number, 
             a.passport_issue_country_code, 
             a.passort_issue_date, 
             a.passort_expiry_date, 
             a.nationality_code, 
             lpad(rtrim(ltrim(a.visa_number)),8,0) visa_number, 
             a.visa_issue_date, 
             a.visa_expiry_date, 
             v.lct_occupation_clear_id, 
             DECODE(V.LCM_VISA_APPL_TYPE_ID,7,'LOCAL ENDORSEMENT','ARRIVAL') TRAN
      FROM WEEKLY_ROP_CARD_A A
      JOIN LCT_OCCUPATION_CLEAR L ON L.OCCUPATION_CODE = A.OCCUPATION_CODE
      JOIN LCT_APPL_VISA V ON V.LCT_OCCUPATION_CLEAR_ID = L.ID
      where lpad(v.visa_no,8,0)=lpad(rtrim(ltrim(a.visa_number)),8,0) 
      and v.lct_occupation_clear_id=l.id 
      and a.last_mov_type='IN' 
      AND ( LCM_VISA_APPL_TYPE_ID <> 6 or LCM_VISA_APPL_TYPE_ID is null) 
      and lpad(v.visa_no,8,0)=i.VISA_NUMBER
      and NOT EXISTS (SELECT 1 FROM LC.LCT_MOMP_ROP WHERE VISA_NO = a.VISA_NUMBER)
;

Also, if LC.LCT_MOMP_ROP.VISA_NO is guaranteed to be not null you might want to check whether NOT IN is more performative than NOT EXISTS.

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

2 Comments

This query seems OK but according to my current data structure, I have over 400,000 records to insert. Will INSERT INTO statement execute without any issue?
Yes, providing your DBA has done a good job on configuration.
0

All credit goes to APC for having the patience to rewrite it.
If it takes too long you can try below version and look up into the execution plan for waits and some hints like the ones below ...not all long queries are because the dba:

-PQ_DISTRIBUTE (for partitioned tables)

-LEADING

-PRECOMPUTE_SUBQUERY (for subqueries)

-USE_HASH

-FULL

   insert /*+ append parallel(aaa,8) */ into your_table aaa
          SELECT /*+ parallel(A,4) parallel(L,4) parallel(V,4) */DISTINCT l.new_clearance_no, 
                 l.occupation_code, 
                 l.sex_code, 
                 rtrim(ltrim(a.civil_number)) civil_number,
                 a.name_e, 
                 a.name_a, 
                 a.date_of_birth,
                 a.passport_number, 
                 a.passport_issue_country_code, 
                 a.passort_issue_date, 
                 a.passort_expiry_date, 
                 a.nationality_code, 
                 lpad(rtrim(ltrim(a.visa_number)),8,0) visa_number, 
                 a.visa_issue_date, 
                 a.visa_expiry_date, 
                 v.lct_occupation_clear_id, 
                 DECODE(V.LCM_VISA_APPL_TYPE_ID,7,'LOCAL ENDORSEMENT','ARRIVAL') TRAN
          FROM WEEKLY_ROP_CARD_A A
          JOIN LCT_OCCUPATION_CLEAR L ON L.OCCUPATION_CODE = A.OCCUPATION_CODE
          JOIN LCT_APPL_VISA V ON V.LCT_OCCUPATION_CLEAR_ID = L.ID
          where lpad(v.visa_no,8,0)=lpad(rtrim(ltrim(a.visa_number)),8,0) 
          and v.lct_occupation_clear_id=l.id 
          and a.last_mov_type='IN' 
          AND ( LCM_VISA_APPL_TYPE_ID <> 6 or LCM_VISA_APPL_TYPE_ID is null) 
          and lpad(v.visa_no,8,0)=i.VISA_NUMBER
          and NOT EXISTS (SELECT 1 FROM LC.LCT_MOMP_ROP WHERE VISA_NO = a.VISA_NUMBER)
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.