force engine to use indexes

Hi experts, I have a strange problem. I have created a duplicated index with two fields on a table lets say 'table1'. When I do a select of these two fields sqexplain.out file says that he uses that index. But when I do a select of all fields sqexplain.out file says that he uses a SEQUENTIAL SCAN. Why for god sakes?! I've done: 1) create index i1 on table table1(field1,field4) 2) select field1,field4 from table1 order by field1,field4 (this works fine) 3) select * from table1 order by field1,field4 (this takes a lifetime to search because I have 151000 records) Is there a way to force him to use that index? I use an OWS 7.31.UC6 database. Many thanks, Danny

1) Complete schema of the table could be useful. 2) Relevant outputs of sqexplain.out could be useful. 3) You have UPDATEd STATISTICS, haven't you? 4) Remember, when you only select field1,field4 you have performed a "index only query" (all data requested is in an index). The optimiser should realise this and will read only the index - not the data pages at all, whereas select * will always read the data pages. Index only scan could be a substantial I/O saving, especially if each row is wide and the index is small (could already be in buffer). Don't be fooled by blindingly fast data return when the system may be just reading the index and no data pages. 5) Sorting by traversing an index can be very expensive I/O if your table is not also clustered on that index. Since you have asked the DBMS to return you data in sorted order, it has two choices - read it in any order then sort it in memory/disk, or read it in the order of the index. Since the data order on the disk is arbitrary, the I/O to read it in index order will produce a lot of random, non-sequential disk accesses. So the optimiser chooses to read-then-sort. 6) All that said, you can force the optimiser to use the index by :- select {+INDEX(table1 i1)} * from table1 order by field1, field4; ... but as per 5) above, prepare for some I/O behavior that may be expensive (especially for other database threads). Unless you cluster of course. BTW - I always refer to IDS as she ... :-) HTH Brett Randall Danny De Koster wrote: > > Hi experts, > > I have a strange problem. > I have created a duplicated index with two fields on a table lets say > 'table1'. > When I do a select of these two fields sqexplain.out file says that he uses > that index. > But when I do a select of all fields sqexplain.out file says that he uses a > SEQUENTIAL SCAN. > Why for god sakes?! > I've done: > > 1) create index i1 on table table1(field1,field4) > 2) select field1,field4 from table1 order by field1,field4 (this works > fine) > 3) select * from table1 order by field1,field4 (this > takes a lifetime to search because I have 151000 records) > > Is there a way to force him to use that index? > I use an OWS 7.31.UC6 database. > > Many thanks, > > Danny

Have you ran update statistics? "Danny De Koster" <ddk@fidelity-soft.be> wrote in message news:958ov3$27sn$1@rivage.news.be.easynet.net... > Hi experts, > > I have a strange problem. > I have created a duplicated index with two fields on a table lets say > 'table1'. > When I do a select of these two fields sqexplain.out file says that he uses > that index. > But when I do a select of all fields sqexplain.out file says that he uses a > SEQUENTIAL SCAN. > Why for god sakes?! > I've done: > > 1) create index i1 on table table1(field1,field4) > 2) select field1,field4 from table1 order by field1,field4 (this works > fine) > 3) select * from table1 order by field1,field4 (this > takes a lifetime to search because I have 151000 records) > > Is there a way to force him to use that index? > I use an OWS 7.31.UC6 database. > > Many thanks, > > Danny > > > >

Because your update statistics indicate that the data is highly unordered and will result in multiple hits for the same data page. Because of that, it is mathematically faster to do a sequential scan of the data pages and sort them than it is to hit the same data pages multiple times by using the index to bypass the sort. You might try to cluster the index. Danny De Koster wrote: > Hi experts, > > I have a strange problem. > I have created a duplicated index with two fields on a table lets say > 'table1'. > When I do a select of these two fields sqexplain.out file says that he uses > that index. > But when I do a select of all fields sqexplain.out file says that he uses a > SEQUENTIAL SCAN. > Why for god sakes?! > I've done: > > 1) create index i1 on table table1(field1,field4) > 2) select field1,field4 from table1 order by field1,field4 (this works > fine) > 3) select * from table1 order by field1,field4 (this > takes a lifetime to search because I have 151000 records) > > Is there a way to force him to use that index? > I use an OWS 7.31.UC6 database. > > Many thanks, > > Danny

Brett Randall <brett_s_rREMOVECAPITALS@hotmail.com> wrote in message news:3A7804AD.387E32AB@hotmail.com... > 6) All that said, you can force the optimiser to use the index by :- > > select {+INDEX(table1 i1)} * > from table1 > order by field1, field4 Can you? I've been told by Informix UK Tech Support, in relation to a separate problem, that "optimiser directives" is a misnomer, and "hints" would be more accurate, in that the optimiser might still choose to ignore them and do its own thing.

Neil Truby wrote: > > Brett Randall <brett_s_rREMOVECAPITALS@hotmail.com> wrote in message > news:3A7804AD.387E32AB@hotmail.com... > > > 6) All that said, you can force the optimiser to use the index by :- > > > > select {+INDEX(table1 i1)} * > > from table1 > > order by field1, field4 > > Can you? I've been told by Informix UK Tech Support, in relation to a > separate problem, that "optimiser directives" is a misnomer, and "hints" > would be more accurate, in that the optimiser might still choose to ignore > them and do its own thing. Yes, thanks for clarifying Neil. Force is a strong word. My experience is that the optimiser follows "directives" often. Brett Randall

Trust your optimiser Luke... Until you study the entertaining subject of how it works, don't try to assume you know better ways of performing queryies when compared to guys who have been doing it for many years - ie the guys maintaining the engines. Having said that, to save other people the effort of chipping in: As long as you don't have a defective version of the engine!;-) These appear from time to time - my guess is, whenever somebody at Informix gets a bright idea that withers in the full sunlight. There's a 7.31.UCx version floating around right now which is said to be questionable. x == 5 I think? Some of the 5.0x were very entertaining too - where x is an even number! These bad query plans will most likely show up in complex queries, but not in trivial single-table selects which are too easy to get right. Before you can judge the quality of the query path, spend a few weeks to several months (depending on how much spare time you have) studying the art from the manuals. Other benefits will be the ability to tailor your SQL towards efficient methods, and to avoid database designs which are questionable in Informix or relational databases generally.