SQL優化經驗

gholay發表於2014-04-15
SQL優化

SQL優化經驗：
1. 避免SQL中的函式呼叫，否則會產生很多的遞迴呼叫。
2. 如果使用函式呼叫，儘量在最外層使用函式呼叫，否則會產生很多次的遞迴呼叫。
3. 插入多行資料的時候，可以考慮集合寫法。
   eg: insert into t SELECT LEVEL FROM dual CONNECT BY LEVEL < 10 ;
  這樣的寫法比寫for迴圈會快很多。
4. 只取你所需要的列，訪問檢視會更快，因為如果你訪問的列越少，訪問的表也越少，那麼在執行計劃中，可能涉及的表越少，訪問效率越高。
5. 只取你所需要的列，索引讀無需回表了。



針對select count(*) from t的優化方案：
1. 索引什麼都不建立。（可能最慢）
2. 建立普通索引。
3. 建立點陣圖索引。
4. 建立物化檢視。
5. 快取結果集（select /*+ result_cache */ count(*) from t ;）
   如果結果集改變了，這個快取中的資料會失效。而不會查錯。如果經常改變，就會多做事。這種優化就沒有用了。



下面舉個例子。

將1到1000000數字插入到T表：

速度由慢到快的優化方式 ：

1. 使用儲存過程迴圈，並且使用動態sql ，並未繫結變數。

2. 使用儲存過程迴圈，並且使用動態sql ，使用繫結變數。

3. 使用儲存過程迴圈，使用靜態sql. 每次插入資料都commit .

4. 使用儲存過程迴圈，使用靜態sql. 批量提交資料。

5. 使用集合的寫法。 insert into t SELECT rownum FROM dual CONNECT BY LEVEL < 1000000 ; commit ;

6. 使用直接路徑寫法：create table t as select rownum X from dual connect by level<1000000  ;

7. 使用並行。 create table t nologging parallel 64 as select rownum x from dual connect by level < 1000000  ;



