时间戳

首页 > 解决方案 > Remove function call from select statement

sql - Remove function call from select statement

问题描述

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

标签： sqloracleoracle11g

解决方案

相关子查询值得检查：

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

索引(ProductId, Date_Purch)会很好。结果：

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

推荐阅读