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.

------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                  |
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

Other related posts: