Re: Subquery partition pruning
- From: amonte <ax.mount@xxxxxxxxx>
- To: Greg Rahn <greg@xxxxxxxxxxxxxxxxxx>
- Date: Fri, 2 Apr 2010 10:24:35 +0200
Hello Greg
Thanks for the tip, I modified the query and added TIME_EID (it contains 24
hours time dimension) as follows but no luck even it looks like it is doing
pruning it filters first the second column of partition key so it is still
reading the whole table! Statistics is refreshed as well. (Global and
partition level statistics)
SELECT
TO_CHAR(A.DATE_NAME,'YYYY-MM-DD'),
B.MSISDN,
B.IMSI,
SUM(B.EVENT_AMOUNT)
FROM DW.D_DATE A,
DWH_OWN.T_MOBILE_EVENT B
WHERE A.DATE_EID = B.DATE_EID
AND A.DATE_NAME = TO_DATE('20100322', 'yyyymmdd')
AND B.TIME_EID between 1 and 86400
GROUP BY TO_CHAR(A.DATE_NAME,'YYYY-MM-DD'),
B.MSISDN,
B.IMSI
and got this plan
-----------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes
|TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | |
| | 48M(100)| | | |
| 1 | HASH GROUP BY | | 211M|
10G| 26G| 48M (3)| 38:39:19 | | |
|* 2 | HASH JOIN | | 211M|
10G| | 44M (3)| 36:03:41 | | |
|* 3 | INDEX RANGE SCAN | D_DATE_NAME_WID_UI | 1 | 12
| | 2 (0)| 00:00:01 | | |
| 4 | PARTITION RANGE MULTI-COLUMN| | 6322M|
235G| | 44M (3)| 35:57:09 | KEY(M | KEY(M |
| 5 | PARTITION HASH ALL | | 6322M|
235G| | 44M (3)| 35:57:09 | 1 | 8 |
|* 6 | TABLE ACCESS FULL | F_SGSN_EVENT | 6322M|
235G| | 44M (3)| 35:57:09 | KEY | KEY |
-----------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("A"."DATE_WID"="B"."DATE_WID")
3 - access("A"."DATE_NAME"=TO_DATE(' 2010-03-22 00:00:00', 'syyyy-mm-dd
hh24:mi:ss'))
6 - filter(("B"."TIME_WID">=1 AND "B"."TIME_WID"<=86400)) --> *no good,
should be filter(("B"."TIME_WID">=1 AND "B"."TIME_WID"<=86400 AND
"A"."DATE_WID"="B"."DATE_WID"))*
If I add NL hint as previous tests it runs fast in 25 minutes, expected
time.
Thank you Greg
2010/4/2 Greg Rahn <greg@xxxxxxxxxxxxxxxxxx>
> In order to get partition elimination with subquery pruning you need
> to specify both columns in the partition key. Since your query does
> not include TIME_EID, partition elimination with subquery pruning can
> not take place.
>
> On Thu, Apr 1, 2010 at 12:58 PM, amonte <ax.mount@xxxxxxxxx> wrote:
> > here are the informations, I am gathering statistics now, refreshing and
> see
> > how this goes, using sample percent 1 since the table is huge, thanks
> >
> > partition_key
> > NAME OBJEC COLUMN_NAME COLUMN_POSITION
> > -------------- ----- ---------------- ---------------
> > T_MOBILE_EVENT TABLE DATE_EID 1
> > T_MOBILE_EVENT TABLE TIME_EID 2
> >
> > subpartiton_key
> > NAME OBJEC COLUMN_NAME COLUMN_POSITION
> > -------------- ----- ---------------- ---------------
> > T_MOBILE_EVENT TABLE TXES_ID 1
>
>
>
> --
> Regards,
> Greg Rahn
> http://structureddata.org
>
Other related posts:
