显式游标范围大小和复杂间隔的相关问题介绍-入门基础-IT客

　　我们的技术专家回答关于游标、范围（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 find X=29000
　　Incremental cgets:　　4,101
　　Implicit to find X=29000
　　Incremental cgets:　　4,219
　　
　　PL/SQL procedure successfully completed.
　　
　　现在你就可以明白在你的例子中为什么显式游标好像比隐式游标运行得更快了。当我使用显式游标进行测试的时候，只取一次数据X＝1，为了找到答案，查询只需要扫描非常少的块（很少的一致的读次数）。然而，只要我使显式游标来进行隐式游标的工作，检查确保没有其他记录满足同一条件，你就会看到显式游标检查表中的每一个块。现在，我接着说隐式游标，通过使用ROWNUM=1看看它是否也会在找到第一条符合条件的记录时停下来，它和显式游标做相同的工作量。当它检查表中的第二行是否符合条件时，你会看到它同显式游标一样进行相同次数的一致读；它也不得不对表进行全面扫描以核定只有一行X＝1。
　　
　　最有趣的是当我查询X＝29,000的时候。因为那行接近表的"结尾"，所以无论我采用什么方法，两个查询的工作量都差不多。为了找到满足条件的第一行它们都必须扫描几乎整个表。
　　
　　现在，如果在X上有一个索引，两个查询都会使用索引范围扫描，而且两个查询都不必对表进行全面扫描，便能快速地发现只有一行满足条件。
　　
　　这就解释了你的游标行为：SELECT INTO检查第二行，但显式游标却不这么做。如果你对应地进行比较：第二次显式地取数据或者把"rownum = 1"添加到SELECT INTO语句中－－你就会发现两个游标的工作量相同。
　　
　　简而言之，隐式游标更好。它们比使用显式游标的相同代码运行地更快，更容易编码（需要键入的代码更少），而且我个人认为使用隐士游标的代码更容易读也更容易理解。
　　
　　小、中和大
　　在我们的新应用程序中，我们设计了数据库并创建了数据模型，甚至还估计了表的大小并为每个标指定了存储参数。但现在我们的数据库管理员告诉我们将给我们三个表空间：范围大小统一为160K的TS_small表空间、范围大小统一为5MB的TS_med表空间和范围大小统一为160MB的TS_large表空间。他们告诉我们在TS_small中创建小于5MB的表，在TS_med中创建小于160MB的表，在TS_large中创建大于160MB的表。另外，他们不希望我们对表使用任何存储参数。对索引也是这样。这好像并不合理，因为对于一个预计大小为120MB的表，我们应把它放在TS_med中，接下来如果我们在那个表空间中创建它，它会占24个范围！数据库管理员声称许多测试已经证明这种设计提供了最佳的性能并可以防止碎片。我的问题是，他们说的对吗？我担心对象会有太多的范围。
　　
　　看来他们已经读过asktom Web站点（asktom.oracle.com）和互联网讨论组的相关内容，并发现了好的建议。从他们的数字看，我注意到他们允许一个表占用的最大空间是5GB，可以有32个或更少的范围。假设上百个（或者上千个）范围不会影响运行时数据操纵语言（DML）的性能，我会说他们做得非常好。
　　
　　他们的前提都是正确的：