Query works fine in 8i and not in 9i
- From: "Guerra, Abraham J" <AGUERRA@xxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 11 Aug 2004 10:01:28 -0500
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 //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
