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/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/23135684/viewspace-628661/