What kind of indexes do you have on t_acc_usage_3?

I have a table with over 2 billion records and I can get
350,000 records back in 7 seconds with an index seek using
LIKE.


>-----Original Message-----
>Hi,
>
>We are testing why optimizer is behaving differently and
>what is point where it stop using index seek ..so we are
>testing the following query and checking explain plan,
>statistics io, dbcc show_statistics, time taken....and we
>found that within a span of 1000 rows optimizer plan
>changes.........
>Table t_acc_usage_3 has about 42 million rows
>we are doing update statistics t_acc_usage_3 with sample
>20 percent.......
>
>select TOP 1 id_ancestor, sum(num_generations) as
>total_generations, count(*) as ancestors
>from t_account_ancestor a1 inner join
>(select distinct(id_payee) from t_acc_usage_3 au where
>au.id_acc=599 and au.id_usage_interval = 22889
> ) foo on a1.id_descendent=foo.id_payee
>where a1.vt_start <= {ts '2002-05-31 23:59:59'} and
>a1.vt_end > {ts '2002-05-31 23:59:59'}
>group by id_ancestor
>order by ancestors desc, total_generations asc
>
>If about 99872 rows satisfying the criterion in
>t_acc_usage_3 optimizer is doing index seek and query
>takes about 46 seconds but if about 100123 rows
satisfying
>the criterion in t_acc_usage_3 optimizer is not doing
>index scan and time taken is about 4 minutes.........so
>for table having about 42 million rows selecting this
>different number of rows changes the plan......and more
of
>this result is based on dbcc show_statistics........
>so in many article is is mentioned that if u r selecting
>some percentage of rows that optimizer will use index
seek
>does not seek accurate..there are some other factors also
>which are not clear to me...........
>so it will be great if someone can give feedback or point
>to some articles where i can read about optimizer
>behaviour?
>
>Thanks
>--Harvinder
>
>.
>

Report this post to a moderator