Re: SQL Tuning Guru's HELP HELP !
From: Alan MCCulloch <amccullo_at_ccu1.auckland.ac.nz>
Date: 20 Aug 1998 22:13:09 GMT
Message-ID: <6ri71l$bge$1_at_scream.auckland.ac.nz>
Date: 20 Aug 1998 22:13:09 GMT
Message-ID: <6ri71l$bge$1_at_scream.auckland.ac.nz>
(re query where doing trunc() on an indexed date field means index not used in query)
Hi, I'm not a guru, but one possibility would be to
- change your query so that it definitely uses the index and returns a smaller result-set, that is a super-set of what you want. E.G.
....
AND c.col2 > c.col3 - 1
....
2) now select from your query in 1 using it as a temporary view :
select * from
( your query in 1) superset
where trunc(superset.col2) > superset.col3;
..i.e. get a result-set that contains the one you want via a fast query, then extract exactly what you want from that with the query involving trunc.
A bit of a hack, but sometimes you have to to get speed.
Good Luck
Alan McCulloch Received on Fri Aug 21 1998 - 00:13:09 CEST