RE: Resources for tuning DW (star_transformation fact) queries

Thanks to Chris and Mike for their suggestions.

Thanks!
-----Original Message-----
From: Michael McMullen [mailto:ganstadba_at_hotmail.com] Sent: Tuesday, November 22, 2005 11:18 AM To: Khemmanivanh, Somckit; oracle-l_at_freelists.org Subject: Re: Resources for tuning DW (star_transformation fact) queries

I always look for some type of summary functions in the where clause ie.
where orderdate = (select max(something) from blah where a. = b. ) and then rewrite as analytical.

look for same statement being called over and over and rewrite as with clause

look for joins which are used just to get a lookup value and rewrite as in-line selects.

look for things obviously not right. like outerjoins with where conditions
on the outer-joined table,or functions in the where clause ie
select *
from
a.blah, b.blah
where a.x = b.x(+)
and upper(b.something) = 'xxx'

Mike
ganstadba_at_hotmail.com

• Original Message ----- From: Khemmanivanh, Somckit To: oracle-l_at_freelists.org Sent: Tuesday, November 22, 2005 2:01 PM Subject: Resources for tuning DW (star_transformation fact) queries

Hi,

Can anyone recommend some resources for getting started on tuning these types of massive DW queries.

The explain plans on these queries can be pages and pages long, which is a
little overwhelming at first.

I'm looking for some quick tips from the experts to help me get started :)

Thanks much.

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