Home » RDBMS Server » Performance Tuning » Insert data fast on table (Oracle 11.2 RAC )
Insert data fast on table [message #598476] Tue, 15 October 2013 04:39 Go to next message
nehaverma
Messages: 80
Registered: January 2010
Location: JAIPUR
Member
Hello

I am inserting data using a procedure for 2012 and 2013 year
which is using partitioned tables includes crore of data in a partition taking lot of time or taking months
Is there any other way by which I can insert data fast from our query
Re: Insert data fast on table [message #598477 is a reply to message #598476] Tue, 15 October 2013 04:40 Go to previous messageGo to next message
John Watson
Messages: 4383
Registered: January 2010
Location: Global Village
Senior Member
crore?
Re: Insert data fast on table [message #598478 is a reply to message #598477] Tue, 15 October 2013 04:47 Go to previous messageGo to next message
nehaverma
Messages: 80
Registered: January 2010
Location: JAIPUR
Member
Yes about to ten million of data in a partition
Re: Insert data fast on table [message #598480 is a reply to message #598478] Tue, 15 October 2013 04:55 Go to previous messageGo to next message
John Watson
Messages: 4383
Registered: January 2010
Location: Global Village
Senior Member
Do you mean that crore is 10,000,000? And that it takes months to insert 10,000,000 rows? It should take a few minutes only.
Re: Insert data fast on table [message #598481 is a reply to message #598480] Tue, 15 October 2013 04:59 Go to previous messageGo to next message
nehaverma
Messages: 80
Registered: January 2010
Location: JAIPUR
Member
No But I mean is there any way like loader or datapump where I can use procedure to insert data
Re: Insert data fast on table [message #598482 is a reply to message #598481] Tue, 15 October 2013 05:03 Go to previous messageGo to next message
John Watson
Messages: 4383
Registered: January 2010
Location: Global Village
Senior Member
I don't understand what you are asking, man.
First, you talk about a crore (what ever that is - it is not an English word) taking months. Now you are asking if Data Pump and SQL*Loader (which I assume is what you mean by "loader") can insert rows. Well, they can. That is what they are for.

Re: Insert data fast on table [message #598483 is a reply to message #598482] Tue, 15 October 2013 05:12 Go to previous messageGo to next message
cookiemonster
Messages: 10846
Registered: September 2008
Location: Rainy Manchester
Senior Member
Generally the first step to making something fast is working out why it's slow.
We have no idea what code you running or where the time is being spent.
I suggest you trace the session to find out where the time is being spent.
Re: Insert data fast on table [message #598485 is a reply to message #598476] Tue, 15 October 2013 05:26 Go to previous messageGo to next message
Lalit Kumar B
Messages: 1826
Registered: May 2013
Location: World Wide on the Web
Senior Member
nehaverma wrote on Tue, 15 October 2013 15:09

I am inserting data using a procedure for 2012 and 2013 year


Unless there is a business need, you should not use PL/SQL which could be easliy done using plain SQL.

Quote:

which is using partitioned tables includes crore of data in a partition taking lot of time or taking months

So, Crore wiki says it is ten million. Something which should not take a minute or two depnding on your system environment. And number of records is never a measure of how big the data is. It is always the size. So ten million records is how much in KB/MB/GB?

Quote:

Is there any other way by which I can insert data fast from our query

Show us the code, what exactly are you doing inside the procedure?
Re: Insert data fast on table [message #598486 is a reply to message #598480] Tue, 15 October 2013 05:27 Go to previous messageGo to next message
Roachcoach
Messages: 1178
Registered: May 2010
Location: UK
Senior Member
John Watson wrote on Tue, 15 October 2013 10:55
Do you mean that crore is 10,000,000? And that it takes months to insert 10,000,000 rows? It should take a few minutes only.



Agree. If even that.

  
11:23:12 SQL> create table foo (a number, b varchar2(200));

Table created.

Elapsed: 00:00:00.03


1   insert into foo select * from (
  2   with data as (select level, rpad('x',200,'x') from dual connect by level < 10000)
  3   select data.* from data, data data2
  4*  where rownum <=10000000)
11:23:59 SQL> /

10000000 rows created.

Elapsed: 00:00:39.10


Ok, it's "only" 3gb worth of data, but the point John makes remains, now with an example Very Happy
Re: Insert data fast on table [message #598489 is a reply to message #598486] Tue, 15 October 2013 05:35 Go to previous messageGo to next message
nehaverma
Messages: 80
Registered: January 2010
Location: JAIPUR
Member
Dear

Here is my code:


CREATE OR REPLACE PROCEDURE RAJEMITRA.DIG_CRT (
   p_from_dt       VARCHAR2,
   p_to_dt         VARCHAR2,     
   p_record    OUT sys_refcursor
)
AS
   V_P_FROM_DT   DATE;
   V_P_TO_DT     DATE;     
   CHK_TOKEN     NUMBER;
   CHK_TOKEN2    NUMBER;
   SL_TOKEN     NUMBER := 0;
   CNT_NO     NUMBER := 0;
BEGIN
   BEGIN
      SELECT   TO_DATE (P_FROM_DT, 'DD-MM-RRRR'),
               TO_DATE (P_TO_DT, 'DD-MM-RRRR')             
        INTO   V_P_FROM_DT, V_P_TO_DT
        FROM   DUAL;
   END;

   OPEN p_record FOR
     --   SELECT *  FROM   RAJEMITRA.MST_DIG_CRT_WEB_BAKLOG WHERE UPDT_TIMESTAMP BETWEEN V_P_FROM_DT AND V_P_TO_DT;
     select * from dual;
       -- DBMS_OUTPUT.PUT_LINE('Select in Main table ');
        
        FOR t IN (SELECT 
         rcpt RCPT_NO,
         token TOKEN_NO, 
         MAX (audit_no) audit_no,
         scd srv_cd,
         dept_cd dept_cd,         
         dcd dist_cd,
         dc_cd dc_cd,
         stscd stscd,
         lcd lsp_cd, 
         kiosk,
         tcd teh_cd,
         MAX (updt) UPDT_TIMESTAMP,
         trans_dt_time trans_dt_time,
         para APPLICANT_NAME,
         father_name,
         pics PICS,
         eid,
         uid1 uaid,                 
         max(remrk) remarks
    FROM (  SELECT TO_CHAR (a.rcpt_no) rcpt,
                   TO_CHAR (a.token_no) token,
                   kiosk.kiosk_cd kiosk,
                   srv.dept_cd dept_cd,                                   
                   DIST.DIST_CD dcd,
                   A.DC_CD,
                   STST.STAT_CODE stscd,                  
                   CONVERT (para_text_val, 'UTF8', 'AL32UTF8') para,
                   srv.srv_cd scd,                  
                   a.audit_no audit_no,
                   TO_CHAR (a.updt_dt_tm, 'dd-mon-yyyy') updt,
                   a.rmrk remrk,                   
                   TEHI.TEH_CD tcd,
                   TO_CHAR (h.trans_dt_time, 'dd-mon-yyyy') trans_dt_time,                   
                   LSP1.LSP_CD lcd,
                   (SELECT para_text_val
                      FROM RAJEMITRA.MST_SRV_TRN_DTL_WEB partition(jul_2013)   dtl1
                     WHERE     dtl1.TOKEN_NO = a.token_no
                           AND dtl1.para_cd =
                                  (SELECT para_cd
                                     FROM RAJEMITRA.MST_PARA_DTL_WEB para1
                                    WHERE     PARA1.SRV_CD = a.srv_cd
                                          AND PARA1.VERSION_ID = A.VERSION_ID
                                          AND (   UPPER (para_nm_e) =
                                                     'FATHER''S NAME'
                                               OR UPPER (para_nm_e) =
                                                     'FATHER''S/HUSBAND NAME')))
                      father_name,                                         
                   (SELECT aa.PARA_TEXT_VAL
                      FROM rajemitra.MST_SRV_TRN_DTL_WEB partition(jul_2013) aa,
                           rajemitra.MST_PARA_DTL_WEB bb
                     WHERE     aa.TOKEN_NO = a.token_no
                           AND bb.PARA_DATA_TP_CD = 18
                           AND aa.SRV_CD = bb.SRV_CD
                           AND aa.VERSION_ID = bb.VERSION_ID
                           AND aa.PARA_CD = bb.para_cd
                           AND LOWER (
                                  SUBSTR (aa.para_text_val,
                                          INSTR (aa.para_text_val, '.', -1) + 1,
                                          5)) IN
                                  ('jpg', 'png', 'bmp','gif')
                           AND aa.dc_Cd = a.dc_cd)
                         pics,
                          (SELECT para_text_val
                      FROM RAJEMITRA.MST_SRV_TRN_DTL_WEB partition(jul_2013)  dtl1
                     WHERE     dtl1.TOKEN_NO = a.token_no
                           AND dtl1.para_cd =
                                  (SELECT para_cd
                                     FROM RAJEMITRA.MST_PARA_DTL_WEB para1
                                    WHERE     PARA1.SRV_CD = a.srv_cd
                                          AND PARA1.VERSION_ID = A.VERSION_ID
                                          AND  UPPER (para_nm_e) =
                                                     'AADHAR (UID Number 12 Digit)'
                                              ))
                      eid ,
                      (SELECT para_text_val
                      FROM RAJEMITRA.MST_SRV_TRN_DTL_WEB partition(jul_2013)  dtl1
                     WHERE     dtl1.TOKEN_NO = a.token_no
                           AND dtl1.para_cd =
                                  (SELECT para_cd
                                     FROM RAJEMITRA.MST_PARA_DTL_WEB para1
                                    WHERE     PARA1.SRV_CD = a.srv_cd
                                          AND PARA1.VERSION_ID = A.VERSION_ID
                                          AND  UPPER (para_nm_e) =
                                                     'AADHAR (EID Number 14 Digit)'
                                              ))
                           uid1
              FROM rajemitra.mst_srv_trn_dtl_audit_web partition(jul_2013)  a
                   INNER JOIN (  SELECT MAX (audit_no) audit_no
                                   FROM rajemitra.MST_SRV_TRN_DTL_AUDIT_WEB partition(jul_2013)  auditweb
                                  WHERE                                          
                                         (auditweb.srv_cd, auditweb.version_id) IN
                                               (SELECT srv_cd, version_id
                                                  FROM rajemitra.mst_srv_web web
                                                 WHERE WEB.SRV_MAJ_GROUP_CD = 1)
                               GROUP BY token_no) audit2
                      ON   audit2.audit_no = a.audit_no
                         --AND (a.stat_cd IN (1, 2, 5, 6, 99))                         
                   INNER JOIN rajemitra.TOKEN_NO_RVNU_HD_REL partition(jul_2013)  
                               h
                      ON  1=1  -- h.cancel_flg = 'N'  
                      AND h.trans_dt_time BETWEEN  V_P_FROM_DT AND V_P_TO_DT                       
                         AND h.token_no = a.token_no and h.dept_cd=3137 
                   INNER JOIN rajemitra.MST_SRV_TRN_DTL_WEB partition(jul_2013) f
                      ON f.rcpt_no = h.rcpt_no AND f.token_no = h.token_no
                   INNER JOIN rajemitra.PARA_CHARS_WEB para
                      ON f.PARA_CD = PARA.PARA_CD AND PARA.CHAR_CODE = 14
                   INNER JOIN rajemitra.mst_srv_web srv
                      ON srv.srv_cd = a.srv_cd AND SRV.VERSION_ID = a.version_id
                   INNER JOIN rajemitra.MST_SP_KIOSK kiosk
                      ON KIOSK.KIOSK_CD = a.kiosk_cd AND KIOSK.DC_CD = a.dc_cd
                   INNER JOIN rajemitra.mst_loc_teh tehi
                      ON a.tehsil_code = tehi.teh_cd AND tehi.dist_cd = a.dist_cd
                   INNER JOIN rajemitra.MST_LOC_DIST dist
                      ON DIST.DIST_CD = a.dist_cd
                   INNER JOIN rajemitra.mst_sp_lsp lsp1
                      ON     lsp1.lsp_cd = kiosk.active_lsp_cd
                         AND lsp1.dc_cd = kiosk.dc_cd
                   INNER JOIN rajemitra.MST_TRAN_STST_WEB stst
                      ON STST.STAT_CODE = a.stat_cd
          ) ddd
GROUP BY para,
         father_name,
         kiosk,
         dcd,
         stscd,
         dc_cd,
         lcd,
         rcpt,
         dept_cd,
         token,
         tcd,
         trans_dt_time,
         scd,
         pics,
         eid,
         uid1 order by 12) LOOP 
        dbms_output.put_line(t.token_no); 
        select count(*) into CHK_TOKEN from RAJEMITRA.MST_DIG_CRT_WEB_BAKLOG where token_no=t.token_no;
        if CHK_TOKEN > 0 then
            dbms_output.put_line(t.token_no||' chck token-- '||CHK_TOKEN);
            select count(*) into CHK_TOKEN2 from RAJEMITRA.MST_DIG_CRT_WEB_BAKLOG where token_no=t.token_no and stscd=1;
            IF  ((t.stscd =99 or t.stscd =6)  and CHK_TOKEN2 > 0)   then
                UPDATE RAJEMITRA.MST_DIG_CRT_WEB_BAKLOG SET CANCEL_DATE=t.UPDT_TIMESTAMP , REMARKS = t.REMARKS , APR_STSCD = t.stscd where STSCD =1 AND TOKEN_NO=t.token_no ;
            ELSE
                UPDATE RAJEMITRA.MST_DIG_CRT_WEB_BAKLOG SET UPDT_TIMESTAMP=t.UPDT_TIMESTAMP , STSCD = t.STSCD , REMARKS = t.REMARKS where STSCD !=1 AND TOKEN_NO=t.token_no ;
            END if; 
            commit;
        else
            select  count(*)  into CNT_NO from RAJEMITRA.MST_DIG_CRT_WEB_BAKLOG mdcw where 
            MDCW.APPLICANT_NAME =t.APPLICANT_NAME  and MDCW.FATHER_NAME =t.father_name 
            and MDCW.SRV_CD =t.srv_cd ;
            select  max(DUB_TOKEN)  into SL_TOKEN from RAJEMITRA.MST_DIG_CRT_WEB_BAKLOG mdcw where 
            MDCW.APPLICANT_NAME =t.APPLICANT_NAME  and MDCW.FATHER_NAME =t.father_name 
            and MDCW.SRV_CD =t.srv_cd and MDCW.DUB_NO=0 ;
            if SL_TOKEN is null then
                SL_TOKEN := t.token_no;
            end if;  
           dbms_output.put_line(t.token_no||' else chck token '||SL_TOKEN);         
           
            
            INSERT /*+ parallel(4) */  INTO RAJEMITRA.MST_DIG_CRT_WEB_BAKLOG NOLOGGING  VALUES (t.RCPT_NO,t.TOKEN_NO,t.AUDIT_NO,t.SRV_CD,t.DEPT_CD,t.DIST_CD,t.DC_CD,t.STSCD,t.LSP_CD,t.KIOSK,
            t.TEH_CD,t.UPDT_TIMESTAMP,t.TRANS_DT_TIME,t.APPLICANT_NAME,t.FATHER_NAME,SL_TOKEN,CNT_NO,t.PICS,t.EID,t.UAID,t.REMARKS,NULL,NULL);
            commit;
        end if;
        
        END LOOP;
EXCEPTION
   WHEN NO_DATA_FOUND
   THEN
      NULL;
   WHEN OTHERS
   THEN
      -- Consider logging the error and then re-raise
      RAISE;
END;
/


*BlackSwan added {code} tags. Please do so yourself in the future.

[Updated on: Tue, 15 October 2013 08:59] by Moderator

Report message to a moderator

Re: Insert data fast on table [message #598492 is a reply to message #598489] Tue, 15 October 2013 05:40 Go to previous messageGo to next message
cookiemonster
Messages: 10846
Registered: September 2008
Location: Rainy Manchester
Senior Member
After 80 posts I'm sure you must have been asked to read and follow How to use [code] tags and make your code easier to read?.
Do so, and format your code, the above is unreadable.
Re: Insert data fast on table [message #598496 is a reply to message #598489] Tue, 15 October 2013 06:07 Go to previous messageGo to next message
Lalit Kumar B
Messages: 1826
Registered: May 2013
Location: World Wide on the Web
Senior Member
After looking at that code, one thing is for sure. It is not only about the INSERT that could be the culprit(if it really is), however, now we have so many things to see which could cause the slowness. There are ways to get to the SQL which needs to be tuned.

But first follow the link cookiemonster has given to make the code readable.
Re: Insert data fast on table [message #598501 is a reply to message #598489] Tue, 15 October 2013 07:07 Go to previous messageGo to next message
John Watson
Messages: 4383
Registered: January 2010
Location: Global Village
Senior Member
Your code is unreadable, but even so, I suspect that whoever wrote does not understand that he is working with SQL and with an Oracle database. SQL is a set oriented language, and that code looks like an attempt to do row-by-row procedural programming. Then take that insert at the end (which I have formatted for you):
INSERT /*+ parallel(4) */ INTO rajemitra.mst_dig_crt_web_baklog NOLOGGING
VALUES      (t.rcpt_no,
             t.token_no,
             t.audit_no,
             t.srv_cd,
             t.dept_cd,
             t.dist_cd,
             t.dc_cd,
             t.stscd,
             t.lsp_cd,
             t.kiosk,
             t.teh_cd,
             t.updt_timestamp,
             t.trans_dt_time,
             t.applicant_name,
             t.father_name,
             sl_token,
             cnt_no,
             t.pics,
             t.eid,
             t.uaid,
             t.remarks,
             NULL,
             NULL); 
It makes no sense to parallelize a single row insert. You should be inserting sets of rows, then (perhaps) parallelism will help. If you have enabled parallel DML. Have you? There are also conditions to be met before the NOLOGGING command can have any effect. Do you really want NOLOGGING? Very dangerous.
I would advise starting from the beginning, try to write equivalent statements using only SQL, not PL/SQL.
Re: Insert data fast on table [message #598508 is a reply to message #598501] Tue, 15 October 2013 08:00 Go to previous messageGo to next message
cookiemonster
Messages: 10846
Registered: September 2008
Location: Rainy Manchester
Senior Member
I suspect the whole thing can be rewritten as a single merge statement.
Re: Insert data fast on table [message #598512 is a reply to message #598489] Tue, 15 October 2013 08:59 Go to previous message
Lalit Kumar B
Messages: 1826
Registered: May 2013
Location: World Wide on the Web
Senior Member
nehaverma wrote on Tue, 15 October 2013 16:05

CREATE OR REPLACE PROCEDURE RAJEMITRA.DIG_CRT (
   p_from_dt       VARCHAR2,
   p_to_dt         VARCHAR2,     
   ..............
AS
   V_P_FROM_DT   DATE;
   V_P_TO_DT     DATE;     
   ..............
      SELECT   TO_DATE (P_FROM_DT, 'DD-MM-RRRR'),
               TO_DATE (P_TO_DT, 'DD-MM-RRRR')             
        INTO   V_P_FROM_DT, V_P_TO_DT
        FROM   DUAL;
   ..............


The variables V_P_FROM_DT and V_P_TO_DT are unnecessary. The SELECT...INTO DUAL is also not required.

You could simply take p_from_dt and p_to_dt IN parameters as DATE data type and directly use in the predicate.

So the below :
AND H.TRANS_DT_TIME BETWEEN V_P_FROM_DT AND V_P_TO_DT


Becomes :
AND H.TRANS_DT_TIME BETWEEN p_from_dt AND p_to_dt


Regards,
Lalit
Previous Topic: database links question
Next Topic: Help with 12c vs 10.2g
Goto Forum:
  


Current Time: Fri Jul 25 22:46:21 CDT 2014

Total time taken to generate the page: 0.09416 seconds