顯式游標范圍大小和復雜間隔的相關問題介紹

　　我們的技術專家回答關於游標范圍（extent）和間隔的問題
　　是不是從Oracle第版以後的版本隱式游標得到了優化不會兩次取數據？還有為什麼當表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中隱式游標比顯式游標運行得更快是一個事實在Oracle 版之前的版本中就是這樣事實上我在Oracle 版中就測試過這樣的情況並得到了同樣的結論（這些測試請參見/~tkyte/l）隱式游標運行得更快的原因（FOR LOOP隱式游標和SELECT INTO隱式游標）是PL/SQL引擎只需要解釋和執行很少的代碼一般來說PL/SQL引擎在後台做的越多程序就運行地越快上面的隱式游標只使用了一行PL/SQL代碼顯式游標至少使用了三行代碼如果要正確地運行實際上要使用行代碼你的顯式代碼並不像隱式游標那樣運行它要確保你得到一條且只得到一條記錄你的顯式代碼缺少了許多你要做的工作為了精確地比較你的兩個游標例子你的顯式代碼應該被擴展出以下幾行
　　
　　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 )
　　　pctfree pctused ;
　　Table created
　　
　　SQL> insert into t
　　　select rownum
　　　　from all_objects;
　　 rows created
　　
　　SQL> analyze table t compute statistics;
　　Table analyzed
　　
　　SQL> select blocks empty_blocks num_rows
　　　　from user_tables
　　　 where table_name = T;
　　
　　BLOCKS　　 EMPTY_BLOCKS　　 NUM_ROWS
　　　　
　　　　　　　　　　　　
　　
　　我創建了一個有許多數據塊的表值pctfree 為隨後更新數據保留了％的塊作為空閒空間因此即使表中的數據量很小表本身也相當大接著我通過INSERT把值一直到嚴格按順序插入到表中因此X＝在該表的第一個塊中而X＝在表中相當接近表的最後一個塊
　　
　　接下來我將運行一個小PL/SQL塊它會顯示各種隱式和顯式游標對數據進行一致讀的次數因為沒有索引查詢將對整個表進行全面掃描一旦我運行這個程序然後評審查詢結果將很容易對性能的差異進行量化
　　
　　SQL> declare
　　　　 l_last_cgets number default ;
　　　　 l_x　　　number;
　　　　 cursor c( p_x in number ) is
　　　　 select x
　　　　 from t
　　　　　　　where x = p_x;
　　
　　　procedure cgets( p_msg in varchar
　　)
　　　is
　　　　l_value number;
　　　begin
　　　　select bvalue into l_value
　　　　　from v$statname a v$mystat b
　　　　 where astatistic# = bstatistic#
　　　　　 and aname = consistent gets;
　　
　　　　dbms_outputput_line( p_msg );
　　　　dbms_outputput_line
　　　　(　Incremental cgets: ||
　　　　　to_char(l_valuel_last_cgets
　　　　　　　　　　　　　 ) );
　　　　l_last_cgets := l_value;
　　　end;
　　
　　　begin
　　　　cgets(Starting);
　　
　　　　open c();
　　　　fetch c into l_x;
　　　　close c;
　　　　cgets(Explicit to find X= ||
　　　　　　　　　stop at first hit );
　　
　　　　open c();
　　　　fetch c into l_x;
　　　　fetch c into l_x;
　　　　close c;
　　　　cgets(Explicit to find X= ||
　　　　　　　　　check for dups );
　　
　　　　select x into l_x
　　　　　from t
　　　　 where x = AND rownum = ;
　　　　cgets(Implicit to find X= ||
　　　　　　　　　stop at first hit );
　　
　　　　select x into l_x
　　　　　from t
　　　　 where x = ;
　　　　cgets(Implicit to find X= ||
　　　　　　　　　check for dups );
　　
　　　　open c();
　　　　fetch c into l_x;
　　　　close c;
　　　　cgets(Explicit to find X=);
　　
　　　　select x into l_x
　　　　　　　　from t
　　　　　　　 where x = ;
　　　　cgets(Implicit to find X=);
　　　end;
　　　/
　　Starting
　　Incremental cgets:　
　　Explicit to find X= stop at first hit
　　Incremental cgets:　　&nb
　　sp;　
　　Explicit to find X= check for dups
　　Incremental cgets:　　
　　Implicit to find X= stop at first hit
　　Incremental cgets:　　　　
　　Implicit to find X= check for dups
　　Incremental cgets:　　
　　Explicit to find X=
　　Incremental cgets:　　
　　Implicit to find X=
　　Incremental cgets:　　
　　
　　PL/SQL procedure successfully completed
　　
　　現在你就可以明白在你的例子中為什麼顯式游標好像比隱式游標運行得更快了當我使用顯式游標進行測試的時候只取一次數據X＝為了找到答案查詢只需要掃描非常少的塊（很少的一致的讀次數）然而只要我使顯式游標來進行隱式游標的工作檢查確保沒有其他記錄滿足同一條件你就會看到顯式游標檢查表中的每一個塊現在我接著說隱式游標通過使用ROWNUM=看看它是否也會在找到第一條符合條件的記錄時停下來它和顯式游標做相同的工作量當它檢查表中的第二行是否符合條件時你會看到它同顯式游標一樣進行相同次數的一致讀它也不得不對表進行全面掃描以核定只有一行X＝
　　
　　最有趣的是當我查詢X＝的時候因為那行接近表的結尾所以無論我采用什麼方法兩個查詢的工作量都差不多為了找到滿足條件的第一行它們都必須掃描幾乎整個表
　　
　　現在如果在X上有一個索引兩個查詢都會使用索引范圍掃描而且兩個查詢都不必對表進行全面掃描便能快速地發現只有一行滿足條件
　　
　　這就解釋了你的游標行為SELECT INTO檢查第二行但顯式游標卻不這麼做如果你對應地進行比較第二次顯式地取數據或者把rownum = 添加到SELECT INTO語句中－－你就會發現兩個游標的工作量相同
　　
　　簡而言之隱式游標更好它們比使用顯式游標的相同代碼運行地更快更容易編碼（需要鍵入的代碼更少）而且我個人認為使用隱士游標的代碼更容易讀也更容易理解
　　
　　小中和大
　　在我們的新應用程序中我們設計了數據庫並創建了數據模型甚至還估計了表的大小並為每個標指定了存儲參數但現在我們的數據庫管理員告訴我們將給我們三個表空間范圍大小統一為K的TS_small表空間范圍大小統一為MB的TS_med表空間和范圍大小統一為MB的TS_large表空間他們告訴我們在TS_small中創建小於MB的表在TS_med中創建小於MB的表在TS_large中創建大於MB的表另外他們不希望我們對表使用任何存儲參數對索引也是這樣這好像並不合理因為對於一個預計大小為MB的表我們應把它放在TS_med中接下來如果我們在那個表空間中創建它它會占個范圍！數據庫管理員聲稱許多測試已經證明這種設計提供了最佳的性能並可以防止碎片我的問題是他們說的對嗎？我擔心對象會有太多的范圍
　　
　　看來他們已經讀過asktom Web站點（）和互聯網討論組的相關內容並發現了好的建議從他們的數字看我注意到他們允許一個表占用的最大空間是GB可以有個或更少的范圍假設上百個（或者上千個）范圍不會影響運行時數據操縱語言（DML）的性能我會說他們做得非常好
　　
　　他們的前提都是正確的
From:http://tw.wingwit.com/Article/program/Oracle/201311/16936.html