RE: Question about cost estimated by Informix engine

Hi , Don't have all the answers but I think the reason the 'a.last index' is not used is because it is not occuring anywhere in the join conditions i.e. the where clause. The join conditions decide which indices to use. > Hi, All! > I didn't receive any comments or suggestions about subj NVARCHAR > posted > by me sometime ago. Sorry... But OK, life continues. Next question. > I have ODS for NT and simple query: > SELECT > a.id, a.code, a.birth, a.first, a.last, a.zip, > b.name, > c.name, > d.name, > e.name > FROM person a, state b, family c, status d, sex e > WHERE > a.state = b.id AND > a.family = c.id AND > a.status = d.id AND > a.sex = e.id AND > a.id > 0 > ORDER BY a.last; > Table PERSON has 120000 rows and index on column LAST, table STATE has > 250 rows, FAMILY - 10, STATUS - 10, SEX - 2 (of course). It is > unpossible to get a result, after half an hour I geted error: "no free > disk space for sort". What I saw in explain file: > Estimated Cost: 123986 - Wow! > Estimated # of Rows Returned: 80513 - ? > Temporary Files Required For: Order By - What? WHY?! > 1) e: SEQUENTIAL SCAN > 2) c: SEQUENTIAL SCAN > NESTED LOOP JOIN > 3) a: INDEX PATH > Filters: ( a.sex = e.id AND a.id > 0 ) > (1) Index Keys: family > Lower Index Filter: a.family = c.id > NESTED LOOP JOIN > 4) d: INDEX PATH > (1) Index Keys: id > Lower Index Filter: d.id = a.status > NESTED LOOP JOIN > 5) b: INDEX PATH > (1) Index Keys: id > Lower Index Filter: b.id = a.state > NESTED LOOP JOIN > Is there anybody who can explain me why server chose this execution > plan? It must read table PERSON according to index LAST and then use > nested loop join for other small tables, I think. Explain file must be > approximately like this: > Estimated Cost: Sorry, I don't know formula > Estimated # of Rows Returned: Really there is only one row with id = > -1 > in the table PERSON > 1) a: INDEX PATH > Filters: a.id > 0 > (1) Index Keys: last > 2) b: INDEX PATH > (1) Index Keys: id > Lower Index Filter: b.id = a.state > NESTED LOOP JOIN > 3) c: INDEX PATH > (1) Index Keys: id > Lower Index Filter: c.id = a.family > NESTED LOOP JOIN > 4) d: INDEX PATH > (1) Index Keys: id > Lower Index Filter: d.id = a.status > NESTED LOOP JOIN > 5) e: INDEX PATH > (1) Index Keys: id > Lower Index Filter: e.id = a.sex > NESTED LOOP JOIN > With this plan time will be used only for reading and transfering > data, > first rows user will see momentary. Anybody knows where I can see > exact > algorithm how Informix calculates estimated cost? May be there is > serious error in their approach. I can search (may be find) it? > Best wishes. Leonid. ( Leonids.Voroncovs@dati.lv ) >