ORACLE 9i PLSQL优化器导致常量变量的某些性能问题

Question

我正在开发一些基于夜间的批处理作业，在这些作业中，我使用rowids来定位表中的记录，但是如果rowid未被传递，则总是会有一些逻辑键的回退。

我开发了一个小测试用例来向您展示我的问题所在，并提供一些您可以在自己的环境中复制的东西。

创建表并将一些数据放入其中

create table table_with_4M_records (varchar_column varchar2(7));

begin
  for i in 1..4000000
  loop
    insert into table_with_4M_records(varchar_column) values (''||lpad(i,7,0));
  end loop;
end;

我发现如果我运行的是：

DECLARE
  w_rowid constant VARCHAR2(30) := 'AAF6CCAAnAAAaz9AHX';
  w_counter NUMBER;
  w_t0 TIMESTAMP;
  w_t1 TIMESTAMP;
  w_time_diff_in_ms NUMBER:=null;
BEGIN
  w_t0 := SYSTIMESTAMP;
  SELECT count(*) 
  INTO w_counter 
  FROM table_with_4M_records 
  WHERE ((w_rowid IS NOT NULL AND ROWID = w_rowid )
          OR
          (w_rowid IS NULL  /*Then do some heavy operations which I only want to do if the rowid comes with no value*/ )
        );
  w_t1 := SYSTIMESTAMP;      
  SELECT EXTRACT(DAY FROM diff )*24*60*60*1000 +             --Days
         EXTRACT(HOUR FROM diff )*60*60*1000 +               --Hours
         EXTRACT(MINUTE FROM diff )*60*1000 +                --Minutes
   round(EXTRACT(SECOND FROM diff )*1000)total_milliseconds  --Seconds
    INTO w_time_diff_in_ms
  FROM (SELECT (w_t1-w_t0) diff FROM dual);

  dbms_output.put_line('REC COUNTER: '||w_counter);
  dbms_output.put_line('APROX EXEC TIME IN MILLISECS: '||w_time_diff_in_ms);

END;

我有几秒钟的时间，大概5秒.怪怪的对吧？因为我使用的是ROWID，应该是直接获取的。

但是如果我用值替换代码中的w_rowid。就像这样：

DECLARE
  w_rowid constant VARCHAR2(30) := 'AAAGg5AAWAAAaffAA0';
  w_counter NUMBER;
  w_t0 TIMESTAMP;
  w_t1 TIMESTAMP;
  w_time_diff_in_ms NUMBER:=null;
BEGIN
  w_t0 := SYSTIMESTAMP;
  SELECT count(*) 
  INTO w_counter 
  FROM table_with_4M_records 
  WHERE (('AAF6CCAAnAAAaz9AHX' IS NOT NULL AND ROWID = 'AAF6CCAAnAAAaz9AHX' )
          OR
          ('AAF6CCAAnAAAaz9AHX' IS NULL  /*Then do some heavy operations witch I only want to do if the rowid comes with no value*/ )
        );
  w_t1 := SYSTIMESTAMP;      
  SELECT EXTRACT(DAY FROM diff )*24*60*60*1000 +             --Days
         EXTRACT(HOUR FROM diff )*60*60*1000 +               --Hours
         EXTRACT(MINUTE FROM diff )*60*1000 +                --Minutes
   round(EXTRACT(SECOND FROM diff )*1000)total_milliseconds  --Seconds
    INTO w_time_diff_in_ms
  FROM (SELECT (w_t1-w_t0) diff FROM dual);

  dbms_output.put_line('REC COUNTER: '||w_counter);
  dbms_output.put_line('APROX EXEC TIME IN MILLISECS: '||w_time_diff_in_ms);

END;

我的执行时间几乎为零。(？)

我发现问题主要是检查是否为null，如果我删除/注释代码，我的次数也接近于零.

最后一个代码片段在这里：

DECLARE
  w_rowid constant VARCHAR2(30) := 'AAF6CCAAnAAAaz9AHX';
  w_counter NUMBER;
  w_t0 TIMESTAMP;
  w_t1 TIMESTAMP;
  w_time_diff_in_ms NUMBER:=null;
BEGIN
  w_t0 := SYSTIMESTAMP;
  SELECT count(*) 
  INTO w_counter 
  FROM table_with_4M_records 
  WHERE ((w_rowid IS NOT NULL AND ROWID = w_rowid )
          --OR
          --(w_rowid IS NULL  /*Then do some heavy operations witch I only want to do if the rowid comes with no value*/ )
        );
  w_t1 := SYSTIMESTAMP;      
  SELECT EXTRACT(DAY FROM diff )*24*60*60*1000 +             --Days
         EXTRACT(HOUR FROM diff )*60*60*1000 +               --Hours
         EXTRACT(MINUTE FROM diff )*60*1000 +                --Minutes
   round(EXTRACT(SECOND FROM diff )*1000)total_milliseconds  --Seconds
    INTO w_time_diff_in_ms
  FROM (SELECT (w_t1-w_t0) diff FROM dual);

  dbms_output.put_line('REC COUNTER: '||w_counter);
  dbms_output.put_line('APROX EXEC TIME IN MILLISECS: '||w_time_diff_in_ms);

END;

如果你们中的任何人有什么好的建议来解决这个问题，我会非常感激的。如果您中的任何一个人能够用不同版本的oracle在本地环境中测试这段代码，我也会很感激。

我目前正在运行Oracle9.2I，我认为优化器会更聪明，并意识到我使用的是一个常量varchar2，它在查询中没有改变。如果第一次为非空，则在该查询的所有测试用例中都不为空.我显然错了

非常感谢。

Answer 1

在第一种情况下：

WHERE ((w_rowid IS NOT NULL AND ROWID = w_rowid ) OR
          (w_rowid IS NULL  /*Then do some heavy operations*/ )
        );

查询优化器不会对OR条件进行“懒惰”评估。它评估一切，包括你的“重型操作”。还没有意识到您将w_rowid声明为常量。

在第二种情况下：

WHERE (('AAF6CCAAnAAAaz9AHX' IS NOT NULL AND ROWID = 'AAF6CCAAnAAAaz9AHX' ) OR
          ('AAF6CCAAnAAAaz9AHX' IS NULL  /*Then do some heavy operations*/ )
        );

优化器可以在编译时简化表达式，因为它具有常量值，因此忽略了OR的后半部分。

Answer 2

为什么您甚至需要在SQL代码中包括w_rowid？您提前知道它是否为空，所以将其提升到PL/SQL代码，并为这两种不同的情况优化SQL查询(为清晰起见删除一些代码)：

DECLARE
  w_rowid constant VARCHAR2(30) := 'AAF6CCAAnAAAaz9AHX';
  w_counter NUMBER;

BEGIN
  IF w_rowid IS NOT NULL THEN
      SELECT count(*) 
      INTO w_counter 
      FROM table_with_4M_records 
      WHERE ROWID = w_rowid;
  ELSE
      /* do another select if w_rowid is null */
  END IF;


END;