Re: Cost Based Optimizer

In <1993Sep28.130906.26091@mnemosyne.cs.du.edu> aburt@mnemosyne.cs.du.edu (Andrew Burt) writes: >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. IMHO this is going about it the wrong way. What I want to do is tell it how to access the table, I don't want to have to know or care about details like selectivity factors (and I suspect I am not alone!!!). >As for how would I know the hint: (a) sometimes you just do, based on how But is "sometimes" good enough? >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. I hadn't thought of that admittedly. Nor would Informix were they to implement this suggestion, I'd wager. >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.) But you're still not seeing the forest for the trees (IMHO). >>>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! >> ; Whoops! Those hints should have been: --+ INDEX (<tab> <idx>) - ie index_name not column_name. >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! I don't follow you. Yes, this should force the optimiser to use the index (assuming it exists, of course, but non-existence should not generate an error, just leave the optimiser to it's own devices again). And yes it would scan the index, I don't see what's wrong with that. In my book, even if it's unique it's still a scan, just a very short one! >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 Yes. Oracle allows you to explicitly choose: ALL_ROWS cost-based optimised for best throughput (ie like OnLine) FIRST_ROWS cost-based optimised for best response time RULE rule-based a la Oracle V6 (yeeuch) FULL full table scan ROWID scan by rowid CLUSTER cluster scan HASH hash scan INDEX index scan (supports more than one index per table) INDEX_ASC ascending index scan (currently the default) INDEX_DESC descending index scan AND_EQUAL merged index scans (supports 2 to 5 indexes) ORDERED the join order (specified with the FROM clause) USE_NL nested loop join USE_MERGE sort-merge join Although the INDEX hints allow you to specify multiple indexes, the Oracle optimiser will choose to use the combination of one or more indexes for which it calculates the lowest cost, not necessarily every index in your list. RULE, CLUSTER and HASH would not be appropriate for Informix' current technology, but it would be nice to see equivalents of the others implemented (6.0 pretty_please?). >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/2>thus forcing the first 'matches' to be performed before the second, etc. Not sure what you're getting at here. With Informix' current restriction of one index per table, a full table scan will give best throughput on this query. If you're assuming that restriction has been lifted then: --+ FIRST_ROWS INDEX(foo idx1 idx2) should do the trick and IMHO is a lot more readable/maintainable. >[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")..."] one step at a time would do for me! >>>(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. But I still feel that the better approach is to tell the optimiser *what* to do, not to tell it *why* it should do what you want it to do. >>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." graeme -- Disclaimer: The author's opinions are his own, and not necessarily those of Pyramid Technology Ltd. or Pyramid Technology Inc. --------------------------------------------------------------------------- -m------- Grae