Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Query works fine in 8i and not in 9i

Query works fine in 8i and not in 9i

From: Guerra, Abraham J <AGUERRA_at_amfam.com>
Date: Wed, 11 Aug 2004 10:01:28 -0500
Message-ID: <A787F31512A25E4F9782045CFE320C8001658712@NHQ1ACCOEX05VS1.corporate.amfam.com>


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_at_freelists.org 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
Received on Wed Aug 11 2004 - 09:58:38 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US