为什么查询时间太长

Question

我有一个查询应该返回大约 10000 行。数据库本身非常大。我运行了一个简单的查询，它在不到 3 秒的时间内返回了一个结果。但是当更复杂的代码时，它需要的时间太长。

在我的代码中，我做了一个嵌套的选择和一个案例语句。但是，当我运行代码时，需要一个多小时才能返回结果。我可以对代码执行哪些操作来减少此执行时间。

SELECT ticker_symb, day_sum_eff, cusip, 
clos_prc, 
nclos_prc,
 case
     when  clos_prc is null and nclos_prc is not null 
     then (nclos_prc - LAG( nclos_prc ignore nulls) OVER (ORDER BY cusip)) 
     when  clos_prc is not null and nclos_prc is null
       then (LEAD( nclos_prc ignore nulls) OVER (ORDER BY cusip)- LAG( nclos_prc ignore nulls) OVER (ORDER BY cusip))
       else NULL
   end DIFF
FROM (SELECT  
      day_sum_eff, 
      cusip,
      ticker_symb, 
      clos_prc, 
      nclos_prc,
      case
           when clos_prc is null and nclos_prc is not null 
           then (nclos_prc - LAG( nclos_prc ignore nulls) OVER (ORDER BY cusip)) 
           when  clos_prc is not null and nclos_prc is null
           then LEAD( nclos_prc ignore nulls) OVER (ORDER BY cusip)- LAG( nclos_prc ignore nulls) OVER (ORDER BY cusip)
          else NULL
          end DIFF
    from  MKTDATA.MARKET_DAILY_SUMMARY 
    WHERE day_sum_eff >=  '1-JUN-2017' and 
          day_sum_eff <=  '10-JUN-2017' )
order by  day_sum_eff_,fmr_iss_cusip OFFSET 0 ROWS FETCH NEXT 3 ROW ONLY;

执行计划表

PLAN_TABLE_OUTPUT

计划哈希值：831959278

----------------------------------------------------------
| Id  | Operation                 | Name                 |
----------------------------------------------------------
|   0 | SELECT STATEMENT          |                      |
|   1 |  VIEW                     |                      |
|   2 |   WINDOW SORT PUSHED RANK |                      |
|   3 |    WINDOW SORT            |                      |
|   4 |     PARTITION RANGE SINGLE|                      |
|   5 |      TABLE ACCESS FULL    | MARKET_DAILY_SUMMARY |
----------------------------------------------------------

Answer 1

试试这个：-

在day_sum_eff列上创建索引，然后再次运行查询，看看执行时间是否有任何变化。

这可能是工作。

Answer 2

试试这个

WITH q1 AS (
SELECT  
      day_sum_eff, 
      cusip,
      ticker_symb, 
      clos_prc, 
      nclos_prc,
      case
           when clos_prc is null and nclos_prc is not null 
           then (nclos_prc - LAG( nclos_prc ignore nulls) OVER (ORDER BY 
cusip)) 
           when  clos_prc is not null and nclos_prc is null
           then LEAD( nclos_prc ignore nulls) OVER (ORDER BY cusip)- LAG( 
nclos_prc ignore nulls) OVER (ORDER BY cusip)
          else NULL
          end DIFF
    from  MKTDATA.MARKET_DAILY_SUMMARY 
    WHERE day_sum_eff >=  '1-JUN-2017' and 
          day_sum_eff <=  '10-JUN-2017' )
)
SELECT ticker_symb, 
       day_sum_eff, 
       cusip, 
       clos_prc, 
       nclos_prc,
       diff
  FROM q1
ORDER BY  day_sum_eff_,fmr_iss_cusip OFFSET 0 ROWS FETCH NEXT 3 ROW ONLY