优化器应根据列表中的项目数和表中的行数做出决定。如果表有数百万行,并且列表甚至有几千个项目,我通常期望它会使用索引进行几千次单行查找。如果表有几千行并且列表有几千个项目,我希望优化器对表进行完整扫描。当然,在中间,所有有趣的事情都会发生,并且很难准确地计算出优化器将选择什么计划。
然而,一般来说,从性能角度来看,动态构建此类查询将会出现问题,不是因为特定查询执行的成本有多高,而是因为您生成的查询不可共享。由于您不能使用绑定变量(或者,如果您使用绑定变量,则需要不同数量的绑定变量)。这迫使 Oracle 每次都对查询进行相当昂贵的硬解析,并对共享池施加压力,这可能会强制排除其他可共享的查询,从而导致系统中进行更多的硬解析。通常,最好将要匹配的数据放入临时表(甚至永久表)中,这样您的查询就可以共享并仅解析一次。
对于 Branko 的评论,虽然 Oracle 的字面量限制为 1000 个,IN
列表,仅当您使用“正常”语法时,即
WHERE projectID IN (1,2,3,...,N)
但是,如果您使用之前发布的元组语法,则可以拥有无限数量的元素。
因此,举例来说,如果我构建一个包含 2000 个项目的查询,则会收到错误IN
list
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 -- p.l( 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 -- p.l( l_sql_stmt );
12 execute immediate l_sql_stmt into l_cnt;
13* end;
SQL> /
PL/SQL procedure successfully completed.