Query running for more than 16 hrs for insertion operation

Database
Enthusiast

Query running for more than 16 hrs for insertion operation

Hi Friends,

I have one query as mentioned below through which we are inserting records into one the empty table(i.e. this table gets delete and reload everytime we perform the insertion). This query is a huge headche for us as it takes more than 16-17 hrs to insert the data from one of historical table(call_hist as mentioned in the below query). There are lots of substr functions used in the SELECT query as well as in the WHERE clause condition. Few months back, we tried to create a volatile table and inserted the result of one of the substr condition getting used in this same query thereby we were able to reduce the execution time by 1-2 hrs but isn't worth improving so far.

Call_hist table is of size around 250 GB and we are inserting the records from this table for the duration of around 4 yrs.

Can anyone please analyze the query if possible and suggest me some workaround that I can add for improving performance of query. Thanks in advance.

Query:

locking table jedi_cdw_db.call_hist for access

locking table jedi_agr_db.accs_meth_free for access
locking table jedi_cdw_db.CALL_CLASSIFICATION for access
insert into RIF_F_2_CALL_HIST_XCPT
( accs_meth_no
, call_start_dt
, call_start_tm
, accs_meth_id
, hstd_carr_pslctn_no
, acct_id
, prod_id
, co_party_id
, call_no
, call_no_no_dup_svc
, call_dur
, call_dur_no_dup_svc
, call_svc_type_cd
, call_rate_per_cd
, origtg_phone_no_elemt_id
, call_gross_rvnu_am
, call_estimated_rvnu_am
, src_id
, traf_type_id
, incoming_rt_cd
, outgoing_rt_cd
, call_accs_type_cd
, call_class_grp_id
, termntg_phone_no_elemt_id
, call_origtg_no
)
select
case
when CHst.prod_id = 1000005 then CHst.call_termntg_no

WHEN CHst.prod_id = 1000007 THEN CHst.call_termntg_no

when CHst.prod_id = 1000006 then CHst.call_origtg_no
when CHst.prod_id = 1000378 then CHst.call_termntg_no
when
(not CHst.call_dialed_no is null
and substring ( CHst.call_dialed_no from 1 for 5 )
in ('39166','39199','39702','39709','39840','39848','39892','39899','39891'))
then CHst.call_dialed_no
when
(not CHst.call_dialed_no is null
and substring ( CHst.call_dialed_no from 1 for 6 )
in ( '390878', '391266', '391277','391299','391248','391288','391260' ) )
then CHst.call_dialed_no
else CHst.call_termntg_no
end
, CHst.call_start_dt
, CHst.call_start_tm
, CHst.accs_meth_id
, CHst.hstd_carr_pslctn_no
, coalesce ( CHst.acct_id , 0 )
, coalesce ( CHst.prod_id , 0 )
, CHst.co_party_id
, coalesce ( CHst.call_record_no , 1 )
, cast ( ( case when
( coalesce ( CHst.call_dup_svc_in ,'0' ) ) in ('0',' ')
then (coalesce ( CHst.call_record_no , 1 ))
else 0
end ) as integer )
, coalesce ( CHst.call_dur , 0 )
, cast ( ( case when
( coalesce ( CHst.call_dup_svc_in ,'0') ) in ('0',' ')
then
( coalesce ( CHst.call_dur , 0 )
) else 0
end ) as integer )
, CHst.call_svc_type_cd
, CHst.call_rate_per_cd
, CHst.origtg_phone_no_elemt_id
, coalesce ( CHst.call_gross_rvnu_am , 0 )
, coalesce ( CHst.call_estimated_rvnu_am , 0 )
, CHst.src_id
, case
when CHst.prod_id = 1000005
then 12

when CHst.prod_id = 1000007
then 13

