2

I was optimizing one of horrible views we have and it came as surprise that one of subselects with CASE statements was running faster than LEFT JOIN with OR. Original view is substantially bigger but parts that I am interested in can be boiled down to following queries

SELECT CASE 
     WHEN tdcurr.productid = 1 THEN (SELECT addressid 
                                     FROM   address a 
                                     WHERE  a.customerid = tm.customerid 
                                            AND a.addressid = 
                                                tdcurr.addressid 
                                            AND a.addresstypeid = 3) 
     WHEN tdcurr.productid = 2 THEN (SELECT addressid 
                                     FROM   address a 
                                     WHERE  a.customerid = tm.customerid 
                                            AND a.addressid = 
                                                tdcurr.addressid 
                                            AND a.addresstypeid = 4) 
   END AS t_buyselladdressid 
FROM   vleaf_transactiondetail_all tdcurr 
   inner join transactionmain tm 
           ON tm.transactionid = tdcurr.transactionid

enter image description here

Execution plan

enter image description here

while one with join is consistently slower

SELECT bsaddr.addressid AS t_buyselladdressid 
FROM   vleaf_transactiondetail_all tdcurr 
   inner join transactionmain tm 
           ON tm.transactionid = tdcurr.transactionid 
   left outer join address bsaddr 
                ON tm.customerid = bsaddr.customerid 
                   AND bsaddr.addressid = tdcurr.addressid 
                   AND ( ( tdcurr.productid = 1 
                           AND bsaddr.addresstypeid = 3 ) 
                          OR ( tdcurr.productid = 2 
                               AND bsaddr.addresstypeid = 4 ) )

enter image description here

Execution Plan

enter image description here

Why would this be the case?

Matas Vaitkevicius
  • 58,075
  • 31
  • 238
  • 265
  • I'll not realy trust the timing of the tools (espetially in the decimal points). Time to time I wait pretty much only to see a milliseconds response. If you want to understand the difference investigate the execution plans. – Marmite Bomber Jul 01 '15 at 10:58
  • @MarmiteBomber added execution plans. – Matas Vaitkevicius Jul 01 '15 at 11:25
  • I think that what I would do is modify the join case to use a clause of the form: a.addresstypeid = (case tdcurr.productid when 1 then 3 when 2 then 4 end) – David Aldridge Jul 01 '15 at 12:04

1 Answers1

2

It's possible that the SQL with subselects is benefitting from scalar subquery caching. From the explain plans, it definitely looks like it's benefitting from not doing the Nested Loops Outer Join!

See https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:2683853500346598211 for more information about scalar subquery caching.

Boneist
  • 22,910
  • 1
  • 25
  • 40
  • I thing this (subquery caching) could be the correct explanation in case there is a lot of transactions records and only a few customers. The NL will loop for each transaction records, but the subquery will be called only once per customer. @Matas Vaitkevicius pls provide the number of transactionId and the distict number of customerId. – Marmite Bomber Jul 01 '15 at 16:48