Archives of the TeradataForum

Message Posted: Tue, 06 Jan 2004 @ 21:18:56 GMT

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

I have the following view

REPLACE VIEW OBC_MASTER.VW_RECONTACTS AS
LOCKING TABLE OBC_MASTER.LU_OBC_JOB_INFO FOR ACCESS
LOCKING TABLE OBC_MASTER.FA_OBC_MDN_MTD FOR ACCESS
LOCKING TABLE OBC_MASTER.LU_OBC_RCONTACT_JOB_ADDNL_INFO FOR ACCESS
LOCKING TABLE OBC_LOAD.NPA_NXX_OUTBOUND FOR ACCESS (
     SELECT L.OWNER_CARRIER_ID AS OCID,
            RECJOB.LOCATION_ID AS ORIG,
           (RECJOB.REPORTING_YEAR-2000)*12+RECJOB.REPORTING_MONTH AS MID,
            RECJOB.SCHEDULED_DATE, RECMTD.RUN_DATE,
            RECJOB.JOB_ID, RECJOB.CONTACT_METHOD,
            REC.ORIG_CONTACT_METHOD,
            REC.ORIG_METRIC_TYPE,
            TRIM (RECMTD.NPANXXX) || RECMTD.LAST_THREE_DIGIT AS MDN,
            RECMTD.CONFIG_ID AS CID,
            RECMTD.ACTIVITY_CODE AS ACT
       FROM OBC_MASTER.LU_OBC_RCONTACT_JOB_ADDNL_INFO REC
            INNER JOIN OBC_MASTER.LU_OBC_JOB_INFO RECJOB
         ON RECJOB.JOB_ID         = REC.JOB_ID
            INNER JOIN OBC_MASTER.FA_OBC_MDN_MTD RECMTD
         ON RECMTD.JOB_ID         = REC.JOB_ID
            INNER JOIN OBC_LOAD.NPA_NXX_OUTBOUND L
         ON L.NPANXXX             = RECMTD.NPANXXX
      WHERE RECJOB.CONTACT_TYPE   = 'R'
        AND REC.ORIG_CONTACT_TYPE = 'P');

where OBC_MASTER.LU_OBC_JOB_INFO  has 10K rows,
      OBC_MASTER.LU_OBC_RCONTACT_JOB_ADDNL_INFO has 5K rows
      OBC_LOAD.NPA_NXX_OUTBOUND has 100K rows
  and OBC_MASTER.FA_OBC_MDN_MTD has 20 Million rows

1. THIS Query is very fast(run time 10 sec):

SELECT * FROM OBC_MASTER.VW_RECONTACTS WHERE OCID = 9 AND MID = 48

2. THIS Query is very slow( takes more than 6 minutes):

SELECT * FROM OBC_MASTER.VW_RECONTACTS WHERE OCID = 9 AND MID = 48 AND ACT IN (1,2)

the ACT comes from the big table.

Is there anyway the second query can be speed up without any secondary index?

thnaks in advance for all suggestions

Ganga Palakattil

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