I don't get this
ok, the more test I do, the more confused I am about this.
simple table with one composite key:
create index index0 on m4566prm_rec (pol_state,company,acct_date);
an update satistics is done like this:
update statistics high for table m4566prm_rec
(pol_state,company,acct_date);
query and explain as following:
QUERY:
------
select *
from M4566PRM_REC p
where (p.pol_state = 'NY')
Estimated Cost: 3340
Estimated # of Rows Returned: 57014
1) root.p: SEQUENTIAL SCAN
Filters: root.p.pol_state = 'NY'
Why was this a sequential scan? I read books and manuals both claims
that you could use the first parameter of a composte index as query and
still will use the index?
i am using INFORMIX-OnLine Version 7.23.UC2 on solaris 2.6 ultra 450.
thanks a lot.
yan
Yan Zhu wrote:
> ok, the more test I do, the more confused I am about this.
> simple table with one composite key:
> create index index0 on m4566prm_rec (pol_state,company,acct_date);
> an update satistics is done like this:
> update statistics high for table m4566prm_rec
> (pol_state,company,acct_date);
> query and explain as following:
> QUERY:
> ------
> select *
> from M4566PRM_REC p
> where (p.pol_state = 'NY')
> Estimated Cost: 3340
> Estimated # of Rows Returned: 57014
> 1) root.p: SEQUENTIAL SCAN
> Filters: root.p.pol_state = 'NY'
> Why was this a sequential scan? I read books and manuals both claims
> that you could use the first parameter of a composte index as query and
> still will use the index?
> i am using INFORMIX-OnLine Version 7.23.UC2 on solaris 2.6 ultra 450.
> thanks a lot.
Same answer as your last two questions, Yan. The data distribution
tells the engine that a sequential scan is the FASTEST way to get you
the data you want. Post the output of:
dbschema -d yourdatabase -hd m4566prm_rec
This will print out the data distributions for the table and you/we
will be able to see why the optimizer feels that a sequential scan is
a good thing in this case. If you do not see the reason yourself then
feel free to post the output for the column pl_state (there is usually
too much output to post it all) and we'll take a look.
Art S. Kagel