when CHst.prod_id = 1000006
then 78
when CHst.prod_id = 1000378
then 79
when ( not CHst.call_dialed_no is null
and substring ( CHst.call_dialed_no from 1 for 5 )
in ( '39166' , '39199' , '39702' , '39709' , '39840' , '39848' , '39892' , '39899','39891' ) )
then 6
when ( not CHst.call_dialed_no is null
and substring ( CHst.call_dialed_no from 1 for 6 )
in ( '390878' , '391266', '391277','391299','391248','391288' ,'391260' ) )
then 6
else 99
end
, CHst.incoming_rt_cd
, CHst.outgoing_rt_cd
, CHst.call_accs_type_cd
, CCls.call_class_grp_id
, CHst.termntg_phone_no_elemt_id
, CHst.call_origtg_no
from CALL_HIST CHst
left join CALL_CLASSIFICATION CCls
on CHst.call_class_id = CCls.call_class_id

left outer join route_hist rh
on CHst.outgoing_rt_cd = rh.rt_cd
and CHst.call_start_dt between rh.rt_eff_dt and rh.rt_end_dt

where CHst.call_start_dt >= 1120401

and (
( CHst.prod_id in ( 1000005 ) )

OR (CHst.prod_id = 1000007 and CHst.src_id = 6 and CHst.call_svc_type_cd = '5' and rh.party_id = 797 AND rh.rt_incoming_outgoing_in = 'ON')

or
( CHst.prod_id in (1000006) and src_id <> 6 )

or
( CHst.prod_id in (1000378) )

or
( not CHst.call_dialed_no is null and substring ( CHst.call_dialed_no from 1 for 5 )
in ( '39166' , '39199' , '39702' , '39709' , '39840' , '39848' , '39892' , '39899','39891' ))
or
( not CHst.call_dialed_no is null and substring ( CHst.call_dialed_no from 1 for 6 )
in ( '390878', '391266', '391277','391299','391248','391288','391260' ))

or

( CHst.call_dialed_no is null
and not CHst.call_termntg_no is null
and ( substring ( CHst.call_termntg_no from 1 for 5 )
in ( '39709' , '39702' , '39163' , '39164' , '39840' ,'39848')
Or (src_id =8 And Substring ( CHst.call_termntg_no From 1 For 5 ) ='39199')
)
)
)
OR
( CHst.call_dialed_no is null
and CHst.prod_id <> 1000005

AND NOT ( CHst.prod_id = 1000007 and CHst.src_id = 6 and CHst.call_svc_type_cd = '5' and rh.party_id = 797 AND rh.rt_incoming_outgoing_in = 'ON')

and not CHst.call_termntg_no is null
and CHst.call_termntg_no IN
( sel call_termntg_no From RIF_CALL_HIST_CTN_AMF
)
)
;

8 REPLIES
Junior Contributor

Re: Query running for more than 16 hrs for insertion operation

What's the PI of the target table, maybe a SET table plus many rows per value?

Which step is the one consuming most of the resouces, did you check QryLogStepsV?

Enthusiast

Re: Query running for more than 16 hrs for insertion operation

Hi,

The primary Index is defined on fields accs_meth_no ,call_start_dt ,call_start_tm on target table namely "RIF_F_2_CALL_HIST_XCPT". I had explain plan for this query accroding to which the total execution of the query should take around 5-6 hrs but in actuall run, this query is running for more than 15-16 hrs. As per my observation, there are below two joins-

 from         CALL_HIST      CHst

left  join    CALL_CLASSIFICATION  CCls

    on       CHst.call_class_id  =    CCls.call_class_id ( The estimated time for this step is 2 hours and 11 minutes)

 

left outer join route_hist rh

on CHst.outgoing_rt_cd = rh.rt_cd 

and CHst.call_start_dt between  rh.rt_eff_dt  and rh.rt_end_dt

 

