Re: dimension table
- From: Kar <kp0773@xxxxxxxxx>
- To: tim@xxxxxxxxx
- Date: Wed, 9 Dec 2009 00:18:35 -0800
Thanks Tim . It is going to take me sometime to digest what you have
suggested . :)
On Tue, Dec 8, 2009 at 7:16 PM, Tim Gorman <tim@xxxxxxxxx> wrote:
> You've already received "suggestions" based on 15 years of "real time
> implementation" experience in data warehousing using Oracle.
>
> It appears you missed it.
>
>
>
>
> Kar wrote:
>
>
> 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@xxxxxxxxx> 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@xxxxxxxxx
>> 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@xxxxxxxxx> wrote:
>>
>>> On Tue, Dec 8, 2009 at 11:17 AM, Kar <kp0773@xxxxxxxxx> 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
>>>
>>>
>>>
>>
>
Other related posts:
