All Forums Database
aymanmonem 4 posts Joined 01/14
08 Feb 2015
Selecting with WHERE clause leads to all-row scan .

I got an issue while selecting a from table with huge data and using the following WHERE clause :
WHERE ( (EXTRACT ( MONTH  FROM ( VDB.AGG_NW_ACTIVITY_SUBS_DAILY.Call_Start_Date ))(TITLE 'Month')) = (EXTRACT ( MONTH  FROM ( current_date - iNtErVaL '1' MONTH ))(TITLE 'Month')) 

AND (EXTRACT ( YEAR  FROM ( VDB.AGG_NW_ACTIVITY_SUBS_DAILY.Call_Start_Date ))(TITLE 'Year')) = (EXTRACT ( YEAR  FROM ( current_date - iNtErVaL '1' MONTH ))(TITLE 'Year')) 


it leads to all - row scan in this table which takes alot of CPU time


below is an explain :





WHERE (EXTRACT ( MONTH  FROM ( Call_Start_Date ))(TITLE 'Month')) = (EXTRACT ( MONTH  FROM ( current_date - iNtErVaL '1' MONTH ))(TITLE 'Month')) 

AND (EXTRACT ( YEAR  FROM ( Call_Start_Date ))(TITLE 'Year')) = (EXTRACT ( YEAR  FROM ( current_date - iNtErVaL '1' MONTH ))(TITLE 'Year'))


  1) First, we lock PLDB.AGG_NW_ACTIVITY_SUBS_DAILY in view


  2) Next, we do an all-AMPs SUM step to aggregate from


     VDB.AGG_NW_ACTIVITY_SUBS_DAILY by way of an all-rows scan with a

     condition of ("((EXTRACT(MONTH FROM


     VDB.AGG_NW_ACTIVITY_SUBS_DAILY.Call_Start_Date )))= 1) AND


     VDB.AGG_NW_ACTIVITY_SUBS_DAILY.Call_Start_Date )))= 2015)"). 

     Aggregate Intermediate Results are computed globally, then placed

     in Spool 3.  The input table will not be cached in memory, but it

     is eligible for synchronized scanning.  The size of Spool 3 is

     estimated with high confidence to be 1 row (23 bytes).  The

     estimated time for this step is 16 minutes and 45 seconds. 

  3) We do an all-AMPs RETRIEVE step from Spool 3 (Last Use) by way of

     an all-rows scan into Spool 1 (group_amps), which is built locally

     on the AMPs.  The size of Spool 1 is estimated with high

     confidence to be 1 row (25 bytes).  The estimated time for this

     step is 0.00 seconds. 

  4) 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 16 minutes and 45







can you please guide in going through this issue ? 

dnoeth 4628 posts Joined 11/04
08 Feb 2015

Of course this is a full table scan, you do some calculation on the Call_Start_Date.
Additionally it will fail due to the interval calculation when you run this on 2015-03-29.

   Call_Start_Date BETWEEN TRUNC(ADD_MONTHS(CURRENT_DATE,-1), 'mon') -- first day of previous month
              AND LAST_DAY(TRUNC(ADD_MONTHS(CURRENT_DATE,-1), 'mon')) -- last day of previous month



aymanmonem 4 posts Joined 01/14
09 Feb 2015

Thanks Dieter for reply, 
Is it possible defining variable to get dates from a table so it wont do an all-row scan ? 
Using _Date_ST_TM ,_Date_EN_TM
so the optimizer changes the plan and access required partitions, if yes can you please provide me with an example or link of how to create such a variable in teradata SQL  ? 



shavyani 23 posts Joined 03/15
01 May 2015

Hello All,
I have a query1:

select columns names from
(queries with left outer join
WHERE QTR_WK_NUM IN(valuessss....)
WHERE QTR_WK_NUM IN(valuessss....)---------------------> same filter as above.

The above is one way of retrieving the data's
The 2nd way the query can be written:

select *from x.*
(queries1 with  left outer join
 WHERE x.QTR_WK_NUM IN(valuessss....)

This is the second way I have retrieved the data.
My concern here is...........

1.Will both the queries, that is , the first one in which "WHERE" clause is defined inside sub query and the second one in which the "WHERE" clause is defined outside the subquery, will both retrieve the same result set?

2. I have a mismatch in data's when I give a filter outside the sub query.( Like the 2nd way I have structured the query)

Please advice, and any suggestion is greatly appreciated.


dnoeth 4628 posts Joined 11/04
01 May 2015

Without the actual SELECTs it's hard to tell if both queries return the same result. 
Compare EXPLAINs and chekc if the condition is pushed into the Derived Table in Q2.
Otherwise you might try to change UNION to UNION ALL, which performs better (but might return duplicate rows). 


You must sign in to leave a comment.