Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Performance: Dates vs Varchar2

Performance: Dates vs Varchar2

From: Tom Barnes <barnest_at_san.rr.com>
Date: 13 Feb 2002 08:08:38 -0800
Message-ID: <ae6b6116.0202130808.6076c9cc@posting.google.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)?

  1. 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')"
  2. 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 Received on Wed Feb 13 2002 - 10:08:38 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US