Partition wise merge -continued

Continuing the Partition wise merge post. Putting more lines to target and creating a bitmap index. The note last was that the plan strategy was nested loops. Indexing the access path to tgt at the end of plan makes possibly the execution do less.

alter session set statistics_level = all;

create bitmap index tgt_cust_bidx on tgt(cust) local;


insert into tgt
select level, level, trunc(sysdate), level 
from dual 
connect by level < 10000;


exec dbms_stats.gather_table_stats(user,'TGT');

exec dbms_stats.gather_table_stats(user,'STG');


merge /*+index (tgt tgt_cust_bidx)*/into tgt 
using (select cust, prd, dt, num from stg) sou 
   on (tgt.dt=sou.dt and tgt.cust=sou.cust and tgt.prd=sou.prd)
when matched then update set tgt.num=tgt.num+sou.num
when not matched then insert (tgt.cust,tgt.prd,tgt.dt,tgt.num)
values(sou.cust,sou.cust,sou.dt,sou.num)
;

select * from table(dbms_xplan.display_cursor(format=>'allstats last +partition'));

------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                | Name          | Starts | E-Rows | Pstart| Pstop | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------------------------------------------
|   0 | MERGE STATEMENT                          |               |      1 |        |       |       |      0 |00:00:00.10 |      26 |
|   1 |  MERGE                                   | TGT           |      1 |        |       |       |      0 |00:00:00.10 |      26 |
|   2 |   PX COORDINATOR                         |               |      1 |        |       |       |      4 |00:00:00.10 |      22 |
|   3 |    PX SEND QC (RANDOM)                   | :TQ10000      |      0 |      2 |       |       |      0 |00:00:00.01 |       0 |
|   4 |     VIEW                                 |               |      0 |        |       |       |      0 |00:00:00.01 |       0 |
|   5 |      NESTED LOOPS OUTER                  |               |      0 |      2 |       |       |      0 |00:00:00.01 |       0 |
|   6 |       PX BLOCK ITERATOR                  |               |      0 |      2 |     1 |     4 |      0 |00:00:00.01 |       0 |
|*  7 |        TABLE ACCESS FULL                 | STG           |      0 |      2 |     1 |     4 |      0 |00:00:00.01 |       0 |
|   8 |       PARTITION RANGE ITERATOR           |               |      0 |      1 |   KEY |   KEY |      0 |00:00:00.01 |       0 |
|   9 |        PARTITION HASH ITERATOR           |               |      0 |      1 |   KEY |   KEY |      0 |00:00:00.01 |       0 |
|* 10 |         TABLE ACCESS BY LOCAL INDEX ROWID| TGT           |      0 |      1 |       |       |      0 |00:00:00.01 |       0 |
|  11 |          BITMAP CONVERSION TO ROWIDS     |               |      0 |        |       |       |      0 |00:00:00.01 |       0 |
|* 12 |           BITMAP INDEX SINGLE VALUE      | TGT_CUST_BIDX |      0 |        |       |       |      0 |00:00:00.01 |       0 |
------------------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   7 - access(:Z>=:Z AND :Z<=:Z)
  10 - filter(("TGT"."PRD"="PRD" AND "TGT"."DT"="DT"))
  12 - access("TGT"."CUST"="CUST")
  

It was possible to add partition information to the format of display_cursor How about putting more lines to the source table? Next post about that...