Re: Cost Based Optimizer

Andrew Burt writes: |> No, it may *not* be the desirable thing. Suppose: |> 1) table foo has a million rows, say 25 rows/page |> 2) index on field1 in foo, say 200 keys/page. |> 3) I write, "select * from foo where field1 in (select field1 from bar |> where field2 = 42) |> 4) The "in (select..." returns, say, 100 rows. |> 5) Now, 100 index lookups on foo ought to cost, say, 4 page reads each, |> or 400 pages read (or less, assuming most of the upper-tree |> index pages remain cached). |> 6) However, the optimizer, seeing this as an 'or' with 100 components, |> decides to do a sequential scan of the whole table. We pay |> 40,000 page reads. |> 7) If it decided to do a sequential scan on the index (rather than |> the table itself in row order), that's 5,000 page reads. |> We can't use lower/upper filters because we have 100 "randomly" |> distributed values to search for. |> |> My point is, I'm seeing the #6 behavior (and on a table with more like 10 |> million records, and where the # rows returned by the 'in' is usually <10). |> Net effect is that what "ought" to be a blindingly fast search (high end of |> 40 page reads, though with the cache, I'd guess maybe 25) ends up running for |> a couple hours reading the whole table. I ran the following query in 6.0 against two tables, tab having 1000 rows and tab2 having 10: set explain on; select * from tab where col1 in (select col1 from tab2 where col2 = 5); The sqexplain.out results are as follows: QUERY: ------ select * from tab where col1 in (select col1 from tab2 where col2 = 5) Estimated Cost: 26 Estimated # of Rows Returned: 500 1) davek.tab: INDEX PATH (1) Index Keys: col1 Lower Index Filter: davek.tab.col1 = ANY <subquery> Subquery: --------- Estimated Cost: 3 Estimated # of Rows Returned: 10 1) davek.tab2: INDEX PATH (1) Index Keys: col2 Lower Index Filter: davek.tab2.col2 = 5 Is this not the behavior you were seeking? Dave Disclaimer: These opinions are not those of Informix Software, Inc. ************************************************************************** "I look back with some satisfaction on what an idiot I was when I was 25, but when I do that, I'm assuming I'm no longer an idiot." - Andy Rooney