Which are taking more time. Also, there is one where condition as below which seems to be  creating some more bad performance.

 

 And  Substring ( CHst.call_dialed_no

From 1 For 5 )   In ( '39166' , '39199' , '39702' , '39709' ,  '39840' , '39848' ,  '39892' , '39899','39891'  ))

 Or                  ( Not  CHst.call_dialed_no  is  Null

 And  Substring ( CHst.call_dialed_no

From 1 For 6 )   In ( '390878', '391266', '391277','391299','391248','391288','391260'  ))

 Or     (  CHst.call_dialed_no is Null

 And Not CHst.call_termntg_no is Null

 And (  Substring ( CHst.call_termntg_no

From 1 For 5 )                 In ( '39709' , '39702' , '39163' , '39164' , '39840' ,'39848')

 Or (src_id =8

 And Substring ( CHst.call_termntg_no

From 1 For 5 ) ='39199')       )    ) )

 

Thanks & Regards,

Sachin

 

Teradata Employee

Re: Query running for more than 16 hrs for insertion operation

Whats the PI of source table? Also, are there any stats collected on underlying tables?

And as Dieter asked, can you access check QryLogStepsV or viewpoint and see which actual step takes most of the time.

Also, share EXPLAIN plan for this INSERT.

Highlighted
Enthusiast

Re: Query running for more than 16 hrs for insertion operation

Hi Adeel,

The PI of source table(call_hist) is -  UNIQUE PRIMARY INDEX XPKRevenue ( accs_meth_id ,switch_id ,call_start_dt ,

call_start_tm ,call_record_seq_no ).Also there is a secondary index INDEX nusi1 ( accs_meth_id ,call_start_dt ) defined on this table.

There are collect stats applied on the source table. In additon I used diagnostic helpstat command to know for any missing stats and have tried applying them too but didn't get mych benefit.

We are using teradata V5 version so I have checked the query in PMON tool and observed the steps which are taking much time. I have already mentioned those details in my last post.

Also, can you share any email id of yours to me to send you the explain plan since the number of lines are more(around 940 lines).

Thanks & Regards,

Sachins

Enthusiast

Re: Query running for more than 16 hrs for insertion operation

Sachins,

Can you post the table DDL for call hist, call class and route hist?

There are several strategies that may significantly reduce your processing time.  Eliminating the substrings in the in where clause is a biggie.  You could add additional columns to the table, update them with the appropriate substrings, collect stats on the new columns and change the join to refer to those columns.  

You could paritition the call history table by call start date and potentially prod_id to get some dpe involved reducing the data reads as well.

You also have the option of a STJI on the route history table if rt_cd isn't a single column PI.

One last word of caution, the "time" labels in the explain plan bear no relation to clocks, the passage of time or elapsed time.  If the explain plan says "2 hours 11 minutes" that is a measure of the cost of the query NOT an expectation of elapsed time.

Cheers!

Enthusiast

Re: Query running for more than 16 hrs for insertion operation

Hi,

Thanks for the information shared. Please find below the DDLs for the tables you asked.

DDL for call_hist:

