Re: Star transformation prerequisites

From: Deepak Sharma <sharmakdeep_oracle_at_yahoo.com>
Date: Thu, 16 Feb 2006 16:43:33 -0800 (PST)
Message-ID: <20060217004333.18010.qmail@web52806.mail.yahoo.com>

tim.x.brown_at_jpmchase.com wrote:

> Oracle version 9.2.0.6 Enterprise Edition
> Solaris 2.8
>
> Can someone please definitively clarify if it is
> mandatory for foreign
> keys (not necessarily enabled) to be declared, in
> order that that CBO will
> apply star transformation in a traditional star
> schema? (fact table and
> dimension tables are indexed according to Bert
> Scalzo's Guide to Oracle 8i
> Data Warehousing). In the Bert Scalzo book I did not
> see a categorical
> statement that the fks MUST be declared.
>
> In the Oracle 9.2 Data warehousing guide, I also
> cannot find where it is
> clearly stated that the fks MUST be declared, only
> that the fks (join
> columns) must have bitmap indexes (subtle
> difference).
>
> I have also read Jonathan Lewis's various articles
> on bitmap indexes and
> can find nothing clear there either.
>
> star_transformation_enabled = temp_disable
>
> # As I recall (from a year ago), hit a bug in
> 9.2.0.4 where use of temp
> tables had serious negative impact on some queries.
> Never turned it back
> to value = "true"
>
> My questions are:
> 1. Are foreign key declarations mandatory for the
> CBO to use star
> transformation, if fks are not declared, but the
> bitmap indexes exist and
> all objects are appropriately analyzed?
>
> 2. Do fks influence the CBO? If so, how? Can someone
> point me at or
> recommend documentation?
>
> I posted the same question on the Metalink forum,
> and the only response so
> far has been from someone who has NOT declared fks
> but has observed star
> transformation, (but no explain plans or schema
> descriptions were given).
>
> If this is a case of me misreading documentation, I
> apologize.

For question (1), you are correct in saying that only Bitmap Indexes are required on FK columns of the FACT table. The FK relationship are not required to be defined. We use this in our database all the time.

There is one restriction though, the query requiring the STAR transformation, must include at least 2 Dimension tables.

You should also be aware of some Bugs that may occur.

Example (in an earlier 9i version) joining a FACT to a DATE_DIM, where you specified a condition on the DATE_DIM with SYSDATE instead if a literal date, did not result in a STAR, and we got a one-off patch to fix the issue.

Following didn't work:

select f1.col_A, f1.col_B
from f1, loc_dim d1, date_dim d2

where f1.loc_key = d1.loc_key
  and f1.date_key = d2.date_key
  and d2.process_date between (sysdate - 2) and

sysdate

Following worked:

select f1.col_A, f1.col_B
from f1, loc_dim d1, date_dim d2

where f1.loc_key = d1.loc_key
  and f1.date_key = d2.date_key
  and d2.process_date between '14-FEB-2006' and

'16-FEB-2006' Thanks,
Deepak

Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com

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

Received on Thu Feb 16 2006 - 18:43:33 CST