Re: getting records for 1 day

On Nov 4, 2:09 pm, Maxim Demenko <mdeme..._at_gmail.com> wrote:
> Charles Hooper schrieb:
> > 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.
>
> Charles, he has crossposted on all possible RDBMS newsgroups, i don't
> think, there is a commons answer (in terms of performance) to his
> question, first should be specified, on which RDBMS is it intended to run.
>
> Best regards
>
> Maxim

Maxim, thanks for pointing that out - I did not notice the crosspost. Right now there is probably a handful of people in the MySQL and SQLServer groups wondering about function based indexes, the TRUNC function, and the odd date format.

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