Informix use the wrong index

Hi, I have a view (MyWiew) for 2 large tables (tab1 and tab2), in this view I merge these 2 tables(tab1 and tab2) by "union all", each table has 2 composed indexes, first composed index on columns (Account,Month and year) and second index on just columns (month + year), I'm launching update on a third table thru this request: Update Tab3 set col1=(select col1 from MyView where MyView.account=tab3.account and month=9 and year=2015) this request work so slow, when I've checked the execution Plan, I found that Informix use the second index on (Month+year) instead of using the first index containing the account (Account+month+year), so when I modifiy the view by removing the union all for tab2 and keeping only Tab1 in this wiew, Informix use the correct index (account+month+year), so the question is why this happen, why the optimizer take the wrong index without the account, when I add tab2 to the Wiew?? thanks for answering

Are the data distributions up-to-date and HIGH on account and month? If not ... Does the UNION have an ORDER BY on month and year? The optimizer may be selecting the index that reduces the sort rather than one that improves the selection. Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.com Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on 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 Tue, Oct 27, 2015 at 7:59 AM, CHALLENGER212 ABDERRAFI < abderrafi212@gmail.com> wrote: > Hi, I have a view (MyWiew) for 2 large tables (tab1 and tab2), in this > view I > merge these 2 tables(tab1 and tab2) by "union all", each table has 2 > composed > indexes, first composed index on columns (Account,Month and year) and > second > index on just columns (month + year), > I'm launching update on a third table thru this request: > > Update Tab3 set col1=(select col1 from MyView where > MyView.account=tab3.account and month=9 and year=2015)> > this request work so slow, when I've checked the execution Plan, I found > that > Informix use the second index on (Month+year) instead of using the first > index > containing the account (Account+month+year), so when I modifiy the view by > removing the union all for tab2 and keeping only Tab1 in this wiew, > Informix > use the correct index (account+month+year), so the question is why this > happen, why the optimizer take the wrong index without the account, when I > add > tab2 to the Wiew?? > > thanks for answering > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a114220ac2c872d052315d666

hi, i did not understand the first question "ditribution up to date....", for the second, the union does not have any order by its a simple select like that: create view Mview as Select * from Tab1 unionj all select * from Tab2 When I select on the view with a where clause on account+month+year its wrk fine and the index is used, but in this special update it does not use the correct index.

I was asking about you data distributions aka "UPDATE STATISTICS ... HIGH". Are they up to date? Recent? Also are the levels for the account and year columns HIGH or MEDIUM? Art On Oct 27, 2015 07:52, "CHALLENGER212 ABDERRAFI" <abderrafi212@gmail.com> wrote: > hi, i did not understand the first question "ditribution up to date....", > for > the second, the union does not have any order by its a simple select like > that: > > create view Mview as Select * from Tab1 unionj all select * from Tab2> > When I select on the view with a where clause on account+month+year its wrk > fine and the index is used, but in this special update it does not use the > correct index. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a113ff8268f77d605231776d4

Yes the distribution is up to date bu Low not high for the 2 tables

That's your problem then. The optimizer needs HIGH stats to distinguish the value of one index versus another. Art On Oct 27, 2015 8:19 AM, "CHALLENGER212 ABDERRAFI" <abderrafi212@gmail.com> wrote: > Yes the distribution is up to date bu Low not high for the 2 tables > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --089e0116030014c9f1052317c8b6

ok, I will launch update statistics high for the 2 tables

Get my dostats utility in my utils2_ak package to manage your statistcs. Art On Oct 27, 2015 9:37 AM, "CHALLENGER212 ABDERRAFI" <abderrafi212@gmail.com> wrote: > ok, I will launch update statistics high for the 2 tables > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a11349cee0d4934052318e7f5

We would need the schema for the three tables and eventuall a "dbschema -d database -hd table" for each of them also. Only after UPDATE STATISTICS done properly. You can use the package from Art, a shell script, pure SQL following the performance guide guidelines etc. Also provide the number of records in each table (although we can get that from the histograms...) Also provide the query plan... The optimizer is a wonderful piece of software with a few bugs in each version. Most of the times, proper statistics will do the trick. Sometimes we may need some slight adjustments on the statistics due to some data skew... and yes, there there are bugs, but that's clearly a very small minority of the cases. Naturally to analyze all of the above is a job for support.... but if we find some spare time... Regards On Tue, Oct 27, 2015 at 11:59 AM, CHALLENGER212 ABDERRAFI < abderrafi212@gmail.com> wrote: > Hi, I have a view (MyWiew) for 2 large tables (tab1 and tab2), in this > view I > merge these 2 tables(tab1 and tab2) by "union all", each table has 2 > composed > indexes, first composed index on columns (Account,Month and year) and > second > index on just columns (month + year), > I'm launching update on a third table thru this request: > > Update Tab3 set col1=(select col1 from MyView where > MyView.account=tab3.account and month=9 and year=2015)> > this request work so slow, when I've checked the execution Plan, I found > that > Informix use the second index on (Month+year) instead of using the first > index > containing the account (Account+month+year), so when I modifiy the view by > removing the union all for tab2 and keeping only Tab1 in this wiew, > Informix > use the correct index (account+month+year), so the question is why this > happen, why the optimizer take the wrong index without the account, when I > add > tab2 to the Wiew?? > > thanks for answering > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... --089e015387eeebd3d405231b1b1b

Hi, I don't know if the solution proposed elsewhere has worked for you but my approach would be slightly different, even if it leads to the same action. I would run the query with "set explain statistics" and then again with optimiser directives for the indices you wish to use. Then use it to find answers to these questions: Are the costs higher for the "correct" index? Are there any large discrepancies between the number of rows returned and the estimated rows that might explain the selection of the wrong index? Of course it may be that your distributions are inadequate or out of date but from what you're posting it looks like it's a more complicated scenario involving a view. I can't see how updating the distribution will make the optimiser use the "correct" index on tab1 with the view when it was doing so when tab1 was referenced alone. You didn't say what index was being used on tab2. If you had a test system and could reproduce the problem there you could drop the "incorrect" index the optimiser is using and see if it uses the "correct" one. Ben.