RE: How to switch the RULE hint in outlines hints in 12.2 on standard edition.

haha. that list doesn’t even have merged indexes. Careful with the words “only ever” unless you have all the manuals.  

As I noted previously, I’m not entering into this argument about whether a listed heuristic was documented or ever could possibly be used or which versions in which it was used.  

mwf  

From: Neil Chandler [mailto:neil_chandler_at_hotmail.com] Sent: Wednesday, June 26, 2019 5:36 PM
To: mwf_at_rsiz.com
Cc: gogala.mladen_at_gmail.com; oracle-l_at_freelists.org Subject: Re: How to switch the RULE hint in outlines hints in 12.2 on standard edition.  

17 or 26 heuristic rules?  

The manuals only ever listed 15 RBO Heuristics. Anyone care to expand the list beyond that?  

Access paths and their ranking

Path 1

Single Row by Rowid

Path 2

Single Row by Cluster Join

Path 3

Single Row by Hash Cluster key with Unique or Primary Key

Path 4

Single Row by Unique or Primary Key

Path 5

Clustered Join

Path 6

Hash Cluster Key

Path 7

Indexed Cluster Key

Path 8

Composite Index

Path 9

Single-Column Index

Path 10

Bounded Range Search on Indexed Columns

Path 11

Unbounded Range Search on Indexed Columns

Path 12

Sort-Merge Join

Path 13

MAX or MIN of Indexed Column

Path 14

ORDER BY on Indexed Column

Path 15

Full Table Scan  

Neil.

sent from my phone

On 26 Jun 2019, at 17:28, Mark W. Farnham <mwf_at_rsiz.com> wrote:

NODS. AND it should be noted that the fact that some of Oracle’s own recursive queries still reporting as using rule does NOT matter regarding the desupport note. Oracle is not the customer and they can do whatever they need to in order to make the RDBMS engine run fast.  

The “supported” way to simulate RULE is to write the plan with hints (very often a set of no_merge with clauses is sufficient to model RULE’s choices, which are static and not dependent on cardinality). And once you’ve done that, an outline is not actually needed.  

If you cannot simulate the RULE plan with just no_merge hints (and possibly some use_index hints), then I recommend you consult JL and Chris Antognini sites and books and sqlmaria for how to beat your query into submission.  

IF you think RULE can be good for your query, that is a declaration that stats don’t matter for that query. You also have no “strait jacket” going this route and if there seems to be an effective hash plan here or there, you and hint that (whereas if RULE still functioned you would be stuck with those (17 or 26, argue amongst yourselves) heuristic rules and capabilities.  

mwf  

--
http://www.freelists.org/webpage/oracle-l