ISSUE IN THE PERFORMANCE [message #596967] |
Fri, 27 September 2013 22:06 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/de1124217a0ef2905f307c37386d46e1?s=64&d=mm&r=g) |
db_learner
Messages: 5 Registered: September 2011
|
Junior Member |
|
|
Hi, I am facing performance issue due to my below query
I have tried a lot by alternate solutions like rearranging the order of tables in join and moving where conditions before
but no success
Its a bottleneck and I could not have indexes on these tables in production
I want to change the appraoch in subquery using OR below but could not think of any solution yet
SELECT
g.COLUMN1,
g.COLUMN2,
e.COLUMN3,
g.COLUMN4,
MIN(e.dat1) KEEP ( DENSE_RANK FIRST ORDER BY date2 Desc) * -1,
min(to_char(date3,'dd-mm-yyyy'))
from table1 e, table2 g
where
(
e.COLUMN3 in
(
select COLUMN3 from table1
where
date2 =( to_date(?SVAR,'DD-MM-YYYY') - (?SVAR + 1 ))
and dat1 >= 0 and column6 = ?SVAR
)
OR
e.COLUMN3 in
(
select COLUMN3 from table1 having min(date3) = (to_date(?SVAR,'DD-MM-YYYY') - (?SVAR)) group by COLUMN3
)
)
and date3 >= (to_date(?SVAR,'DD-MM-YYYY') - ?SVAR)
and g.column6 = e.column6
and e.column6 = ?SVAR
and g.COLUMN3 = e.COLUMN3
and (g.column7 = 'SBA' or g.column7 = 'CAA')
and g.column8 = ?SVAR
and g.column9 = ?SVAR
and g.column10 = ?SVAR
having max(dat1) <0
group by g.COLUMN1,g.COLUMN2,e.COLUMN3,g.COLUMN4
Kindly suggest and help
|
|
|
|
Re: ISSUE IN THE PERFORMANCE [message #596971 is a reply to message #596967] |
Sat, 28 September 2013 03:18 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
John Watson
Messages: 8944 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
There are some odd structures in your code. For instance, thismin(to_char(date3,'dd-mm-yyyy')) Today is 28 September 2013, which is less than 1 October 2013 - but not when you compare the strings as you are. Also, you have two subqueries where one would do.
Fiddling around with the order of joins and predicates will have no effect if you are using the cost based optimizer.
|
|
|
|
|
|