Re: Cost Based Optimizer

In <1993Sep28.104721.9014@pyra.co.uk> graeme@pyra.co.uk (Graeme Sargent) writes: >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? Er, right, and a constant selectivity factor of 0.2 for "matches" changes? I'd like to at least give it a hint that this match is pretty selective, 1 in 10^5 rather than 1 in 5. As for how would I know the hint: (a) sometimes you just do, based on how users enter data; e.g., if you "highly suspect" users enter "lastname *" then it'd be a _much_ better guess to assume 1/10^5 than 1/5 (at least knowing my data). Sure, if they do "S*", but, ah, I doubt they'll _really_ want to look at all two million names starting with S. And (b) if it's a variable, I could twiddle it dynamically, after looking at counts, etc. Chances are the _only_ time it'd be needed by anyone is when they've proven that the optimizer doesn't optimize their particular case, in which case even an order of magnitude or two guess is fine. (1/10^3 would still be far better than 0.2 and would no doubt cause selecting the index.) >>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! > ; I'm not familiar with oracle. If by this you're implying it _forces_ the optimizer to use the index to do individual lookups, rather than a sequential search on the index, that's fine. But if the optimizer says "of _course_ I'll use the index -- to do my sequential scan, I'll just scan the index" -- bzzzzt! Again, most of the time one wouldn't want the "selectivity n" clause in there, but it would be mighty handy at times. Does the oracle method allow for the converse, forcing a sequential scan? How about forcing which clause in a query is performed first, as this "no doubt" would do: select * from foo where field1 matches "blufl *" selectivity 1/100000 and field2 matches "[a-m]*" selectivity 1/2thus forcing the first 'matches' to be performed before the second, etc. [And, oh yes, full regexps would be nice too, egrep --better yet, perl-- style.] [Or, the ability to call user-defined functions in a query; then one could put the RE code in as "...and mymatch(field2, "[a-m]*|\\d")..."] >>(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! Ok, but you could apply the same comment-as-code approach to my idea. >Hopefully we'll be getting optimiser hints in 6.0. Any comments, >Informix? Let's hope. -- Andrew Burt aburt@du.edu "But if he was dying he wouldn't bother to carve "Aaaaargh", he'd just say it."