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?
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?
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;
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?