Re: getting records for 1 day

On Nov 4, 6:45 am, Summercool <Summercooln..._at_gmail.com> wrote:
> in SQL, i wonder if I do a
>
> select * from tablefoo where add_date = "2007-11-01"
>
> then it may not show any record as it will only match recorded added
> exactly at 2007-11-01 00:00:00
> so to limit that day, I could use
>
> select * from tablefoo where date(add_date) = "2007-11-01"
>
> except I think if the table has millions of records, then it can take
> forever to run, as it will go through all records and apply the date
> function on each record's add_date.
>
> so the following
>
> select * from tablefoo where add_date >= "2007-11-01" and add_date <
> "2007-11-02"
>
> should work... except it is quite verbose... i wonder if there is a
> better way?

Is the ADD_DATE column defined in the database as a DATE? If so, some people may be tempted to write:
SELECT
  *
FROM
  TABLEFOO
WHERE
  TRUNC(ADD_DATE) = '01-NOV-2007'; The assumption of the above is that there is an index on the ADD_DATE column that will help speed data retrieval... only to find that Oracle performs a full tablescan to identify the matching rows. A function based index could be set up to allow the above syntax to execute without a full tablescan, but is that the best approach? In my opinion, I would not create another index unless there were no other choices. For example, I would use one of the following, most likely the first:
SELECT
  *
FROM
  TABLEFOO
WHERE
  ADD_DATE >= '01-NOV-2007'
  AND ADD_DATE < '02-NOV-2007';

SELECT
  *
FROM
  TABLEFOO
WHERE
  ADD_DATE BETWEEN '01-NOV-2007' AND '02-NOV-2007'   AND ADD_DATE <> '02-NOV-2007';

The second predicate in the second SQL statement's WHERE clause is necessary to prevent those matches that occur at exactly midnight on 02-NOV-2007 from being included. If ADD_DATE is a VARCHAR2 column, either of the above methods will also work (after reformatting the date constant), but Oracle may incorrectly predict the number of rows that will be returned by the query, and may force a full tablescan, even if there is an index on the ADD_DATE column - if that happens, an INDEX hint may be used to force an index based execution plan.

In summary: The shortest programming solution may not be the most efficient solution.

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc. Received on Sun Nov 04 2007 - 11:57:04 CST