2012-04-30

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...

No comments:

Post a Comment

About Me

My photo
I am Timo Raitalaakso. I have been working since 2001 at Solita Oy as a Senior Database Specialist. I have received Oracle ACE nomination. My main focus is on projects involving Oracle database. In this Rafu on db blog I write some interesting issues that evolves from my interaction with databases. Mainly Oracle.