First row optimization returned single row

We have a partitioned table with one index only. Both table and index are partitioned on date column (column_name='dated'). For every month there is one partition. We set up more than one instances. Given (Below) SQL query is giving correct result except one one instance where it returns only 1 record unless we use "set optimization all_rows" Configurations on all instances are alike. SQL Query: select dated, amount from trans where client ='a0045' order by 1 desc; How can we resolve this issue. Here are some specs. -- Values in configuration file OPTCOMPIND 2 OPT_GOAL 0 IDS version is 11.50.FC7 One more thing; For other partitioned tables we are not facing this issue even on same database (instance). regards, Kamran

Optimization first_rows will use the index on 'dated' to locate the rows that match the filter and to satisfy the order by clause while optimization all_rows may perform a table scan and sort the rows to satisfy the order by clause unless the data distributions indicate that this will be slower. So, I think you probably have two problems. First, this table's data distributions are either stale or non-existent. Run: UPDATE STATISCICS MEDIUM FOR TABLE trans; UPDATE STATISTICS HIGH FOR TABLE trans( dated ); Second, it is likely that for some reason the index on the column 'dated' has become corrupted. You can check that with: oncheck -cDI <databasename>:trans If oncheck reports that it is corrupted, don't let oncheck rebuild the index, it is too slow if the table is large. You will need to manually drop and recreate the index yourself. If you rebuild the index, do that BEFORE running the update statistics commands above. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) IIUG Board of Directors (art@iiug.org) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Wed, Feb 23, 2011 at 7:10 PM, KAMRAN HAQ <khaq@i2cinc.com> wrote: > We have a partitioned table with one index only. Both table and index are > partitioned on date column (column_name='dated'). > For every month there is one partition. We set up more than one instances. > Given (Below) SQL query is giving correct result except > one one instance where it returns only 1 record unless we use "set > optimization all_rows" > Configurations on all instances are alike. > > SQL Query: > select dated, amount from trans where client ='a0045' > order by 1 desc;> > How can we resolve this issue. Here are some specs. > > -- Values in configuration file > OPTCOMPIND 2 > OPT_GOAL 0> > IDS version is 11.50.FC7 > > One more thing; For other partitioned tables we are not facing this issue > even > on same database (instance). > regards, > Kamran > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001517503b0854948c049cfca677

Hello Art, We are still getting this issue after following all your instruction . No index is corrupted in that table , is there any other reason which is causing this problem, as we are getting same issues on different tables using order by clause on different type of column (column type = char, date)

Now I would open a case with IBM. Art On Mar 1, 2011 1:42 PM, "ABRAR RASHID" <mabrar@i2cinc.com> wrote: > Hello Art, > > We are still getting this issue after following all your instruction . > > No index is corrupted in that table , is there any other reason which is > causing this problem, as we are getting same issues on different tables using > order by clause on different type of column (column type = char, date) > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > --0015175cde54150444049d710c33