Oracle中與分區表相關的問題總結（下）

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 |

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