Re: dimension table

From: Kar <kp0773_at_gmail.com>
Date: Tue, 8 Dec 2009 17:13:44 -0800
Message-ID: <39a33df90912081713v3be51db3v652e9a87fe12e0ee_at_mail.gmail.com>

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

On Tue, Dec 8, 2009 at 3:18 PM, Tim Gorman <tim_at_evdbt.com> 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
> "http://download.oracle.com/docs/cd/E11882_01/server.112/e10810/schemas.htm#i1006335"<http://download.oracle.com/docs/cd/E11882_01/server.112/e10810/schemas.htm#i1006335>.
> 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 => http://www.EvDBT.com/
> email => Tim_at_EvDBT.com
> mobile => +1-303-885-4526
> fax => +1-303-484-3608
> twitter => http://www.twitter.com/timothyjgorman
> Lost Data? => http://www.ora600.be/
>
>
>
> 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 <jkstill_at_gmail.com> wrote:
>
>> On Tue, Dec 8, 2009 at 11:17 AM, Kar <kp0773_at_gmail.com> wrote:
>>
>>> Appreciate any suggestions.
>>>
>>>
>> Details?
>>
>> Jared Still
>> Certifiable Oracle DBA and Part Time Perl Evangelist
>> Oracle Blog: http://jkstill.blogspot.com
>> Home Page: http://jaredstill.com
>>
>>
>>
>

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

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