Pulling only half of the records into a volatile table with a qualify statement

Teradata Studio
Highlighted
Fan

Pulling only half of the records into a volatile table with a qualify statement

I am creating a volatile table that includes three joins and a qualify statement.  I keep running into spool space issues in this particular query when I run it and I would like to pull only half of the records in from each join then the 'other half' of the records then unioin them afterwards.  I am also seeing that I cannot do a 'top [#]' statement with a qualify statement.  

 

Is this possible?

3 REPLIES
Junior Contributor

Re: Pulling only half of the records into a volatile table with a qualify statement

I doubt that splitting into two joins plus UNION ALL will lower spool usage, but you you could try wrapping the Qualify-Select in a Common Table Expression (or a Dervied Table) and then apply TOP:

WITH cte AS
 (
   SELECT ...
   QUALIFY
 )
SELECT TOP ...
FROM cte

But how to get the 2nd half? Adding ORDER will add overhead.

 

You could also filter in the existing QUALIFY using a Percent_Rank:

 

PERCENT_RANK() OVER (PARTITION BY .... ORDER BY ...) <0.5/>=0.5

To avoid an extra STATS-step both Partition & Order should be copied from an existing Window definition.

 

Can you show your actual query?

 

Fan

Re: Pulling only half of the records into a volatile table with a qualify statement

This is the volatile table that is giving me trouble...

 

create volatile table attach_hhs as (
select distinct
a.*
,c.livg_unt_id
from all_trans a
inner join analyst_vw.v_bridge_instnc b
on a.id_shopping_trans = b.id_shopping_trans
and a.id_date = b.id_date
and a.id_str = b.id_str
inner join analyst_append_vw.v_consolidated_ccrn_hh c
on b.instnc = c.instnc
inner join prod_by_store e on
e.dsc_sub_dept = a.dsc_sub_dept
and (c.lu_first_store_by_sales_ex = e.id_str or c.lu_second_store_by_sales_ex = e.id_str)
where ap_flag = 1
QUALIFY ROW_NUMBER () OVER (PARTITION BY a.id_shopping_trans, a.id_prod ORDER BY c.livg_unt_id DESC) = 1
) with data unique primary index(id_shopping_trans,id_date,id_str,livg_unt_id, id_prod ) on commit preserve rows;
Junior Contributor

Re: Pulling only half of the records into a volatile table with a qualify statement

Do you have access to the QryLogSteps to find the failing step?

 

There's no DISTINCT needed as you already use a ROW_NUMBER = 1.

Removing it will reduce spool usage.

 

The ORed join is also bad, might result in a huge intermediate spool, too.

 

But the failing step is probably the QUALIFY, which needs two spools, doubling the size.

 

Can you post Explain, too?