FAQ
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.

thanks

Tim Brown
PCS Technology Group
JPMChase

tim.x.brown_at_jpmchase.com
(614) 213-9259
(614) 226-8605

Search Discussions

  • Deepak Sharma at Feb 17, 2006 at 12:43 am

    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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedFeb 16, '06 at 10:04p
activeFeb 17, '06 at 12:43a
posts2
users2
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase