| sql slow [message #278892] |
Tue, 06 November 2007 03:31  |
mwansalovewell
Messages: 71
Registered: October 2007
Location: uk
|
Member |
|
|
I have this two statements. the first one runs fast and returns no rows, but the second one hangs.
----
1. select b.centre, b.gl_code, A.NL_ACCOUNT_CODE, sum(a.amount) "Actual Movement", NVL(C.AMOUNT, 0) "Year End Value",
NVL(C.AMOUNT, 0) - sum(a.amount) "Difference"
from nlacvals a, nlpaccn b, nlacvals c
where a.company = 'UK'
and b.gl_code = '93231'
AND B.COMPANY = A.COMPANY
AND B.NL_ACCOUNT_CODE = A.NL_ACCOUNT_CODE
AND A.PERIOD_NO BETWEEN 200000 AND 200012
AND C.COMPANY (+) = A.COMPANY
AND C.NL_ACCOUNT_CODE (+) = A.NL_ACCOUNT_CODE
AND C.PERIOD_NO (+) = 200100
GROUP BY B.CENTRE, B.GL_CODE, A.NL_ACCOUNT_CODE, C.AMOUNT
HAVING NVL(C.AMOUNT, 0) - sum(a.amount) <> 0
ORDER BY 1,2
---
2.
select b.centre, b.gl_code, A.NL_ACCOUNT_CODE, sum(a.amount) "Actual Movement", NVL(C.AMOUNT, 0) "Year End Value",
NVL(C.AMOUNT, 0) - sum(a.amount) "Difference"
from nlacvals a, nlpaccn b, nlacvals c
where a.company = 'UK'
and b.gl_code = '93231'
AND B.COMPANY = A.COMPANY
AND B.NL_ACCOUNT_CODE = A.NL_ACCOUNT_CODE
AND A.PERIOD_NO BETWEEN 200100 AND 200112
AND C.COMPANY (+) = A.COMPANY
AND C.NL_ACCOUNT_CODE (+) = A.NL_ACCOUNT_CODE
AND C.PERIOD_NO (+) = 200200
GROUP BY B.CENTRE, B.GL_CODE, A.NL_ACCOUNT_CODE, C.AMOUNT
HAVING NVL(C.AMOUNT, 0) - sum(a.amount) <> 0
ORDER BY 1,2
--
Any idea where tuing is required
explain plan.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=12 Card=1 Bytes=108)
1 0 FILTER
2 1 SORT (GROUP BY) (Cost=12 Card=1 Bytes=108)
3 2 TABLE ACCESS (BY INDEX ROWID) OF 'NLPACCN' (Cost=2 Car
d=1 Bytes=30)
4 3 NESTED LOOPS (Cost=4 Card=1 Bytes=108)
5 4 NESTED LOOPS (OUTER) (Cost=2 Card=1 Bytes=78)
6 5 TABLE ACCESS (BY INDEX ROWID) OF 'NLACVALS' (Cos
t=1 Card=1 Bytes=39)
7 6 INDEX (RANGE SCAN) OF 'NLACVALS_PK' (UNIQUE) (
Cost=2 Card=1)
8 5 TABLE ACCESS (BY INDEX ROWID) OF 'NLACVALS' (Cos
t=1 Card=1 Bytes=39)
9 8 INDEX (RANGE SCAN) OF 'NLACVALS_PK' (UNIQUE) (
Cost=1 Card=1)
10 4 INDEX (RANGE SCAN) OF 'XIF1667NLPACCN' (NON-UNIQUE
) (Cost=1 Card=267)
------
help appreciated
|
|
|
|
|
|
| Re: sql slow [message #278895 is a reply to message #278892] |
Tue, 06 November 2007 03:42  |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
When you say 'hangs', how long have you let it run for before losing patience with it?
When were the statistics last refreshed?
|
|
|
|
|
|
| Re: sql slow [message #278899 is a reply to message #278897] |
Tue, 06 November 2007 03:57 |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
Run Dbms_Stats on the un-analysed table and try again.
It's a bad idea to have only some of your tables analysed, as it forces the CBO to use it's default statistics for your table, and they're almost certain to be badly wrong.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
