Teradata query need optimization

Question

Is there any other way to write the following statement:

(select coalesce(max(ch.chistdate),to_date('01011900','MMDDYYYY'))
      from mydb.chist ch 
      where ch.cactionid in (1,2) 
        and ch.chistdate < inv.jdate 
       and ch.cid = cbo.cid)     = (select max(ch.chistdate) 
                                                from mydb.chist ch 
                                                where ch.cactionid in (3,4) 
                                                  and ch.chistdate < inv.jdate 
                                                  and ch.cid = cbo.cid)

This statement is part of very big query and is included in the where part This is the only part giving no spool space error. I checked tables used for this they give back results quickly.

Main query is here :

select *
from   (select inv.domainnm
  , count(distinct cbo.cid) as numbo
  , trunc(inv.asdate) as asdate
from mydb.inv inv
                  join mydb.ComittedBO cbo on (inv.domainnm = cbo.domainnm 
                                      and inv.jdate >= cbo.sdate
                                      and coalesce(cbo.edate, to_date('01-JAN-3000','DD-MON-RRRR')) >= inv.jdate
                                      and cbo.cid not in (select cpp.cid 
                                                                 from mydb.cpprices cpp
                                                                 where NOT EXISTS (select 1 from mydb.cpitems cpt where cpp.cid = cpt.cid)
                                                                   and trunc(cpp.sdate) <= current_date
                                                                   and ((to_number(to_char(current_date, 'dd')) = 1
                                                                       and coalesce(trunc(cpp.edate),trunc(current_date+1)) >= add_months(trunc(current_date,'mm'),-1))
                                                                       or coalesce(trunc(cpp.edate),trunc(current_date+1)) >= trunc(current_date,'month'))))

                                                                       where ((NOT EXISTS (select 1
            from mydb.customers cu
            where cu.statusid = 3
              and cu.cid = cbo.cid)
   and NOT EXISTS (select 1 
          from mydb.chist ch 
          where ch.cactionid in (3,4) 
            and ch.chistdate < inv.jdate 
            and ch.cid = cbo.cid))



  or
  ((select coalesce(max(ch.chistdate),to_date('01011900','MMDDYYYY'))
      from mydb.chist ch 
      where ch.cactionid in (1,2) 
        and ch.chistdate < inv.jdate 
       and ch.cid = cbo.cid) in (select max(ch.chistdate) 
                                                from mydb.chist ch 
                                                where ch.cactionid in (3,4) 
                                                  and ch.chistdate < inv.jdate 
                                                  and ch.cid = cbo.cid  )
                                                  )   



                                                  )
group by inv.domainnm,trunc(inv.asdate)) inventory
  left join mydb.ahist ah on (inventory.domainnm = ah.domainnm 
                                  and ah.aaid = 7 

                                    and ah.astatusid in (7,9,14,17,19)
                                  and ah.asdate + interval'1' day >= inventory.asdate
                                  and inventory.asdate + interval'1' day >= ah.asdate)
  left join mydb.auction au on (inventory.domainnm = au.domainnm
                               and au.asdate + interval'1' day >= inventory.asdate
                               and inventory.asdate + interval'1' day >= au.asdate)
  left join sdb.harv hv on (inventory.domainnm = hv.domainnm
                                  and hv.sndate + interval'1' day >= inventory.asdate
                                  and inventory.asdate + interval'1' day >= hv.sndate)
  left join mydb.customers "cs" on ah.cid = "cs".cid
  left join mydb.aresult ar on ah.aid = ar.aid
  left join mydb.cdphist cdph on ar.cdpid = cdph.cdpid
  left join mydb.reg rgr on cdph.rid = rgr.rid
where ((to_number(to_char(current_date, 'dd')) = 1
      and inventory.asdate >= add_months(trunc(current_date,'mm'),-1))
      or inventory.asdate >= trunc(current_date,'month'))
  and inventory.asdate < current_date

Thanks.

Answer 1

Then probably volatile table will solve your problem. Create a volatile table before your main query like below :-

create volatile table tab1
(
chistdateM  date format 'MMDDYYYY'
)primary index(chistdate)
on commit preserve rows;

      insert into tab1 `enter code here`
      select coalesce(max(ch.chistdate),to_date('01011900','MMDDYYYY')) as chistdate
      from mydb.chist ch  , mydb.ComittedBO cbo
      where ch.cactionid in (1,2) 
      and ch.chistdate < inv.jdate 
      and ch.cid = cbo.cid;

create volatile table tab2
(
chistdateS  date format 'MMDDYYYY'
)primary index(chistdate)
on commit preserve rows;
      insert into tab2
      select max(ch.chistdate) from mydb.chist ch 
      where ch.cactionid in (3,4) 
      and ch.chistdate < inv.jdate 
      and ch.cid = cbo.cid  ; 

And use them in you invertory derived table :-

