Comment below.
--
----------------------------------------------
Resources for Oracle: http://www.hjrdba.com
===============================
"Sybrand Bakker" <postbus_at_sybrandb.demon.nl> wrote in message
news:e0bl6u4a1n4qr6a11ort528u9tid9lbcg7_at_4ax.com...
> 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)
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
> 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 - 13:25:02 CST