count(*)將返回表格中所有存在的行的總數包括值為null的行，然而count(列名)將返回表格中除去null以外的所有行的總數(有預設值的列也會被計入）.  
distinct 列名,得到的結果將是除去值為null和重複資料後的結果.
可以自己去試驗一下下面三種情況的執行結果：
select count(distinct sal) from test;  
select count(sal) from test;  
select count(*) from test;  


not in 與 not exist的測試：
test2中object_id含有空的資料。下面看一下執行計劃：
18:15:47 SQL> select * from test t where not exists(
18:16:09   2  select 1 from test2 i where i.object_id=t.object_id
18:16:09   3  ) ;


已選擇71594行。




執行計劃
----------------------------------------------------------
Plan hash value: 2923433643


------------------------------------------------------------------------------
| Id  | Operation            | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |       | 72537 |  7792K|   565   (1)| 00:00:07 |
|*  1 |  HASH JOIN RIGHT ANTI|       | 72537 |  7792K|   565   (1)| 00:00:07 |
|   2 |   TABLE ACCESS FULL  | TEST2 |  6323 | 82199 |   281   (0)| 00:00:04 |
|   3 |   TABLE ACCESS FULL  | TEST  | 72537 |  6871K|   283   (1)| 00:00:04 |
------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------


   1 - access("I"."OBJECT_ID"="T"."OBJECT_ID")


Note
-----
   - dynamic sampling used for this statement (level=2)




統計資訊
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       6775  consistent gets
          0  physical reads
          0  redo size
    3443327  bytes sent via SQL*Net to client
      52908  bytes received via SQL*Net from client
       4774  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      71594  rows processed


看not in的執行計劃：
18:16:18 SQL> select * from test t where t.object_id not in(
18:16:34   2  select i.object_id from test2 i
18:16:34   3  ) ;


未選定行




執行計劃
----------------------------------------------------------
Plan hash value: 1660021635


--------------------------------------------------------------------------------
-
| Id  | Operation               | Name  | Rows  | Bytes | Cost (%CPU)| Time
|
--------------------------------------------------------------------------------
-
|   0 | SELECT STATEMENT        |       | 72537 |  7792K|   565   (1)| 00:00:07
|
|*  1 |  HASH JOIN RIGHT ANTI NA|       | 72537 |  7792K|   565   (1)| 00:00:07
|
|   2 |   TABLE ACCESS FULL     | TEST2 |  6323 | 82199 |   281   (0)| 00:00:04
|
|   3 |   TABLE ACCESS FULL     | TEST  | 72537 |  6871K|   283   (1)| 00:00:04
|
--------------------------------------------------------------------------------
-


Predicate Information (identified by operation id):
---------------------------------------------------


   1 - access("T"."OBJECT_ID"="I"."OBJECT_ID")


Note
-----
   - dynamic sampling used for this statement (level=2)




統計資訊
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         15  consistent gets
          0  physical reads
          0  redo size
       1184  bytes sent via SQL*Net to client
        405  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed


可以看出來，使用not exists的一致性讀遠遠大於not in。 因為我把test2這張表的資料有部分object_id這一列的資料部分設定為空了。在關聯的欄位如果含有為空的資料比較比，儘量用not in. 一般情況下，not exists和not in在11g以後，如果關聯欄位不為空，代價基本上是一致的。都使用了anti的半連線演算法（排除法）。具體情況需要看執行計劃。我做過試驗，是幾乎一樣的。有好奇的朋友可以自己做實驗驗證。


原來表連線順序的說法早就過時了，那是基於規則的時代，現在我們是基於代價的。與順序無關。
下面可以看一下如下的基於規則的例子來實驗：
select /*+rule*/ count(*) from tab_big,tab_small ;  
select /*+rule*/ count(*) from tab_small,tab_big ;



檢視檢視不需要的欄位儘量不要顯示出來。可以節省效率：
下面看個例子：
drop table t1 cascade constraints purge;
drop table t2 cascade constraints  purge;
create table t1 as select * from dba_objects;
create table t2 as select * from dba_objects where rownum<=10000;
update t1 set object_id=rownum ;
update t2 set object_id=rownum ;
commit;


create or replace view v_t1_join_t2 
as select t2.object_id,t2.object_name,t1.object_type,t1.owner from t1,t2 
where t1.object_id=t2.object_id;


set autotrace traceonly
set linesize 1000
select * from v_t1_join_t2;
select object_id,object_name from v_t1_join_t2;


--做個試驗
alter table T1 add constraint pk_object_id primary key (OBJECT_ID);
alter table T2 add constraint fk_objecdt_id foreign key (OBJECT_ID) references t1 (OBJECT_ID);


select * from v_t1_join_t2;
select object_id,object_name from v_t1_join_t2;
看執行計劃，可以看出來，第二個語句只掃描了一個表的資料，而第一個語句，掃描了兩個表的資料。



大表建議使用分割槽：
drop table part_tab purge;
create table part_tab (id int,col2 int,col3 int)
        partition by range (id)
        (
        partition p1 values less than (10000),
        partition p2 values less than (20000),
        partition p3 values less than (30000),
        partition p4 values less than (40000),
        partition p5 values less than (50000),
        partition p6 values less than (60000),
        partition p7 values less than (70000),
        partition p8 values less than (80000),
        partition p9 values less than (90000),
        partition p10 values less than (100000),
        partition p11 values less than (maxvalue)
        )
        ;
insert into part_tab select rownum,rownum+1,rownum+2 from dual connect by rownum <=110000;
commit;
create  index idx_par_tab_col2 on part_tab(col2) local;
create  index idx_par_tab_col3 on part_tab(col3) ;


drop table norm_tab purge;
create table norm_tab  (id int,col2 int,col3 int);
insert into norm_tab select rownum,rownum+1,rownum+2 from dual connect by rownum <=110000;
commit;
create  index idx_nor_tab_col2 on norm_tab(col2) ;
create  index idx_nor_tab_col3 on norm_tab(col3) ;


set autotrace traceonly statistics
set linesize 1000
set timing on 
select * from part_tab where col2=8 ;
select * from norm_tab where col2=8 ;


select * from part_tab where col2=8 and id=2;
select * from norm_tab where col2=8 and id=2;

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29196873/viewspace-1142130/，如需轉載，請註明出處，否則將追究法律責任。

相關文章