| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> 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
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_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Guerra, Abraham J
Sent: Wednesday, August 11, 2004 11:01 AM
To: oracle-l_at_freelists.org
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
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
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
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
 |
 |