Resources for Oracle: http://www.hjrdba.com
> >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
> >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 188.8.131.52 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).
> 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)
> Sybrand Bakker, Senior Oracle DBA
> To reply remove -verwijderdit from my e-mail address