Factor out COALESCE function?

Question

I have a large query (not written by me, but I'm making some modifications). One thing that kind of bugs me is that I've got the same COALESCE function in about four places. Is there a way to factor that out, possibly by joining with a select from DUAL? Is there a performance benefit to factoring it out?

Here is the query slightly bolwderized:

select 
tbl1.gid
[snip]
         ,COALESCE(t1_end_dt, t6_actl_end_dt,t6_calc_end_dt) perd_end_dt
        ,t1_end_dt
FROM    tbl1
....
JOIN    tbl2               ON (t2_pk          = wpck_wrkr_id  AND
                                            t2_ind_1    ='Y'    AND
        COALESCE(t1_end_dt, t6_actl_end_dt, t6_calc_end_dt) 
        BETWEEN  t2_strt_dt AND t2_end_dt)
JOIN    tbl3                ON (t3_pk               = t2_fk_t3_pk AND
       COALESCE(t1_end_dt, t6_actl_end_dt, t6_calc_end_dt) 
        BETWEEN t3_strt_dt AND t3_end_dt)    
LEFT JOIN tbl4 tbl4_a             ON (tbl4_a.t4_pk         = chkw_wkt_id)
.....
GROUP BY tbl1.gid 
.....
        ,COALESCE(t1_end_dt, t6_actl_end_dt, t6_calc_end_dt)
        ,COALESCE(tbl4_b.t4_or_id, tbl4_a.t4_or_id, t7_or_id )
        ,t1_end_dt
ORDER BY perd_end_dt

Note that I already factored out one of the COALESCEs by naming it pred_end_dt in the SELECT and then referring to it by that name in the ORDER BY.

Answer 1

Maybe you can use a with clause?

with data as
(
   select COALESCE(t1_end_dt, t6_actl_end_dt,t6_calc_end_dt) perd_end_dt
   ,      .....
   ,      ...
   from   tbl1
)
select ...
from   data
,      ....
where  ....

Answer 2

You should be able to replace the occurrence in the GROUP BY clause with your column alias perd_end_dt. But the occurrences in JOIN conditions cannot use the column alias.

This does have an impact on performance. I don't think the JOIN conditions can use an index the way they are written currently. However, it's only one term in the JOIN condition, so the impact might be very slight. That is, the terms involving t2_pk and t2_ind_1 may already reduce the rowset so the date comparison only has to deal with a small set. It depends on how many rows we're talking about and how the data is distributed.

One way to factor out the COALESCE() and take advantage of indexing would be to create a redundant column which is the result of that expression. Use triggers to make sure it has the right value after every insert or update. Then index the column.

But like I said, it might be more trouble than it's worth. It'd be more work, and you should be sure that this change would make the code more maintainable, more correct, or more speedy.

---

Another option would be to define an alias for the COALESCE() expression in a subquery. I can't tell which tables the columns come from, because you're not qualifying them with table aliases. But if I can assume they're all columns of tbl1:

...
FROM (
    SELECT COALESCE(t1_end_dt, t6_actl_end_dt, t6_calc_end_dt) perd_end_dt,
      t1_fk_t2_pk
    FROM tbl1) t1
  JOIN tbl2
    ON (tbl2.t2_pk = t1.t1_fk_t2_pk AND tbl2.t2_ind_1 = 'Y'
      AND t1.perd_end_dt BETWEEN tbl2.t2_strt_dt AND tbl2.t2_end_dt)
...

Answer 3

You could crate a virtual table

(Select Coalesce( all your stuff ....) perdEndDt 
   From [all your tables necessary for this one value])

and include this asa separate joined table in your Select SQL

  Select [all yr other stuff,
      Z.perdEndDt
  From [Other Tables Joined together]
      Join 
         (Select Coalesce( all your stuff ....) perdEndDt,
               [other columns necessary for join conditions]
         From [all your tables necessary for this one value]) As Z
         On [Join conditions for Z to other tables]
  Where  [any filter predicates]
  Order By Z.perdEndDt
     ... etc.

Answer 4

Does Oracle have views? (It must do.) If you find yourself using that COALESCE() expression in lots of different places, it may be worth creating a VIEW and using that instead of the original table(s). Underneath, the same calculations will be performed, but the syntax of your queries will be more concise and therefore clearer.

To my way of thinking, "view" is to "table" in a database as "function" is to "lines of code" in a program. Whenever you find a bunch of queries using similar logic, write a view that factors out the common logic and use that instead.