1.先来看不垮分区访问时的性能对比，这里我们主要关注逻辑度(consistent gets),
由于第一次访问sql存在解析，因此我们对比时都看第二次访问时的情况，
下面的试验对比都是这样...
注意：目前2个表上都没有index
SQL> select * from t1 where object_id<2000;
已选择249984行。
执行计划
----------------------------------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 270K| 20M| 1069 (4)| 00:00:13 |
|* 1 | TABLE ACCESS FULL| T1 | 270K| 20M| 1069 (4)| 00:00:13 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID"<2000)
Note
-----
- dynamic sampling used for this statement
统计信息
----------------------------------------------------------
222 recursive calls
0 db block gets
21446 consistent gets
4719 physical reads
0 redo size
7439711 bytes sent via SQL*Net to client
183700 bytes received via SQL*Net from client
16667 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
249984 rows processed
SQL> select * from t1 where object_id<2000;
已选择249984行。
执行计划
----------------------------------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 270K| 20M| 1069 (4)| 00:00:13 |
|* 1 | TABLE ACCESS FULL| T1 | 270K| 20M| 1069 (4)| 00:00:13 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID"<2000)
Note
-----
- dynamic sampling used for this statement
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
21341 consistent gets
0 physical reads
0 redo size
7439711 bytes sent via SQL*Net to client
183700 bytes received via SQL*Net from client
16667 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
249984 rows processed
SQL> select * from t where object_id<2000;
已选择249984行。
执行计划
----------------------------------------------------------
Plan hash value: 2931986080
--------------------------------------------------------------------------------
---------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
Pstart| Pstop |
--------------------------------------------------------------------------------
---------------
| 0 | SELECT STATEMENT | | 241K| 7063K| 238 (19)| 00:00:03 |
| |
| 1 | PARTITION RANGE SINGLE| | 241K| 7063K| 238 (19)| 00:00:03 |
1 | 1 |
| 2 | TABLE ACCESS FULL | T | 241K| 7063K| 238 (19)| 00:00:03 |
1 | 1 |
--------------------------------------------------------------------------------
---------------
Note
-----
- dynamic sampling used for this statement
统计信息
----------------------------------------------------------
1305 recursive calls
0 db block gets
17770 consistent gets
890 physical reads
0 redo size
7439711 bytes sent via SQL*Net to client
183700 bytes received via SQL*Net from client
16667 SQL*Net roundtrips to/from client
12 sorts (memory)
0 sorts (disk)
249984 rows processed
SQL> select * from t where object_id<2000;
已选择249984行。
执行计划
----------------------------------------------------------
Plan hash value: 2931986080
--------------------------------------------------------------------------------
---------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
Pstart| Pstop |
--------------------------------------------------------------------------------
---------------
| 0 | SELECT STATEMENT | | 241K| 7063K| 238 (19)| 00:00:03 |
| |
| 1 | PARTITION RANGE SINGLE| | 241K| 7063K| 238 (19)| 00:00:03 |
1 | 1 |
| 2 | TABLE ACCESS FULL | T | 241K| 7063K| 238 (19)| 00:00:03 |
1 | 1 |
--------------------------------------------------------------------------------
---------------
Note
-----
- dynamic sampling used for this statement
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
17498 consistent gets
0 physical reads
0 redo size
7439711 bytes sent via SQL*Net to client
183700 bytes received via SQL*Net from client
16667 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
249984 rows processed
SQL>
--========================
效果比较明显，很显然是通过访问分区表的性能更好一些，逻辑读21341大于17498，并且都没有发生物理读，
同时我们从执行计划也能看到访问t1时访问的是单个分区(PARTITION RANGE SINGLE),很明显如果我们能把数据
控制在一个分区之内的话那么分区表的性能要好于普通的heap表
oracle视频教程请关注:http://u.youku.com/user_video/id_UMzAzMjkxMjE2.html
