Vamshi,
You have a partitioned fact table which is (evidently) range
partitioned on a DATE column, though you don't indicate which of the
two DATE columns referenced in the query's JOIN-ON clause or whether it
is another DATE column altogether. So, partition-pruning to reduce the
work performed by the query is not a possibility here. In your query
from the DBA_TAB_PARTITIONS view, SUBPARTITION_COUNT shows as "0", so
the fact table is not composite partitioned (a.k.a. subpartitioned), so
there is no chance of subpartition-pruning either.
Since the only filter on either of the tables in the query is
JOURNALENTRYTYPEID and it has no relation whatsoever to the TIMEDIMREL
table, the optimizer is likely to choose a FULL table scan on
TIMEDIMREL table, even if the columns POSTINGDATE and EFFECTIVEDATE on
the TIMEDIMREL table were indexed, because the optimizer will have to
assume that all rows in TIMEDIMREL are going to be accessed, and
retrieving all rows in a table via index makes no sense at all.
Similarly, since there are no partition-key columns or indexed columns
to help with the scan of the fact table, that table will have to be
FULL table scanned as well. So, Oracle is joining a 24m row fact table
to a 2244 row dimension table via sort-merge or hash join in 180
seconds, which is pretty good serial performance (in my opinion).
The ironic thing is that the query is taking this performance hit just
so that the single column TIMEID can be obtained from the TIMEDIMREL
table; I hope that one column is really worth the cost... :-)
In a perfect world (from the perspective of this particular query), the
fact table would be hash sub-partitioned on the JOURNALENTRYTYPEID
column (so that you don't fetch rows only to be subsequently filtered
out) and the DEGREE attribute on the fact table would equal the
parameter CPU_COUNT (so you can throw more resources at this
operation). Alternatively, instead of sub-partitioning, it is possible
that a local index on JOURNALENTRYTYPEID on the fact table might be
helpful, but not nearly as helpful as the hash sub-partitioning --
still, creating an index is often easier than re-building a table, so
it might be worth a try?
Good luck! Hope this helps....
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
Lost Data? => http://www.ora600.be/ for info about DUDE...
Vamshi Damidi wrote:
HI all,
here are the partitions that i have.
TABLE_NAME PARTITION_NAME
SUBPARTITION_COUNT HIGH_VALUE
------------------------------ ------------------------------
------------------
--------------------------------------------------------------------------------
POSITIONTRANSACTION_PARTS
PT_1 0 TO_DATE(' 2006-04-01
00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
POSITIONTRANSACTION_PARTS
PT_10 0 TO_DATE(' 2008-07-01
00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
POSITIONTRANSACTION_PARTS
PT_11 0 TO_DATE(' 2009-01-01
00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
POSITIONTRANSACTION_PARTS
PT_12 0 TO_DATE(' 2009-10-01
00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
POSITIONTRANSACTION_PARTS
PT_2 0 TO_DATE(' 2006-07-01
00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
POSITIONTRANSACTION_PARTS
PT_3 0 TO_DATE(' 2006-10-01
00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
POSITIONTRANSACTION_PARTS
PT_4 0 TO_DATE(' 2007-01-01
00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
POSITIONTRANSACTION_PARTS
PT_5 0 TO_DATE(' 2007-04-01
00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
POSITIONTRANSACTION_PARTS
PT_6 0 TO_DATE(' 2007-07-01
00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
POSITIONTRANSACTION_PARTS
PT_7 0 TO_DATE(' 2007-10-01
00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
POSITIONTRANSACTION_PARTS
PT_8 0 TO_DATE(' 2008-01-01
00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
POSITIONTRANSACTION_PARTS
PT_9 0 TO_DATE(' 2008-04-01
00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
.
Thanks,
Vamshi .D
On Sat, May 22, 2010 at 11:18 AM, Timothy
Kras <timk131@xxxxxxxxx> wrote:
Vamshi,
Are POSTINGDATE and EFFECTIVEDATE columns indexed? An 'explain
plan' of the query - with and without the join - should help reveal
what is happening.
Regards,
Tim
On Sat, May 22, 2010 at 9:05 AM, Vamshi
Damidi <dbaprimatics@xxxxxxxxx>
wrote:
I have a query
select a12.TIMEID,
sum(CASE WHEN a11.JOURNALENTRYTYPEID in
(169,170,172,173,175,176,385,386,388,389,391,392,601,602,604,605,607,608
) then a11.AMOUNT else NULL end) BALANCETRANSFER,
sum(CASE WHEN a11.JOURNALENTRYTYPEID in
(107,108,109,323,324,325,539,540,541 ) then a11.AMOUNT else NULL end)
CARRYOVERCOSTBASIS1,
sum(CASE WHEN a11.JOURNALENTRYTYPEID in
(131,132,133,134,135,164,166,167,184,347,348,349,350,351,380,382,383,400,563,564,565,566,567,596,598,599,616)
then a11.AMOUNT else NULL end) CHARGEOFF,
sum(CASE WHEN a11.JOURNALENTRYTYPEID in
(107,108,109,110,323,324,325,326,539,540,541,542 ) then a11.AMOUNT else
NULL end) COSTBASISRELIEF1,
sum(CASE WHEN a11.JOURNALENTRYTYPEID in
(110,148,149,150,152,162,163,186,326,364,365,366,368,378,379,402,542,580,581,582,584,594,595,618
) then a11.AMOUNT else NULL end) COSTBASISRELIEVEDDUETOREO1,
sum(CASE WHEN a11.JOURNALENTRYTYPEID in
(136,177,178,179,180,185,352,393,394,395,396,401,568,609,610,611,612,617
) then a11.AMOUNT else NULL end) COSTBASISRELIEVEDDUETOSALES1,
sum(CASE WHEN a11.JOURNALENTRYTYPEID in (101,317,533 ) then a11.AMOUNT
else NULL end) FUNDEDUPB,
sum(CASE WHEN a11.JOURNALENTRYTYPEID in
(126,127,201,342,343,417,558,559,633 ) then a11.AMOUNT else NULL end)
IMPAIRMENT,
sum(CASE WHEN a11.JOURNALENTRYTYPEID in (115,331,547 ) then a11.AMOUNT
else NULL end) INTERESTACCRETIONAIR,
sum(CASE WHEN a11.JOURNALENTRYTYPEID in
(104,116,168,320,332,384,536,548,600 ) then a11.AMOUNT else NULL end)
NETCAPITALIZEDINTEREST,
sum(CASE WHEN a11.JOURNALENTRYTYPEID in
(113,114,157,329,330,373,545,546,589 ) then a11.AMOUNT else NULL end)
NETINTERESTACCRUEDCU,
sum(CASE WHEN a11.JOURNALENTRYTYPEID in
(121,122,123,124,125,337,338,339,340,341,553,554,555,556,557 ) then
a11.AMOUNT else NULL end) NETINTERESTPAYMENTS,
sum(CASE WHEN a11.JOURNALENTRYTYPEID in
(117,118,119,333,334,335,549,550,551 ) then a11.AMOUNT else NULL end)
NETPRINCIPALPAYMENTS,
sum(CASE WHEN a11.JOURNALENTRYTYPEID in (111,327,543 ) then a11.AMOUNT
else NULL end) NETPRORATEDFEESCU,
sum(CASE WHEN a11.JOURNALENTRYTYPEID in (214,430,646 ) then a11.AMOUNT
else NULL end) NETRECOVERIES1,
sum(CASE WHEN a11.JOURNALENTRYTYPEID in
(106,184,185,186,187,322,400,401,402,403,538,616,617,618,619 ) then
a11.AMOUNT else NULL end) NEWDEFERREDFEESAMOUNT,
sum(CASE WHEN a11.JOURNALENTRYTYPEID in
(142,143,144,145,146,147,187,358,359,360,361,362,363,403,574,575,576,577,578,579,619
) then a11.AMOUNT else NULL end) PAIDOFFCOSTBASIS1,
sum(CASE WHEN a11.JOURNALENTRYTYPEID in (102,103,318,319,534,535 ) then
a11.AMOUNT else NULL end) PURCHASEADJUSTMENTCU,
sum(CASE WHEN a11.JOURNALENTRYTYPEID in (105,321,537 ) then a11.AMOUNT
else NULL end) RECOURSEADJUSTMENT,
sum(CASE WHEN a11.JOURNALENTRYTYPEID in
(195,196,197,198,199,200,411,412,413,414,415,416,627,628,629,630,631,632
) then a11.AMOUNT else NULL end) REDESIGNATION,
sum(CASE WHEN a11.JOURNALENTRYTYPEID in
(128,129,130,171,344,345,346,387,560,561,562,603 ) then a11.AMOUNT else
NULL end) TOTALAMORTIZATIONCU
from mstrlarge26.TIMEDIMREL a12
join mstrlarge26.POSITIONTRANSACTION_PARTS a11
on (a12.POSTINGDATE = a11.POSTINGDATE and a12.EFFECTIVEDATE =
a11.EFFECTIVEDATE)
-- on (a12.POSTINGDATE,a12.EFFECTIVEDATE) =
(a11.POSTINGDATE,a11.EFFECTIVEDATE)
where a11.JOURNALENTRYTYPEID in
(169,170,172,173,175,176,385,386,388,389,391,392,601,602,604,605,607,608,
107,108,109,323,324,325,539,540,541,
131,132,133,134,135,164,166,167,184,347,348,349,350,351,380,382,383,400,563,564,565,566,567,596,598,599,616,
107,108,109,110,323,324,325,326,539,540,541,542,
110,148,149,150,152,162,163,186,326,364,365,366,368,378,379,402,542,580,581,582,584,594,595,618,
136,177,178,179,180,185,352,393,394,395,396,401,568,609,610,611,612,617,
101,317,533,
126,127,201,342,343,417,558,559,633,
115,331,547,
104,116,168,320,332,384,536,548,600,
113,114,157,329,330,373,545,546,589,
121,122,123,124,125,337,338,339,340,341,553,554,555,556,557,
117,118,119,333,334,335,549,550,551,
111,327,543,
214,430,646,
106,184,185,186,187,322,400,401,402,403,538,616,617,618,619,
142,143,144,145,146,147,187,358,359,360,361,362,363,403,574,575,576,577,578,579,619,
102,103,318,319,534,535,
105,321,537,
195,196,197,198,199,200,411,412,413,414,415,416,627,628,629,630,631,632,
128,129,130,171,344,345,346,387,560,561,562,603)
group by a12.TIMEID;
The above query runs fine with out the timedim table join.
when the timedim table is joined it takes 3 min.
Env. : 10.2.0.1 Enterprise Edition no patches applied on top it just
basic version.
Dataware house environment. has millions of records.
This is table count.
SQL> select count(*) from mstrlarge26.TIMEDIMREL;
COUNT(*)
----------
2244
SQL> select count(*) from mstrlarge26.POSITIONTRANSACTION_PARTS;
COUNT(*)
----------
24173149
sga_target : 15G
pga_target : 10g
Any hint would be great help.
Let me know if you need any more information.
Thanks,
Vamshi .D
--
//www.freelists.org/webpage/oracle-l
|
