I have a query that contains these lines as part of the WHERE clause:
WHERE trunc(it.transaction_date) = to_date('May-19-2001', 'Mon-dd-yyyy')
This works fine -- the query returns in about a minute, which is what
I'd expect for the table sizes, the rest of the joins, etc.
Changing to the following causes the time to go to about 7 hours!
WHERE trunc(it.transaction_date) BETWEEN
to_date('May-19-2001', 'Mon-dd-yyyy') AND
to_date('May-28-2001', 'Mon-dd-yyyy')
Changing the BETWEEN to >= AND <= gives the same long results.
What makes it go back to normal is dropping the trunc(), or otherwise
rewriting it so there is no trunc(). So I have a workaround. But I'm
curious why there'd be such a huge difference. Running just the BETWEEN
piece by itself works fine.
This is Oracle 8.1.6 on Solaris. I've tried indexing the transaction_date
column both as transaction_date and trunc(transaction_date) but there's no
difference. I've used both individual column indexes and combined with
other WHERE clause columns used.
One web site I found said there's an 8i bug creating functional indexes
using
trunc() but I have not verified that.
I've started to analyze but SQL*Plus autotrace takes almost as long to
return (7 hours) so it's not too useful at this point.
----
Steve Sapovits
Global Sports Interactive
Work Email: [EMAIL PROTECTED]
Home Email: [EMAIL PROTECTED]
Work Phone: 610-491-7087
Cell: 610-574-7706
Pager: 877-239-4003
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Steve Sapovits
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
