can I use different hints in one clause of union in oracle

I have a merge query of below format -

merge into dest 
using (select /*+ use_hash(t1,t2) parallel (4)*/ t1_name ,count(*) from table1 , table2 on t1.col=t2.col
group by  t1_name) src
on (values)
when matched then update dest.col

now I have to do union in this query

merge into dest 
using (select count(*),t1_name from (select /*+ use_hash(t1,t2) parallel (4)*/ t1_name ,count(*) from table1 , table2 on t1.col=t2.col
group by  t1_name
union all
select t1_name,count(*) from table t1 group by t1_name from table t1  group by t1_name ) group by t1_name) src
on (values)
when matched then update dest.col

My question is do I have to give parallel hint in second union clause also . Also use_hash hint which was running good earlier would not have impact on adding union clause in the query

Solution

The hints apply to each select independently. First, you need to understand how parallel execution works in terms of session level and/or hints applied.

In this you have three elements

The merge statement itself which will update some rows and will not run in parallel
The first data set recover with the first select will run in parallel
The second will run in parallel because of the hints applied to the first statement

I am assuming the tables are configured with noparallel and that you are not enabling parallel dml. I won't even try to replicate the query, because I don't know exactly what you want to do with it.

Test case

SQL> create table t1 ( c1 number, c2 number ) ;

Table created.

SQL> create table t2 ( c1 number, c3 number ) ;

Table created.

SQL> ed
Wrote file afiedt.buf

  1  declare
  2  begin
  3  for i in 1 .. 10000
  4  loop
  5   insert into t1 values ( i , dbms_random.value );
  6   insert into t2 values ( i , dbms_random.value );
  7  end loop;
  8* end;
SQL> /

PL/SQL procedure successfully completed.

SQL> ed
Wrote file afiedt.buf

  1  declare
  2  begin
  3  for i in 1 .. 10000
  4  loop
  5   insert into t1 values ( i , dbms_random.value );
  6  end loop;
  7* end;
SQL> /

PL/SQL procedure successfully completed.

SQL> select count(*) from t1 ;

  COUNT(*)
----------
     20000

SQL> select count(*) from t2 ;

  COUNT(*)
----------
     10000

Now I am going to build a merge statement to update the table t2 using a source based on a union select. Forget in this case the meaning of the query, which has no sense at all, but the resulting execution plan

SQL> merge into t dest
  2  using ( select distinct t1 from (
  3  select /*+ use_hash(t2,t1) parallel (4)*/ t1.c1 as t1 from t1 inner join t2 on ( t1.c1=t2.c1)
  4  union all
  5  select t2.c1 as t1 from t1 inner join t2 on ( t1.c1=t2.c1 )
  6   ))  src on ( src.t1 = dest.c1 )
  7* when matched then update set dest.c2 = dbms_random.value

10000 rows merged.


Execution Plan
----------------------------------------------------------
Plan hash value: 889987475

