Create table

[kiranrajenimbalkar]

Hi,
I have to create table from one sql but its taking long time for creating the table so please provide the solution ASAP

CREATE TABLE DN_UNCOMMON NOLOGGING AS
SELECT *
  FROM DR_CONTRIBUTION_DN dn
 WHERE NOT EXISTS (
          SELECT *
            FROM DR_CONTRIBUTION_PS ps
           WHERE (dn.ID = ps.ri_donor_id
                  AND dn.idtype = ps.ri_donor_type
                  AND dn.giftseqnum = ps.doc_seq_nbr
                  AND ROUND (dn.giftamt) = ROUND (ps.monetary_amount_up)
                  AND TRUNC (dn.batchdt) = TRUNC (ps.accounting_dt))
                  OR (dn.ID = ps.ri_donor_id
                  AND dn.idtype = ps.ri_donor_type
                  AND dn.psbusinessunit = ps.deposit_bu
                  AND dn.psdepositid = ps.deposit_id
                  AND dn.pspymntseqnum = ps.payment_seq_num
                  AND dn.psdstseqnum = ps.dst_seq_num
                  AND ROUND (dn.giftamt) = ROUND (ps.monetary_amount_up)
                  AND TRUNC (dn.batchdt) = TRUNC (ps.accounting_dt)))
 

This sql query contains two tables DR_CONTRIBUTION _DN(Records 7665067)
and second table contains DR_CONTRIBUTION _PS(Records 10025191)

Please provide me the solution for creating this table fast.

Best Regards,
Kiran

[magicwand]

Kirian,

could you cut/paste the following code into a SQL*Plus session and post the result ?

set autotrace on
SELECT * 
  FROM DR_CONTRIBUTION_DN dn 
 WHERE NOT EXISTS ( 
          SELECT * 
            FROM DR_CONTRIBUTION_PS ps 
           WHERE (dn.ID = ps.ri_donor_id 
                  AND dn.idtype = ps.ri_donor_type 
                  AND dn.giftseqnum = ps.doc_seq_nbr 
                  AND ROUND (dn.giftamt) = ROUND (ps.monetary_amount_up) 
                  AND TRUNC (dn.batchdt) = TRUNC (ps.accounting_dt)) 
                  OR (dn.ID = ps.ri_donor_id 
                  AND dn.idtype = ps.ri_donor_type 
                  AND dn.psbusinessunit = ps.deposit_bu 
                  AND dn.psdepositid = ps.deposit_id 
                  AND dn.pspymntseqnum = ps.payment_seq_num 
                  AND dn.psdstseqnum = ps.dst_seq_num 
                  AND ROUND (dn.giftamt) = ROUND (ps.monetary_amount_up) 
                  AND TRUNC (dn.batchdt) = TRUNC (ps.accounting_dt))) 
and rownum < 1;
 

[kiranrajenimbalkar]

Hi
Above SQL giving no rows..
I want all records insert into that created table DN_UNCOMMON.

Please provide me the solution that how to create the above mentioned table.

[magicwand]

Hi,

I didn't want the rows (that's the reason for the rownum < 1).

The important part is the "set autotrace on" which should give you the execution plan ...