CREATE SET TABLE jedi_cdw_db.call_hist ,NO FALLBACK ,

     NO BEFORE JOURNAL,

     NO AFTER JOURNAL,

     CHECKSUM = DEFAULT

     (

      accs_meth_id INTEGER TITLE 'ACCESS METHOD ID' NOT NULL,

      switch_id INTEGER TITLE 'SWITCH ID' NOT NULL,

      call_start_dt DATE FORMAT 'DD-MM-YYYY' TITLE 'CALL START DATE' NOT NULL,

      call_start_tm FLOAT FORMAT '99:99:99' TITLE 'CALL START TIME' NOT NULL,

      call_record_seq_no INTEGER TITLE 'CALL RECORD SEQUENCE NO' NOT NULL,

      subsctn_id INTEGER TITLE 'SUBSCRIPTION ID' COMPRESS ,

      offrng_id INTEGER TITLE 'OFFERING ID' COMPRESS ,

      prod_id INTEGER TITLE 'PRODUCT ID' COMPRESS ,

      bill_stmt_id INTEGER TITLE 'BILLING STATEMENT ID' COMPRESS ,

      bill_stmt_ref_id INTEGER TITLE 'BILLING STATEMENT REF ID' COMPRESS ,

      bill_stmt_ref_rsts_id SMALLINT TITLE 'BILLING STATEMENT REF RSTS ID' COMPRESS ,

      call_end_dt DATE FORMAT 'DD-MM-YYYY' TITLE 'CALL END DATE' COMPRESS ,

      call_end_tm FLOAT FORMAT '99:99:99' TITLE 'CALL END TIME' COMPRESS ,

      call_dur INTEGER TITLE 'CALL DURATION' COMPRESS ,

      call_bill_dur INTEGER TITLE 'CALL BILL DURATION' COMPRESS ,

      dur_unit_of_measure_id INTEGER TITLE 'DURATION UNIT OF MEASURE ID' COMPRESS ,

      call_type_cd CHAR(2) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'CALL TYPE CD' COMPRESS ,

      call_svc_type_cd CHAR(6) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'CALL SERVICE TYPE CD' COMPRESS ,

      call_enhncd_svc_type_cd CHAR(2) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'CALL ENHANCED SERVICE TYPE CD' COMPRESS ,

      call_spec_svc_type_cd CHAR(6) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'CALL SPECIAL SERVICE TYPE CD' COMPRESS ,

      call_rate_per_cd CHAR(2) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'CALL RATE PERIOD CD' COMPRESS ,

      call_gross_rvnu_am DECIMAL(18,6) TITLE 'CALL GROSS REVENUE AMT' COMPRESS ,

      call_gross_rvnu_dt DATE FORMAT 'DD-MM-YYYY' TITLE 'CALL GROSS REVENUE DATE' COMPRESS ,

      call_compl_type_cd CHAR(2) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'CALL COMPLETION TYPE CD' COMPRESS ,

      call_fault_cd DECIMAL(5,0) TITLE 'CALL FAULT CD' COMPRESS ,

      call_inbnd_outbnd_in CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'CALL INBOUND OUTBOUND IND' COMPRESS ,

      call_curr_cust_in CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'CALL CURRENT CUSTOMER IND' COMPRESS ,

      call_inter_intra_st_in CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'CALL INTER/INTRA STATE IND' COMPRESS ,

      call_inter_intra_lata_in CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'CALL INTER/INTRA LATA IND' COMPRESS ,

      call_intnl_dom_call_in CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'CALL INTERNATIONAL/DOMESTIC CALL IND' COMPRESS ,

      call_incollect_tot_chrg_am DECIMAL(18,6) TITLE 'CALL INCOLLECT TOTAL CHARGE AMT' COMPRESS ,

      call_outcollect_tot_chrg_am DECIMAL(18,6) TITLE 'CALL OUTCOLLECT TOTAL CHARGE AMT' COMPRESS ,

      monty_unit_of_measure_id INTEGER TITLE 'MONETARY UNIT OF MEASURE ID' COMPRESS ,

      termntg_phone_no_elemt_id INTEGER TITLE 'TERMINATING PHONE NO ELEMENT ID' COMPRESS ,

      call_termntg_no VARCHAR(35) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'CALL TERMINATING NO',

      origtg_phone_no_elemt_id INTEGER TITLE 'ORIGINATING PHONE NO ELEMENT ID' COMPRESS ,

      call_origtg_no VARCHAR(35) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'CALL ORIGINATING NO',

      call_dialed_no VARCHAR(35) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'CALL DIALED NO',

      call_wirels_call_in CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'CALL WIRELESS CALL IND' COMPRESS ,

      airtm_chrg_am DECIMAL(18,6) TITLE 'AIRTIME CHARGE AMT' COMPRESS ,

      wirels_airtm_per_cd CHAR(6) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'WIRELESS AIRTIME PERIOD CD' COMPRESS ,

      wirels_landtm_per_cd CHAR(6) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'WIRELESS LANDTIME PERIOD CD' COMPRESS ,

      wirels_call_landtm_chrg_am DECIMAL(18,6) TITLE 'WIRELESS CALL HISTORY LANDTIME CHARGE AMT' COMPRESS ,

      wirels_call_handoff_cnt INTEGER TITLE 'WIRELESS CALL HISTORY HANDOFF COUNT' COMPRESS ,

      wirels_roamer_type_cd CHAR(6) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'WIRELESS ROAMER TYPE CD' COMPRESS ,

      wirels_call_roamer_surchrg_am DECIMAL(18,6) TITLE 'WIRELESS CALL HISTORY  ROAMER SURCHARGE AMT' COMPRESS ,

      wirels_call_incolct_rom_chg_am DECIMAL(18,6) TITLE 'WIRELESS CALL HISTORY INCOLLECT ROAMER CHARGE AMT' COMPRESS ,

      orig_cell_site_id INTEGER TITLE 'ORIGINAL CELL SITE ID' COMPRESS ,

      term_cell_site_id INTEGER TITLE 'TERMATING CELL SITE ID' COMPRESS ,

      call_origtg_crypt_no VARCHAR(35) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'CALL ORIGINATING CRYPT NO',

      call_termntg_crypt_no VARCHAR(35) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'CALL TERMINATING CRYPT NO',

      call_dup_svc_in CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'CALL DUP SERVICE IND' COMPRESS ,

      call_dup_local_loop_in CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'CALL DUP LOCAL LOOP IND' COMPRESS ,

      call_estimated_rvnu_am DECIMAL(18,6) TITLE 'CALL ESTIMATED REVENUE AMT' COMPRESS ,

      call_estimated_rvnu_dt DATE FORMAT 'DD-MM-YYYY' TITLE 'CALL ESTIMATED REVENUE DATE' COMPRESS ,

      acct_id INTEGER TITLE 'ACCT ID' COMPRESS ,

      co_party_id INTEGER TITLE 'COMPANY PARTY ID' COMPRESS ,

      call_accs_type_cd BYTEINT TITLE 'CALL ACCESS TYPE CD' COMPRESS ,

      call_egrs_type_cd BYTEINT TITLE 'CALL EGRS TYPE CD' COMPRESS ,

      call_tele_svc_cd BYTEINT TITLE 'CALL TELEPHONE SERVICE CD' COMPRESS ,

      call_call_id INTEGER TITLE 'CALL CALL ID' COMPRESS ,

      call_record_no SMALLINT TITLE 'CALL RECORD NO' DEFAULT 1  COMPRESS ,

      call_tt_no SMALLINT TITLE 'CALL TT Number' COMPRESS ,

      call_cost_cd BYTEINT TITLE 'CALL COST CD' COMPRESS ,

      call_a_subscriber_no VARCHAR(35) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'CALL A SUBSCRIBER NO',

      call_b_subscriber_no VARCHAR(35) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'CALL B SUBSCRIBER NO',

      outgoing_rt_cd CHAR(7) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'OUTGOING ROUTE CD' COMPRESS ,

      call_origin_outgoing_rt_in CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'CALL ORIGIN OUTGOING ROUTE IND' COMPRESS ,

      incoming_rt_cd CHAR(7) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'INCOMING ROUTE CD' COMPRESS ,

      call_origin_incoming_rt_in CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'CALL ORIGIN INCOMING ROUTE IND' COMPRESS ,

      call_cause_out BYTEINT TITLE 'CALL Cause Output' COMPRESS ,

      call_jurisdiction_cd INTEGER TITLE 'CALL JURISDICTION CD' COMPRESS ,

      call_internal_rt_cd CHAR(6) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'CALL INTERNAL ROUTE CD' COMPRESS ,

      call_elemt_id SMALLINT TITLE 'CALL ELEMENT ID' COMPRESS ,

      call_subscr_no INTEGER TITLE 'CALL SUBSCR NO' COMPRESS ,

      call_subscr_no_resets SMALLINT TITLE 'CALL SUBSCR NO RESETS' COMPRESS ,

      call_file_id INTEGER TITLE 'CALL FILE ID' COMPRESS ,

      call_class_id BYTEINT TITLE 'CALL CLASSIFICATION ID' COMPRESS ,

      call_stat_id BYTEINT TITLE 'CALL STATUS ID' COMPRESS ,

      incollect_call_stat_id BYTEINT TITLE 'INCOLLECT CALL STATUS ID' COMPRESS ,

      outcollect_call_stat_id BYTEINT TITLE 'OUTCOLLECT CALL STATUS ID' COMPRESS ,

      call_record_eff_dt DATE FORMAT 'DD-MM-YYYY' TITLE 'CALL RECORD EFFECTIVE DATE' COMPRESS ,

      call_record_eff_tm FLOAT FORMAT '99:99:99' TITLE 'CALL RECORD EFFECTIVE TIME' COMPRESS ,

      src_id BYTEINT TITLE 'SOURCE ID' COMPRESS ,

      arbor_instnc_cd CHAR(2) CHARACTER SET LATIN NOT CASESPECIFIC TITLE _UNICODE '004100520042004F005200200049004E005300540041004E004300450020000D0020002000200020002000430044'XC,

      call_auth_cd VARCHAR(24) CHARACTER SET LATIN NOT CASESPECIFIC,

      call_cli_type_in CHAR(2) CHARACTER SET LATIN NOT CASESPECIFIC TITLE _UNICODE '00430041004C004C00200043004C0049002000540059005000450020000D002000200020002000200049004E0044'XC COMPRESS ,

      hstd_carr_pslctn_no SMALLINT TITLE 'HOSTED CARRIER PRESELECTION NO' NOT NULL DEFAULT 0 ,

      call_ip_qn DECIMAL(18,0) TITLE 'BYTES QTY (es. GPRS, BBerry,ecc.)' COMPRESS ,

      call_evt_qn INTEGER TITLE 'EVENTS QTY (es. SMS, MMS, ecc.)' COMPRESS )

