Databases
StackOverflow2013
Postsdbo
POSpool Space Error. Please help optimize
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSpool Space Error. Please help optimize
    primarykey
    data
    text
    <p>I am trying to generate the below query in Teradata SQL Assistant. Unfortunately I am running into a spool space error. Any tips on how to make this query execute using less resources?</p> <pre><code>SELECT TOP 10 A.USER_ID, C.SUPER_NM_LVL2, C.SUPER_NM_LVL1, DIR.SUPER_NM_LVL2, A.ACTIVITY_DT, B.MTN, A.ACCT_NUM, A.PPLAN_CD_CURR FROM DLY_LINE_ACTIVITY_PPLAN_V AS A INNER JOIN CUST_ACCT_LINE_V AS B ON A.ACCT_NUM=B.ACCT_NUM INNER JOIN HR_EMPLOYEE_V AS C ON A.USER_ID=C.NT_USER_ID INNER JOIN HR_EMPLOYEE_V AS DIR ON C.SUPER_ID_LVL2_EMP_ID=DIR.EMP_ID WHERE A.ACTIVITY_DT &gt;= '2012-11-01' AND A.ACTIVITY_DT &lt;= '2012-11-19' AND C.EMP_AREA_CD = 'WE' AND A.PPLAN_CD_CURR IN ('86489', '86491', '86492', '86494', '86495', '86496', '86497', '86498', '86499', '86500', '86501', '86502', '86487', '86489', '86504', '86505', '86506', '86507', '86508', '86509', '86510', '86511', '86512') GROUP BY A.USER_ID, C.SUPER_NM_LVL2, C.SUPER_NM_LVL1, DIR.SUPER_NM_LVL2, A.ACTIVITY_DT, B.MTN, A.ACCT_NUM, A.PPLAN_CD_CURR </code></pre> <p>EDIT: Here is the explain statement. </p> <pre><code>Explanation 1) First, we lock UDM_PRDSTG_SECTBLS.MASKING_PROD_NM_XREF in view NTL_PRD_ALLVM.CUST_ACCT_LINE_V for access, we lock UDM_PRD_TBLS.DLY_LINE_ACTIVITY_PPLAN in view NTL_PRD_ALLVM.DLY_LINE_ACTIVITY_PPLAN_V for access, we lock UDM_PRD_TBLS.CUST_ACCT_LINE in view NTL_PRD_ALLVM.CUST_ACCT_LINE_V for access, and we lock UDM_PRD_TBLS.HR_EMPLOYEE in view NTL_PRD_ALLVM.HR_EMPLOYEE_V for access. 2) Next, we execute the following steps in parallel. 1) We do an all-AMPs RETRIEVE step from a single partition of UDM_PRD_TBLS.DLY_LINE_ACTIVITY_PPLAN in view NTL_PRD_ALLVM.DLY_LINE_ACTIVITY_PPLAN_V with a condition of ( "(NOT (UDM_PRD_TBLS.DLY_LINE_ACTIVITY_PPLAN in view NTL_PRD_ALLVM.DLY_LINE_ACTIVITY_PPLAN_V.ACCT_NUM IS NULL )) AND ((NOT (UDM_PRD_TBLS.DLY_LINE_ACTIVITY_PPLAN in view NTL_PRD_ALLVM.DLY_LINE_ACTIVITY_PPLAN_V.USER_ID IS NULL )) AND ((UDM_PRD_TBLS.DLY_LINE_ACTIVITY_PPLAN in view NTL_PRD_ALLVM.DLY_LINE_ACTIVITY_PPLAN_V.ACTIVITY_DT &lt;= DATE '2012-11-19') AND ((UDM_PRD_TBLS.DLY_LINE_ACTIVITY_PPLAN in view NTL_PRD_ALLVM.DLY_LINE_ACTIVITY_PPLAN_V.ACTIVITY_DT &gt;= DATE '2012-11-01') AND ((UDM_PRD_TBLS.DLY_LINE_ACTIVITY_PPLAN in view NTL_PRD_ALLVM.DLY_LINE_ACTIVITY_PPLAN_V.PPLAN_CD_CURR = '86489') OR ((UDM_PRD_TBLS.DLY_LINE_ACTIVITY_PPLAN in view NTL_PRD_ALLVM.DLY_LINE_ACTIVITY_PPLAN_V.PPLAN_CD_CURR = '86487') OR ((UDM_PRD_TBLS.DLY_LINE_ACTIVITY_PPLAN in view NTL_PRD_ALLVM.DLY_LINE_ACTIVITY_PPLAN_V.PPLAN_CD_CURR = '86491') OR ((UDM_PRD_TBLS.DLY_LINE_ACTIVITY_PPLAN in view NTL_PRD_ALLVM.DLY_LINE_ACTIVITY_PPLAN_V.PPLAN_CD_CURR = '86492') OR ((UDM_PRD_TBLS.DLY_LINE_ACTIVITY_PPLAN in view NTL_PRD_ALLVM.DLY_LINE_ACTIVITY_PPLAN_V.PPLAN_CD_CURR = '86494') OR ((UDM_PRD_TBLS.DLY_LINE_ACTIVITY_PPLAN in view NTL_PRD_ALLVM.DLY_LINE_ACTIVITY_PPLAN_V.PPLAN_CD_CURR = '86495') OR ((UDM_PRD_TBLS.DLY_LINE_ACTIVITY_PPLAN in view NTL_PRD_ALLVM.DLY_LINE_ACTIVITY_PPLAN_V.PPLAN_CD_CURR = '86496') OR ((UDM_PRD_TBLS.DLY_LINE_ACTIVITY_PPLAN in view NTL_PRD_ALLVM.DLY_LINE_ACTIVITY_PPLAN_V.PPLAN_CD_CURR = '86497') OR ((UDM_PRD_TBLS.DLY_LINE_ACTIVITY_PPLAN in view NTL_PRD_ALLVM.DLY_LINE_ACTIVITY_PPLAN_V.PPLAN_CD_CURR = '86498') OR ((UDM_PRD_TBLS.DLY_LINE_ACTIVITY_PPLAN in view NTL_PRD_ALLVM.DLY_LINE_ACTIVITY_PPLAN_V.PPLAN_CD_CURR = '86499') OR ((UDM_PRD_TBLS.DLY_LINE_ACTIVITY_PPLAN in view NTL_PRD_ALLVM.DLY_LINE_ACTIVITY_PPLAN_V.PPLAN_CD_CURR = '86500') OR ((UDM_PRD_TBLS.DLY_LINE_ACTIVITY_PPLAN in view NTL_PRD_ALLVM.DLY_LINE_ACTIVITY_PPLAN_V.PPLAN_CD_CURR = '86501') OR ((UDM_PRD_TBLS.DLY_LINE_ACTIVITY_PPLAN in view NTL_PRD_ALLVM.DLY_LINE_ACTIVITY_PPLAN_V.PPLAN_CD_CURR = '86502') OR ((UDM_PRD_TBLS.DLY_LINE_ACTIVITY_PPLAN in view NTL_PRD_ALLVM.DLY_LINE_ACTIVITY_PPLAN_V.PPLAN_CD_CURR = '86504') OR ((UDM_PRD_TBLS.DLY_LINE_ACTIVITY_PPLAN in view NTL_PRD_ALLVM.DLY_LINE_ACTIVITY_PPLAN_V.PPLAN_CD_CURR = '86505') OR ((UDM_PRD_TBLS.DLY_LINE_ACTIVITY_PPLAN in view NTL_PRD_ALLVM.DLY_LINE_ACTIVITY_PPLAN_V.PPLAN_CD_CURR = '86506') OR ((UDM_PRD_TBLS.DLY_LINE_ACTIVITY_PPLAN in view NTL_PRD_ALLVM.DLY_LINE_ACTIVITY_PPLAN_V.PPLAN_CD_CURR = '86507') OR ((UDM_PRD_TBLS.DLY_LINE_ACTIVITY_PPLAN in view NTL_PRD_ALLVM.DLY_LINE_ACTIVITY_PPLAN_V.PPLAN_CD_CURR = '86508') OR ((UDM_PRD_TBLS.DLY_LINE_ACTIVITY_PPLAN in view NTL_PRD_ALLVM.DLY_LINE_ACTIVITY_PPLAN_V.PPLAN_CD_CURR = '86509') OR ((UDM_PRD_TBLS.DLY_LINE_ACTIVITY_PPLAN in view NTL_PRD_ALLVM.DLY_LINE_ACTIVITY_PPLAN_V.PPLAN_CD_CURR = '86510') OR ((UDM_PRD_TBLS.DLY_LINE_ACTIVITY_PPLAN in view NTL_PRD_ALLVM.DLY_LINE_ACTIVITY_PPLAN_V.PPLAN_CD_CURR = '86511') OR (UDM_PRD_TBLS.DLY_LINE_ACTIVITY_PPLAN in view NTL_PRD_ALLVM.DLY_LINE_ACTIVITY_PPLAN_V.PPLAN_CD_CURR = '86512')))))))))))))))))))))))))") into Spool 9 (all_amps) (compressed columns allowed), which is built locally on the AMPs. The size of Spool 9 is estimated with low confidence to be 2,908,683 rows (119,256,003 bytes). The estimated time for this step is 0.02 seconds. 2) We do an all-AMPs RETRIEVE step from UDM_PRD_TBLS.HR_EMPLOYEE in view NTL_PRD_ALLVM.HR_EMPLOYEE_V by way of an all-rows scan with a condition of ("(NOT (UDM_PRD_TBLS.HR_EMPLOYEE in view NTL_PRD_ALLVM.HR_EMPLOYEE_V.NT_USER_ID IS NULL )) AND ((NOT (UDM_PRD_TBLS.HR_EMPLOYEE in view NTL_PRD_ALLVM.HR_EMPLOYEE_V.SUPER_ID_LVL2_EMP_ID IS NULL )) AND (UDM_PRD_TBLS.HR_EMPLOYEE in view NTL_PRD_ALLVM.HR_EMPLOYEE_V.EMP_AREA_CD = 'WE'))") into Spool 10 (all_amps) (compressed columns allowed), which is duplicated on all AMPs. The size of Spool 10 is estimated with low confidence to be 31,938,444 rows (2,012,121,972 bytes). The estimated time for this step is 0.67 seconds. 3) We do an all-AMPs RETRIEVE step from UDM_PRD_TBLS.A in view NTL_PRD_ALLVM.CUST_ACCT_LINE_V by way of an all-rows scan with a condition of ("(( CASE WHEN (NOT (UDM_PRD_TBLS.A in view NTL_PRD_ALLVM.CUST_ACCT_LINE_V.CUST_ASSOC_ID IS NULL )) THEN (UDM_PRD_TBLS.A in view NTL_PRD_ALLVM.CUST_ACCT_LINE_V.CUST_ASSOC_ID) ELSE (' ') END ))&lt;&gt; '12'") into Spool 11 (all_amps) (compressed columns allowed) fanned out into 14 hash join partitions, which is built locally on the AMPs. The size of Spool 11 is estimated with no confidence to be 186,602,214 rows (5,784,668,634 bytes). The estimated time for this step is 2.27 seconds. 3) We do an all-AMPs JOIN step from Spool 9 (Last Use) by way of an all-rows scan, which is joined to Spool 10 (Last Use) by way of an all-rows scan. Spool 9 and Spool 10 are joined using a single partition hash join, with a join condition of ("USER_ID = NT_USER_ID"). The result goes into Spool 12 (all_amps) (compressed columns allowed), which is duplicated on all AMPs into 14 hash join partitions. The size of Spool 12 is estimated with low confidence to be 360,568,332 rows (30,648,308,220 bytes). The estimated time for this step is 8.09 seconds. 4) We do an all-AMPs JOIN step from Spool 11 (Last Use) by way of an all-rows scan, which is joined to Spool 12 (Last Use) by way of an all-rows scan. Spool 11 and Spool 12 are joined using a hash join of 14 partitions, with a join condition of ("ACCT_NUM = ACCT_NUM"). The result goes into Spool 13 (all_amps) (compressed columns allowed), which is redistributed by the hash code of ( UDM_PRD_TBLS.HR_EMPLOYEE.SUPER_ID_LVL2_EMP_ID) to all AMPs. The size of Spool 13 is estimated with no confidence to be 8,247,457 rows (783,508,415 bytes). The estimated time for this step is 1.01 seconds. 5) We do an all-AMPs JOIN step from UDM_PRD_TBLS.HR_EMPLOYEE in view NTL_PRD_ALLVM.HR_EMPLOYEE_V by way of an all-rows scan with no residual conditions, which is joined to Spool 13 (Last Use) by way of an all-rows scan. UDM_PRD_TBLS.HR_EMPLOYEE and Spool 13 are joined using a single partition hash join, with a join condition of ("SUPER_ID_LVL2_EMP_ID = UDM_PRD_TBLS.HR_EMPLOYEE.EMP_ID"). The result goes into Spool 8 (all_amps) (compressed columns allowed), which is built locally on the AMPs. The size of Spool 8 is estimated with no confidence to be 8,247,457 rows (882,477,899 bytes). The estimated time for this step is 0.06 seconds. 6) We do an all-AMPs SUM step to aggregate from Spool 8 (Last Use) by way of an all-rows scan , grouping by field1 ( UDM_PRD_TBLS.DLY_LINE_ACTIVITY_PPLAN.USER_ID ,UDM_PRD_TBLS.HR_EMPLOYEE.SUPER_NM_LVL2 ,UDM_PRD_TBLS.HR_EMPLOYEE.SUPER_NM_LVL1 ,UDM_PRD_TBLS.HR_EMPLOYEE.SUPER_NM_LVL2 ,UDM_PRD_TBLS.DLY_LINE_ACTIVITY_PPLAN.ACTIVITY_DT ,UDM_PRD_TBLS.A.MTN ,UDM_PRD_TBLS.DLY_LINE_ACTIVITY_PPLAN.ACCT_NUM ,UDM_PRD_TBLS.DLY_LINE_ACTIVITY_PPLAN.PPLAN_CD_CURR). Aggregate Intermediate Results are computed globally, then placed in Spool 14. The size of Spool 14 is estimated with no confidence to be 8,247,455 rows (2,762,897,425 bytes). The estimated time for this step is 0.76 seconds. 7) We do an all-AMPs STAT FUNCTION step from Spool 14 by way of an all-rows scan into Spool 18, which is redistributed by hash code to all AMPs. The result rows are put into Spool 5 (all_amps), which is built locally on the AMPs. This step is used to retrieve the TOP 10 rows. Load distribution optimization is used. If this step retrieves less than 10 rows, then execute step 8. The size is estimated with no confidence to be 10 rows (1,130 bytes). 8) We do an all-AMPs STAT FUNCTION step from Spool 14 (Last Use) by way of an all-rows scan into Spool 18 (Last Use), which is redistributed by hash code to all AMPs. The result rows are put into Spool 5 (all_amps), which is built locally on the AMPs. This step is used to retrieve the TOP 10 rows. The size is estimated with no confidence to be 10 rows (1,130 bytes). 9) Finally, we send out an END TRANSACTION step to all AMPs involved in processing the request. -&gt; The contents of Spool 5 are sent back to the user as the result of statement 1. </code></pre>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USciw916
    1. USciw916
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POSpool Space Error. Please help optimize
      1. This table or related slice is empty.
      1. VTUpMod
    1. COhave you tried tools/explain (F6) ? In general, you can query the largest table first in a subquery, then join all the little ones.
      singulars
      1. POSpool Space Error. Please help optimize
      1. USBeth
    2. COThanks for the reply, Beth. I'm aware of the feature, just not sure how to apply it. Self taught here, but I'll post the explain results in my OP.
      singulars
      1. POSpool Space Error. Please help optimize
      1. USciw916
    3. COHow many rows in each of the tables? I'm assuming one or more are huge (millions) and others, medium-sized (thousands). Cut down your huge tables first, then join to medium-sized tables.
      singulars
      1. POSpool Space Error. Please help optimize
      1. USBeth
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload