1 Reply Latest reply: May 26, 2014 8:14 PM by Top.Gun RSS

    insert taking too long, but select is quick

    Jr.raj

      Hi guys,

       

      i am new to performance tuning.

       

      i am trying to insert to table, select from other table.

      select is quick, but insert is taking too long

      oracle version is 11.2.0.4

       

      query is

       

      INSERT INTO PERSON_DUPLICATE_BATCH (ID_PERSON,ID_TOWN,CD_SEARCH_TYPE,NM_LAST,NM_FIRST,NM_MID,DT_BIRTH,CD_STATUS,CD_STAT_REASON,ID_DMV,ID_SSN,ID_USER ,TM_STAMP)

      SELECT ID_PERSON,ID_TOWN,'FDS',NM_LAST,NM_FIRST,NM_MID,DT_BIRTH,CD_STATUS,CD_STAT_REASON,ID_DMV,ID_SSN,'SYSTEM',DATE '2014-05-23' FROM PERSON WHERE ID_PERSON IN (SELECT DISTINCT V3.ID_PERSON FROM ( SELECT NM_FIRST,DT_BIRTH,id_ssn FROM (SELECT NM_FIRST,DT_BIRTH,MOD(TRUNC(To_NUMBER(id_ssn)), 10000) id_ssn,LENGTH(ID_SSN) FROM PERSON V1 WHERE LENGTH(ID_SSN) IN (9,4) AND COALESCE(cd_stat_reason,'NA') NOT IN ('DUP','PVR') AND NOT EXISTS (SELECT 'Z' FROM PERSON_DUPLICATE D1 WHERE D1.ID_FROM_PERSON=V1.ID_PERSON) ) V GROUP BY NM_FIRST,DT_BIRTH,id_ssn HAVING COUNT(*)>1 ) V2 JOIN (SELECT ID_PERSON,NM_FIRST,DT_BIRTH,MOD(TRUNC(To_NUMBER(id_ssn)), 10000) id_ssn FROM PERSON V2 WHERE LENGTH(ID_SSN) IN (9,4) AND COALESCE(cd_stat_reason,'NA') NOT IN ('DUP','PVR') AND NOT EXISTS (SELECT 'Z' FROM PERSON_DUPLICATE_BATCH D WHERE D.ID_PERSON=V2.ID_PERSON) AND NOT EXISTS (SELECT 'Z' FROM PERSON_DUPLICATE D1 WHERE D1.ID_FROM_PERSON=V2.ID_PERSON)) V3 ON V3.NM_FIRST=V2.NM_FIRST AND V3.DT_BIRTH=V2.DT_BIRTH AND V2.id_ssn= V3.id_ssn)

       

      Thanks & Regards

      Raj