如何有效的Oracle是否处理IN操作符列表很长

我有以下查询(这是一个更复杂的查询的简化版本):

SELECT * FROM TPM_TASK WHERE (PROJECTID, VERSIONID) IN ((3,1), (24,1), (4,1))

在代码中,我将构建(PROJECTID,VERSIONID)键列表编程,并且这个名单可能是一对夫妇一千双长。

我的问题是Oracle将如何优化这个查询,因为ProjectIdVersionId进行索引。 将列表转换为一个哈希表,类似于join对临时表? 或将每个键查找完成一次?

我想这下查询我的测试数据库并获得:

SELECT STATEMENT 68.0 68 2989732 19 8759 68 ALL_ROWS TABLE ACCESS (FULL) 68.0 68 2989732 19 8759 1 TPMDBO TPM_TASK FULL TABLE ANALYZED 1

不过,我相信这个数据库没有足够的数据,以保证索引扫描。 我试着对生产的查询,并得到:

SELECT STATEMENT 19.0 19 230367 23 9683 19 ALL_ROWS INLIST ITERATOR 1 TABLE ACCESS (BY INDEX ROWID) 19.0 19 230367 23 9683 1 TPMDBO TPM_TASK BY INDEX ROWID TABLE ANALYZED 1 INDEX (RANGE SCAN) 4.0 4 64457 29 1 TPMDBO TPM_H1_TASK RANGE SCAN INDEX ANALYZED 1

这似乎击中了指数,但是我不知道什么INLIST ITERATOR手段。 我猜这意味着Oracle通过列表迭代,做对列表中的每个项目,这可能不会与成千上万的键太有效率的表的访问。 不过,也许甲骨文是足够聪明,优化这个更好,如果我居然给它几千年的密钥。

注意:我不想这些密钥加载到一个临时表,因为坦率地说,我不喜欢的方式临时表甲骨文下工作,他们通常最终会在更多的挫折比他们的价值(在我非专家意见无论如何。)

--------------解决方案-------------

优化应该立足对列表中的项目的数量和表中的行数决定。 如果表中有数百万行和列表中有甚至几千项,我一般会期望它会使用索引做一对夫妇一千单排查找。 如果表中有几千行的列表中有几千项,我期望优化器做表进行全面扫描。 在中间,当然,是所有有趣的事情发生,它变得更难制定出什么计划优化器要选择。

但一般情况下,动态地构建这种查询将是从性能的角度,不是因为某个特定查询的执行有多贵​​,而是因为你正在生成的查询不共享问题。 由于不能使用绑定变量(或者,如果你正在使用绑定变量,你需要不同数量的绑定变量)。 这迫使甲骨文每次做查询的一个相当昂贵的硬解析,并提出您的共享池,这将可能迫使出可以共享,这将导致更多的硬盘系统中的解析其他查询压力。 你通常会得到更好的服务折腾你想匹配到一个临时表(或甚至永久表),使您的查询就可以进行共享和解析只有一次的数据。

要布兰科的评论,而甲骨文是仅限于在1000文字IN列表中,这只是如果你使用的是“正常”的语法,即

WHERE projectID IN (1,2,3,...,N)

如果您使用您先前发布的元组语法,但是,你可以拥有的元素数量不限。

因此,举例来说,我会,如果我建立一个查询,在2000年的项目得到一个错误IN列表

SQL> ed
Wrote file afiedt.buf

1 declare
2 l_sql_stmt varchar2(32000);
3 l_cnt integer;
4 begin
5 l_sql_stmt := 'select count(*) from emp where empno in (';
6 for i in 1..2000
7 loop
8 l_sql_stmt := l_sql_stmt || '(1),';
9 end loop;
10 l_sql_stmt := rtrim(l_sql_stmt,',') || ')';
11 -- pl( l_sql_stmt );
12 execute immediate l_sql_stmt into l_cnt;
13* end;
SQL> /
declare
*
ERROR at line 1:
ORA-01795: maximum number of expressions in a list is 1000
ORA-06512: at line 12

但如果我用的元组语法

SQL> ed
Wrote file afiedt.buf

1 declare
2 l_sql_stmt varchar2(32000);
3 l_cnt integer;
4 begin
5 l_sql_stmt := 'select count(*) from emp where (empno,empno) in (';
6 for i in 1..2000
7 loop
8 l_sql_stmt := l_sql_stmt || '(1,1),';
9 end loop;
10 l_sql_stmt := rtrim(l_sql_stmt,',') || ')';
11 -- pl( l_sql_stmt );
12 execute immediate l_sql_stmt into l_cnt;
13* end;
SQL> /

PL/SQL procedure successfully completed.

更好的解决方案,它不需要临时表,可能是把数据放到一个PL / SQL表,然后再加入它。 汤姆凯特在这里有一个很好的例子:PL / SQL表连接的例子

希望有所帮助。

分类:SQL 时间:2015-03-15 人气:0
本文关键词: SQL,甲骨文,Oracle11g的
分享到:

相关文章

Copyright (C) 55228885.com, All Rights Reserved.

55228885 版权所有 京ICP备15002868号

processed in 0.328 (s). 10 q(s)