I had to optimize a query in an old database, version 9 and is in rules (requerid by application). Below show you how rules work and order that is weighted, although you can always use some hint to optimize our way. In Oracle 9.2 hay 15 rules for generate the execution plan, from the best case, access Single Row by Rowid, to worst case, FTS.
Is recommended since version 8 does not use rules instead have to use the optimizer based in costs (CBO), but it’s funny because Oracle to own, in some internal sentences using the hint / * + rule * /, to resolve statements, especially when query on views, get it more faster than CBO.
- Single Row by Rowid.
- Single Row by Cluster Join.
- Single Row by Hash Cluster Key with Unique or Primary Key.
- Single Row by Unique or Primary Key.
- Clustered Join.
- Hash Cluster Key.
- Indexed Cluster Key.
- Composite Index.
- Single-Column Indexes.
- Bounded Range Search on Indexed Columns.
- Unbounded Range Search on Indexed Columns.
- Sort Merge Join.
- MAX or MIN of Indexed Column.
- ORDER BY on Indexed Column.
- Full Table Scan.
HTH – Antonio NAVARRO