Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Loading 50-60 million records efficiently

TinaJan 2 2020 — edited Apr 13 2020

Hi Gurus,

  I am having some performance issues on bulk loading and have tried to break down pieces below on whats happening and what am I trying.

  Would you be able to suggest any way that would be better or I am missing?

1)

    create table table_a

    (

      loan_nbr      number,

      yr_mnth_cd    varchar2(6 byte),

      start_dt      date,

      maturity_dt   date ,

      number_of_terms number

      );  

    primary_key loan_acct_nbr, yr_mnth_cd (unique indexed)

    indexed by loan_nbr,yr_mnth_cd

  

  

    create table final_load_table

       (

          loan_nbr      number,

          yr_mnth_cd    varchar2(6 byte),

          ......,

          ......,

          ......);

                      

    indexed by loan_nbr,yr_mnth_cd   

  

2)  function my_function ( loan_nbr,loan_start_dt,maturity_dt,number_of_terms) return table_type

3)  Cursor below is used in a code which joins table_a and function my_function and that resultset is being inserted into final_table

    one loan would have 50-65 rows generated by function which is equal to 'NUMBER_OF_TERMS'.

    some business logic is applied on for each row and then that row is loaded to collection and returned by function.

    so i have roughly not 50-60 million records on each every month,

    I have tried few approach on loading this

         1- using bulk collect and comitting on every 100K records

         2- direct insert

               insert into FINAL_LOAD_TABLE

               as select * from cursor;

             

    However both of this takes very long time.Is there any approach we can take for this loading?

    We are planning for creating a partition on final_load_table based on yr_month_Cd (for each month).              

               

Cursor:

    SELECT Y.*,

           TRUNC(SYSDATE,'MM'),

           USER

     FROM TABLE_A A

     JOIN TABLE(MY_FUNCTION (A.LOAN_NBR,A.START_DT,A.MATURITY_DT,A.NUM_OF_TERMS)) Y

           ON A.LOAN_NBR = Y.LOAN_ACCT_NBR

     WHERE A.YR_MNTH_CD = TO_CHAR(ADD_MONTHS(SYSDATE,-1),'YYYYMM')  --last month

     AND A.LOAN_TYP='ABC'

     AND NOT EXISTS ( SELECT 1 FROM FINAL_LOAD_TABLE L

                                                        WHERE L.LOAN_ACCT_NBR =Y.LOAN_ACCT_NBR

                                                        AND Y.YR_MNTH_CD=L.YR_MNTH_CD );

Thanks

Comments

Lokanath Giri
To find all dept not existing in EMP table
 select *
 from DEPT
 where NOT EXISTS
   (select *
     from EMP
     where EMP.deptno = DEPT.deptno)
 /
737905
Try this:
select 
from T1 a,T3 c
where a.C1=c.C1
and Not EXISTS (select 1 from T2 WHERE T2.C1 = a.C1);
Aketi Jyuuzou
At not in condition,
Sometimes we must think whether there is NULL or not :-)
select 
from T1 a,T3 c
where a.C1=c.C1
and Not EXISTS (select 1 from T2 WHERE T2.C1 = a.C1
                                    or T2.C1 is null);
767585
Changed my actual query with NOT EXISTS,Still performance lacks , Any idea to improve?

select /*+ PUSH_SUBQ */
from T1 a,T3 c
where a.C1=c.C1
and a.C2=c.C3
and trunc(Update_date)>='19-APR-2009'
and trunc(Update_date)  <  sysdate
and c.C3 ='P'
and Not EXISTS (select 1 from T2 WHERE T2.C1 = a.C1);


There is a PK on a.C1,T2.C1
index on c.C3
Edited by: user13006393 on Aug 17, 2010 6:57 AM
SomeoneElse
Please read these:

When your query takes too long
503834

