Performance: Dates vs Varchar2

Performance: Dates vs Varchar2

Post by Tom Barn » Fri, 15 Feb 2002 01:08:38



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

 
 
 

Performance: Dates vs Varchar2

Post by Sybrand Bakke » Fri, 15 Feb 2002 03:12:12



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

 
 
 

Performance: Dates vs Varchar2

Post by Howard J. Roger » Fri, 15 Feb 2002 04:25:02


Comment below.
--
----------------------------------------------
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
> >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

- Show quoted text -

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

 
 
 

Performance: Dates vs Varchar2

Post by Mark J. Boba » Fri, 15 Feb 2002 11:38:04


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

-Mark


> 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

 
 
 

Performance: Dates vs Varchar2

Post by John Russel » Fri, 15 Feb 2002 18:28:05



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/

 
 
 

Performance: Dates vs Varchar2

Post by Tom Barn » Sat, 16 Feb 2002 01:00:42


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

 
 
 

1. Performance of index on varchar2 vs integer

I haven't tried this with ORACLE, though I tried it with Informix and
found essentially no difference.

I've lots of short strings, avg length is probably 4 with min = 1 and
max = 18.  I could replace these strings with ints and have a lookup
table in memory if querying for something like = 1173 would be faster
than = 'macaroni'.

Ideas?

Thanks in advance.

2. Problem Query

3. char vs varchar vs varchar2

4. NY-NEW YORK-93150--UNIX-Windows NT-ORACLE-Database Architect

5. SQL statements

6. Performance difference between varchar2 and number

7. performance: Ordering my varchar2 keys...

8. Performance implications of increasing a varchar2 column width

9. Help! Performance cost of varchar2 primary keys

10. Poor stored procedure performance using varchar2 parameters

11. CHAR v. VARCHAR2: Space or Performance Optimization