Re: Update statistics high vs medium

In article <6kibp5$t0p$1@news.xmission.com>, Chung-Wong, Susanna@EDD <SChung@edd.ca.gov> writes > >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.) > Yes please... >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 -- David Williams Maintainer of the Informix FAQ Primary site (Beta Version) http://www.smooth1.demon.co.uk Official site http://www.iiug.org/techinfo/faq/faq_top.html I see you standin', Standin' on your own, It's such a lonely place for you, For you to be If you need a shoulder, Or if you need a friend, I'll be here standing, Until the bitter end... So don't chastise me Or think I, I mean you harm... All I ever wanted Was for you To know that I care