Oracle環境下SQL語句的不同寫法效率對比
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.沒必要排序就不要進行排序。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/23135684/viewspace-628661/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- oracle 對比sql語句執行環境OracleSQL
- sqlserver 檢視和sql語句的效率對比SQLServer
- 【SQL】Oracle sql語句 minus函式執行效率與join對比SQLOracle函式
- 高效率Oracle SQL語句OracleSQL
- SQL語句規範的寫法SQL
- OCI插入SQL語句的寫法SQL
- Oracle和SQL對比語法總結OracleSQL
- Oracle 同一sql語句在測試和開發環境不同的執行計劃OracleSQL開發環境
- 不同環境Odoo執行速度對比Odoo
- Oracle sql with 語句語法與例子OracleSQL
- oracle update語句的幾點寫法Oracle
- 查詢效率低下的sql的語句SQL
- oracle的sql語句OracleSQL
- mysql查詢效率慢的SQL語句MySql
- ORACLE優化SQL語句，提高效率(1)Oracle優化SQL
- ORACLE優化SQL語句，提高效率(2)Oracle優化SQL
- ORACLE優化SQL語句，提高效率(3)Oracle優化SQL
- oracle sql語句OracleSQL
- 我透過諮詢才知道的sql語句寫法SQL
- 在mysql查詢效率慢的SQL語句MySql
- Oracle中SQL語句執行效率的查詢與解決 （3）OracleSQL
- Oracle中SQL語句執行效率的查詢與解決 （2）OracleSQL
- Oracle中SQL語句執行效率的查詢與解決 （1）OracleSQL
- SQL Server中巧用另類寫法代替Like語句SQLServer
- 在solaris環境下,根據java程式的不同,設定不同的環境變數Java變數
- 一條SQL語句的書寫SQL
- Oracle基本SQL語句OracleSQL
- oracle常用SQL語句OracleSQL
- orcale 語句基本語法縮寫
- 不同環境下的mvn執行指令
- 【SQL】Oracle 表新增列提高效率語句參考SQLOracle
- MySql定位執行效率較低的SQL語句MySql
- ORACLE很重要的sql語句OracleSQL
- 不同的SQL語句執行時需要申請並持有對應的鎖SQL
- 利用sql語句找出表中有重複記錄的三種sql寫法SQL
- 不同性別玩家對於遊戲環境的不同選擇遊戲
- Oracle中SQL語句執行效率問題的查詢與解決OracleSQL
- Oracle SQL寫法OracleSQL