 |
 |
Archives of the TeradataForum
Message Posted: Thu, 08 Jan 2004 @ 22:50:49 GMT
| Subj: | | Re: View Performance |
| |
| From: | | Ganga Palakattil |
Victor,
We have V2R4.1 now. We will be moving to V2R5.1 this month.
I changed the view in the old way as follows:
replace view obc_master.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,obc_master.lu_obc_job_info recjob ,obc_master.fa_obc_mdn_mtd recmtd
,obc_load.npa_nxx_outbound l
where recjob.job_id = rec.job_id
and recmtd.job_id = rec.job_id
and l.npanxxx = recmtd.npanxxx
and recjob.contact_type = 'r'
and rec.orig_contact_type = 'p'
);
The query select * from obc_master.recontacts where ocid = 9 and mid = 48 and act in (1,2) never finishes as before.
Here is the Plan
Explanation
-------------------------------------------------- | |
| | 1) | First, we lock obc_master.lu_obc_rcontact_job_addnl_info for access, we lock obc_master.fa_obc_mdn_mtd for access, we lock
obc_master.lu_obc_job_info for access, and we lock obc_load.npa_nxx_outbound for access.
| |
| | 2) | Next, we execute the following steps in parallel.
| |
| | |
| | 1) | We do an all-AMPs JOIN step from obc_master.rec by way of a RowHash match scan. with a condition of ("obc_master.rec.ORIG_CONTACT_TYPE =
'p'"), which is joined to obc_master.recjob with a condition of ("((((obc_master.recjob.REPORTING_YEAR - 2000 )* 12 )+
obc_master.recjob.REPORTING_MONTH )= 48) AND (obc_master.recjob.CONTACT_TYPE = 'r')"). obc_master.rec and obc_master.recjob are joined using a
merge join, with a join condition of ("obc_master.recjob.JOB_ID = obc_master.rec.JOB_ID"). The result goes into Spool 2, which is duplicated on
all AMPs. The size of Spool 2 is estimated with no confidence to be 2,656 rows. The estimated time for this step is 0.28 seconds.
| | |
| | |
| | 2) | We do an all-AMPs RETRIEVE step from obc_load.l by way of an all-rows scan with a condition of ("(obc_load.l.OWNER_CARRIER_ID = 9) AND
(NOT (obc_load.l.NPANXXX IS NULL ))") into Spool 3, which is redistributed by hash code to all AMPs. Then we do a SORT to order Spool 3 by row
hash. The size of Spool 3 is estimated with no confidence to be 11,521 rows. The estimated time for this step is 0.43 seconds.
| | |
| | 3) | We do an all-AMPs JOIN step from Spool 2 (Last Use) by way of an all-rows scan, which is joined to obc_master.recmtd with a condition of
("(obc_master.recmtd.ACTIVITY_CODE = 1) OR (obc_master.recmtd.ACTIVITY_CODE = 2)"). Spool 2 and obc_master.recmtd are joined using a product
join, with a join condition of ("(obc_master.recmtd.JOB_ID = JOB_ID) AND (JOB_ID = obc_master.recmtd.JOB_ID)"). The input table obc_master.recmtd
will not be cached in memory. The result goes into Spool 4, which is redistributed by hash code to all AMPs. Then we do a SORT to order Spool 4
by row hash. The size of Spool 4 is estimated with no confidence to be 28,913 rows. The estimated time for this step is 1 minute and 38 seconds.
| |
| | 4) | We do an all-AMPs JOIN step from Spool 3 (Last Use) by way of a RowHash match scan., which is joined to Spool 4 (Last Use). Spool 3 and
Spool 4 are joined using a merge join, with a join condition of ("NPANXXX = NPANXXX"). The result goes into Spool 1, which is built locally on
the AMPs. The size of Spool 1 is estimated with no confidence to be 347,329 to 333,106,673 rows. The estimated time for this step is 2.99
seconds to 44 minutes and 23 seconds.
| |
| | 5) | Finally, we send out an END TRANSACTION step to all AMPs involved in processing the request.
| |
| | -> | The contents of Spool 1 are sent back to the user as the result of statement 1. The total estimated time is 1 minute and 42 seconds.
| |
I joined the view with Temp table and executed the query
select * from OBC_MASTER.LU_temp A, obc_master.recontacts B
where A.act = B.act
and ocid = 9 and mid = 48
It brought the results in 10 seconds
Explanation
-------------------------------------------------- | |
| | 1) | First, we lock a distinct OBC_MASTER."pseudo table" for read on a RowHash to prevent global deadlock for OBC_MASTER.A.
| |
| | 2) | Next, we lock OBC_MASTER.A for read, we lock obc_master.lu_obc_rcontact_job_addnl_info for access, we lock obc_master.fa_obc_mdn_mtd for
access, we lock obc_master.lu_obc_job_info for access, and we lock obc_load.npa_nxx_outbound for access.
| |
| | 3) | We do an all-AMPs RETRIEVE step from obc_master.rec by way of an all-rows scan with a condition of ("obc_master.rec.ORIG_CONTACT_TYPE =
'p'") into Spool 2, which is duplicated on all AMPs. Then we do a SORT to order Spool 2 by row hash. The size of Spool 2 is estimated with no
confidence to be 6,048 rows. The estimated time for this step is 0.04 seconds.
| |
| | 4) | We execute the following steps in parallel.
| |
| | |
| | 1) | We do an all-AMPs JOIN step from Spool 2 (Last Use) by way of an all-rows scan, which is joined to obc_master.recmtd by way of a
traversal of index # 4 extracting row ids only. Spool 2 and obc_master.recmtd are joined using a nested join, with a join condition of
("obc_master.recmtd.JOB_ID = JOB_ID"). The input table obc_master.recmtd will not be cached in memory. The result goes into Spool 3, which is
built locally on the AMPs. Then we do a SORT to order Spool 3 by field Id 1. The size of Spool 3 is estimated with no confidence to be 346,516
rows. The estimated time for this step is 1 minute and 54 seconds.
| | |
| | |
| | 2) | We do an all-AMPs RETRIEVE step from obc_master.recjob by way of an all-rows scan with a condition of
("((((obc_master.recjob.REPORTING_YEAR - 2000 )* 12 )+ obc_master.recjob.REPORTING_MONTH )= 48) AND (obc_master.recjob.CONTACT_TYPE = 'r')") into
Spool 4, which is redistributed by hash code to all AMPs. Then we do a SORT to order Spool 4 by row hash. The size of Spool 4 is estimated with
low confidence to be 3,288 rows. The estimated time for this step is 0.17 seconds.
| | |
| | 5) | We do an all-AMPs JOIN step from Spool 3 (Last Use) by way of an all-rows scan, which is joined to obc_master.recmtd. Spool 3 and
obc_master.recmtd are joined using a row id join, with a join condition of ("(1=1)"). The input table obc_master.recmtd will not be cached in
memory. The result goes into Spool 5, which is redistributed by hash code to all AMPs. Then we do a SORT to order Spool 5 by row hash. The size
of Spool 5 is estimated with no confidence to be 346,516 rows. The estimated time for this step is 1 minute and 59 seconds.
| |
| | 6) | We do an all-AMPs JOIN step from Spool 4 (Last Use) by way of a RowHash match scan., which is joined to Spool 5 (Last Use). Spool 4 and
Spool 5 are joined using a merge join, with a join condition of ("(JOB_ID = JOB_ID) AND (JOB_ID = JOB_ID)"). The result goes into Spool 6, which
is redistributed by hash code to all AMPs. Then we do a SORT to order Spool 6 by row hash. The size of Spool 6 is estimated with no confidence
to be 3,288 rows. The estimated time for this step is 1.12 seconds.
| |
| | 7) | We execute the following steps in parallel.
| |
| | |
| | 1) | We do an all-AMPs JOIN step from OBC_MASTER.A by way of a RowHash match scan. with no residual conditions, which is joined to Spool 6
(Last Use). OBC_MASTER.A and Spool 6 are joined using a merge join, with a join condition of ("OBC_MASTER.A.act = ACTIVITY_CODE"). The result
goes into Spool 7, which is redistributed by hash code to all AMPs. Then we do a SORT to order Spool 7 by row hash. The size of Spool 7 is
estimated with no confidence to be 3,288 rows. The estimated time for this step is 0.12 seconds.
| | |
| | |
| | 2) | We do an all-AMPs RETRIEVE step from obc_load.l by way of an all-rows scan with a condition of ("(obc_load.l.OWNER_CARRIER_ID = 9) AND
(NOT (obc_load.l.NPANXXX IS NULL ))") into Spool 8, which is redistributed by hash code to all AMPs. Then we do a SORT to order Spool 8 by row
hash. The size of Spool 8 is estimated with no confidence to be 11,521 rows. The estimated time for this step is 0.43 seconds.
| | |
| | 8) | We do an all-AMPs JOIN step from Spool 7 (Last Use) by way of a RowHash match scan., which is joined to Spool 8 (Last Use). Spool 7 and
Spool 8 are joined using a merge join, with a join condition of ("NPANXXX = NPANXXX"). The result goes into Spool 1, which is built locally on
the AMPs. The size of Spool 1 is estimated with no confidence to be 62,624 to 37,881,048 rows. The estimated time for this step is 0.71 seconds
to 5 minutes and 11 seconds.
| |
| | 9) | Finally, we send out an END TRANSACTION step to all AMPs involved in processing the request.
| |
| | -> | The contents of Spool 1 are sent back to the user as the result of statement 1. The total estimated time is 3 minutes and 55 seconds.
| |
| |
