On Wed, Jun 1, 2011 at 6:28 PM, CS DBA <cs_dba@consistentstate.com> wrote:
> On 06/01/2011 03:38 PM, Kevin Grittner wrote:
>>
>> CS DBA<cs_dba@consistentstate.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
