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);

Report message to a moderator

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: 68722
Registered: March 2007
Location: Saint-Maur, France, https...
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

Report message to a moderator

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: 26766
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.

Report message to a moderator

Re: Tune the query [message #418084 is a reply to message #417817] Wed, 12 August 2009 21:54 Go to previous message
rleishman
Messages: 3728
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

Report message to a moderator

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

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Wed Dec 11 22:22:15 CST 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.