SQL是影響資料庫效能的唯一手段。也是資料庫最佳化最重要的部分。SQL語句並不是獨立執行的，它需要在一定的場景下執行，同一個SQL在不同的場景下執行效率的效果可能完全不同。SQL語句在很大程度上要依靠索引、統計量的配合才能達到最高的執行效率。以下舉例在開發過程中SQL的不同寫法及各自的執行效率。每個例子返回的結果都是相同的，但是SQL的寫法不同。主要用例子來直觀的展現出各種寫法的執行效率。下面舉例的各種寫法的效率並不是固定不變的。在不同的資料量，不同的環境下要靈活使用各種寫法，SQL的寫法沒有固定模式，SQL的執行本來就不是獨立的，需要各方面的支援配合，方可效率最大化。複雜的SQL都是由簡單的SQL組成的，在寫SQL的時候一定注意每一部分的執行效率，多檢視執行計劃、統計量資訊。在OLTP系統中，要使每個SQL查詢的資料量儘量的少，在執行事務操作過程中，操作的資料也要儘量的少，事務要儘量的小，多多提交。總結起來就是小事務，多提交。同樣SQL的執行也應該和應用伺服器來配合實用。部分經常使用，但不常改變的資料可以考慮在應用伺服器進行快取來減小資料庫伺服器的壓力。

注意：
1.以下的SQL是在Oracle 10gR2 版本中執行的結果。
2.檢視SQL的效率我們透過(consistent gets+physical reads)值來確定，值越小效率越高，值越大效率越低。

例子1(COUNT的使用):
SQL> create table test as select * from dba_segments;

Table created.

SQL> set autotrace trace explain statistics;
SQL> select count(*) from test where segment_name='TABLE'; //沒有建立索引的表上執行COUNT會導致全表掃描。

Execution Plan
----------------------------------------------------------
Plan hash value: 3467505462

---------------------------------------------------------------------------
| Id | Operation          | Name | Rows | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    42 |    21   (0)| 00:00:01 |
|   1 | SORT AGGREGATE    |      |     1 |    42 |            |          |
|* 2 |   TABLE ACCESS FULL| TEST |     1 |    42 |    21   (0)| 00:00:01 |
---------------------------------------------------------------------------

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

2 - filter("SEGMENT_NAME"='TABLE')

Note
-----
- dynamic sampling used for this statement

Statistics
----------------------------------------------------------
         28 recursive calls
          0 db block gets
        166 consistent gets
          0 physical reads
          0 redo size
        515 bytes sent via SQL*Net to client
        492 bytes received via SQL*Net from client
          2 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
          1 rows processed

SQL> create index idx_tmp01 on test (segment_name);

Index created.

SQL> select count(*) from test where segment_name='TABLE'; //在segment_name欄位上建立有索引，執行count(*)只掃描索引，避免了全表掃面，效能有所提高。

Execution Plan
----------------------------------------------------------
Plan hash value: 1193456058

-------------------------------------------------------------------------------
| Id | Operation         | Name      | Rows | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |           |     1 |    42 |     1   (0)| 00:00:01 |
|   1 | SORT AGGREGATE   |           |     1 |    42 |            |          |
|* 2 |   INDEX RANGE SCAN| IDX_TMP01 |     1 |    42 |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------

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

2 - access("SEGMENT_NAME"='TABLE')

Note
-----
- dynamic sampling used for this statement

Statistics
----------------------------------------------------------
          9 recursive calls
          0 db block gets
         76 consistent gets
          1 physical reads
          0 redo size
        515 bytes sent via SQL*Net to client
        492 bytes received via SQL*Net from client
          2 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
          1 rows processed

SQL> select count(segment_name) from test where segment_name='TABLE'; //在segment_name建立的索引，執行count(*)和count(segment_name)效果是一樣的。

Execution Plan
----------------------------------------------------------
Plan hash value: 1193456058

-------------------------------------------------------------------------------
| Id | Operation         | Name      | Rows | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |           |     1 |    42 |     1   (0)| 00:00:01 |
|   1 | SORT AGGREGATE   |           |     1 |    42 |            |          |
|* 2 |   INDEX RANGE SCAN| IDX_TMP01 |     1 |    42 |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------

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

2 - access("SEGMENT_NAME"='TABLE')

Note
-----
- dynamic sampling used for this statement

