RE: Query works fine in 8i and not in 9i

INSERT  INTO PS_CLO_LEDG_TMP001
(ACCOUNT,ACCOUNTING_PERIOD,AFFILIATE,AF_GEOMKT,AF_SLSMKT,AF_SUBACCT,ALTACCT,
BASE_CURRENCY,BUSINESS_UNIT,CURRENCY_CD,DEPTID,DTTM_STAMP_SEC,
FISCAL_YEAR,LEDGER,OPERATING_UNIT,POSTED_BASE_AMT,POSTED_TOTAL_AMT,POSTED_TR
AN_AMT,PROCESS_INSTANCE,PRODUCT,PROJECT_ID,STATISTICS_CODE,CF_GROUP_NBR,
CLOS_PROC_FLG,CURRENCY_CD1,FOREIGN_AMOUNT,FOREIGN_CURRENCY,MONETARY_AMOUNT,S
CENARIO,SEQNUM)
SELECT

A.ACCOUNT,0,A.AFFILIATE,A.AF_GEOMKT,A.AF_SLSMKT,A.AF_SUBACCT,A.ALTACCT,A.BAS
E_CURRENCY,'XXXX',A.CURRENCY_CD,A.DEPTID,
   TO_DATE(SUBSTR('2004-08-10-13.53.06.780000', 0, 19),
'YYYY-MM-DDHH24:MI:SS'),2004,A.LEDGER,A.OPERATING_UNIT, -SUM(A.POSTED_BASE_A
MT),
   -SUM(A.POSTED_TOTAL_AMT),-SUM(A.POSTED_TRAN_AMT),0009999999,A.PRODUCT,A.P
ROJECT_ID,A.STATISTICS_CODE, 0, 0,' ', 0.0,' ', 0,' ', 0
FROM
  PS_LEDGER A,
 (select rownum, c.* from PS_CLO_ACCT_TMP001 C where c.balance_fwd_sw='N'
and c.statistics_account='N' order by c.account) c
WHERE A.BUSINESS_UNIT='XXXX'
  AND A.LEDGER IN ('NON-CASH')
  AND A.FISCAL_YEAR=2004
  AND A.ACCOUNTING_PERIOD>=1
  AND A.ACCOUNTING_PERIOD<=998
  AND A.ACCOUNT=C.ACCOUNT
GROUP BY
A.LEDGER,A.CURRENCY_CD,A.ACCOUNT,A.ALTACCT,A.OPERATING_UNIT,A.DEPTID,A.PRODU
CT,

A.PROJECT_ID,A.AF_GEOMKT,A.AF_SLSMKT,A.AF_SUBACCT,A.AFFILIATE,A.STATISTICS_C
ODE,A.BASE_CURRENCY;

might convince the optimizer to try something useful if my assumptions about
your data texture are within reason and I didn't screw up the syntax just
typing this untested and wiping out a bunch of 3D's and =20's.

Then again, I could be way off target. Let me know how you make out.

mwf
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Guerra, Abraham J
Sent: Wednesday, August 11, 2004 11:01 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: Query works fine in 8i and not in 9i


Hello list members,

I have a query that runs fast in SUN 8i and terrible in HP 9i.=20

This is the execution plan in 8i (8.1.7.2)

  INSERT STATEMENT     CHOOSE  Cost=3D20 Rows Expected=3D11
    SORT  GROUP BY     Cost=3D20 Rows Expected=3D11
      NESTED LOOPS       Cost=3D18 Rows Expected=3D11
        TABLE ACCESS  FULL  SYSADM. PS_CLO_ACCT_TMP001 ANALYZED  =
Cost=3D1
Rows Exected=3D1
        TABLE ACCESS  BY INDEX ROWID  SYSADM. PS_LEDGER ANALYZED
Cost=3D17 Rows Expected=3D15779
          INDEX  RANGE SCAN  SYSADM. PS_LEDGER ANALYZED  Cost=3D5 Rows
Expected=3D1779

This is the execution plan in 9i (9.2.0.5)

  INSERT STATEMENT     CHOOSE  Cost=3D1951 Rows Expected=3D4978
    SORT  GROUP BY     Cost=3D1951 Rows Expected=3D4978
      HASH JOIN       Cost=3D1867 Rows Expected=3D4978
        TABLE ACCESS  FULL  SYSADM. PS_CLO_ACCT_TMP001 ANALYZED  =
Cost=3D2
Rows Expected=3D499
        TABLE ACCESS  BY INDEX ROWID  SYSADM. PS_LEDGER ANALYZED
Cost=3D1863 Rows Expected=3D4233
          INDEX  RANGE SCAN  SYSADM. PSCLEDGER ANALYZED  Cost=3D67 Rows
Expected=3D10

Both environments have the same amount of rows and have been analyzed.

This is the culprit query ( I know it is not pretty!):

INSERT  INTO PS_CLO_LEDG_TMP001
(ACCOUNT,ACCOUNTING_PERIOD,AFFILIATE,AF_GEOMKT,AF_SLSMKT,
AF_SUBACCT,ALTACCT,BASE_CURRENCY,BUSINESS_UNIT,CURRENCY_CD,DEPTID,DTTM_S
TAMP_SEC,
FISCAL_YEAR,LEDGER,OPERATING_UNIT,POSTED_BASE_AMT,POSTED_TOTAL_AMT,POSTE
D_TRAN_AMT,
PROCESS_INSTANCE,PRODUCT,PROJECT_ID,STATISTICS_CODE,CF_GROUP_NBR,CLOS_PR
OC_FLG,
CURRENCY_CD1,FOREIGN_AMOUNT,FOREIGN_CURRENCY,MONETARY_AMOUNT,SCENARIO,SE
QNUM)=20
SELECT A.ACCOUNT,
0,A.AFFILIATE,A.AF_GEOMKT,A.AF_SLSMKT,A.AF_SUBACCT,A.ALTACCT,
A.BASE_CURRENCY,'XXXX',A.CURRENCY_CD,A.DEPTID,
TO_DATE(SUBSTR('2004-08-10-13.53.06.780000', 0, 19), 'YYYY-MM-DD
HH24:MI:SS'),2004,
A.LEDGER,A.OPERATING_UNIT, -SUM(A.POSTED_BASE_AMT),
-SUM(A.POSTED_TOTAL_AMT),=20
-SUM(A.POSTED_TRAN_AMT),0009999999,A.PRODUCT,
A.PROJECT_ID,A.STATISTICS_CODE, 0, 0,' ', 0.0,' ', 0,' ', 0=20
FROM PS_LEDGER A,PS_CLO_ACCT_TMP001 C=20
WHERE A.BUSINESS_UNIT=3D'XXXX'=20
AND A.LEDGER IN ('NON-CASH')=20
AND A.FISCAL_YEAR=3D2004=20
AND A.ACCOUNTING_PERIOD>=3D1=20
AND A.ACCOUNTING_PERIOD<=3D998=20
AND A.ACCOUNT=3DC.ACCOUNT=20
AND C.BALANCE_FWD_SW=3D'N'=20
AND C.STATISTICS_ACCOUNT=3D'N'=20
GROUP BY A.LEDGER,A.CURRENCY_CD,
A.ACCOUNT,A.ALTACCT,A.OPERATING_UNIT,A.DEPTID,A.PRODUCT,
A.PROJECT_ID,A.AF_GEOMKT,A.AF_SLSMKT,A.AF_SUBACCT,A.AFFILIATE,
A.STATISTICS_CODE,A.BASE_CURRENCY;

Any help is appreciated.

Abraham Guerra
Oracle DBA
American Family Insurance
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------


----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Other related posts: