Re: create a view with parallel hint
- From: "Tony Adolph" <tony.adolph.dba@xxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Mon, 31 Mar 2008 11:20:32 +1300
Hi All,
Thanks for everyone who noticed I can't cut-n-paste.... the view's hint was
incorrectly placed in my post (numpty)
So I should have posted:
create or replace view pinpap.EVENT_T_parallel_historic as
select /*+ full(eph) parallel(eph,8) */
*
from
PINPAP.EVENT_T partition (PARTITION_HISTORIC) eph;
The rest of my post's query is still valid though... this view's hints are
ignored.
When I explain the query that uses this view, I get an index lookup instead
of a full scan:
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT |
|
|* 1 | HASH JOIN OUTER |
|
| 2 | NESTED LOOPS |
|
| 3 | NESTED LOOPS |
|
| 4 | NESTED LOOPS |
|
| 5 | NESTED LOOPS |
|
*|* 6 | TABLE ACCESS BY LOCAL INDEX ROWID| EVENT_T
|
|* 7 | INDEX RANGE SCAN | I_EVENT_SESS_OBJ__ID
|
*| 8 | TABLE ACCESS BY LOCAL INDEX ROWID| EVENT_PAYMENT_BATCH_T
|
|* 9 | INDEX RANGE SCAN | I_EVENT_PAYMENT_BATCH__ID
|
| 10 | TABLE ACCESS BY INDEX ROWID | ACCOUNT_T
|
|* 11 | INDEX UNIQUE SCAN | I_ACCOUNT__ID
|
| 12 | TABLE ACCESS BY INDEX ROWID | ACCOUNT_NAMEINFO_T
|
|* 13 | INDEX UNIQUE SCAN | I_ACCOUNT_NAMEINFO__ID
|
|* 14 | TABLE ACCESS BY LOCAL INDEX ROWID | EVENT_BILLING_PAYMENT_T
|
|* 15 | INDEX UNIQUE SCAN | I_EVENT_BILLING_PAYMENT__ID
|
| 16 | TABLE ACCESS FULL |
CONFIG_PAYMENT_CHANNEL_MAP_T |
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("EBP"."CHANNEL_ID"="CPCM"."CHANNEL_ID"(+))
6 - filter("EPH"."POID_ID0"<35184372088832 AND "EPH"."START_T">=1206442800
AND "EPH"."START_T"<=1206529199 AND
"EPH"."POID_TYPE"<>'/event/billing/payment/failed')
7 - access("EPH"."SESSION_OBJ_ID0"<35184372088832)
9 - access("EPH"."SESSION_OBJ_ID0"="EPB"."OBJ_ID0")
filter("EPB"."OBJ_ID0"<35184372088832)
11 - access("EPH"."ACCOUNT_OBJ_ID0"="A"."POID_ID0")
13 - access("A"."POID_ID0"="ANI"."OBJ_ID0" AND "ANI"."REC_ID"=1)
14 - filter("EBP"."STATUS"<>30)
15 - access("EPH"."POID_ID0"="EBP"."OBJ_ID0")
filter("EBP"."OBJ_ID0"<35184372088832)
--------------------------------------------------------------------------------
Sorry for the dodgy original post. Beers for anyone who chased up this red
herring on behalf* [must be in Auckland to collect :-) ]
Cheers
Tony
Other related posts:
