Archives of the TeradataForum

Message Posted: Tue, 24 Feb 2004 @ 08:57:09 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Hi,

I am trying to insert data into a table using the below query

Insert  Into A.TMP_T8_3
        (batch_no,
        seq_in_batch_no,
        Bill_ESA_Cd,
        Aprty
        )
Select
        TMP.batch_no,
        TMP.seq_in_batch_no,
        LSN.Orgt_ESA_Cd,
        LSN.SRVC_NO
From
        B.Table2 LSN
Inner Join
        A.TMP_T8_1 TMP
        On      LSN.accs_meth_id=TMP.accs_meth_id;

The select statement when executed seperately returns data (approx 10mil rows) in 10 minutes.But entire INSERT - SELECT is taking more than 10 hrs in execution.

Anybody let me know how can we reduce this insertion time.

Structure of tables are,

B.Table2
REPLACE VIEW B.Table2
AS LOCKING C.Table2
FOR ACCESS SELECT * FROM C.Table2;

C.Table2
CREATE SET TABLE C.Table2 ,NO FALLBACK ,
     NO BEFORE JOURNAL,
     NO AFTER JOURNAL
     (
      Accs_Meth_Id INTEGER TITLE 'Access Method Identifier' NOT NULL,
      Orgt_ESA_Id INTEGER TITLE 'Originating Exchange Service Area Identifier',
      Orgt_ESA_Ds VARCHAR(100) CHARACTER SET LATIN CASESPECIFIC TITLE 'Originating Exchange Service Area Description',
      Srvc_Tp_Cd CHAR(1) CHARACTER SET LATIN CASESPECIFIC TITLE 'Service Type Code',
      Srvc_Tp_Ds VARCHAR(100) CHARACTER SET LATIN CASESPECIFIC TITLE 'Service Type Description',
      Srvc_No VARCHAR(35) CHARACTER SET LATIN CASESPECIFIC TITLE 'Service Number',
      Orgt_ESA_Cd CHAR(4) CHARACTER SET LATIN CASESPECIFIC TITLE 'Originating Exchange Service Area Code',
      EPAC_Cd CHAR(6) CHARACTER SET LATIN CASESPECIFIC TITLE 'EPAC Code',
      Line_No CHAR(4) CHARACTER SET LATIN CASESPECIFIC TITLE 'Line Number',
      Extns1_Sufx CHAR(1) CHARACTER SET LATIN CASESPECIFIC TITLE 'Extension1 Suffix',
      Extns1_EPAC_Cd CHAR(6) CHARACTER SET LATIN CASESPECIFIC TITLE 'Extension1 EPAC Code',
      Extns1_Line_No CHAR(4) CHARACTER SET LATIN CASESPECIFIC TITLE 'Extension1 Line Number',
      Extns2_Sufx CHAR(1) CHARACTER SET LATIN CASESPECIFIC TITLE 'Extension2 Suffix',
      Extns2_Line_No CHAR(4) CHARACTER SET LATIN CASESPECIFIC TITLE 'Extension2 Line Number',
      Qlfng_Dt DATE FORMAT 'YYYY-MM-DD' TITLE 'Qualifying Date',
      Stat_Flag_Ind CHAR(1) CHARACTER SET LATIN CASESPECIFIC TITLE 'Status Flag Indicator',
      SAMIS_Srvc_Tp_Cd CHAR(1) CHARACTER SET LATIN CASESPECIFIC TITLE 'SAMIS Service Type Code',
      SAMIS_Srvc_Tp_Ds VARCHAR(100) CHARACTER SET LATIN CASESPECIFIC TITLE 'SAMIS Service Type Description',
      Src_Sys_Id INTEGER TITLE 'Source System Identifier',
      Src_Sys_Ds VARCHAR(100) CHARACTER SET LATIN CASESPECIFIC TITLE 'Source System Description')
PRIMARY INDEX ( Accs_Meth_Id );


A.TMP_T8_1
CREATE SET TABLE A.TMP_T8_1 ,NO FALLBACK ,
     NO BEFORE JOURNAL,
     NO AFTER JOURNAL
     (
      Batch_No INTEGER,
      Seq_In_Batch_No INTEGER,
      Accs_Meth_Id INTEGER,
      Bill_Subtn_ID INTEGER,
      Trmng_srvc_no VARCHAR(24) CHARACTER SET LATIN NOT CASESPECIFIC,
      Orgt_No CHAR(24) CHARACTER SET LATIN NOT CASESPECIFIC,
      Dt DATE FORMAT 'YYYY-MM-DD',
      Tm TIME(0) FORMAT 'hh:mi:ss',
      Call_Drtn DECIMAL(8,0),
      Destn CHAR(12) CHARACTER SET LATIN NOT CASESPECIFIC,
      Ofrng_id INTEGER,
      CNR_IND DECIMAL(11,0),
      UPS_ACCS_Cd CHAR(4) CHARACTER SET LATIN NOT CASESPECIFIC,
      CREV_STD DECIMAL(15,7) FORMAT '-9.9999999',
      Hrbnd_Id INTEGER,
      Call_Rate_Nm_Cd CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC,
      Rated_Wrln_Call_Dist_Grp_Cd CHAR(4) CHARACTER SET LATIN NOT CASESPECIFIC,
      Call_Srvc_Tp_Cd CHAR(8) CHARACTER SET LATIN NOT CASESPECIFIC,
      Drtn_Unit_Of_Msr_Cd CHAR(5) CHARACTER SET LATIN NOT CASESPECIFIC,
      Orig_Ntwk_Accs_Tp_Cd CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC,
      CREV_CNR DECIMAL(15,7) FORMAT '-9.9999999',
      Bill_Srvc_Tp_Cd CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC,
      Trmng_Srvc_Tp_Cd CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC)
UNIQUE PRIMARY INDEX ( Batch_No ,Seq_In_Batch_No );


A.TMP_T8_3
CREATE SET TABLE A.TMP_T8_3 ,NO FALLBACK ,
     NO BEFORE JOURNAL,
     NO AFTER JOURNAL
     (
      Batch_No INTEGER,
      Seq_In_Batch_No INTEGER,
      Bill_ESA_Cd CHAR(4) CHARACTER SET LATIN NOT CASESPECIFIC,
      Aprty VARCHAR(35) CHARACTER SET LATIN NOT CASESPECIFIC)
PRIMARY INDEX ( Batch_No ,Seq_In_Batch_No );

Regards,

Nitin Jain

	<Prev	Next>		<<First	<Prev	Next>	Last>>