Re: [sqr-users] Oracle Insert/Select From issue

["George Jansen" <GJANSEN@aflcio.org>]

1. If you run the query in SQL*Plus with SET AUTOTRACE TRACEONLY, how does your 
plan differ from the one in the trace you ran on the SQR?

2. If you hard-code the dates, does it get your time down where it should be? 
If so, are the variables declared as date variables?



>>> Edward.Hiney@thehartford.com 09/26/05 11:36 AM >>>
                All,
                Here's the problem.
                We have an SQR with code (below) found in a procedure that runs 
for a VERY LONG TIME (HOURS).I tried separating in into a "Select" and an 
"Insert" but that did not help.  If anyone can come up with a solution to break 
up the WHERE clause into 2 parts (doing the 2nd join to PS_VCHR_ACCTG_LINE D ) 
I think that would do it.  It's not as easy as it looks.

                We did an Oracle SQL trace, so we know that this is where the 
bottleneck is.  We also pu SHOW stmts in the SQR.

                The caveat is that when this sql is executed in SQL-plus or 
TOAD, it runs in 2 minutes!  Any ideas would be appreciated.


                TIA.
                ed.hiney




                Begin-SQL on-error=170-sql-error
                INSERT INTO PS_HIG_RPT7X_DTL SELECT 
                0,
                '71F',
                $Tdate,
                B.BANK_SETID, 
                B.BANK_CD , 
                B.BANK_ACCT_KEY,
                A.ACCOUNTING_DT, 
                1,
                3,
                C.HIG_CCPS_IND,
                C.HIG_SRS_IND, 
                &HIG_FREQ_IND,
                SUM(A.MONETARY_AMOUNT - ABS(D.MONETARY_AMOUNT))                 
     
                FROM PS_VCHR_ACCTG_LINE A, PS_PYMNT_VCHR_XREF B , 
PS_HIG_BPROFL_VW C, PS_VCHR_ACCTG_LINE D
                WHERE A.BUSINESS_UNIT = B.BUSINESS_UNIT 
                AND A.VOUCHER_ID = B.VOUCHER_ID 
                AND A.PYMNT_CNT = B.PYMNT_CNT
                AND B.BUSINESS_UNIT = C.BUSINESS_UNIT 
                AND C.ORIGIN = 'ONL' 
                AND a.monetary_amount > 0
                and b.pymnt_gross_amt > 0
                AND a.accounting_dt between $Fdate and $Tdate
                And a.appl_jrnl_id = 'PAYMENTS'
                And a.posting_process = 'PYMN'
                And a.dst_acct_type = 'APA'
                And a.pymnt_cnt = (select min(z.pymnt_cnt)
                                   from ps_vchr_acctg_line z
                                   where z.business_unit = a.business_unit
                                   and z.voucher_id = a.voucher_id
                                   and z.appl_jrnl_id = 'PAYMENTS'
                                   and z.posting_process = 'PYMN'
                                   and z.dst_acct_type = 'APA')
                And d.business_unit = a.business_unit
                And d.voucher_id = a.voucher_id
                And d.accounting_dt < a.accounting_dt
                And d.appl_jrnl_id = 'ACCRUAL'
                And d.posting_process = 'ACCR'
                And d.dst_acct_type = 'APA'
                And d.unpost_seq = a.unpost_seq
                And abs(a.monetary_amount) <> abs(d.monetary_amount)
                GROUP BY B.BANK_SETID, B.BANK_CD , B.BANK_ACCT_KEY, 
a.accounting_dt,C.HIG_CCPS_IND,C.HIG_SRS_IND
                end-sql



Thank you,

Ed Hiney
PeopleSoft Developer
Claims IT
The Hartford
edward.hiney@thehartford.com 
860-547-3227





*************************************************************************
This communication, including attachments, is
for the exclusive use of addressee and may contain proprietary,
confidential and/or privileged information.  If you are not the intended
recipient, any use, copying, disclosure, dissemination or distribution is
strictly prohibited.  If you are not the intended recipient, please notify
the sender immediately by return e-mail, delete this communication and
destroy all copies.
*************************************************************************

_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org 
http://www.sqrug.org/mailman/listinfo/sqr-users


_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users