Re: Problem query

On Wed, Jun 1, 2011 at 6:28 PM, CS DBA <cs_dba(at)consistentstate(dot)com> wrote:
> On 06/01/2011 03:38 PM, Kevin Grittner wrote:
>>
>> CS DBA<cs_dba(at)consistentstate(dot)com>  wrote:
>>
>>> The app wants to run a query like this:
>>>
>>> select count(pri_num) from max_xtrv_st_t
>>> where pri_num in (select max(pri_num) from max_xtrv_st_t where 1=1
>>>                                 group by tds_cx_ind, cxs_ind_2)
>>
>> Why not something simpler?  There are a number of possibilities, and
>> I don't claim this one is necessarily best (or even error free), but
>> how about something like?:
>>
>> select count(*) from
>>   (select distinct max(pri_num)
>>   from max_xtrv_st_t
>>   group by tds_cx_ind, cxs_ind_2) x
>>
>> -Kevin
>
> I've tried a number of alternates, each one wants to do a seq scan of the
> table (including your suggestion above).

why wouldn't you expect a sequential scan? what is the number of
unique values for tds_cx_ind, cxs_ind_2 on the table?

one of the most important techniques with query optimization is to put
yourself in the place of the database and try to imagine how *you*
would pass over the records...then try and coerce the database into
that plan.

merlin