How to Post a SQL statement tuning request
865295
Sven W.
user13006393 wrote:
Changed my actual query with NOT EXISTS,Still performance lacks , Any idea to improve?
Maybe like this
select *
from T1 a
JOIN T3 c on  a.C1=c.C1 and a.C2=c.C3
LEFT JOIN T2 on T2.C1 = a.C1
where Update_date >= to_date('19-04-2009','DD-MM-YYYY')
and Update_date <  sysdate
and c.C3 ='P'
and t2.c1 is null;
767585
SELECT STATEMENT                                |                       |   507K|    56M|       |    16M  (3)| 40:02:33 |       |       |        |      |            |
|   1 |  PX COORDINATOR                                 |                       |       |       |       |            |          |       |       |        |      |            |
|   2 |   PX SEND QC (RANDOM)                           | :TQ10005              |   507K|    56M|       |    16M  (3)| 40:02:33 |       |       |  Q1,05 | P->S | QC (RAND)  |
|   3 |    HASH UNIQUE                                  |                       |   507K|    56M|   124M|    16M  (3)| 40:02:33 |       |       |  Q1,05 | PCWP |            |
|   4 |     PX RECEIVE                                  |                       |   507K|    56M|       |    16M  (3)| 40:02:32 |       |       |  Q1,05 | PCWP |            |
|   5 |      PX SEND HASH                               | :TQ10004              |   507K|    56M|       |    16M  (3)| 40:02:32 |       |       |  Q1,04 | P->P | HASH       |
|*  6 |       HASH JOIN OUTER BUFFERED                  |                       |   507K|    56M|       |    16M  (3)| 40:02:32 |       |       |  Q1,04 | PCWP |            |
|   7 |        PX RECEIVE                               |                       |   507K|    47M|       |    16M  (2)| 39:58:18 |       |       |  Q1,04 | PCWP |            |
|   8 |         PX SEND HASH                            | :TQ10002              |   507K|    47M|       |    16M  (2)| 39:58:18 |       |       |  Q1,02 | P->P | HASH       |
|   9 |          NESTED LOOPS                           |                       |   507K|    47M|       |    16M  (2)| 39:58:18 |       |       |  Q1,02 | PCWP |            |
|* 10 |           HASH JOIN RIGHT ANTI                  |                       |  2549K|   192M|   423M|    14M  (3)| 36:26:24 |       |       |  Q1,02 | PCWP |            |
|  11 |            PX RECEIVE                           |                       |    93M|   624M|       | 36738  (10)| 00:05:28 |       |       |  Q1,02 | PCWP |            |
|  12 |             PX SEND HASH                        | :TQ10001              |    93M|   624M|       | 36738  (10)| 00:05:28 |       |       |  Q1,01 | P->P | HASH       |
|  13 |              PX PARTITION HASH ALL              |                       |    93M|   624M|       | 36738  (10)| 00:05:28 |     1 |    12 |  Q1,01 | PCWC |            |
|  14 |               INDEX FULL SCAN                   | PK_T2                 |    93M|   624M|       | 36738  (10)| 00:05:28 |     1 |    12 |  Q1,01 | PCWP |            |
|  15 |            BUFFER SORT                          |                       |       |       |       |            |          |       |       |  Q1,02 | PCWC |            |
|  16 |             PX RECEIVE                          |                       |    79M|  5456M|       |    14M  (3)| 36:16:56 |       |       |  Q1,02 | PCWP |            |
|  17 |              PX SEND HASH                       | :TQ10000              |    79M|  5456M|       |    14M  (3)| 36:16:56 |       |       |        | S->P | HASH       |
|  18 |               PARTITION RANGE ALL               |                       |    79M|  5456M|       |    14M  (3)| 36:16:56 |     1 |    20 |        |      |            |
|* 19 |                TABLE ACCESS BY LOCAL INDEX ROWID| D_T1                  |    79M|  5456M|       |    14M  (3)| 36:16:56 |     1 |    20 |        |      |            |
|  20 |                 INDEX FULL SCAN                 | IN_T1                 |   189M|       |       |   280K  (9)| 00:41:41 |     1 |    20 |        |      |            |
|  21 |           PARTITION RANGE ITERATOR              |                       |     1 |    20 |       |     2   (0)| 00:00:01 |   KEY |   KEY |  Q1,02 | PCWP |            |
|* 22 |            TABLE ACCESS BY LOCAL INDEX ROWID    | T3                    |     1 |    20 |       |     2   (0)| 00:00:01 |   KEY |   KEY |  Q1,02 | PCWP |            |
|* 23 |             INDEX UNIQUE SCAN                   | PK_T3                 |     1 |       |       |     1   (0)| 00:00:01 |   KEY |   KEY |  Q1,02 | PCWP |            |
|  24 |        PX RECEIVE                               |                       |    65M|  1129M|       | 27512  (31)| 00:04:06 |       |       |  Q1,04 | PCWP |            |
|  25 |         PX SEND HASH                            | :TQ10003              |    65M|  1129M|       | 27512  (31)| 00:04:06 |       |       |  Q1,03 | P->P | HASH                 |
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
791732
Assuming third table is t2...


SELECT * FROM T1 a, T3 c
where a.c1 = c.c1
and not exists (select b.c1 from t2 b where b.c1 = a.c1)
1 - 8

Post Details

Added on Jan 2 2020
15 comments
10,956 views