Re: dimension table

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