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