Charles Hooper schrieb:
> 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.
>
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
Received on Sun Nov 04 2007 - 13:09:42 CST
