RE: Anyway to optimize the optimizer

>Creating a view that captures this fact:
>Select *
> from table
> Where  closed_dt >=3D open_dt
>   and  open_dt   >=3D closed_dt - 10
>
>Now accessing the view:
>
>Select *
> from view
>Where closed_dt =3D 15

As I just wrote in the previous reply, in my opinion, the structures =
used to support end-user queries should be designed exactly for that. =
This means, that such workaround with views should simply not be used.

>Oracle is able to substitue closed_dt with 5 only where closed_dt
>appears by itself
>So the where looks like:
>
>Where open_dt <=3D 15
>  and closed_dt =3D 15
>  and open_dt   >=3D closed_dt - 10
>
>Optimizer can't take advantage of :  open_dt >=3D closed_dt - 10

Notice that if you don't use the view, Oracle should be able to perform =
the correct pruning.

--
http://www.freelists.org/webpage/oracle-l

Other related posts: