Oracle SELECT语句缓慢 [英] Oracle SELECT statement slow

问题描述

我正在查询.

SELECT ttf.default_text
  FROM test_template_field ttf, TEST t
 WHERE ttf.schema_field_id = 2044
--HERE
   AND ttf.test_template_id = t.test_template_id
   AND t.workflow_node_id IN (
          SELECT wn.workflow_node_id
            FROM lims_sys.workflow_node wn, lims_sys.workflow_user wu
           WHERE wn.workflow_id = wu.workflow_id
             AND wn.workflow_node_type_id = 42
             AND wu.u_external_category IN ('M'))
             group by ttf.DEFAULT_TEXT

这很好，在8秒钟内我得到了结果. 但是，如果我添加另一个AND函数，则需要28分钟才能返回结果. 关于这个，他的位置是"--HERE"

This works fine, and in 8 seconds I get my result back. But if I add another AND function, it will take 28 Minutes before I get my results back. Its about this one, his location was "--HERE"

AND ttf.default_text NOT IN ('Preparation Microbiology', 'Other', 'Preparation')

我不知道为什么这么慢.有人可以帮忙吗?

I don't know why it's slow.. Can someone help?

推荐答案

• 使用 DBMS_STATS 更新表统计信息， Oracle用来决定执行计划
• 如果这样做没有帮助，则可以使用优化程序提示在查询中将查询优化器的踢向正确的方向
• Use DBMS_STATS to update the table statistics that Oracle uses to decide the execution plan
• If this doesn't help, you can use optimizer hints in the query to kick the query optimizer's butt into the right direction

这篇关于Oracle SELECT语句缓慢的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！