Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Anyway to optimize the optimizer

RE: Anyway to optimize the optimizer

From: Khedr, Waleed <Waleed.Khedr_at_FMR.COM>
Date: Thu, 3 Mar 2005 19:07:09 -0500
Message-ID: <42BBD772AC30EA428B057864E203C99901233752@MSGBOSCLF2WIN.DMN1.FMR.COM>

        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-l
Received on Thu Mar 03 2005 - 19:10:23 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US