RE: Anyway to optimize the optimizer

  • From: "Christian Antognini" <Christian.Antognini@xxxxxxxxxxxx>
  • To: "Khedr, Waleed" <Waleed.Khedr@xxxxxxx>
  • Date: Fri, 4 Mar 2005 08:41:10 +0100

>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.


Other related posts: