Re: OPTIMIZER-Problem

Carlos Augusto Leite Netto <cnetto_at_cps.softex.br> wrote:

I simply want to add a few comments on the order of where clauses :

> >SELECT TO_CHAR(DAT_ZEIT_VON,'DD.MM., HH24:MI'),AUFTRAG.TYP, >
> >the plan is exactly the same when I change the order of columns in
> >the WHERE-clause in any way (I've permuted all !).
>
> Order of columns in the WHERE doesn't care! I saw two cases where
> changing the order of columns did chances - in 6 years working with
> Oracle! Table in the from clause is really very important when you
> are using RULE optimizer. Oracle seems to be making some LALR parsing
> that usualy build lists from right to left. I think that because of
> this Oracle selects the last table to be the outer loop.
>

The execution order of WHERE clauses is based on a priority execution order rules. The general rule is that the most precise condition will be executed before other less precise conditions.

For example, if Oracle ROW_ID and a UID of a table are provided in WHERE clause, ROW_ID will be picked first since ROW_ID is unique in the whole database.

Another example, if a UID of table and a non-unique key of table are given in a WHERE clause, UID will be picked first since is unique in a table.

In case the conditions in a WHERE clauses have the same priority, the last WHERE clause will be picked first. Received on Tue Aug 15 1995 - 00:00:00 CEST