UNIQUE PRIMARY INDEX XPKRevenue ( accs_meth_id ,switch_id ,call_start_dt ,

call_start_tm ,call_record_seq_no )

PARTITION BY RANGE_N(call_start_dt  BETWEEN DATE '2001-04-01' AND DATE '2015-12-31' EACH INTERVAL '1' DAY ,

DATE '2016-01-01' AND DATE '2025-12-31' EACH INTERVAL '1' DAY )

INDEX nusi1 ( accs_meth_id ,call_start_dt );

DDL for Call Classification:

CREATE SET TABLE jedi_cdw_db.call_classification ,NO FALLBACK ,

     NO BEFORE JOURNAL,

     NO AFTER JOURNAL,

     CHECKSUM = DEFAULT

     (

      call_class_id BYTEINT TITLE 'CALL CLASSIFICATION ID' NOT NULL,

      call_class_grp_id BYTEINT TITLE 'CALL CLASSIFICATION GROUP ID',

      call_class_tl VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'CALL CLASSIFICATION TITLE',

      call_class_ds VARCHAR(100) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'CALL CLASSIFICATION DESC',

FOREIGN KEY ( call_class_grp_id ) REFERENCES JEDI_CDW_DB.CALL_CLASSIFICATION_GROUP ( call_class_grp_id ))

