Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Anyway to optimize the optimizer
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_at_db-nemec.com]=20
Sent: Thursday, March 03, 2005 6:37 PM
To: Christian.Antognini_at_trivadis.com; oracle-l_at_freelists.org
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
trade=20
off must be made.
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
-- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-lReceived on Thu Mar 03 2005 - 19:10:23 CST