Re: dimension table

From: Kar <>
Date: Tue, 8 Dec 2009 17:13:44 -0800
Message-ID: <>

right now in the where clause of the query dim_Date.CALENDAR_DATE In ( '17-NOV-2008' )

there was a index on the calender_date in dim_date . but the index on date column is not helping here.

However when i changed it to dim_Date.date_key=100830 the query came out in few sec. the problem they mention is the reporting tool Business Objects do not give them a option to translate the calender_date to the date_key. Again looking forward to how you guys have any real time implementation over this .

Appreciate any suggestions.
Thanks again.

On Tue, Dec 8, 2009 at 3:18 PM, Tim Gorman <> wrote:

> Kar,
> The real work is being spent accessing all those rows in the FACT_PGP_OPP
> table, and then joining outwards to the various dimension tables, of which
> one is DIM_DATE. There is nothing wrong with that table or the other
> tables, but everything wrong with the indexing on the fact table, as I
> imagine that you are supporting your dimension-key columns with B*Tree
> indexes and not bitmap indexes?
> The query that you're not showing appears to be a classic example of Oracle
> struggling to perform a "star query" without being able to optimize using a
> "star transformation" join, as documented in the Oracle Data Warehousing
> guide at
> ""<>.
> This is Oracle11gR2 documentation, but a similar section of documentation
> within the "Data Warehousing Guide" for each database version going back to
> Oracle9i at least.
> Essentially, a query performing a FULL table scan on the fact table first,
> then gradually (and laboriously) filtering out the just-retrieved fact rows
> by joining outwards to the dimension tables, is incredibly wasteful and
> inefficient. Far better to use a star transformation, which first resolves
> a result set from all of the dimension tables first, then uses that
> intermediate result set to then perform a combined bitmap-merge search into
> the fact table by the bitmap-supported dimension-key columns.
> First however, take a look at the WHERE clause in your star query. Please
> verify that, besides the WHERE-clause predicates to join the fact-table to
> the dimension-tables, you also have WHERE-clause predicates that narrow the
> search by means of filtering predicates. That is, join-predicates are
> generally of the form "tableA.colX = tableB.colY" while filtering predicates
> are generally of the form "tableA.colX = <value>". If your WHERE clause is
> composed only of join-predicates with few or no filter-predicates, then your
> query would seem to be more of a "dump" of the entire star schema, and when
> you're dumping something (i.e. retrieving a huge result set from a huge
> query set), there is nothing you can do from an indexing perspective to
> optimize -- the best plan is FULL table scans all around. Indexes (of any
> kind) just get in the way. A good example of this is if your query is
> coming from the "extraction tool" for a BI/OLAP tool such as Business
> Objects, SAS, or Cognos in order to build an OLAP "cube"? So, be sure that
> you are clear on the intent of your query, be sure that it is not intending
> to dump every row in every table in the schema, and if it is an actual
> analytic query and not a "dump" of everything, then consider implementing
> star transformation.
> Check it out, see what you think?
> Tim Gorman
> consultant -> Evergreen Database Technologies, Inc.
> postal => P.O. Box 630791, Highlands Ranch CO 80163-0791
> website =>
> email =>
> mobile => +1-303-885-4526
> fax => +1-303-484-3608
> twitter =>
> Lost Data? =>
> Kar wrote:
> Thanks Guys . . having a tough time with this table.
> Attaching the plan . wont be able to put the query for nda issues.
> appreciate any help.
> On Tue, Dec 8, 2009 at 11:40 AM, Jared Still <> wrote:
>> On Tue, Dec 8, 2009 at 11:17 AM, Kar <> wrote:
>>> Appreciate any suggestions.
>> Details?
>> Jared Still
>> Certifiable Oracle DBA and Part Time Perl Evangelist
>> Oracle Blog:
>> Home Page:

Received on Tue Dec 08 2009 - 19:13:44 CST

Original text of this message