Very Computer

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

Quote:>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

Comment below.
--
----------------------------------------------
Resources for Oracle: http://www.hjrdba.com
===============================

Not true, actually.  A single, non-unique column can most certainly be
compressed, and usefully too.  I think you are thinking of unique indexes,
where the degree of compression has to be (at most) one less than the number
of columns in the index... which means that a single column unique index can
(at most) have a degree of compression of, er... 0 (ie, it's true that a
single column unique index cannot be compressed).

Regards
HJR

Quote:> 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

If you're storing a date, put in a date datatype.
If it's properly indexed, performance differences
ought to be negligible.

-Mark

Quote:>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')"

Why not use WHERE TRUNC(eventTime) = to_date(...)?  That cuts the
number of possibilities down to a reasonable number and avoids < and >
operations.  You could store TRUNC(eventTime) in its own column, or
build a function-based index on the result of TRUNC(eventTime) for
each row.

John
--
Got an Oracle database question?
Try the search engine for the database docs at:
http://tahiti.oracle.com/

Quote:> 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.

Why wouldn't it use an index? It's just a simple VARCHAR2(10) column
with a normal index on and in the where-clause I'm comparing it with a
VARCHAR2(10) string.

Quote:> Compared to storing the column as a date this is
> putting the horse behind the cart (Dutch proverb)

There are many ways to store the data but in this case I'm not
interested in which one is more elegant, space efficient, etc. I'm
just wondering which one would be fastest, A or B (me and my co-worker
have been disagreeing on this for months).

Thanks