为什么查询花费的时间太长 [英] why query is taking too long

问题描述

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

I have a query that should return around 10000 rows. The db itself is very large. I have run a simple query and it returned a result in less than 3 seconds. But when a more complex code it takes way too long.

在我的代码中，我完成了一个嵌套的select和一个case语句.但是，当我运行代码时，需要一个多小时才能返回结果.我该怎么办才能减少执行时间的代码.

In my code I have done a nested select and a case statement. However, when I run my code it takes over an hour to return a result. What can I do to the code that would decrease this execution time.

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

PLAN_TABLE_OUTPUT

计划哈希值:831959278

Plan hash value: 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 |
----------------------------------------------------------

推荐答案

尝试一下:-

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

Create index on day_sum_eff column then run query once again and see will be any changes in execution time or not.

可能是可行的.

这篇关于为什么查询花费的时间太长的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！