|
Archives of the TeradataForumMessage Posted: Tue, 06 Jan 2004 @ 21:18:56 GMT
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
| ||||||||||||||||||||||||||||||||||||||||||||||||
| ||||||||||||||||||||||||||||||||||||||||||||||||
Copyright 2016 - All Rights Reserved | ||||||||||||||||||||||||||||||||||||||||||||||||
Last Modified: 15 Jun 2023 | ||||||||||||||||||||||||||||||||||||||||||||||||