Home » SQL & PL/SQL » SQL & PL/SQL » Tune the query (oracle 10g)
Tune the query [message #417812] Tue, 11 August 2009 10:59 Go to next message
gauravpuri2002
Messages: 24
Registered: October 2007
Location: Bangalore
Junior Member
Hi I am using the following script but its taking very log time:-
there is a composite primary key(org, acct, seq_date_time, tie_breaker) on TBODS.cbp_trans table.

INSERT INTO TBODS.cbp_trans
(org
,acct
,seq_date_time
,tie_breaker
,logo
,ck_date
,ck_amount
,ck_number
,ck_user_first_maint
,ck_waive_fee
,ck_fee_amt
,cbp_method
,tran_cde
,edit_dt_tm
,edit_id
,delete_yn
,load_dt
,check_cut_date
,OWNING_BRANCH_NBR
,cbp_hips_ref_number
,loc_code)
SELECT org
,acct
,seq_date_time
,tie_breaker
,logo
,ck_date
,ck_amount
,decode(ck_number, null, '999', ck_number) as ck_number
,ck_user_first_maint
,ck_waive_fee
,ck_fee_amt
,cbp_method
,tran_cde
,Decode(GREATEST (TRUNC(seq_date_time), ck_date), trunc(sysdate), trunc(sysdate), trunc(sysdate-1))
,'SYSTEM'
,'N'
,load_dt
,Decode(GREATEST (TRUNC(seq_date_time), ck_date), trunc(sysdate), trunc(sysdate), trunc(sysdate-1))
,OWNING_BRANCH_NBR
,cbp_hips_ref_number
,loc_code
FROM STAGE.cbp_trans
WHERE (org, acct, seq_date_time, tie_breaker)
NOT IN
(SELECT org,acct,seq_date_time,tie_breaker
FROM cbp_trans);


I tried modifying like below but still this is also taking time:-
INSERT INTO TBODS.cbp_trans
(org
,acct
,seq_date_time
,tie_breaker
,logo
,ck_date
,ck_amount
,ck_number
,ck_user_first_maint
,ck_waive_fee
,ck_fee_amt
,cbp_method
,tran_cde
,edit_dt_tm
,edit_id
,delete_yn
,load_dt
,check_cut_date
,OWNING_BRANCH_NBR
,cbp_hips_ref_number
,loc_code)
SELECT a.org
,a.acct
,a.seq_date_time
,a.tie_breaker
,a.logo
,a.ck_date
,a.ck_amount
,decode(a.ck_number, null, '999',a. ck_number) as ck_number
,a.ck_user_first_maint
,a.ck_waive_fee
,a.ck_fee_amt
,a.cbp_method
,a.tran_cde
,Decode(GREATEST (TRUNC(a.seq_date_time), a.ck_date), trunc(sysdate), trunc(sysdate), trunc(sysdate-1))
,'SYSTEM'
,'N'
,a.load_dt
,Decode(GREATEST (TRUNC(a.seq_date_time), a.ck_date), trunc(sysdate), trunc(sysdate), trunc(sysdate-1))
,a.OWNING_BRANCH_NBR
,a.cbp_hips_ref_number
,a.loc_code
FROM STAGE.cbp_trans a
WHERE NOT EXISTS (SELECT 'x' FROM tbods.cbp_trans b
WHERE a.org = b.org
AND a.acct = b.acct
AND a.seq_date_time = b.seq_date_time
AND a.tie_breaker = b.tie_breaker);
Re: Tune the query [message #417816 is a reply to message #417812] Tue, 11 August 2009 11:10 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version with 4 decimals.

Regards
Michel
Re: Tune the query [message #417817 is a reply to message #417812] Tue, 11 August 2009 11:12 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Go to the URL above click the link "Posting Guidelines"
Go to the section labeled "Practice" & do as directed.


INSERT INTO tbods.cbp_trans
           (org,
            acct,
            seq_date_time,
            tie_breaker,
            logo,
            ck_date,
            ck_amount,
            ck_number,
            ck_user_first_maint,
            ck_waive_fee,
            ck_fee_amt,
            cbp_method,
            tran_cde,
            edit_dt_tm,
            edit_id,
            delete_yn,
            load_dt,
            check_cut_date,
            owning_branch_nbr,
            cbp_hips_ref_number,
            loc_code)
SELECT org,
       acct,
       seq_date_time,
       tie_breaker,
       logo,
       ck_date,
       ck_amount,
       Decode(ck_number,NULL,'999',
                        ck_number) AS ck_number,
       ck_user_first_maint,
       ck_waive_fee,
       ck_fee_amt,
       cbp_method,
       tran_cde,
       Decode(Greatest(Trunc(seq_date_time),ck_date),Trunc(SYSDATE),Trunc(SYSDATE),
                                                     Trunc(SYSDATE - 1)),
       'SYSTEM',
       'N',
       load_dt,
       Decode(Greatest(Trunc(seq_date_time),ck_date),Trunc(SYSDATE),Trunc(SYSDATE),
                                                     Trunc(SYSDATE - 1)),
       owning_branch_nbr,
       cbp_hips_ref_number,
       loc_code
FROM   stage.cbp_trans
WHERE  (org,acct,seq_date_time,tie_breaker) NOT IN (SELECT org,
                                                           acct,
                                                           seq_date_time,
                                                           tie_breaker
                                                    FROM   cbp_trans); 


invoke sqlplus
SQL> SET AUTOTRACE TRACEONLY EXPLAIN STATISTICS
SQL> -- INVOKE INSERT HERE
SQL> EXIT

now CUT results & using <code tags> PASTE output back here.
Re: Tune the query [message #418084 is a reply to message #417817] Wed, 12 August 2009 21:54 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Add NOT NULL predicates to allow Oracle to perform a HASH ANTI JOIN.
Always use a table alias on your sub-query and qualify column names to ensure you are not inadvertently (perhaps by mis-spelling) referencing columns from the outer query.

WHERE  org IS NOT NULL
AND    acct IS NOT NULL
AND    seq_date_time IS NOT NULL
AND    tie_breaker IS NOT NULL
AND   (org,acct,seq_date_time,tie_breaker) NOT IN (
    SELECT b.org,
           b.acct,
           b.seq_date_time,
           b.tie_breaker
    FROM   cbp_trans b
    WHERE  b.org IS NOT NULL
    AND    b.acct IS NOT NULL
    AND    b.seq_date_time IS NOT NULL
    AND    b.tie_breaker IS NOT NULL
    );


Ross Leishman
Previous Topic: search for "returns"
Next Topic: group by and order by
Goto Forum:
  


Current Time: Sun Dec 04 20:55:44 CST 2016

Total time taken to generate the page: 0.08569 seconds