how to change this query, so that it will not use the correlation?

[Prince Kumar]

I have the following query, which is takling couple of minutes to
return 500+ rows!

I have all the proper indexes and statistics is upto date.

I correlation after the OR clause is the culprit. I have tried a few
ways to get the sum and then join with the table pr. But am not
getting the exact result.

Can someone show me the way to write this query without using the
correlation? My brain is blanked out!
select recordno, (select sum(amount)
from pmt
where cnyno = 1234 and
pmtdt <= sysdate and
state = 'X' and
(pmt.key = pr.recordno
or
pmt.key in
(select recordno from pr cr
where cr.cnyno = pr.cnyno and
cr.parentrec = pr.recordno
)
)
) tot
from pr
where pr.cnyno = 1234 and
pr.created <= sysdate

thanks!

[Daniel Roy]

Questions:
1- Version?
2- Execution plan?
3- What do you mean by "correlation"?

Daniel

I have the following query, which is takling couple of minutes to
return 500+ rows!

I have all the proper indexes and statistics is upto date.

I correlation after the OR clause is the culprit. I have tried a few
ways to get the sum and then join with the table pr. But am not
getting the exact result.

Can someone show me the way to write this query without using the
correlation? My brain is blanked out!
select recordno, (select sum(amount)
from pmt
where cnyno = 1234 and
pmtdt <= sysdate and
state = 'X' and
(pmt.key = pr.recordno
or
pmt.key in
(select recordno from pr cr
where cr.cnyno = pr.cnyno and
cr.parentrec = pr.recordno
)
)
) tot
from pr
where pr.cnyno = 1234 and
pr.created <= sysdate

thanks!

[Prince Kumar]

To answer your question:

1- Version? 9.2.0.3
2- Execution plan? I will have it attached later. At this time
I am looking for how to modify "OR" part of the query without
changing the semantics.
3- What do you mean by "correlation"?
The table in subquery referring back to the
table outside the subquery.
thanks,

da*************@hotmail.com (Daniel Roy) wrote in message news:<37************************@posting.google.co m>...

Questions:
1- Version?
2- Execution plan?
3- What do you mean by "correlation"?

Daniel

I have the following query, which is takling couple of minutes to
return 500+ rows!

I have all the proper indexes and statistics is upto date.

I correlation after the OR clause is the culprit. I have tried a few
ways to get the sum and then join with the table pr. But am not
getting the exact result.

Can someone show me the way to write this query without using the
correlation? My brain is blanked out!
select recordno, (select sum(amount)
from pmt
where cnyno = 1234 and
pmtdt <= sysdate and
state = 'X' and
(pmt.key = pr.recordno
or
pmt.key in
(select recordno from pr cr
where cr.cnyno = pr.cnyno and
cr.parentrec = pr.recordno
)
)
) tot
from pr
where pr.cnyno = 1234 and
pr.created <= sysdate

thanks!

[Daniel Roy]

Try replacing the "in" by an "exists", as it can perform much better,
especially if the list of possible values is large. I can't think of
any way to remove the "or", but I've never seen an "or" slow down a
select statement considerably. Note also that if the query returns 500
rows, it doesn't mean necessarily that it should run fast. What
determines the speed is the number of rows it has to go through, and
not the number of rows returned. It would be much easier for us to
help if we had the execution plan. Also make sure that your stats are
up-to-date.

Daniel

[Prince Kumar]

Daniel!

Thanks for the response.

I know the number of rows returned is irrelevent. I shouldn't have
have that information there.

EXISTS, wouldn't work either in this case. I believe, I somehow have
to get rid of the correlation, to have the best response time. [All
the stats and indexes are in place]. The table MRECORD has many rows
(a few million) and when the "WHERE" condition (line# 30 to 32) is
applied, it gets only a few hundred rows.

Anyway, I have the query attached with explain plan.

1 select record#,entity, (select nvl(sum(amount),0)
2 from entryitems
3 where comp# = 1234 and
4 paymentdate <= sysdate and
5 state = 'X' and
6 (entryitems.payKey = mr.record#
7 or
8 entryitems.payKey in
9 (select record# from mrecord
childrec
10 where childrec.comp# = 1234
and
11 childrec.parentent =
mr.record#
12 )
13 )
14 ) neg,
15 (select nvl(sum(amount),0)
16 from entryitems
17 where comp# = 1234 and
18 paymentdate <= sysdate and
19 state = 'X' and
20 (entryitems.recKey = mr.record#
21 or
22 entryitems.recKey in
23 (select record# from mrecord
childrec
24 where childrec.comp# = 1234
and
25 childrec.parentent =
mr.record#
26 )
27 )
28 ) pos
29 from mrecord mr
30 where mr.comp# = 1234 and
31 mr.whencreated <= sysdate and
32* mr.type in ('A','B')
/

