Re: create a view with parallel hint

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: