Using parallel query on range partitioned table

Hi,

I am trying to use parallel hint for the following query that uses a range partitioned table/local index. Partition pruning happens, however I am not able to get the PQO kick in.

Oracle9i Enterprise Edition Release 9.2.0.7.0 - 64bit Production

Here is the snippet of the query without the parallel hint, just providing the relevant pieces....

 WITH
 alias1
 AS
 (select *
 from Z_TRANSACTION
 where PART_KEY between 120 AND 124)
 ...
 select * from <xxx> where <filter>, LEFT OUTER JOIN alias1 ON (condition ) .....

Here is the snippet of the execution plan. I will just provide the relevant piece of the plan.

Name                     | Rows  | Bytes | Cost  | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------------



|  28 |        VIEW

|                           |   119K|  2565K|   342K|       |       |

|* 29 |         HASH JOIN


|                           |   119K|  3615K|   342K|       |       |

|* 30 |          TABLE ACCESS FULL                 |
Z_TIME                    |    29 |   232 |     3 |       |       |


|  31 |          PARTITION RANGE ITERATOR

|                           |       |       |       |   122 |   126 |

|  32 |           TABLE ACCESS BY LOCAL INDEX ROWID|

Z_TRANSACTION             |   855K|    18M|   342K|   122 |   126 |
|* 33 |            INDEX RANGE SCAN                |
Z_TXN_ID_IDX              |   342K|       |  1047 |   122 |   126 |

Can you help me with the following questions

• can I make use of parallel query where partition pruning occurs but it makes use of index range scan on the relevant partitions
• if so, where do I need to place the parallel hint: in the WITH clause segment or would it be in the SELECT where the query joins the alias1.

Obviously, I need to spend more time on this, I thought I will check with the List meanwhile for words of wisdom, Thanks!!

-- 
Arul

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