1   INSERT INTO temp1
  2   ( Col1, col2,col3, col4,col5,col6)
  3   SELECT a.col1, a.col2, 709, 'N', SYSDATE, SYSDATE
  4  FROM table1 a, TABLE2 b
  5  WHERE a.Col1 = b.col1
  6  AND a.col2 = 'N'
  7* AND b.col2 = 'N'
21:10:27 SQL> /

434009 rows created.

Elapsed: 00:00:04.87

Execution Plan
----------------------------------------------------------
   0      INSERT STATEMENT Optimizer=CHOOSE (Cost=222 Card=434009 Byte
          s=4774099)

   1    0   NESTED LOOPS* (Cost=222 Card=434009 Bytes=4774099)         :Q483300
                                                                       0

   2    1     TABLE ACCESS* (FULL) OF 'TABLE1' (Cost=222 C :Q483300
          ard=434009 Bytes=3472072)                                    0

   3    1     INDEX* (RANGE SCAN) OF 'IDX2_TABLE2' (NON-UNIQUE)   :Q483300
                                                                       0



   1 PARALLEL_TO_SERIAL            SELECT /*+ ORDERED NO_EXPAND USE_NL(A2) INDE
                                   X(A2 "IDX2_TABLE2") */ A1.C0,A1

   2 PARALLEL_COMBINED_WITH_PARENT
   3 PARALLEL_COMBINED_WITH_PARENT


Statistics
----------------------------------------------------------
        163  recursive calls
      12322  db block gets
       4593  consistent gets
       2309  physical reads
   18982212  redo size
        541  bytes sent via SQL*Net to client
        815  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          6  sorts (memory)
          0  sorts (disk)
     434009  rows processed

-------------------------------------------------------------------------------------

with the composite primary key of col1,col2,col3

Execution Plan
----------------------------------------------------------
   0      INSERT STATEMENT Optimizer=CHOOSE (Cost=222 Card=434009 Byte
          s=4774099)

   1    0   NESTED LOOPS* (Cost=222 Card=434009 Bytes=4774099)         :Q484100
                                                                       0

   2    1     TABLE ACCESS* (FULL) OF 'TABLE1' (Cost=222 C :Q484100
          ard=434009 Bytes=3472072)                                    0

   3    1     INDEX* (RANGE SCAN) OF 'IDX2_TABLE2' (NON-UNIQUE)   :Q484100
                                                                       0



   1 PARALLEL_TO_SERIAL            SELECT /*+ ORDERED NO_EXPAND USE_NL(A2) INDE
                                   X(A2 "IDX2_TABLE2") */ A1.C0,A1

   2 PARALLEL_COMBINED_WITH_PARENT
   3 PARALLEL_COMBINED_WITH_PARENT


Statistics
----------------------------------------------------------
        318  recursive calls
     240382  db block gets
      15473  consistent gets
       2308  physical reads
   88302036  redo size
        541  bytes sent via SQL*Net to client
        822  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
         10  sorts (memory)
          0  sorts (disk)
     434009  rows processed