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