Re: Optimizer hint <*+ RULE *> question

<fitzjarrell_at_cox.net> wrote in message
news:1131044963.277660.308560_at_g43g2000cwa.googlegroups.com...
>
>
> This is not the only reason the CBO could be enabled. The following
> features require use of the CBO:
>
> Partitioned tables and indexes
> Index-organized tables
> Reverse key indexes
> Function-based indexes
> SAMPLE clauses in a SELECT statement
> Parallel query and parallel DML
> Star transformations and star joins
> Extensible optimizer
> Query rewrite with materialized views
> Enterprise Manager progress meter
> Hash joins
> Bitmap indexes and bitmap join indexes
> Index skip scans
>
> And, if any of these are used the CBO is enabled, even if
> optimizer_mode is set to RULE or a /*+ RULE */ hint is used.
>
>
> David Fitzjarrell
>

You need to split that list into two -

    things which will only be used if the CBO is enabled, and

    things that will force the CBO to kick in regardless.

I can never remember which applies to what, but (for example) if you have bitmap indexes in place, the RBO will still run, but will not notice the bitmaps. If you have partitioned tables in your query, the CBO will kick in even if you hint /*+ RULE */.

One final example - the rule-based optimizer can use a 'function-based' index if the leading column(s) of the index are not virtual columns, e.g an index like (col1, col2, upper(col3)) can be used by the RBO if there are predicates on the first two columns.

-- 
Regards

Jonathan Lewis

http://www.jlcomp.demon.co.uk/faq/ind_faq.html
The Co-operative Oracle Users' FAQ

http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html
Cost Based Oracle: Fundamentals

http://www.jlcomp.demon.co.uk/appearances.html
Public Appearances - schedule updated 4th Nov 2005