-----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name     | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------------------------------
|   0 | MERGE STATEMENT                     |          | 10000 |   253K|    43   (3)| 00:00:01 |        |      |            |
|   1 |  MERGE                              | T        |       |       |            |          |        |      |            |
|   2 |   PX COORDINATOR                    |          |       |       |            |          |        |      |            |
|   3 |    PX SEND QC (RANDOM)              | :TQ10006 | 10000 |   498K|    43   (3)| 00:00:01 |  Q1,06 | P->S | QC (RAND)  |
|   4 |     VIEW                            |          |       |       |            |          |  Q1,06 | PCWP |            |
|*  5 |      HASH JOIN                      |          | 10000 |   498K|    43   (3)| 00:00:01 |  Q1,06 | PCWP |            |
|   6 |       PX RECEIVE                    |          | 10000 |   126K|    17   (6)| 00:00:01 |  Q1,06 | PCWP |            |
|   7 |        PX SEND BROADCAST            | :TQ10005 | 10000 |   126K|    17   (6)| 00:00:01 |  Q1,05 | P->P | BROADCAST  |
|   8 |         VIEW                        |          | 10000 |   126K|    17   (6)| 00:00:01 |  Q1,05 | PCWP |            |
|   9 |          HASH UNIQUE                |          | 10000 |   126K|    17   (6)| 00:00:01 |  Q1,05 | PCWP |            |
|  10 |           PX RECEIVE                |          | 10000 |   126K|    17   (6)| 00:00:01 |  Q1,05 | PCWP |            |
|  11 |            PX SEND HASH             | :TQ10004 | 10000 |   126K|    17   (6)| 00:00:01 |  Q1,04 | P->P | HASH       |
|  12 |             HASH UNIQUE             |          | 10000 |   126K|    17   (6)| 00:00:01 |  Q1,04 | PCWP |            |
|  13 |              VIEW                   |          | 40000 |   507K|    16   (0)| 00:00:01 |  Q1,04 | PCWP |            |
|  14 |               UNION-ALL             |          |       |       |            |          |  Q1,04 | PCWP |            |
|* 15 |                HASH JOIN            |          | 20000 |   507K|     8   (0)| 00:00:01 |  Q1,04 | PCWP |            |
|  16 |                 PX RECEIVE          |          | 10000 |   126K|     3   (0)| 00:00:01 |  Q1,04 | PCWP |            |
|  17 |                  PX SEND HASH       | :TQ10000 | 10000 |   126K|     3   (0)| 00:00:01 |  Q1,00 | P->P | HASH       |
|  18 |                   PX BLOCK ITERATOR |          | 10000 |   126K|     3   (0)| 00:00:01 |  Q1,00 | PCWC |            |
|  19 |                    TABLE ACCESS FULL| T2       | 10000 |   126K|     3   (0)| 00:00:01 |  Q1,00 | PCWP |            |
|  20 |                 PX RECEIVE          |          | 20000 |   253K|     5   (0)| 00:00:01 |  Q1,04 | PCWP |            |
|  21 |                  PX SEND HASH       | :TQ10001 | 20000 |   253K|     5   (0)| 00:00:01 |  Q1,01 | P->P | HASH       |
|  22 |                   PX BLOCK ITERATOR |          | 20000 |   253K|     5   (0)| 00:00:01 |  Q1,01 | PCWC |            |
|  23 |                    TABLE ACCESS FULL| T1       | 20000 |   253K|     5   (0)| 00:00:01 |  Q1,01 | PCWP |            |
|* 24 |                HASH JOIN            |          | 20000 |   507K|     8   (0)| 00:00:01 |  Q1,04 | PCWP |            |
|  25 |                 PX RECEIVE          |          | 10000 |   126K|     3   (0)| 00:00:01 |  Q1,04 | PCWP |            |
|  26 |                  PX SEND HASH       | :TQ10002 | 10000 |   126K|     3   (0)| 00:00:01 |  Q1,02 | P->P | HASH       |
|  27 |                   PX BLOCK ITERATOR |          | 10000 |   126K|     3   (0)| 00:00:01 |  Q1,02 | PCWC |            |
|  28 |                    TABLE ACCESS FULL| T2       | 10000 |   126K|     3   (0)| 00:00:01 |  Q1,02 | PCWP |            |
|  29 |                 PX RECEIVE          |          | 20000 |   253K|     5   (0)| 00:00:01 |  Q1,04 | PCWP |            |
|  30 |                  PX SEND HASH       | :TQ10003 | 20000 |   253K|     5   (0)| 00:00:01 |  Q1,03 | P->P | HASH       |
|  31 |                   PX BLOCK ITERATOR |          | 20000 |   253K|     5   (0)| 00:00:01 |  Q1,03 | PCWC |            |
|  32 |                    TABLE ACCESS FULL| T1       | 20000 |   253K|     5   (0)| 00:00:01 |  Q1,03 | PCWP |            |
|  33 |       PX BLOCK ITERATOR             |          | 94911 |  3522K|    26   (0)| 00:00:01 |  Q1,06 | PCWC |            |
|  34 |        TABLE ACCESS FULL            | T        | 94911 |  3522K|    26   (0)| 00:00:01 |  Q1,06 | PCWP |            |
-----------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   5 - access("SRC"."T1"="DEST"."C1")
  15 - access("T1"."C1"="T2"."C1")
  24 - access("T1"."C1"="T2"."C1")

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)
   - Degree of Parallelism is 4 because of hint
   - PDML is disabled in current session

The most important parts of the execution plan below:

As the tables has no statistics, Oracle has used dynamic sampling.
The degree used in 4 due to the hing.
Parallel DML is not enabled at session level, therefore the update resulting of the merge runs in noparallel.
Oracle builds an VIEW dynamically to join the two datasets of the union, as a result of one of them is running in parallel, the CBO runs in parallel the second one even though it has no hints.
In a USE_HASH hint use always as driving table the smaller table. Normally the CBO will always do that, so I would recommend to carefully use the USE_HASH hint, because if the smaller table grows to a point that is even greater than the second one, your hint will produce a lot of performance degradation.

Hope it clarifies.