Re: slow query for MIN function

Alan Mills" <Alan.Mills_at_xservices.pants.fujitsu.com> wrote:

>
>"Kevin" <kevin.zahn_at_usbank.com> wrote in message
>news:ea1e2fb3.0307160435.2f36d30c_at_posting.google.com...
>> I am trying to run the following query:
>>
>> Select min(tran_date)
>> from Sales
>> WHERE (Card1 = '123' OR Card2 = '123' OR Card3 = '123' )
>> and product = 1
>>
>> It takes several minutes to return the tran_date. However, if I do a
>> "Select *" and add in the where clause "and tran_date = "07-Jul-2003"
>> it starts returning rows almost immediately.
>>
>> I have an index on
>>
>> 3 composite indexes:
>> Card1 + tran_date + product
>> Card2 + tran_date + product
>> Card3 + tran_date + product
>>
>> The explain plan on the min(tran_date) query and the select * both use
>> all three indexes.
>>
>>
>> Why does the min(tran_date) query run so slowly?
>>
>> Thanks
>>
>> Kevin
>
>You mentioned the explain plan but didn't include it.
>
>the difference in your SELECT statements is that, although both use the
>indexes, the inclusion of tran_date in the query will cause a better use of
>the indexes in question. The original can only use the Card1/2/3 parts of
>the indexes. introducing tran_date, and already having 'product' in use
>brings the rest of the indexes into play. You should expect better/faster
>results in that instance.
>
>Try changing the indexes to be in the order Card1/2/3 + product +
>tran_date. That way, your original query will use 2/3 of the inded
>capability. Also try product + Card1/2/3 + tran_date to see if that helps.
>Depends whcih reduces the result down fastest.
>

Another factor may be what you are asking :

In a Select * ( with no order by, especially) the return is rapid since no additional processing of the data is needed after the selection criteria are applied..
In the other query, however, the records retrieved after meeting the criteria are then processed to determine which record has the MIN(trans_date) [ This may well happen simultaneously with the retrieval, but still takes additional time] Received on Wed Jul 16 2003 - 09:07:07 CDT