select * from   (select inv.domainnm, count(distinct cbo.cid) as numbo , trunc(inv.asdate) as asdate
from mydb.inv inv  join mydb.ComittedBO cbo on (inv.domainnm = cbo.domainnm    and inv.jdate >= cbo.sdate
                                      and coalesce(cbo.edate, to_date('01-JAN-3000','DD-MON-RRRR')) >= inv.jdate
                                      and cbo.cid not in (select cpp.cid from mydb.cpprices cpp
                                                                 where NOT EXISTS (select 1 from mydb.cpitems cpt where cpp.cid = cpt.cid)
                                                                   and trunc(cpp.sdate) <= current_date
                                                                   and ((to_number(to_char(current_date, 'dd')) = 1
                                                                       and coalesce(trunc(cpp.edate),trunc(current_date+1)) >= add_months(trunc(current_date,'mm'),-1))
                                                                       or coalesce(trunc(cpp.edate),trunc(current_date+1)) >= trunc(current_date,'month'))))
                                                                       where ((NOT EXISTS (select 1
            from mydb.customers cu
            where cu.statusid = 3
              and cu.cid = cbo.cid)
   and NOT EXISTS (select 1 
          from mydb.chist ch 
          where ch.cactionid in (3,4) 
            and ch.chistdate < inv.jdate 
            and ch.cid = cbo.cid))
  or
  ( Select * from tab1 where  chistdateM in (select chistdateS from tab2)    )   
                                                  )
group by inv.domainnm,trunc(inv.asdate)) inventory
  left join mydb.ahist ah on (inventory.domainnm = ah.domainnm 
                                  and ah.aaid = 7 

                                    and ah.astatusid in (7,9,14,17,19)
                                  and ah.asdate + interval'1' day >= inventory.asdate
                                  and inventory.asdate + interval'1' day >= ah.asdate)
  left join mydb.auction au on (inventory.domainnm = au.domainnm
                               and au.asdate + interval'1' day >= inventory.asdate
                               and inventory.asdate + interval'1' day >= au.asdate)
  left join sdb.harv hv on (inventory.domainnm = hv.domainnm
                                  and hv.sndate + interval'1' day >= inventory.asdate
                                  and inventory.asdate + interval'1' day >= hv.sndate)
  left join mydb.customers "cs" on ah.cid = "cs".cid
  left join mydb.aresult ar on ah.aid = ar.aid
  left join mydb.cdphist cdph on ar.cdpid = cdph.cdpid
  left join mydb.reg rgr on cdph.rid = rgr.rid
where ((to_number(to_char(current_date, 'dd')) = 1
      and inventory.asdate >= add_months(trunc(current_date,'mm'),-1))
      or inventory.asdate >= trunc(current_date,'month'))
  and inventory.asdate < current_date

Please validate your data as per your requirement.

Answer 2

Just concentrating on your Inventory derived table, I moved the two SELECT statements that were giving you problems from the WHERE clause to to the FROM clause with a LEFT OUTER JOIN.

I also combined them into a single statement that had three fields: The cid for joining to the cbo table, and then two CASE statements for each set of cactionid (1 and 2, 3 and 4). Then, in the WHERE clause I just tested for where the max(chistdate) from each CASE equaled eachother.

SELECT 
    inv.domainnm, 
    count(DISTINCT cbo.cid) AS numbo, 
    trunc(inv.asdate) AS asdate
FROM mydb.inv inv
INNER JOIN mydb.ComittedBO cbo  
    ON (
            inv.domainnm = cbo.domainnm AND 
            inv.jdate >= cbo.sdate AND 
            coalesce(cbo.edate, to_date('01-JAN-3000', 'DD-MON-RRRR')) >= inv.jdate AND 
            cbo.cid NOT IN 
                (
                    SELECT cpp.cid
                    FROM mydb.cpprices cpp
                    WHERE 
                        NOT EXISTS 
                            (
                                SELECT 1
                                FROM mydb.cpitems cpt
                                WHERE cpp.cid = cpt.cid
                            ) AND 
                        trunc(cpp.sdate) <= CURRENT_DATE 
                        AND 
                            (
                                (
                                    to_number(to_char(CURRENT_DATE, 'dd')) = 1 AND 
                                    coalesce(trunc(cpp.edate), trunc(CURRENT_DATE + 1)) >= add_months(trunc(CURRENT_DATE, 'mm'), - 1)
                                )
                                OR coalesce(trunc(cpp.edate), trunc(CURRENT_DATE + 1)) >= trunc(CURRENT_DATE, 'month')
                            )                                   
                )
            )
LEFT OUTER JOIN 
    (
        SELECT 
            ch.cid, 
            max(CASE WHEN ch.cactionid IN (1,2) THEN coalesce(max(ch.chistdate), DATE '1900-01-01')) as chistdate_ai12,
            max(CASE WHEN ch.cactionid IN (3,4) THEN ch.chistdate) as chistdate_ai34
        FROM chist ch
        WHERE ch.chistdate < inv.jdate
        GROUP BY ch.cid
    ) ch ON
    cbo.cid = ch.cid
WHERE (
        (
            NOT EXISTS (
                SELECT 1
                FROM mydb.customers cu
                WHERE cu.statusid = 3 AND cu.cid = cbo.cid
                ) 
            AND NOT EXISTS (
                SELECT 1
                FROM mydb.chist ch
                WHERE ch.cactionid IN (3, 4) AND ch.chistdate < inv.jdate AND ch.cid = cbo.cid
                )
        ) 
        OR ch.chistdate_ai12 = ch.chistdate_ai34
      )
GROUP BY inv.domainnm, trunc(inv.asdate)

I think there are a lot more efficiencies to be gained by working through this query more, but hopefully this will get you the same results (I believe it will, but without seeing your database schema and some sample data, it's tough to rewrite complex stuff like this), and hopefully it will run without jamming up your limited spool space.