Re: index problem

Hi fred, fm wrote: > > Hi, > > I have 2 indexes: ix1 and ix2 ! > I would like that the engine take first ix1 and then ix2 .... > (sqexplain.out) > > I have ; > > select xxx from tab1, tab2 wehre> tab1.ix1= .... and tab2.ix2=.... > > in sqexplain.out : > > 1) INDEX PATH > ix2 > 2) INDEX PATH > ix1 > > but i need > > 1) INDEX PATH > ix1 > 2)INDEX PATH > ix2 > whats wrong with the way optimizer choose? Wile

Hi Wile The firsrt index ist very slow! I need the second index (faster). The first index is a date type and the between clause is very long (with for example 2 monate) then i need first ix1 and than ix2.Do you understand ? thank you fred. "Wile E. Coyote" <wilec@nettaxi.com> schrieb im Newsbeitrag news:393FDD38.1729934@nettaxi.com... > Hi fred, > > fm wrote: > > > > Hi, > > > > I have 2 indexes: ix1 and ix2 ! > > I would like that the engine take first ix1 and then ix2 .... > > (sqexplain.out) > > > > I have ; > > > > select xxx from tab1, tab2 wehre> > tab1.ix1= .... and tab2.ix2=.... > > > > in sqexplain.out : > > > > 1) INDEX PATH > > ix2 > > 2) INDEX PATH > > ix1 > > > > but i need > > > > 1) INDEX PATH > > ix1 > > 2)INDEX PATH > > ix2 > > > > whats wrong with the way optimizer choose? > Wile >

Fred, fm wrote: > > Hi Wile > > The firsrt index ist very slow! I need the second index (faster). The first > index is a date type and the between clause is very long (with for example 2 > monate) then i need first ix1 and than ix2.Do you understand ? I am not sure, if I understood you right. You have to indexes, Index A (type date) Index B (type ? maybe integer) Your number of returned rows using index A will be higher as the number of rows in index B? Did you run "update statistics"? (standard question) If yes, did you try an update statistics medium or high for the columns in index A/B? Maybe, you have to do this to point your optimizer to the right way. Wile

Alter the order of tables in the SELECT statement. The order of tables in a query is not supposed to be significant, sometimes the order may matter if tables are similar in size and the optimizer costs are similar between two-way join. Run UPDATE STATISTICS with DATA DISTRIBUTION clausule. The information about data distributions of tables is very important for optimizer. Add extra, nonmeaningfull filters. Sometimes you can influence the optimizer by adding a filter in the WHERE clause. For example, a query can be rewriten from SELECT * from a,b,c where a.x = b.x and b.y = c.y and a.y = 20 and c.z = 30 to something like this: SELECT * from a,b,c where a.x = b.x and b.y = c.y and a.y = 20 and c.z = 30 and c.y > 0 Extra filters may give the optimizer incentive to choose the table earlier in the query path. From "Informix Performance Tunning" Elizabeth Suto In article <YH%%4.3$qV6.397@nreader1.kpnqwest.net>, "fm" <fm@ils-consult.at> wrote: > Hi Wile > > The firsrt index ist very slow! I need the second index (faster). The first > index is a date type and the between clause is very long (with for example 2 > monate) then i need first ix1 and than ix2.Do you understand ? > > thank you > > fred. > > "Wile E. Coyote" <wilec@nettaxi.com> schrieb im Newsbeitrag > news:393FDD38.1729934@nettaxi.com... > > Hi fred, > > > > fm wrote: > > > > > > Hi, > > > > > > I have 2 indexes: ix1 and ix2 ! > > > I would like that the engine take first ix1 and then ix2 .... > > > (sqexplain.out) > > > > > > I have ; > > > > > > select xxx from tab1, tab2 wehre> > > tab1.ix1= .... and tab2.ix2=.... > > > > > > in sqexplain.out : > > > > > > 1) INDEX PATH > > > ix2 > > > 2) INDEX PATH > > > ix1 > > > > > > but i need > > > > > > 1) INDEX PATH > > > ix1 > > > 2)INDEX PATH > > > ix2 > > > > > > > whats wrong with the way optimizer choose? > > Wile > > > > Sent via Deja.com http://www.deja.com/ Before you buy.