Remove function call from select statement

Question

I have a oracle query which brings no of product records from the database & the result has a flag which shows whether particular product was ordered in last 12 months. This is derived by checking the order table to see if there was any order placed in last 12 months.

I have a different query, for simplicity I am showing the part of it here.

Product Table - tblProducts
Product_Id   Name   Description   Size   Default_QTY......

Orders Table - tblOrders
Order Id    ProductId    QTY    Price   Date_Purch......

Now, the query that fetches products, has a function that accepts the product Id & returns boolean value whether the product was purchased earlier. Here along with product I am going to join many other tables which returns considerable amount of records (close to 100000) & Order table is huge (28 Million records so far). Because of this calling function in the select statement can impact performance. Is there any other way I can check if product is present in orders table?

Select ProductId, Name, Description, Size, fun_IsPurcInLast12(ProductId) From tblProduct

Ponder Stibbons · Accepted Answer · 2018-12-19 10:48:28Z

The correlated subquery is worth checking:

-- test data
with 
  tblProducts(ProductId, Name, Description, pSize) as (
    select 1, 'P1', 'D1', 7 from dual union all
    select 2, 'P2', 'D2', 4 from dual union all
    select 3, 'P3', 'D3', 8 from dual ),
  tblOrders(OrderId, ProductId, Date_Purch) as (
    select 1, 1, date '2017-05-13' from dual union all
    select 2, 1, date '2018-11-06' from dual union all
    select 3, 2, date '2013-01-30' from dual )
-- end of test data

select ProductId, Name, Description, 
       case when exists (select 1 
                           from tblOrders 
                           where ProductId = p.ProductId 
                             and Date_Purch > add_months(sysdate, -12)) 
            then 1 
            else 0 
       end as flag
  from tblProducts p

Index on (ProductId, Date_Purch) would be nice. Result:

 PRODUCTID NAME DESCRIPTION       FLAG
---------- ---- ----------- ----------
         1 P1   D1                   1
         2 P2   D2                   0
         3 P3   D3                   0

Kaushik Nayak · Accepted Answer · 2018-12-19 10:23:12Z

0

You may do a LEFT JOIN on DISTINCT orders

SELECT productid,
       name,
       description,
       size,
       CASE
            WHEN o.productid IS NOT NULL THEN 'YES'
            ELSE 'NO'
       END
  AS ispurcinlast12
FROM tblproduct p
LEFT JOIN (
     SELECT DISTINCT productid
       FROM tblorders
     WHERE date_purchased >= ADD_MONTHS(SYSDATE,-12)
) o ON p.productid = o.productid

Add an index on productid,date_purchased on tblOrders

answered Dec 19, 2018 at 10:23

Kaushik Nayak

32k6 gold badges36 silver badges54 bronze badges

Collectives™ on Stack Overflow

Remove function call from select statement

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related