2.跨分区访问,依然是分区表的性能好，因为数据集中在了2个分区中，还是比访问

t1要少访问数据...

SQL> select * from t1 where object_id<4000;

已选择504448行。



执行计划

----------------------------------------------------------

Plan hash value: 3617692013


--------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

--------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 547K| 41M| 1070 (4)| 00:00:13 |

|* 1 | TABLE ACCESS FULL| T1 | 547K| 41M| 1070 (4)| 00:00:13 |

--------------------------------------------------------------------------


Predicate Information (identified by operation id):

---------------------------------------------------


1 - filter("OBJECT_ID"<4000)


Note

-----

- dynamic sampling used for this statement



统计信息

----------------------------------------------------------

5 recursive calls

0 db block gets

38318 consistent gets

0 physical reads

0 redo size

15092711 bytes sent via SQL*Net to client

370304 bytes received via SQL*Net from client

33631 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

504448 rows processed


SQL> set timing on

SQL> select * from t1 where object_id<4000;


已选择504448行。


已用时间: 00: 00: 09.79


执行计划

----------------------------------------------------------

Plan hash value: 3617692013


--------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

--------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 547K| 41M| 1070 (4)| 00:00:13 |

|* 1 | TABLE ACCESS FULL| T1 | 547K| 41M| 1070 (4)| 00:00:13 |

--------------------------------------------------------------------------


Predicate Information (identified by operation id):

---------------------------------------------------


1 - filter("OBJECT_ID"<4000)


Note

-----

- dynamic sampling used for this statement



统计信息

----------------------------------------------------------

0 recursive calls

0 db block gets

38236 consistent gets

0 physical reads

0 redo size

15092711 bytes sent via SQL*Net to client

370304 bytes received via SQL*Net from client

33631 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

504448 rows processed


SQL> select * from t where object_id<4000;


已选择504448行。


已用时间: 00: 00: 09.85


执行计划

----------------------------------------------------------

Plan hash value: 1571388083


--------------------------------------------------------------------------------


-----------------


| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time

| Pstart| Pstop |


--------------------------------------------------------------------------------


-----------------


| 0 | SELECT STATEMENT | | 459K| 13M| 837 (53)| 00:00:11

| | |


| 1 | PARTITION RANGE ITERATOR| | 459K| 13M| 837 (53)| 00:00:11

| 1 | 2 |


| 2 | TABLE ACCESS FULL | T | 459K| 13M| 837 (53)| 00:00:11

| 1 | 2 |


--------------------------------------------------------------------------------


-----------------



Note

-----

- dynamic sampling used for this statement



统计信息

----------------------------------------------------------

69 recursive calls

1 db block gets

36078 consistent gets

946 physical reads

48432 redo size

15092711 bytes sent via SQL*Net to client

370304 bytes received via SQL*Net from client

33631 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

504448 rows processed


SQL> select * from t where object_id<4000;


已选择504448行。


已用时间: 00: 00: 10.12


执行计划

----------------------------------------------------------

Plan hash value: 1571388083


--------------------------------------------------------------------------------


-----------------


| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time

| Pstart| Pstop |


--------------------------------------------------------------------------------


-----------------


| 0 | SELECT STATEMENT | | 459K| 13M| 837 (53)| 00:00:11

| | |


| 1 | PARTITION RANGE ITERATOR| | 459K| 13M| 837 (53)| 00:00:11

| 1 | 2 |


| 2 | TABLE ACCESS FULL | T | 459K| 13M| 837 (53)| 00:00:11

| 1 | 2 |


--------------------------------------------------------------------------------


-----------------



Note

-----

- dynamic sampling used for this statement



统计信息

----------------------------------------------------------

0 recursive calls

0 db block gets

35328 consistent gets

0 physical reads

0 redo size

15092711 bytes sent via SQL*Net to client

370304 bytes received via SQL*Net from client

33631 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

504448 rows processed


SQL>


oracle视频教程请关注:http://u.youku.com/user_video/id_UMzAzMjkxMjE2.html