CODEWITHSUNDEEP
sqloracle-databasehint
neha
neha

Reputation: 61

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

Upvotes: 1

Views: 2366

Answers (1)

Roberto Hernandez
Roberto Hernandez

Reputation: 8518

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.

Upvotes: 1

Related Questions