----- Original Message -----
> Have run across some interesting things while reading up on the
optimizer.
Same here! :-)
>
> Always thought that the RBO joins your tables in the order found in
the
> FROM clause? Think again.
Actually, I found one in Metalink that says with RBO, it's the reverse
order of the FROM clause. Which I was aware of. But it also
says: if there are NO STATS whatsoever and you use a hint that forces
the CBO to be used or it is the default, the order of tables is
left to right. Like what you get when using the hint ORDERED.
Also, a few other interesting "rules":
- FIRST_ROWS tends to favour NESTED LOOPS.
- ALL_ROWS tends to favour HASH and table scans.
<old hat in the above>
EXCEPT (!) if the CBO finds a table with no stats in a join.
Then it's most likely hash, for both settings above.
And optimizer_mode is CHOOSE and there is a mix of tables in
the join with/without stats, then ALL_ROWS is the result.
>
> Speaking of the ORDERED hint, it can greatly reduce parse times when
> joining many tables. Obvious when you think about it.
That, it certainly does! My experience too. It never hurts
to "help" the optimizer do its job.
> All kinds of interesting stuff when you Read The Fine Manual. ;)
>
And a few others. I found out the problem I reported
a while ago with CBO suddenly going South on hash scan joins and
completely ignoring nested loops or indexes is actually an introduced
problem due to a change in CBO rules after 8.0.4. It first
affected SAP users. It used to be fixed by a couple of events
which due to their usefulness, became the two "new" optimizer cost
adjust parameters in later versions of 8.0 and some of the 8.1.
<groan... roll on 9i, I've had enough of this optimizer!>
Cheers
Nuno Souto
[EMAIL PROTECTED]
http://www.users.bigpond.net.au/the_Den
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Nuno Souto
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