Statistics
----------------------------------------------------------
          7 recursive calls
          0 db block gets
         76 consistent gets
          0 physical reads
          0 redo size
        526 bytes sent via SQL*Net to client
        492 bytes received via SQL*Net from client
          2 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
          1 rows processed

SQL> select count(owner) from test where segment_name='TABLE'; //count(owner),owner不是索引欄位，所以會多一步根據索引找到的ROWID來在表上掃描，這對COUNT來說是沒有任何意義的，應避免這種錯誤的出現。

Execution Plan
----------------------------------------------------------
Plan hash value: 3967340716

------------------------------------------------------------------------------------------
| Id | Operation                    | Name      | Rows | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |           |     1 |    59 |     2   (0)| 00:00:01 |
|   1 | SORT AGGREGATE              |           |     1 |    59 |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| TEST      |     1 |    59 |     2   (0)| 00:00:01 |
|* 3 |    INDEX RANGE SCAN          | IDX_TMP01 |     1 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------

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

3 - access("SEGMENT_NAME"='TABLE')

Note
-----
- dynamic sampling used for this statement

Statistics
----------------------------------------------------------
        324 recursive calls
          0 db block gets
        115 consistent gets
          0 physical reads
          0 redo size
        519 bytes sent via SQL*Net to client
        492 bytes received via SQL*Net from client
          2 SQL*Net roundtrips to/from client
          4 sorts (memory)
          0 sorts (disk)
          1 rows processed

例子2(對過濾時間的使用):
SQL> drop table test purge;

Table dropped.

SQL> create table test as select * from dba_tables;

Table created.

SQL> create index idx_tmp01 on test(last_analyzed);

Index created.

SQL> select * from test where trunc(last_analyzed)=to_date('2010-2-22','yyyy-mm-dd'); //不要在欄位上使用函式，這樣會導致全表掃描。

36 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 217508114

