CREATE TABLE prod_vendor_record (
  vendor_record_seq_no   NUMBER       NOT NULL,
  study_seq_no           NUMBER       NOT NULL,
  vendor_subject_seq_no  NUMBER       NULL,
  control_dataset_seq_no NUMBER       NOT NULL,
  checksum               NUMBER       NOT NULL,
  processing_flag        VARCHAR2(1)  NULL,
  transaction_user       VARCHAR2(30) NOT NULL,
  transaction_src        VARCHAR2(30) NOT NULL,
  transaction_dt         DATE         NOT NULL,
  transaction_type       VARCHAR2(1)  NOT NULL,
  key_hash               NUMBER(10,0) NULL,
  key_col_val            CLOB         NULL
)


CREATE TABLE prod_temp_vendor_record_20000 (
  rownumber            NUMBER         NULL,
  vendor_record_seq_no NUMBER         NULL,
  checksum             NUMBER         NULL,
  transaction_type     VARCHAR2(1)    NULL,
  error_flag           VARCHAR2(1)    NULL,
  error_code           VARCHAR2(256)  NULL,
  recordstring         VARCHAR2(4000) NULL,
  key_hash             NUMBER(10,0)   NULL,
  key_col_val          CLOB           NULL
)
/

insert into prod_temp_vendor(vendor_record_seq_no,checksum,rownumber,transaction_type,iu_flag)
select vr.vendor_record_seq_no, tvr.checksum, tvr.rownumber, tvr.transaction_type, 'U' from
prod_vendor_record vr, prod_temp_vendor_record_20000 tvr
where vr.study_seq_no=25707 
and vr.control_dataset_seq_no=3910
and vr.key_hash=tvr.key_hash
and dbms_lob.compare(vr.key_col_val, tvr.key_col_val) = 0
and tvr.error_flag is null;

CREATE INDEX idx_prod_vendor_record
  ON prod_vendor_record (
    study_seq_no,
    control_dataset_seq_no,
    key_hash
  )
/

--------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation               | Name                          | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT        |                               |       |       |  5168 (100)|          |        |      |            |
|   1 |  PX COORDINATOR         |                               |       |       |            |          |        |      |            |
|   2 |   PX SEND QC (RANDOM)   | :TQ10002                      |   212 |   445K|  5168   (1)| 00:01:03 |  Q1,02 | P->S | QC (RAND)  |
|*  3 |    HASH JOIN BUFFERED   |                               |   212 |   445K|  5168   (1)| 00:01:03 |  Q1,02 | PCWP |            |
|   4 |     BUFFER SORT         |                               |       |       |            |          |  Q1,02 | PCWC |            |
|   5 |      PX RECEIVE         |                               | 21168 |    41M|   278   (1)| 00:00:04 |  Q1,02 | PCWP |            |
|   6 |       PX SEND HASH      | :TQ10000                      | 21168 |    41M|   278   (1)| 00:00:04 |        | S->P | HASH       |
|*  7 |        TABLE ACCESS FULL| PROD_TEMP_VENDOR_RECORD_20000 | 21168 |    41M|   278   (1)| 00:00:04 |        |      |            |
|   8 |     PX RECEIVE          |                               |  1899K|   197M|  4886   (1)| 00:00:59 |  Q1,02 | PCWP |            |
|   9 |      PX SEND HASH       | :TQ10001                      |  1899K|   197M|  4886   (1)| 00:00:59 |  Q1,01 | P->P | HASH       |
|  10 |       PX BLOCK ITERATOR |                               |  1899K|   197M|  4886   (1)| 00:00:59 |  Q1,01 | PCWC |            |
|* 11 |        TABLE ACCESS FULL| PROD_VENDOR_RECORD            |  1899K|   197M|  4886   (1)| 00:00:59 |  Q1,01 | PCWP |            |
--------------------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - access("VR"."KEY_HASH"="TVR"."KEY_HASH")
       filter("DBMS_LOB"."COMPARE"("VR"."KEY_COL_VAL","TVR"."KEY_COL_VAL")=0)
   7 - filter("TVR"."ERROR_FLAG" IS NULL)
  11 - access(:Z>=:Z AND :Z<=:Z)
       filter(("VR"."STUDY_SEQ_NO"=25707 AND "VR"."CONTROL_DATASET_SEQ_NO"=3910))
 
Note
-----
   - dynamic sampling used for this statement 

INSERT /*+ NOPARALLEL(prod_temp_vendor) */ INTO prod_temp_vendor 
                                                (vendor_record_seq_no, 
                                                 checksum, 
                                                 rownumber, 
                                                 transaction_type, 
                                                 iu_flag) 
SELECT /*+ NOPARALLEL(vr) NOPARALLEL(tvr) */ vr.vendor_record_seq_no, 
                                             tvr.checksum, 
                                             tvr.rownumber, 
                                             tvr.transaction_type, 
                                             'U' 
FROM   prod_vendor_record vr, 
       prod_temp_vendor_record_20000 tvr 
WHERE  vr.study_seq_no = 25707 
       AND vr.control_dataset_seq_no = 3910 
       AND vr.key_hash = tvr.key_hash 
       AND dbms_lob.Compare(vr.key_col_val, tvr.key_col_val) = 0 
       AND tvr.error_flag IS NULL; 

SELECT COUNT(*)
FROM   prod_vendor_record vr, 
       prod_temp_vendor_record_20000 tvr 
WHERE  vr.study_seq_no = 25707 
       AND vr.control_dataset_seq_no = 3910 
       AND vr.key_hash = tvr.key_hash 
       AND dbms_lob.Compare(vr.key_col_val, tvr.key_col_val) = 0 
       AND tvr.error_flag IS NULL; 

SELECT COUNT(*)
FROM   prod_vendor_record vr, 
       prod_temp_vendor_record_20000 tvr 
WHERE  vr.study_seq_no = 25707 
       AND vr.control_dataset_seq_no = 3910 
       AND dbms_lob.Compare(vr.key_col_val, tvr.key_col_val) = 0 
       AND tvr.error_flag IS NULL;

SELECT COUNT(*)
FROM   prod_vendor_record vr, 
       prod_temp_vendor_record_20000 tvr 
WHERE  vr.study_seq_no = 25707 
       AND vr.control_dataset_seq_no = 3910 
       AND dbms_lob.Compare(vr.key_col_val, tvr.key_col_val) = 0 ;

SELECT COUNT(*)
FROM   prod_vendor_record vr, 
       prod_temp_vendor_record_20000 tvr 
WHERE  vr.study_seq_no = 25707 
       AND vr.control_dataset_seq_no = 3910;

SELECT COUNT(*)
FROM   prod_vendor_record vr, 
       prod_temp_vendor_record_20000 tvr 
WHERE  vr.study_seq_no = 25707 ;


SELECT COUNT(*)
FROM   prod_vendor_record vr;

SELECT COUNT(*)
FROM       prod_temp_vendor_record_20000 tvr ;