Selecting with WHERE clause leads to all-row scan .

[aymanmonem]

Hello,

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 :

Explain SELECT COUNT(*)

FROM VDB.AGG_NW_ACTIVITY_SUBS_DAILY

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

     VDB.AGG_NW_ACTIVITY_SUBS_DAILY for access. 

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

     PLDB.AGG_NW_ACTIVITY_SUBS_DAILY in view

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

     condition of ("((EXTRACT(MONTH FROM

     (PLDB.AGG_NW_ACTIVITY_SUBS_DAILY in view

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

     ((EXTRACT(YEAR FROM (PLDB.AGG_NW_ACTIVITY_SUBS_DAILY in view

     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

     seconds. 

can you please guide in going through this issue ? 

[dnoeth]

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.

WHERE
   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]

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]

Hello All,

I have a query1:

select columns names from
(queries with left outer join
WHERE QTR_WK_NUM IN(valuessss....)
UNION
query....
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
UNION
query....
)x
 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]

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).