Re: Re[2]: Optimizer of Online 7.22 selects wrong index
On Wed, 30 Jul 1997, Dianne Pendleton wrote:
> Can you clarify something for me? In your answer at 1)a) you talk
> about using HIGH but the answer confuses me. Here is what we were
> told to do: MEDIUM on the database DISTRIBUTIONS ONLY
Do this one table at a time and it runs MUCH faster! I'd figured this
out myself long ago and Informix modified this recommendation in the
ODS Performance Guide (Chapter 4 "Queries and the Query Optimizer"
where is mentions that running the whole database takes "slightly"
more resources.
> HIGH on single column index or leading column of multi-column
> indexes LOW on the trailing columns of multi-column indexes This is
> in the Informix Guide to SQL Syntax 7.2, Volume 2, Pg.1-631. We are
> running 7.11 in production but will be upgrading to 7.23 soon.
BTW: You can follow these recommendations for 7.11 also.
> So are you saying that where I have LOW, to use HIGH instead? Or
> is there something else?
No. As far as the recommendation regarding LOW I simply forgot to
include it. I was getting long winded even for me :-} and I thought I
had already mentioned that. My apologies if I confused anyone.
The new recommendation relates only to the situation where one has
several compound indexes with one or more of the leading columns the
same. Here one should use high on the first column(s) that are
different to help the optimizer choose one over the other for specific
queries. Ex:
create index f1 on fred( id, lname, fname, city );
create index f2 on fred( id, lname, fname, state );
create index f3 on fred( id, lname, fname, zip );
create index f4 on fred( id, zip, lname, fname );
The difficulty in deciding whether to use index f1->f4 requires better
statistics (HIGH) for city, state, and zip to help choose between f1,
f2, & f3 and for lname and zip to help choose between f3 and f4 (for
certain queries the better stats may actually help the optimizer to
choose f4 where it might choose f1 and f2 otherwise). Obviously one
need only perform UPDATE STATISTICS HIGH for the zip column once.
I had attributed this recommendation to the release notes for 7.2. I
have searched the documents that I have and discovered that my
recollections were incorrect. I believe that the recommendation came
from an ATG engineer, probably Larry Grant, so you can all stop
frantically grepping the release notes. I found the recommendation so
purely sensible that I tried it on some rather complex databases and
found that indeed the optimizer's index choices did sometimes change,
often dependent on the individual key values supplied, from the
original statistics scheme to the new one and further that the new
scheme always produced better results (well except for one that table
where I had to drop distributions).
>
> ***********************************************************************
>
> Meantime what to do? Two possibilities:
>
> 1) Your situation is EXACTLY like ours, you only need those first 30
> rows but many more match the query.
> a) Try the recommended STATISTICS scheme in the 7.21 release notes:
> MEDIUM on the table, HIGH on any columns which are the first
> column of an index, HIGH on the first indexed columns that are
> different if several indexes begin with the same columns. (This
> last was not in the 7.13 release notes!) This worked for other
> tables where we had the same problem, though not for the one
> described.
>
Art S. Kagel, kagel@bloomberg.com