--------------------------------------------------------------------------
| Id | Operation         | Name | Rows | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT |      |    39 | 18993 |    19   (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TEST |    39 | 18993 |    19   (0)| 00:00:01 |
--------------------------------------------------------------------------

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

1 - filter(TRUNC(INTERNAL_FUNCTION("LAST_ANALYZED"))=TO_DATE('
2010-02-22 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

Note
-----
- dynamic sampling used for this statement

Statistics
----------------------------------------------------------
        988 recursive calls
          0 db block gets
        251 consistent gets
          0 physical reads
          0 redo size
       6376 bytes sent via SQL*Net to client
        514 bytes received via SQL*Net from client
          4 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
         36 rows processed

SQL> select *
2    from test
3   where last_analyzed >= to_date('2010-2-22', 'yyyy-mm-dd')
4     and last_analyzed <= to_date('2010-2-23', 'yyyy-mm-dd'); //這樣就使用到了欄位last_analyzed上的索引，不會導致全表掃描，效能大幅度提升。

36 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 867549113

-----------------------------------------------------------------------------------------
| Id | Operation                   | Name      | Rows | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           |    36 | 17532 |    11   (0)| 00:00:01 |
|   1 | TABLE ACCESS BY INDEX ROWID| TEST      |    36 | 17532 |    11   (0)| 00:00:01 |
|* 2 |   INDEX RANGE SCAN          | IDX_TMP01 |    36 |       |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

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

   2 - access("LAST_ANALYZED">=TO_DATE(' 2010-02-22 00:00:00', 'syyyy-mm-dd
              hh24:mi:ss') AND "LAST_ANALYZED"<=TO_DATE(' 2010-02-23 00:00:00', 'syyyy-mm-dd
              hh24:mi:ss'))

Note
-----
- dynamic sampling used for this statement

Statistics
----------------------------------------------------------
          9 recursive calls
          0 db block gets
        104 consistent gets
          1 physical reads
          0 redo size
       6740 bytes sent via SQL*Net to client
        514 bytes received via SQL*Net from client
          4 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
         36 rows processed

例子3(對NOT IN的使用):
SQL> create table segments as select * from dba_segments;

Table created.

SQL> create table extents as select * from dba_extents;

Table created.

SQL> create index idx_tmp01 on extents(segment_name);

Index created.

SQL> analyze table extents compute statistics;

Table analyzed.

SQL> set autotrace trace explain statistics;
SQL> set linesize 200
SQL>
SQL> alter system flush buffer_cache;

System altered.

SQL> select *
2    from segments
3   where segment_name not in (select segment_name
4                                from extents
5                               group by segment_name
6                              having count(segment_name) < 20); //not in會導致全表掃描。

12 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 717850282

---------------------------------------------------------------------------------
| Id | Operation            | Name     | Rows | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |          | 1952 |   529K|   702 (11)| 00:00:09 |
|* 1 | FILTER              |          |       |       |            |          |
|   2 |   TABLE ACCESS FULL | SEGMENTS | 1984 |   538K|     9   (0)| 00:00:01 |
|* 3 |   FILTER             |          |       |       |            |          |
|   4 |    HASH GROUP BY     |          |     1 |    16 |    11 (10)| 00:00:01 |
|   5 |     TABLE ACCESS FULL| EXTENTS | 3422 | 54752 |    10   (0)| 00:00:01 |
---------------------------------------------------------------------------------

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

   1 - filter( NOT EXISTS (SELECT /*+ */ 0 FROM "EXTENTS" "EXTENTS"
              GROUP BY "SEGMENT_NAME" HAVING LNNVL("SEGMENT_NAME"<>:B1) AND
              COUNT("SEGMENT_NAME")<20))
   3 - filter(LNNVL("SEGMENT_NAME"<>:B1) AND COUNT("SEGMENT_NAME")<20)

Note
-----
- dynamic sampling used for this statement

Statistics
----------------------------------------------------------
          8 recursive calls
          0 db block gets
      67110 consistent gets
         61 physical reads
          0 redo size
       2427 bytes sent via SQL*Net to client
        491 bytes received via SQL*Net from client
          2 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
         12 rows processed

SQL> alter system flush buffer_cache;

System altered.

SQL> select s1.*
2    from segments s1
3   where not exists (select 1
4            from extents s2
5           where s1.segment_name = s2.segment_name
6           group by s2.segment_name
7          having count(s2.segment_name) < 20); //用not exists代替not in效能得到大幅度提升，not exists只適合操作小資料量的情況下使用。

12 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 732092259

------------------------------------------------------------------------------------
| Id | Operation              | Name      | Rows | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |           | 1952 |   529K|    71   (0)| 00:00:01 |
|* 1 | FILTER                |           |       |       |            |          |
|   2 |   TABLE ACCESS FULL    | SEGMENTS | 1984 |   538K|     9   (0)| 00:00:01 |
|* 3 |   FILTER               |           |       |       |            |          |
|   4 |    SORT GROUP BY NOSORT|           |     1 |    16 |     1   (0)| 00:00:01 |
|* 5 |     INDEX RANGE SCAN   | IDX_TMP01 |     2 |    32 |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------

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

   1 - filter( NOT EXISTS (SELECT /*+ */ 0 FROM "EXTENTS" "S2" WHERE
              "S2"."SEGMENT_NAME"=:B1 GROUP BY "S2"."SEGMENT_NAME" HAVING
              COUNT("S2"."SEGMENT_NAME")<20))
   3 - filter(COUNT("S2"."SEGMENT_NAME")<20)
   5 - access("S2"."SEGMENT_NAME"=:B1)

Note
-----
- dynamic sampling used for this statement

Statistics
----------------------------------------------------------
          7 recursive calls
          0 db block gets
       4052 consistent gets
         43 physical reads
          0 redo size
       2427 bytes sent via SQL*Net to client
        491 bytes received via SQL*Net from client
          2 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
         12 rows processed

SQL> alter system flush buffer_cache;

System altered.

SQL> select s1.*
2    from segments s1
3    left outer join (select segment_name
4                       from extents
5                      group by segment_name
6                     having count(segment_name) < 20) s2 on s1.segment_name =
7                                                            s2.segment_name
8   where s2.segment_name is null;                                              //用左外連線代替not in效率也是非常高的。

12 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 2913256074

----------------------------------------------------------------------------------
| Id | Operation             | Name     | Rows | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |          | 1885 |   589K|    21 (10)| 00:00:01 |
|* 1 | HASH JOIN RIGHT ANTI |          | 1885 |   589K|    21 (10)| 00:00:01 |
|   2 |   VIEW                |          |    95 | 3990 |    11 (10)| 00:00:01 |
|* 3 |    FILTER             |          |       |       |            |          |
|   4 |     HASH GROUP BY     |          |    95 | 1520 |    11 (10)| 00:00:01 |
|   5 |      TABLE ACCESS FULL| EXTENTS | 3422 | 54752 |    10   (0)| 00:00:01 |
|   6 |   TABLE ACCESS FULL   | SEGMENTS | 1984 |   538K|     9   (0)| 00:00:01 |
----------------------------------------------------------------------------------

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

1 - access("S1"."SEGMENT_NAME"="S2"."SEGMENT_NAME")
3 - filter(COUNT("SEGMENT_NAME")<20)

Note
-----
- dynamic sampling used for this statement

Statistics
----------------------------------------------------------
          0 recursive calls
          0 db block gets
         66 consistent gets
         61 physical reads
          0 redo size
       2427 bytes sent via SQL*Net to client
        491 bytes received via SQL*Net from client
          2 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
         12 rows processed

例子4(減少不必要的資料關聯):

SQL> create index idx_tmp003 on segments (owner,segment_type,segment_name);

Index created.

SQL> create index idx_tmp002 on extents (owner,segment_name);

Index created.

SQL> alter system flush buffer_cache;

System altered.

SQL> select s1.*
2    from extents s1,
3         (select owner, segment_name from SEGMENTS where wner = 'SYS') s2
4   where s1.owner = s2.owner
5     and s1.segment_name = s2.segment_name
6     and s1.segment_type = 'TABLE';

1189 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 4114193231

------------------------------------------------------------------------------------
| Id | Operation             | Name       | Rows | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |            |    53 | 5989 |    16   (7)| 00:00:01 |
|* 1 | HASH JOIN            |            |    53 | 5989 |    16   (7)| 00:00:01 |
|* 2 |   TABLE ACCESS FULL   | EXTENTS    |    52 | 2808 |    10   (0)| 00:00:01 |
|* 3 |   INDEX FAST FULL SCAN| IDX_TMP001 | 1591 | 93869 |     5   (0)| 00:00:01 |
------------------------------------------------------------------------------------

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

   1 - access("S1"."OWNER"="OWNER" AND "S1"."SEGMENT_NAME"="SEGMENT_NAME")
   2 - filter("S1"."SEGMENT_TYPE"='TABLE' AND "S1"."OWNER"='SYS')
   3 - filter("OWNER"='SYS')

Note
-----
- dynamic sampling used for this statement

Statistics
----------------------------------------------------------
         23 recursive calls
          0 db block gets
        152 consistent gets
         76 physical reads
          0 redo size
      40901 bytes sent via SQL*Net to client
       1360 bytes received via SQL*Net from client
         81 SQL*Net roundtrips to/from client
          1 sorts (memory)
          0 sorts (disk)
       1189 rows processed

SQL> alter system flush buffer_cache;

System altered.

SQL> select s1.*
2    from extents s1,
3         (select owner, segment_name
4            from SEGMENTS
5           where wner = 'SYS'
6             and segment_type = 'TABLE') s2
7   where s1.owner = s2.owner
8     and s1.segment_name = s2.segment_name;           //儘量在子查詢SQL中過濾最大量的資料，在連線中儘量使參與連線的資料量最小，以減少不必要的資料操作。

1189 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 3107011821

---------------------------------------------------------------------------------
| Id | Operation          | Name       | Rows | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |            |   573 | 71052 |    16   (7)| 00:00:01 |
|* 1 | HASH JOIN         |            |   573 | 71052 |    16   (7)| 00:00:01 |
|* 2 |   TABLE ACCESS FULL| EXTENTS    |   570 | 30780 |    10   (0)| 00:00:01 |
|* 3 |   INDEX RANGE SCAN | IDX_TMP003 |   552 | 38640 |     5   (0)| 00:00:01 |
---------------------------------------------------------------------------------

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

   1 - access("S1"."OWNER"="OWNER" AND
              "S1"."SEGMENT_NAME"="SEGMENT_NAME")
   2 - filter("S1"."OWNER"='SYS')
   3 - access("OWNER"='SYS' AND "SEGMENT_TYPE"='TABLE')

Note
-----
- dynamic sampling used for this statement

Statistics
----------------------------------------------------------
          5 recursive calls
          0 db block gets
        131 consistent gets
         68 physical reads
          0 redo size
      40933 bytes sent via SQL*Net to client
       1360 bytes received via SQL*Net from client
         81 SQL*Net roundtrips to/from client
          1 sorts (memory)
          0 sorts (disk)
       1189 rows processed

例子5 (最快速的分頁方法):

SQL> select t.ri, t.owner, t.segment_name
2    from (select *
3            from (select rownum rn,t.*
4                    from (select rowid ri, tt.*
5                            from test tt
6                           order by segment_name desc) t
7                   where wner = 'SYS')
8           where rownum <= 4000) t
9   where t.rn >= 3990;                                   //這是傳統的分頁方法：先查詢出資料，後分頁。

11 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 3735168129

--------------------------------------------------------------------------------
| Id | Operation               | Name | Rows | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |      | 1148 | 96432 |    21   (5)| 00:00:01 |
|* 1 | VIEW                   |      | 1148 | 96432 |    21   (5)| 00:00:01 |
|* 2 |   COUNT STOPKEY         |      |       |       |            |          |
|   3 |    VIEW                 |      | 1148 | 96432 |    21   (5)| 00:00:01 |
|   4 |     COUNT               |      |       |       |            |          |
|   5 |      VIEW               |      | 1148 | 81508 |    21   (5)| 00:00:01 |
|   6 |       SORT ORDER BY     |      | 1148 | 71176 |    21   (5)| 00:00:01 |
|* 7 |        TABLE ACCESS FULL| TEST | 1148 | 71176 |    20   (0)| 00:00:01 |
--------------------------------------------------------------------------------

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

   1 - filter("T"."RN">=3990)
   2 - filter(ROWNUM<=4000)
   7 - filter("TT"."OWNER"='SYS')

Statistics
----------------------------------------------------------
        184 recursive calls
          0 db block gets
         96 consistent gets
         67 physical reads
          0 redo size
        942 bytes sent via SQL*Net to client
        491 bytes received via SQL*Net from client
          2 SQL*Net roundtrips to/from client
          1 sorts (memory)
          0 sorts (disk)
         11 rows processed

SQL> select /*+ordered use_nl(t,test)*/
2   test.owner, test.segment_name
3    from (select ri
4            from (select rownum rn, ri
5                    from (select rowid ri,rownum rn
6                            from test
7                           where wner = 'SYS' order by segment_name desc
8                           )
9                   where rownum <= 4000)
10           where rn >= 3990) t,
11         test
12   where t.ri = test.rowid;                                               //此種寫法是先在索引上分好頁，再根據索引上的ROWID來掃描表資料。減少了對錶資料塊的掃描。

11 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 895365311

------------------------------------------------------------------------------------------------
| Id | Operation                        | Name        | Rows | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |             | 1148 | 65436 | 1156   (1)| 00:00:14 |
|   1 | NESTED LOOPS                    |             | 1148 | 65436 | 1156   (1)| 00:00:14 |
|* 2 |   VIEW                           |             | 1148 | 28700 |     7   (0)| 00:00:01 |
|* 3 |    COUNT STOPKEY                 |             |       |       |            |          |
|   4 |     VIEW                         |             | 1148 | 13776 |     7   (0)| 00:00:01 |
|   5 |      COUNT                       |             |       |       |            |          |
|* 6 |       INDEX RANGE SCAN DESCENDING| IDX_TMP0001 | 1148 | 36736 |     7   (0)| 00:00:01 |
|   7 |   TABLE ACCESS BY USER ROWID     | TEST        |     1 |    32 |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------

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

   2 - filter("RN">=3990)
   3 - filter(ROWNUM<=4000)
   6 - access("OWNER"='SYS')

Statistics
----------------------------------------------------------
          0 recursive calls
          0 db block gets
         32 consistent gets
          0 physical reads
          0 redo size
        695 bytes sent via SQL*Net to client
        491 bytes received via SQL*Net from client
          2 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
         11 rows processed

例子6 (> 與>=比較)：

SQL> alter system flush shared_pool;

System altered.

SQL> alter system flush buffer_cache;

System altered.

SQL> select * from test where bytes>81920;

734 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 1357081020

--------------------------------------------------------------------------
| Id | Operation         | Name | Rows | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT |      |   734 |   130K|    10   (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TEST |   734 |   130K|    10   (0)| 00:00:01 |
--------------------------------------------------------------------------

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

1 - filter("BYTES">81920)

Note
-----
- dynamic sampling used for this statement

Statistics
----------------------------------------------------------
       1143 recursive calls
          0 db block gets
        277 consistent gets
         55 physical reads
          0 redo size
      21556 bytes sent via SQL*Net to client
       1019 bytes received via SQL*Net from client
         50 SQL*Net roundtrips to/from client
         13 sorts (memory)
          0 sorts (disk)
        734 rows processed

SQL> alter system flush shared_pool;

System altered.

SQL> alter system flush buffer_cache;

System altered.

SQL> select * from test where bytes>=81921; //從統計量來看>=的效率確實要比>的效率高一些。

734 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 1357081020

--------------------------------------------------------------------------
| Id | Operation         | Name | Rows | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT |      |   734 |   130K|    10   (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TEST |   734 |   130K|    10   (0)| 00:00:01 |
--------------------------------------------------------------------------

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

1 - filter("BYTES">=81921)

Note
-----
- dynamic sampling used for this statement

Statistics
----------------------------------------------------------
        745 recursive calls
          0 db block gets
        211 consistent gets
         49 physical reads
          0 redo size
      21556 bytes sent via SQL*Net to client
       1019 bytes received via SQL*Net from client
         50 SQL*Net roundtrips to/from client
          9 sorts (memory)
          0 sorts (disk)
        734 rows processed

例子7 (用union來代替in 和 or)：

SQL> alter system flush shared_pool;

System altered.

SQL> alter system flush buffer_cache;

System altered.

SQL> select * from test where owner in ('SYS','SYSTEM');

3408 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 1357081020

--------------------------------------------------------------------------
| Id | Operation         | Name | Rows | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT |      | 3408 |   605K|    10   (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TEST | 3408 |   605K|    10   (0)| 00:00:01 |
--------------------------------------------------------------------------

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

1 - filter("OWNER"='SYS' OR "OWNER"='SYSTEM')

Note
-----
- dynamic sampling used for this statement

Statistics
----------------------------------------------------------
       1143 recursive calls
          0 db block gets
        456 consistent gets
         55 physical reads
          0 redo size
     127442 bytes sent via SQL*Net to client
       2988 bytes received via SQL*Net from client
        229 SQL*Net roundtrips to/from client
         13 sorts (memory)
          0 sorts (disk)
       3408 rows processed

SQL> alter system flush shared_pool;

System altered.

SQL> alter system flush buffer_cache;

System altered.

SQL> select * from test where wner ='SYS' or wner='SYSTEM'; //使用or 其實跟in完全相同。從Predicate Information就可以看出。

3408 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 1357081020

--------------------------------------------------------------------------
| Id | Operation         | Name | Rows | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT |      | 3408 |   605K|    10   (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TEST | 3408 |   605K|    10   (0)| 00:00:01 |
--------------------------------------------------------------------------

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

1 - filter("OWNER"='SYS' OR "OWNER"='SYSTEM')

Note
-----
- dynamic sampling used for this statement

Statistics
----------------------------------------------------------
       1143 recursive calls
          0 db block gets
        456 consistent gets
         55 physical reads
          0 redo size
     127442 bytes sent via SQL*Net to client
       2988 bytes received via SQL*Net from client
        229 SQL*Net roundtrips to/from client
         13 sorts (memory)
          0 sorts (disk)
       3408 rows processed

SQL> alter system flush shared_pool;

System altered.

SQL> alter system flush buffer_cache;

System altered.

SQL> select * from test where wner ='SYS'
2 union
3 select * from test where wner='SYSTEM'; //從統計量來看,使用union代替in 或or效率確實有一些提高。

3408 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 2608128534

------------------------------------------------------------------------------------
| Id | Operation           | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      | 3408 |   605K|       |   145   (9)| 00:00:02 |
|   1 | SORT UNIQUE        |      | 3408 |   605K| 1256K|   145   (9)| 00:00:02 |
|   2 |   UNION-ALL         |      |       |       |       |            |          |
|* 3 |    TABLE ACCESS FULL| TEST | 3026 |   537K|       |    10   (0)| 00:00:01 |
|* 4 |    TABLE ACCESS FULL| TEST |   382 | 69524 |       |    10   (0)| 00:00:01 |
------------------------------------------------------------------------------------

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

3 - filter("OWNER"='SYS')
4 - filter("OWNER"='SYSTEM')

Note
-----
- dynamic sampling used for this statement

Statistics
----------------------------------------------------------
       1146 recursive calls
          0 db block gets
        298 consistent gets
         55 physical reads
          0 redo size
     131069 bytes sent via SQL*Net to client
       2988 bytes received via SQL*Net from client
        229 SQL*Net roundtrips to/from client
         14 sorts (memory)
          0 sorts (disk)
       3408 rows processed

SQL> alter system flush shared_pool;

System altered.

SQL> alter system flush buffer_cache;

System altered.

SQL>
SQL>
SQL> select * from test where wner ='SYS'
2 union all
3 select * from test where wner='SYSTEM'; //但是使用union all來代替in 或or 效率沒有提升。

3408 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 2275963031

---------------------------------------------------------------------------
| Id | Operation          | Name | Rows | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      | 3408 |   605K|    20 (50)| 00:00:01 |
|   1 | UNION-ALL         |      |       |       |            |          |
|* 2 |   TABLE ACCESS FULL| TEST | 3026 |   537K|    10   (0)| 00:00:01 |
|* 3 |   TABLE ACCESS FULL| TEST |   382 | 69524 |    10   (0)| 00:00:01 |
---------------------------------------------------------------------------

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

2 - filter("OWNER"='SYS')
3 - filter("OWNER"='SYSTEM')

Note
-----
- dynamic sampling used for this statement

Statistics
----------------------------------------------------------
       1146 recursive calls
          0 db block gets
        525 consistent gets
         55 physical reads
          0 redo size
     127279 bytes sent via SQL*Net to client
       2988 bytes received via SQL*Net from client
        229 SQL*Net roundtrips to/from client
         13 sorts (memory)
          0 sorts (disk)
       3408 rows processed

例子8 (LIKE '%XXX%' 和 LIKE 'XXX%'是一樣的)：

SQL> create table test as select * from dba_objects;

Table created.

SQL> create index idx_tmp0001 on test(owner);

Index created.

SQL> select owner from test where owner like '%SYS%';

7188 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 1344756310

------------------------------------------------------------------------------------
| Id | Operation            | Name        | Rows | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             | 6120 |   101K|     8   (0)| 00:00:01 |
|* 1 | INDEX FAST FULL SCAN| IDX_TMP0001 | 6120 |   101K|     8   (0)| 00:00:01 |
------------------------------------------------------------------------------------

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

1 - filter("OWNER" LIKE '%SYS%')

Note
-----
- dynamic sampling used for this statement

Statistics
----------------------------------------------------------
         28 recursive calls
          0 db block gets
        567 consistent gets
         22 physical reads
          0 redo size
     124166 bytes sent via SQL*Net to client
       5760 bytes received via SQL*Net from client
        481 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
       7188 rows processed

SQL> select owner from test where owner like 'SYS%'; //’%xxx%’和’xxx%’兩種寫法都可以用到索引。

7185 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 1344756310

------------------------------------------------------------------------------------
| Id | Operation            | Name        | Rows | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             | 6114 |   101K|     8   (0)| 00:00:01 |
|* 1 | INDEX FAST FULL SCAN| IDX_TMP0001 | 6114 |   101K|     8   (0)| 00:00:01 |
------------------------------------------------------------------------------------

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

1 - filter("OWNER" LIKE 'SYS%')

Note
-----
- dynamic sampling used for this statement

Statistics
----------------------------------------------------------
          8 recursive calls
          0 db block gets
        571 consistent gets
          0 physical reads
          0 redo size
     123961 bytes sent via SQL*Net to client
       5749 bytes received via SQL*Net from client
        480 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
       7185 rows processed

其他應遵循的規則：
1.檢視執行計劃，發現有全表掃描，應該考慮是否可以透過建立索引，改變SQL寫法來避免全表掃描。
2.能使用UNION ALL情況下，儘量用UNION ALL 代替UNION；
3.養成給表一個別名的習慣。
4.避免在索引欄位使用IS NULL,IS NOT NULL。
5.沒必要排序就不要進行排序。

Oracle環境下SQL語句的不同寫法效率對比

相關文章