Re: Cost Based Optimizer

In <1993Sep27.185056.23855@mnemosyne.cs.du.edu> aburt@mnemosyne.cs.du.edu (Andrew Burt) writes: >In <1993Sep27.130957.8224@pyra.co.uk> graeme@pyra.co.uk (Graeme Sargent) writes: >>This is to be expected. OnLine uses a Selectivity Factor of >>1/sysindexes.nunique for an indexed_col = <literal> filter and a >>Selectivity Factor of 0.2 for a matches expression. >I'd be happy to be allowed to write, > select * from foo where bar matches "blurfl*" selectivity 1/100000 ... >Or even being able to tell it the estimated #rows directly (or better yet,>allow either). But this is clumsy! How are you going to know the selectivity in advance? And what is going to maintain it as it changes? >How 'bout it, Informix? I'd love to tell your optimizer what I know... >Imagine, doing "or" wouldn't necessarily do a sequential scan all the time! > select * from foo where (key = 1000 selectivity 1/100000 or > key in > (select groupkey from groups where key = 1000 > selectivity 1/10e7) > ) and date > 1/1/1990 selectivity 0.2 And getting clumsier! What's wrong with something more conventional like: SELECT --+ INDEX(foo key) Use index on key as subselect is selective * FROM foo WHERE key = 1000 OR key IN (SELECT --+ INDEX(groups key) to keep Andrew happy -- although I think this must be default. groupkey FROM groups WHERE key = 1000) AND date > 1/1/1990 -- At least that's what I think he must have meant! ; >(Which is, BTW, nearly like some code in our system that must be done >with a union else it does a sequential scan on millions of records.) >I would gladly give up sql portability for this... >-- I wouldn't! My version may not work on anything else except Oracle, but it won't blow up either! Hopefully we'll be getting optimiser hints in 6.0. Any comments, Informix? graeme -- Disclaimer: The author's opinions are his own, and not necessarily those of Pyramid Technology Ltd. or Pyramid Technology Inc. --------------------------------------------------------------------------- -m------- Graeme Sargent Voice: +44 (0)252 373035 ---mmm----- Senior Database Consultant Fax : +44 (0)252 373135 -----mmmmm--- Pyramid Technology Ltd. Telex: Tell who??? -------mmmmmmm- Farnborough, Hants GU14 7PL Email: graeme@pyra.co.uk --------------------------------------------------------------------------- We have the technology. The tricky bit is learning how to use it.