Re: SQL SELECT question

Oracle 7 introduced the cost based optimizer. Versions prior to 7 used the rules based optimizer where the last table in the where clause was the driving table in the query plan. This does not necessarily mean that there is an index scan used on the driving table, and it does not necessarily mean that exact index lookups are used to join subsequent tables. All it means to be a driving table is that any predicates are applied to that table first, then subsequent tables are joined to the result set.

The rules based optimizer can be set as the default in Oracle 7, however I believe it is most common to use the cost based optimizer. In our shop, we still use the rules based optimizer because we have a large amount of SQL tuned to use it, and we don't know what kind of query plans the cost based optimizer will generate (without using explain plan). However we do know what kind query plans the optimizer will generate with the rules based optimizer.

You can force your SQL to use the rules based optimizer with the /*+ RULE */ hint. Any other hints will be ignored if you do this, though.

Cheers,

Blair Received on Sat Feb 07 1998 - 00:00:00 CST