Re: Performance: Dates vs Varchar2

On 13 Feb 2002 08:08:38 -0800, barnest_at_san.rr.com (Tom Barnes) wrote:

>Lets say we store time/dates of certain events in a table and we want
>to select all events for a certain day. Which method will be faster
>(02/13/2002 is the in-parameter, can/should be replaced with bind
>variable)?
>
>A. Store the event time/date in a DATE field, index this field, and
>have a where-clause like this:
>"where eventTime between to_date('02/13/2002','MM/DD/YYYY') and
>to_date('02/13/2002 23:59:59','MM/DD/YYYY HH24:MI:SS')"
>
>B. Store the day of the event in a VARCHAR2(10) field, index this
>field, and have a where-clause like this:
>"where day = '02/13/2002'"
>
>It will be easier to compress the index in B but for this discussion,
>lets assume we don't use compression.
>
>Oracle 8.1.6.3 Enterprise Edition on Solaris

Index compression works only with concatenated indexes (ie indexes consisting of multiple columns)
So the second scenario won't use an index, unless you have a function based index on it. Compared to storing the column as a date this is putting the horse behind the cart (Dutch proverb)

Regards

Sybrand Bakker, Senior Oracle DBA

To reply remove -verwijderdit from my e-mail address Received on Wed Feb 13 2002 - 12:12:12 CST