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

By comparing the partitioned table and the normal table, we simply tested the performance and maintained the index when the partitioned table existed, and finally we had to say

The use of partitioned tables may not always improve performance, primarily for easy maintenance, if we can access the data centrally

In one or a limited number of partitions, performance is certainly better than accessing a normal whole table, or the sentence

Make the executed SQL as less readable and less written as possible, which is the key to improving SQL performance, like the formula Anlinew mentions:

T=s/v, in fact, the purpose of less reading, less writing is to reduce S ...

--Create 2 identical, almost identical tables of data, just partitioned tables, a regular heap table, and then a simple performance comparison to make a better understanding of how to use partitioned tables properly

Sql> CREATE TABLE T (object_id number,object_name VARCHAR2 (30))

2 partition by range (OBJECT_ID)

3 (

4 partition P1 values less than (Watts) tablespace users,

5 partition P2 values less than (4000) tablespace users,

6 partition P3 values less than (6000) tablespace users,

7 partition P4 values less than (8000) tablespace users,

8 partition P5 values less than (MaxValue) tablespace users

9);

Table has been created.

sql> INSERT INTO T-select object_id,object_name from Dba_objects;

9848 lines have been created.

sql> INSERT INTO T-select * from T;

9848 lines have been created.

sql> INSERT INTO T-select * from T;

19696 lines have been created.

sql> INSERT INTO T-select * from T;

39392 lines have been created.

sql> INSERT INTO T-select * from T;

78784 lines have been created.

sql> INSERT INTO T-select * from T;

157568 lines have been created.

sql> INSERT INTO T-select * from T;

315136 lines have been created.

sql> INSERT INTO T-select * from T;

630272 lines have been created.

Sql> commit;

Submit completed.

sql> CREATE TABLE T1 tablespace users as select Object_id,object_name from Dba_o

bjects;

Table has been created.

sql> INSERT INTO T1 select *from T1;

9849 lines have been created.

sql> INSERT INTO T1 select *from T1;

19698 lines have been created.

sql> INSERT INTO T1 select *from T1;

39396 lines have been created.

sql> INSERT INTO T1 select *from T1;

78792 lines have been created.

sql> INSERT INTO T1 select *from T1;

157584 lines have been created.

sql> INSERT INTO T1 select *from T1;

315168 lines have been created.

sql> INSERT INTO T1 select *from T1;

630336 lines have been created.

Sql> commit;

Submit completed.

Sql>

1. Take a look at the performance comparison of partitioned access, here we focus on the logical degree (consistent gets),

Because the first access to SQL exists parsing, we look at the second visit when we compare.

The following experimental comparisons are ...

Note: There are no index on the 2 tables

Sql> SELECT * from t1 where object_id<2000;

249984 rows have been selected.

Execution plan

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

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)

Back to the column page: http://www.bianceng.cnhttp://www.bianceng.cn/database/Oracle/