UNIQUE PRIMARY INDEX XPKCALL_CLASSIFICATION ( call_class_id );

DDL for ROUTE_HIST:

CREATE SET TABLE jedi_cdw_db.ROUTE_HIST ,NO FALLBACK ,

     NO BEFORE JOURNAL,

     NO AFTER JOURNAL,

     CHECKSUM = DEFAULT

     (

      rt_cd CHAR(7) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'ROUTE CD' NOT NULL,

      rt_direction_in CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'ROUTE DIRECTION IND' NOT NULL,

      rt_ds VARCHAR(100) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'ROUTE DESC',

      rt_record_eff_dt DATE FORMAT 'DD-MM-YYYY' TITLE 'ROUTE RECORD EFFECTIVE DATE' COMPRESS ,

      rt_record_upd_dt DATE FORMAT 'DD-MM-YYYY' TITLE 'ROUTE RECORD UPDATE DATE' COMPRESS ,

      party_id INTEGER TITLE 'PARTY ID' COMPRESS ,

      rt_incoming_outgoing_in CHAR(3) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'ROUTE INCOMING OUTGOING IND' COMPRESS ,

      rt_connection_cd CHAR(6) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'ROUTE CONNECTION CD' COMPRESS ,

      codice_carrier CHAR(2) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'ROUTE CARRIER CD',

      cid_oracle VARCHAR(40) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'PARTY ORIGINAL CD',

      rt_eff_dt DATE FORMAT 'DD-MM-YYYY' TITLE 'ROUTE EFF DATE' NOT NULL,

      rt_end_dt DATE FORMAT 'DD-MM-YYYY' TITLE 'ROUTE END DATE' NOT NULL)

