Oracle table partition and index partition Summary

Oracle table partition and index partition Summary

To simplify the management of large tables in the database, for example, data warehouses are generally measured in terabytes. partition options are introduced after oracle8. partitions are separated from tables in different tablespaces. They are partitioned and managed to support a large table with limited expansion. A large table group is physically manageable. splitting a large table into smaller partitions can improve table maintenance, backup, recovery, transaction and query performance.

Advantages of partitioning:

1. Enhanced availability: If a partition in the table cannot be used due to system failure, the remaining good partitions in the table can still be used;

2. Reduce the shutdown time: if the system failure only affects one partition of the table, only this partition needs to be repaired, and it takes less time to fix the entire large table than the whole;

3. Easy Maintenance: If you need to create a table, it is much easier to manage each public area in a single production environment than to manage a single large table;

4. Balanced I/O: You can allocate different partitions of a table to different disks to balance I/O and improve performance;

5. Improved performance: the query, add, modify, and other operations on large tables can be divided into different partitions of the table for parallel execution, which can make the operation faster, it is particularly useful for TP queries in data warehouses.

6. partitions are transparent to users, and the end user cannot feel the existence of partitions.

Create tablespace dw1

Datafile 'd: \ oracle \ oradata \ ora9 \ dw11.ora size 50 M
 

Create tablespace dw2

Datafile 'd: \ oracle \ oradata \ ora9 \ dw21.ora size 50 M

 

1. partitioning by range: Fixed-name partitions are partitioned by a certain range. See the following example:

SQL> set linesize 1000

SQL> create table niegc_part

2 (

3 part_id integer primary key,

4 part_date date,

5 part_dec varchar2 (100)

6)

7 partition by range (part_date)

8 (

9 partition part_01 values less than (to_date ('1970-01-01 ', 'yyyy-mm-dd') tablespace dw1,

10 partition part_02 values less than (to_date ('1970-01-01 ', 'yyyy-mm-dd') tablespace dw2,

11 partition part_03 values less than (maxvalue) tablespace dw1

12 );

 

The table has been created.

 

SQL>

SQL> insert into niegc_part values (1, to_date ('2017-12-30 ', 'yyyy-mm-dd'), 'less 20

06-01-01 ');

 

One row has been created.

 

SQL> commit;

 

Submitted.

 

SQL> insert into niegc_part values (2, to_date ('2017-01-01 ', 'yyyy-mm-dd'), 'equal 2

007-01-01 ');

 

One row has been created.

 

SQL> commit;

 

Submitted.

 

SQL> insert into niegc_part values (3, sysdate, 'sysdate ');

 

One row has been created.

 

SQL> commit;

 

Submitted.

 

SQL>

SQL>

SQL> select * from niegc_part partition (part_01 );

 

PART_ID PART_DATE PART_DEC

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

1--05 less

SQL>

 

I believe that as long as you are familiar with oracle, you can understand the meaning of the range partition above.

 

The range partitions with two or more fields are similar. See the following example:

Create table niegc_part
(
Part_id integer primary key,
Part_date date,
Part_dec varchar2 (100)
)
Partition by range (part_id, part_date)
(
Partition part_01 values less than (1, to_date ('1970-01-01 ', 'yyyy-mm-dd') tablespace dw,
Partition part_02 values less than (10, to_date ('1970-01-01 ', 'yyyy-mm-dd') tablespace dw,
Partition part_03 values less than (maxvalue, maxvalue) tablespace dw
);
 

 

Ii. Hash partition (Hash partition ). Hash partitions distribute data evenly by specifying partition numbers, because these partitions are consistent in size by performing hash partitioning on the I/O device. For example, the data of part_id is hashed in the specified three tablespaces according to its own situation:

Create table niegc_part

(

Part_id integer primary key,

Part_date date,

Part_dec varchar2 (100)

)

Partition by hash (part_id)

(

Partition part_01 tablespace dw1,

Partition part_02 tablespace dw2

);

 

The system inserts records into three partitions by part_id, which is two different tablespaces.

 

3. Composite partitioning. After partitioning by range, the data in each partition is scattered in several tablespaces, so we need to use a composite partition. A composite partition uses a range partition first, and then uses a hash partition for each partition. For example, you can partition the records of part_date by time, data in each partition is then divided into three sub-partitions, and the data is hashed and stored in three specified tablespaces:

Create table niegc_part

(

Part_id integer primary key,

Part_date date,

Part_dec varchar2 (100)

)

Partition by range (part_date) subpartition by hash (part_id)

Subpartitions 2 store in (dw1, dw2)

(

Partition part_01 values less than (to_date ('1970-01-01 ', 'yyyy-mm-dd') tablespace dw1,

Partition part_02 values less than (to_date ('1970-01-01 ', 'yyyy-mm-dd') tablespace dw2,

Partition part_03 values less than (maxvalue) tablespace dw1

);

Partition by range based on part_date, and store records in two tablespaces according to the transaction ID.

 

 

Iv. Index partitioning:

Note: If a field has been partitioned, index partitions cannot be created. Pay attention to this.
When a global index is created, the global clause allows you to specify the index range value. The range value is the range value of the index field:

Create index idx_part_id on niegc_part (part_dec)

Global partition by range (part_dec)

(

Partition idx_1 values less than ('20170901') tablespace dw,

Partition idx_2 values less than (maxvalue) tablespace dw

)

 

Create a local index partition: (Note: The table must have partitions. The number of partitions must be the same as the number of partitions in the Partition Table. Otherwise, the partitions cannot be created)

Create index idx_part_id on niegc_part (part_dec)

Local

(

Partition idx_1 tablespace dw1,

Partition idx_2 tablespace dw2

)

 

5. Partition maintenance: (only for range partitions)

(1) Add a partition: The partition range can only be increased, but cannot add a partition smaller than the original partition:

Alter table niegc_part add partition part_03 values less than (maxvalue)

(2) merge partitions: (the merged partition must be the last partition with a large value)

Alter table niegc_part merge partitions part_02, part_03 into partition part_03

(3) Delete a partition:

Alter table niegc_part drop partition part_01

 

Partition maintenance: (only for range partitions)

(1) Add a partition: The partition range can only be increased, but cannot add a partition smaller than the original partition:

Alter table tablename add partition new_partitionname values less than (maxvalue)

(2) Merge/split partitions: (the merged partition must be the last partition with a large value)

Alter table tablename merge partitions partitionname1, partitionname2 into partition partitionname2;

Alter table tablename split partition partitionname1 at (xx) (

Partition newpartition1, partition newpartition2 );

Note: xx is the split point.

(3) Delete a partition:

Alter table niegc_part drop partition partitionname;

(4) rename a partition

Alter table table_name rename Partition partition_name to partition_name

(5) change the partition to the tablespace.

Alter table table_name move partition_name

Tablespace tablespace_name nologging

(6) query specific partitions

Select count (*) from table_name partition (partition_name );

(7) add data

Insert into table_name select * from table_name partition (partition_name)

(8) Partition Table Export

Userid = USER/PWD

Buffer= 102400

Tables = table_name: partition_name,

File = E: exp_paraxxx.dmp

Log = E: exp_paraxxx.log

(9) Tips: delete a field in a table

Alter table table_name set unused column column_name;

(10) Add a field

Alter table table_name add column_name number (1 );