Update statistics high vs medium
Hi, we are running Informix ODS v7.23 on AIX 4.2. There is a table with
27000 records look like this:
TABLE dot
( dot_serial_num char(11) not null PK,
dot_title char(82),
create_dt date indexed)
We have several queries that join multiple tables including this DOT
table and they perform wild card search on dot_title like this:
dot_title like '%CLERK%'. I found out if the update statistics is not
run correctly for this column, the optimizer chooses to sequential scan
the other tables in these queries. And I found out only update
statistics medium works for this column:
Update statistics medium for table dot(dot_title) distributions only
Originally, I run these two statements everyday:
Update statistics low;
Update statistics high distributions only;
Now I added the third statement in order for these queries to run with
indexes:
Update statistics low;
Update statistics high distributions only;
Update statistics medium for table dot(dot_title) distributions only;
My question is:
1. Why?
(I have output from sqexplain.out if anyone is interested.)
I also have some general questions:
2. I know that Informix and the folks in this group recommends running
update stat using low, med, high accordingly. If there is no time
constraint on our system, would the way I run the update stat (as above)
serve the same purpose?
3. If I have a composite index like this:
index1 on table1(col1,col2,col3,col4)
Is there a difference between this:
Update statistics high on table1(col1) distributions only;
Update statistics low on table1(col2,col3,col4); And this:
Update statistics high on table1(col1) distributions only;
Update statistics low on table1(col2);
Update statistics low on table1(col3);
Update statistics low on table1(col4);
Thank you very much in advance.
Susanna Chung-Wong
Employment Development Department
phone: (916) 654-7378
email: schung@edd.ca.gov