| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: CBO irregularity
In practice, I had to resort to these "plan-restricting" hints in a small minority of cases, especially where partitioning is involved.
I don't use RULE, except to see what effect it has.
I don't use USE_NL because I often get nested-loop of inner full-table-scans, usually if an index is missing. If the index is really missing, a hash-join is almost-always a better option. Even without the hint, the CBO does choose NL of inner FTS by itself (more in 9i than previous releases). I think this particular combination should be outlawed by the CBO because it doesn't make sense. I would be glad to hear feedback on this.
I would not classify ORDERED as "restricting". It is very useful if you know the data distribution, and allows you to start with the most selective order (still true of 9i). This selective order should hold true for future releases, unless Oracle comes up with a newer join-method (the last great one was HASH!).
INDEX is sometimes required, in conjunction with the other hints.
FULL, I use with PARALLEL Adjusting the (parallel degree) is what I consider an art.
.
By (b), I mean things like:
 |
 |