RE: problem with trunc()

From: Kevin Lange <kgel_at_ppoone.com>
Date: Thu, 31 May 2001 09:45:02 -0700
Message-ID: <F001.00315CEF.20010531083613@fatcity.com>

I have always been told that using functions on fields would stop the efficient use of indexes so ..... what if you said

WHERE it.transaction_date BETWEEN

        to_date('May-19-2001.00.00.00', 'Mon-dd-yyyy.hh24.mi.ss') AND
        to_date('May-28-2001.23.59.59', 'Mon-dd-yyyy')

This would at least eliminate the Trunc and give you another possibility.

Kevin

-----Original Message-----
Sent: Thursday, May 31, 2001 11:01 AM
To: Multiple recipients of list ORACLE-L

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: sapovitss_at_globalsports.com
Home Email: steves_at_delanet.com
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: SapovitsS_at_globalsportsinc.com

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: ListGuru_at_fatcity.com (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).
--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Kevin Lange
INET: kgel_at_ppoone.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------