# RE: Anyway to optimize the optimizer

• From: "Khedr, Waleed" <Waleed.Khedr@xxxxxxx>
• To: <jaromir@xxxxxxxxxxxx>, <Christian.Antognini@xxxxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
• Date: Thu, 3 Mar 2005 19:07:09 -0500

```        Thanks for replying on this. This is the case.
Specially when there is a relation between the open and closed dates.
If it's partitioned on open_dt, why can't partitioning take advantage of
it in:

For example:
Open_dt <=3D closed_dt <=3D open_dt + 10

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 *=20
from view
Where closed_dt =3D 15

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

-----Original Message-----
From: jaromir nemec [mailto:jaromir@xxxxxxxxxxxx]=20
Sent: Thursday, March 03, 2005 6:37 PM
To: Christian.Antognini@xxxxxxxxxxxx; oracle-l@xxxxxxxxxxxxx
Subject: Re: Anyway to optimize the optimizer

Hallo Chris,

> To achieve correct partition pruning you should have a restriction on
the=20
> partition
> key.

> If the actual structure doesn't provide it in a simply way, in my
opinion,

> the partition key was wrongly chosen!

I can't completely agree with this argumentation. It is very probable
that a=20
fact table has more than one related dimensions and therefore there are
more=20
access paths. Only one  of them can be chosen as a partition key. So a

A simple example is a table of events that have an opening and closing
date.

Does it mean, if I decide to partition on opening date and some one wont
to=20
report on closing date, I commited a design flaw?

Regards,

Jaromir

----- Original Message -----=20
From: "Christian Antognini" <Christian.Antognini@xxxxxxxxxxxx>
To: <Waleed.Khedr@xxxxxxx>
Cc: <oracle-l@xxxxxxxxxxxxx>
Sent: Thursday, March 03, 2005 11:51 PM
Subject: RE: Anyway to optimize the optimizer

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