如何减少具有巨大数据的表的查询执行时间 [英] How to reduce query execution time for table with huge data

问题描述

我在生产环境中运行这个查询（Oracle），它需要超过3分钟。有没有办法减少执行时间？ svc_order和事件表包含近100万条记录。

I am running this query in production(Oracle) and it is taking more than 3 minutes . Is there any way out to reduce the execution time ? Both svc_order and event table contains almost 1million records .

select 0 test_section, count(1) count, 'DD' test_section_value  
from svc_order so, event e  
where so.svc_order_id = e.svc_order_id  
  and so.entered_date >= to_date('01/01/2012', 'MM/DD/YYYY')  
  and e.event_type = 230 and e.event_level = 'O'  
  and e.current_sched_date between 
      to_date( '09/01/2010 00:00:00', 'MM/DD/YYYY HH24:MI:SS')
      and to_date('09/29/2013 23:59:59', 'MM/DD/YYYY HH24:MI:SS')  
  and (((so.sots_ta = 'N') and (so.action_type = 0)) 
       or  ((so.sots_ta is null) and (so.action_type = 0)) 
       or  ((so.sots_ta = 'N') and (so.action_type is null)))
  and so.company_code = 'LL'

推荐答案

看看你说的不能创建索引。我希望查询在表上进行全表扫描。请尝试并行提示。

Looking at the what you said that you cannot create indexes. I hope that the query is making a full table scan on the table. Please try a parallel hint.

select /*+ full(so) parallel(so, 4) */ 0 test_section, count(1) count, 'DD' test_section_value  
from svc_order so, event e  
where so.svc_order_id = e.svc_order_id  
  and so.entered_date >= to_date('01/01/2012', 'MM/DD/YYYY')  
  and e.event_type = 230 and e.event_level = 'O'  
  and e.current_sched_date between 
      to_date( '09/01/2010 00:00:00', 'MM/DD/YYYY HH24:MI:SS')
      and to_date('09/29/2013 23:59:59', 'MM/DD/YYYY HH24:MI:SS')  
  and (((so.sots_ta = 'N') and (so.action_type = 0)) 
       or  ((so.sots_ta is null) and (so.action_type = 0)) 
       or  ((so.sots_ta = 'N') and (so.action_type is null)))
  and so.company_code = 'LL'

这篇关于如何减少具有巨大数据的表的查询执行时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！