Re: I don't get this
On Thu, 28 Jan 1999, 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?
There are a few possibilities that spring to mind:
1. How many rows are there in the table? How many of those rows
have pol_state of 'NY'. If the ratio of the total rows to the
selected rows is small enough, a sequential scan may be more
efficient than an indexed scan.
2. You've hit on a bug.
3. Maybe the manuals don't recommend doing UPDATE STATISTICS HIGH
for this sort of table. The rules are complex and I've not
checked on what the recommendations are for 7.2x.
> 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?
But only if the index gives some benefit; since you did an update
statistics high, it probably has a very good idea of the distribution of
the rows in the table.
> i am using INFORMIX-OnLine Version 7.23.UC2 on solaris 2.6 ultra 450.
Maybe you should think about upgrading to 7.24 or even 7.30.
Yours,
Jonathan Leffler (jleffler@informix.com) #include <wish/I/was/skiing.h>
Guardian of DBD::Informix v0.60 (v0.61_02) -- http://www.perl.com/CPAN
Informix IDN for D4GL & Linux -- http://www.informix.com/idn