Re: create a view with parallel hint

From: Tony Adolph <tony.adolph.dba_at_gmail.com>
Date: Mon, 31 Mar 2008 11:20:32 +1300
Message-ID: <4a38d9060803301520p16cb9c3ft58017a9a751c3a66@mail.gmail.com>

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

--
http://www.freelists.org/webpage/oracle-l

Received on Sun Mar 30 2008 - 17:20:32 CDT