UNIQUE PRIMARY INDEX XPKROUTE ( rt_cd ,rt_direction_in ,rt_eff_dt );

Please let me know if you need some more information.

Thanks & Regards,

Sachins

Enthusiast

Re: Query running for more than 16 hrs for insertion operation

Sachins,

From what you've posted DDL wise, this doesn't look like it's optimized for Teradata.  You've got Unique Primary Indexes on all the tables, in TD, the PI is NOT the same as a PK.  The PI should define the most common access path to the data, in the case of call history, you should have a Non-Unique Primary Index of prod_id.  

If you have a composite PI, and don't use ALL of the columns to access the data, you don't get that smooth slick wonderful PI access that we all crave.

You should be using a USI to provide uniqueness protection. 

This guidance holds true for the other tables you provided as well.  The insert query refers to rt_cd and rt_eff_dt, but never references direction_in, change the PI to a NUPI with rd_cd, rt_eff_dt and use a USI with all three columns to provide a uniqueness constraint.

Second point

You're wasting a ton of system resources with SET tables and Hard RI.  Set tables require checking every row in the table against the incoming row to ensure that there are no duplicate rows, if your unique secondary indices are in place and on point, you're already protecting against that.  The math is pretty small for small tables, but as Dieter Noeth notes in a classic post from 2012 in this forum, your overhead will look like this:

"11 Jul 2012

To insert a new row the system has to do a duplicate row check for all existing rows with the same RowHash value, i.e. all rows with the same PI value or another PI value which happens to hash the same.

And of course it's cumulative, to insert N rows with the same RowHash you'll need (N*(N-1)) / 2 checks:

10 rows -> 45 dup row checks

100 -> 4950

1000 -> 499500

The rule of thumb is:

If the number of rows per value is low (maybe up to a few hundred) and all rows fit on a single datablock it might be acceptable.

Dieter" <-- yes that post is GOLD!

 

If you're loading a couple of million rows, your overhead is astronomical with SET tables.  Fixing your PI's is the first step towards fixing this query.  NUPI's with USI's to protect uniqueness and you should see some pretty impressive improvements.  I would also give some serious thought to materializing the substringed values in a column of their own in the same table and replacing those functions in the predicate and join conditions.

 

Cheers!




Junior Contributor

Re: Query running for more than 16 hrs for insertion operation

I fully agree with VandeBergB that your UPIs are really bad & this might be a reason for bad performance, but it's definitely not Duplicate Row Checks, for UPIs there's hardly more than one row per PI :-)

QueryLog was introduced in V2R5 (are you really on a version which is > 10 years old?), so you should switch it on and check for details (PMon only displays a subset).