ORACLE SQL性能優(yōu)化系列 (十一)

2024-08-29 13:30:39

字體：大中小

供稿：網(wǎng)友

36.       用union替換or (適用于索引列)

通常情況下, 用union替換where子句中的or將會起到較好的效果. 對索引列使用or將造成全表掃描. 注意, 以上規(guī)則只針對多個索引列有效. 如果有column沒有被索引, 查詢效率可能會因為你沒有選擇or而降低.

   在下面的例子中, loc_id 和region上都建有索引.

高效:

   select loc_id , loc_desc , region

   from location

   where loc_id = 10

   union

   select loc_id , loc_desc , region

   from location

   where region = “melbourne”

低效:

   select loc_id , loc_desc , region

   from location

   where loc_id = 10 or region = “melbourne”

如果你堅持要用or, 那就需要返回記錄最少的索引列寫在最前面.

注意:

where key1 = 10   (返回最少記錄)

or key2 = 20        (返回最多記錄)

oracle 內(nèi)部將以上轉(zhuǎn)換為

where key1 = 10 and

((not key1 = 10) and key2 = 20)

譯者按:

下面的測試數(shù)據(jù)僅供參考: (a = 1003 返回一條記錄 , b = 1 返回1003條記錄)

sql> select * from unionvsor /*1st test*/

2   where a = 1003 or b = 1;

1003 rows selected.

execution plan

----------------------------------------------------------

   0      select statement optimizer=choose

   1    0   concatenation

   2    1     table access (by index rowid) of 'unionvsor'

   3    2       index (range scan) of 'ub' (non-unique)

   4    1     table access (by index rowid) of 'unionvsor'

   5    4       index (range scan) of 'ua' (non-unique)

statistics

----------------------------------------------------------

          0 recursive calls

          0 db block gets

        144 consistent gets

          0 physical reads

          0 redo size

      63749 bytes sent via sql*net to client

       7751 bytes received via sql*net from client

         68 sql*net roundtrips to/from client

          0 sorts (memory)

          0 sorts (disk)

       1003 rows processed

sql> select * from unionvsor /*2nd test*/

2 where b = 1 or a = 1003 ;

1003 rows selected.

execution plan

----------------------------------------------------------

   0      select statement optimizer=choose

   1    0   concatenation

   2    1     table access (by index rowid) of 'unionvsor'

   3    2       index (range scan) of 'ua' (non-unique)

   4    1     table access (by index rowid) of 'unionvsor'

   5    4       index (range scan) of 'ub' (non-unique)

statistics

----------------------------------------------------------

          0 recursive calls

          0 db block gets

        143 consistent gets

          0 physical reads

          0 redo size

      63749 bytes sent via sql*net to client

       7751 bytes received via sql*net from client

         68 sql*net roundtrips to/from client

          0 sorts (memory)

          0 sorts (disk)

       1003 rows processed

sql> select * from unionvsor /*3rd test*/

2 where a = 1003

3 union

4   select * from unionvsor

5   where b = 1;

1003 rows selected.

execution plan

----------------------------------------------------------

   0      select statement optimizer=choose

   1    0   sort (unique)

   2    1     union-all

   3    2       table access (by index rowid) of 'unionvsor'

   4    3         index (range scan) of 'ua' (non-unique)

   5    2       table access (by index rowid) of 'unionvsor'

   6    5         index (range scan) of 'ub' (non-unique)

statistics

----------------------------------------------------------

          0 recursive calls

          0 db block gets

         10 consistent gets

          0 physical reads

          0 redo size

      63735 bytes sent via sql*net to client

       7751 bytes received via sql*net from client

         68 sql*net roundtrips to/from client

          1 sorts (memory)

          0 sorts (disk)

       1003 rows processed

用union的效果可以從consistent gets和 sql*net的數(shù)據(jù)交換量的減少看出

37.       用in來替換or

下面的查詢可以被更有效率的語句替換:

低效:

select….

from location

where loc_id = 10

or     loc_id = 20

or     loc_id = 30

高效

select…

from location

where loc_in in (10,20,30);



譯者按:

這是一條簡單易記的規(guī)則，但是實際的執(zhí)行效果還須檢驗，在oracle8i下，兩者的執(zhí)行路徑似乎是相同的．　

38.       避免在索引列上使用is null和is not null

避免在索引中使用任何可以為空的列，oracle將無法使用該索引．對于單列索引，如果列包含空值，索引中將不存在此記錄. 對于復合索引，如果每個列都為空，索引中同樣不存在此記錄.　如果至少有一個列不為空，則記錄存在于索引中．

舉例:

如果唯一性索引建立在表的a列和b列上, 并且表中存在一條記錄的a,b值為(123,null) , oracle將不接受下一條具有相同a,b值（123,null）的記錄(插入). 然而如果

所有的索引列都為空，oracle將認為整個鍵值為空而空不等于空. 因此你可以插入1000

條具有相同鍵值的記錄,當然它們都是空!

      因為空值不存在于索引列中,所以where子句中對索引列進行空值比較將使oracle停用該索引.

舉例:

低效: (索引失效)

select …

from department

where dept_code is not null;

高效: (索引有效)

select …

from department

where dept_code >=0;

最大的網(wǎng)站源碼資源下載站，

上一篇：ORACLE常用傻瓜問題1000問(之五)

下一篇：ORACLE SQL性能優(yōu)化系列 (十二)