OPS$GS> l
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=38 Card=89
Bytes=2314)
1 0 SORT (AGGREGATE)
2 1 FILTER
3 2 TABLE ACCESS (BY INDEX ROWID) OF 'ENTRYITEMS' (Cost=63
Card=72 Bytes=1440)
4 3 INDEX (RANGE SCAN) OF 'IX_ENTRYITEMS_PARENTENT'
(NON-UNIQUE) (Cost=12 Card=1454)
5 2 TABLE ACCESS (BY INDEX ROWID) OF 'MRECORD' (Cost=3
Card=1 Bytes=10)
6 5 INDEX (UNIQUE SCAN) OF 'PK_MRECORD' (UNIQUE) (Cost=2
Card=2884447)
7 0 SORT (AGGREGATE)
8 7 FILTER
9 8 TABLE ACCESS (BY INDEX ROWID) OF 'ENTRYITEMS' (Cost=63
Card=72 Bytes=1440)
10 9 INDEX (RANGE SCAN) OF 'IX_ENTRYITEMS_PARENTENT'
(NON-UNIQUE) (Cost=12 Card=1454)
11 8 TABLE ACCESS (BY INDEX ROWID) OF 'MRECORD' (Cost=3
Card=1 Bytes=10)
12 11 INDEX (UNIQUE SCAN) OF 'PK_MRECORD' (UNIQUE) (Cost=2
Card=2884447)
13 0 INLIST ITERATOR
14 13 TABLE ACCESS (BY INDEX ROWID) OF 'MRECORD' (Cost=38
Card=89 Bytes=2314)
15 14 INDEX (RANGE SCAN) OF 'IX_MRECORD_CLRDATE'
(NON-UNIQUE) (Cost=5 Card=106)

Statistics
----------------------------------------------------------
0 recursive calls
4 db block gets
28024824 consistent gets
48 physical reads
0 redo size
19852 bytes sent via SQL*Net to client
961 bytes received via SQL*Net from client
44 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
630 rows processed

da*************@hotmail.com (Daniel Roy) wrote in message news:<37***********************@posting.google.com >...

Try replacing the "in" by an "exists", as it can perform much better,
especially if the list of possible values is large. I can't think of
any way to remove the "or", but I've never seen an "or" slow down a
select statement considerably. Note also that if the query returns 500
rows, it doesn't mean necessarily that it should run fast. What
determines the speed is the number of rows it has to go through, and
not the number of rows returned. It would be much easier for us to
help if we had the execution plan. Also make sure that your stats are
up-to-date.

Daniel

[Prince Kumar]

I think I finally figure it out. Let me do some more testing and see
whether I got it correct and the response is acceptable.

Thanks,

da*************@hotmail.com (Daniel Roy) wrote in message news:<37***********************@posting.google.com >...

Try replacing the "in" by an "exists", as it can perform much better,
especially if the list of possible values is large. I can't think of
any way to remove the "or", but I've never seen an "or" slow down a
select statement considerably. Note also that if the query returns 500
rows, it doesn't mean necessarily that it should run fast. What
determines the speed is the number of rows it has to go through, and
not the number of rows returned. It would be much easier for us to
help if we had the execution plan. Also make sure that your stats are
up-to-date.

Daniel

[Ed prochak]

gs**@yahoo.com (Prince Kumar) wrote in message news:<62**************************@posting.google. com>...

I think I finally figure it out. Let me do some more testing and see
whether I got it correct and the response is acceptable.

Thanks,

can you then please post your final solution? Some of us are curious.