Summary of issues related to partitioned tables in Oracle (next)

2. Cross-partitioned access is still a good performance for partitioned tables, because the data is concentrated in 2 partitions or more than the access

T1 to access data less ...

Sql> SELECT * from t1 where object_id<4000;

504448 rows have been selected.

Execution plan

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

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

Statistical information

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

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 rows have been selected.

Time used: 00:00:09.79

Execution plan

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

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

Statistical information

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

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 rows have been selected.

Time used: 00:00:09.85

Execution plan

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

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

Statistical information

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

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 rows have been selected.

Time used: 00:00:10.12

Execution plan

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

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 |

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