Re: Cost Based Optimizer

Graeme Sargent writes: |> >this whole thing is a carryover from 'or' selectivity being mishandled |> >somehow (since it seems almost any 'or' will cause a sequential scan). |> |> No, I don't think so. Only those which would need more than one index. Graeme, you keep asserting this, but it simply isn't true. At least as of 5.0 (and I think even earlier) two indexes on a table will be used to process or's, as appropriate. Take, for example, the following schema: create table tab ( col1 integer, col2 integer ); create unique index ix1 on tab(col1); create unique index ix2 on tab(col2); --load 1000 rows into the table set explain on;select from tab where col1 = 5 or col2 = 10; -- set explain output follows QUERY: ------ select * from tab where col1 = 5 or col2 = 10 Estimated Cost: 4 Estimated # of Rows Returned: 1 1) davek.tab: INDEX PATH (1) Index Keys: col1 Lower Index Filter: davek.tab.col1 = 5 (2) Index Keys: col2 Lower Index Filter: davek.tab.col2 = 10 In this case, it makes sense to do two indexed accesses and combine the resulting tuples into a single set, mostly because both indexes are 100% selective. If, however, one of them was less selective (offhand, I can't say what the threshold would be), to the point where it would require as much or more i/o in order to use both indexes, then a sequential scan would be done. If you are wondering "why not use one index?", the answer is that once any or clause element requires a sequential scan, there is no point using an index at all, since you already have to read the table for the sequential scan (just in case that isn't obvious to anyone). 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