Re: Question about cost estimated by Informix engine

In article <35507D08.1E32@dati.lv>, Leonid Vorontsov <Leonids.Voroncovs@dati.lv> writes >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 It is documented in the manual you get if you go on the Database Admin course at Informix. I think it also used to be in the 4.x manuals.. >serious error in their approach. I can search (may be find) it? >Best wishes. Leonid. ( Leonids.Voroncovs@dati.lv ) -- David Williams Maintainer of the Informix FAQ Primary site (Beta Version) http://www.smooth1.demon.co.uk Official site http://www.iiug.org/techinfo/faq/faq_top.html I see you standin', Standin' on your own, It's such a lonely place for you, For you to be If you need a shoulder, Or if you need a friend, I'll be here standing, Until the bitter end... So don't chastise me Or think I, I mean you harm... All I ever wanted Was for you To know that I care