Tuning the query [message #393654] |
Tue, 24 March 2009 00:25  |
sivakumar.rj
Messages: 17 Registered: November 2008 Location: Chennai
|
Junior Member |
|
|
I need to tune a query...
SELECT col1, col2
FROM tbl1
WHERE EXISTS
(SELECT 1
FROM tbl2, tbl3
WHERE tbl2.col1 LIKE
SUBSTR(tbl1.col1,
1,
DECODE(tbl1.col2, 1, 2, 3, 4, 5, 6) || '%')) AND
tbl1.col2 <> 'X' AND
tbl1.col3 = (SELECT MAX(col3) FROM tbl3 WHERE tbl1.col1 = tbl2.col2)
This query is taking a long time...pls help on this...already the col1,col2,col3 forms an unique composite index...
especially the substr and decode conditions are taking long time...
[Formatted by RL]
[Updated on: Tue, 24 March 2009 01:50] by Moderator Report message to a moderator
|
|
|
|
|
Re: Tuning the query [message #393698 is a reply to message #393666] |
Tue, 24 March 2009 01:56  |
rleishman
Messages: 3728 Registered: October 2005 Location: Melbourne, Australia
|
Senior Member |
|
|
SELECT col1, col2
FROM tbl1
WHERE EXISTS (
SELECT 1
FROM tbl2, tbl3
WHERE tbl2.col1 LIKE
SUBSTR(tbl1.col1, 1,
DECODE(tbl1.col2, 1, 2, 3, 4, 5, 6) || '%'
)
)
AND tbl1.col2 <> 'X'
AND tbl1.col3 = (
SELECT MAX(col3)
FROM tbl3
WHERE tbl1.col1 = tbl2.col2
)
Doesn't formatting help?
In the EXISTS sub-query, you have joined TBL3 in the FROM clause, but there it is not joined to TBL2 or correllated to TBL1. That makes it a CARTESIAN JOIN. That can't be good.
Ross Leishman
|
|
|