显式游标范围大小和复杂间隔的相关问题

我们的技术专家回答关于游标、范围（extent）和间隔的问题。
　　
　　是不是从Oracle7第7.3版以后的版本，隐式游标得到了优化，不会两次取数据？还有，为什么当表T在列X上有一个索引时下面的隐式游标比显式游标运行得更快，而没有索引时是显式游标运行得较快呢？
　　
　　Implicit Cursor:
　　Select x
　　into y
　　from T
　　where x = j;
　　
　　Explicit Cursor:
　　cursor c(p number) is
　　select x from blah where x = p;
　　open c(j);
　　fetch c into y;
　　close c;
　　
　　为了让每个人都了解显式游标和隐式游标是什么，我先简单介绍一下它们的定义。
　　
　　通常，隐式游标是指程序员并不"显式"声明、打开、从中取数据和关闭的那些游标；这些操作都是隐式的。因此，在上面的例子中，SELECT X INTO Y查询就是一个隐式游标。对于它来说并没有"cursor cursor_name is ..."这样的定义语句。相反，第二个例子是典型的显式关标。程序员显式地声明、打开、取数据和关闭它。
　　
　　在PL/SQL中隐式游标比显式游标运行得更快是一个事实，在Oracle7 7.3版之前的版本中就是这样。事实上，我在Oracle7 7.1版中就测试过这样的情况并得到了同样的结论（这些测试请参见asktom.oracle.com/~tkyte/ivse.html）。隐式游标运行得更快的原因（FOR LOOP隐式游标和SELECT INTO隐式游标）是PL/SQL引擎只需要解释和执行很少的代码。一般来说，PL/SQL引擎在后台做的越多，程序就运行地越快。上面的隐式游标只使用了一行PL/SQL代码；显式游标至少使用了三行代码，如果要"正确地"运行，实际上要使用6行代码。你的显式代码并不像隐式游标那样运行，它要确保你得到一条且只得到一条记录。你的显式代码缺少了许多你要做的工作。为了精确地比较你的两个游标例子，你的显式代码应该被扩展出以下几行：
　　
　　open c(j);
　　fetch c into y;
　　if ( c%notfound ) then raise NO_DATA_FOUND;
　　end if;
　　fetch c into y;
　　if ( c%found ) then raise TOO_MANY_ROWS;
　　end if;
　　close c;
　　
　　如果这就是你的显式游标，你会发现在所有情况下显式游标都运行得比较慢，甚至于无论你的例子中有没有索引都是这样。
　　
　　那么，你的问题的症结所在是：为什么在你的例子中没有索引时，隐式游标好像运行地非常慢，然而当存在一个索引的时候，隐式游标却运行得较快呢？答案在于全表扫描，事实上在得到一条记录后，你的显式测试就停止了。我将给出一个例子来向你展示它们之间的不同之处：
　　
　　SQL> create table t ( x int )
　　2　pctfree 99 pctused 1;
　　Table created.
　　
　　SQL> insert into t
　　2　select rownum
　　3　　from all_objects;
　　29264 rows created.
　　
　　SQL> analyze table t compute statistics;
　　Table analyzed.
　　
　　SQL> select blocks, empty_blocks, num_rows
　　2　　from user_tables
　　3　 where table_name = 'T';
　　
　　BLOCKS　　 EMPTY_BLOCKS　　 NUM_ROWS
　　-------------　------------　 -----------
　　4212　　　　　140　　　　　 29264
　　
　　我创建了一个有许多数据块的表；值pctfree 99为随后更新数据保留了99％的块作为"空闲空间"。因此，即使表中的数据量很小，表本身也相当大。接着，我通过INSERT把值1，2，3，...一直到29,264严格按顺序插入到表中。因此，X＝1在该表的"第一个"块中而X＝29,000在表中相当接近表的最后一个块。
　　
　　接下来，我将运行一个小PL/SQL块，它会显示各种隐式和显式游标对数据进行一致读的次数。因为没有索引，查询将对整个表进行全面扫描。一旦我运行这个程序然后评审查询结果，将很容易对性能的差异进行量化。
　　
　　SQL> declare
　　2　　 l_last_cgets number default 0;
　　3　　 l_x　　　number;
　　4　　 cursor c( p_x in number ) is
　　5　　 select x
　　6　　 from t
　　7　　　　　where x = p_x;
　　8
　　9　procedure cgets( p_msg in varchar2
　　)
　　10　is
　　11　　l_value number;
　　12　begin
　　13　　select b.value into l_value
　　14　　　from v$statname a, v$mystat b
　　15　　 where a.statistic# = b.statistic#
　　16　　　 and a.name = 'consistent gets';
　　17
　　18　　dbms_output.put_line( p_msg );
　　19　　dbms_output.put_line
　　20　　(　'Incremental cgets: ' ||
　　21　　　to_char(l_value-l_last_cgets,
　　22　　　　　　　　　　　 '999,999') );
　　23　　l_last_cgets := l_value;
　　24　end;
　　25
　　26　begin
　　27　　cgets('Starting');
　　28
　　29　　open c(1);
　　30　　fetch c into l_x;
　　31　　close c;
　　32　　cgets('Explicit to find X=1 ' ||
　　33　　　　　　　'stop at first hit' );
　　34
　　35　　open c(1);
　　36　　fetch c into l_x;
　　37　　fetch c into l_x;
　　38　　close c;
　　39　　cgets('Explicit to find X=1 ' ||
　　40　　　　　　　'check for dups' );
　　41
　　42　　select x into l_x
　　43　　　from t
　　44　　 where x = 1 AND rownum = 1;
　　45　　cgets('Implicit to find X=1 ' ||
　　46　　　　　　　'stop at first hit' );
　　47
　　48　　select x into l_x
　　49　　　from t
　　50　　 where x = 1;
　　51　　cgets('Implicit to find X=1 ' ||
　　52　　　　　　　'check for dups' );
　　53
　　54　　open c(29000);
　　55　　fetch c into l_x;
　　56　　close c;
　　57　　cgets('Explicit to find X=29000');
　　58
　　59　　select x into l_x
　　60　　　　　　from t
　　61　　　　　 where x = 29000;
　　62　　cgets('Implicit to find X=29000');
　　63　end;
　　64　/
　　Starting
　　Incremental cgets:　514,690
　　Explicit to find X=1 stop at first hit
　　Incremental cgets:　　&nb
　　sp;　 4
　　Explicit to find X=1 check for dups
　　Incremental cgets:　　4,220
　　Implicit to find X=1 stop at first hit
　　Incremental cgets:　　　　4
　　Implicit to find X=1 check for dups
　　Incremental cgets:　　4,219
　　Explicit to fin