How to Implement Oracle database partition tables

Oracle Database partitioning is an important means and method for optimizing the performance of Oracle databases. Before doing the project at hand, I only heard the name of an over-area. It was very mysterious and I saw it.

Oracle Database partitioning is an important means and method for optimizing the performance of Oracle databases. Before doing the project at hand, I only heard the name of an over-area. It was very mysterious and I saw it.

Oracle Database partitioning is an important means and method for optimizing the performance of Oracle databases. Before doing the project at hand, I only heard the name of an over-area, when I was talking about it at a seminar, I told myself that I was not proficient in learning the technology. Recently I worked on a GPS project and processed dozens of GB of data. In order to meet the real-time requirements of the system, data Query Efficiency must be improved, so data must be partitioned to solve the pressing problem!

Let's talk about the benefits of partitioning first!

1) enhanced availability: If a partition of the table fails, the data of the table in other partitions is still available;

2) Easy Maintenance: If a partition of the table fails, you only need to fix the partition to fix the data;

3) Balanced I/O: You can map different partitions to disks to balance I/O and improve the overall system performance;

4) Improved query performance: You can query partition objects by searching only the partitions you are concerned about, improving the search speed.

Oracle Database provides three partitioning methods for tables or indexes:

Ü range partitioning

Ü Hash partition (Hash partition)

Ü composite Partition

I. Detailed description of range partitions

A range partition is used to partition the range of a value in a data table. It determines the partition in which the data is stored Based on the range of a value. For example, partition by serial number and time. Based on the serial number, for example, placing less than 2000000 in part01, 2000000 ~ Put 4000000 in part02...

Create table AAA
(
Id number primary key,
Indate date not null
)
Partition by range (indate)
(
Partition part_01 values less than (to_date ('1970-01-01 ', 'yyyy-mm-dd') tablespace space01,
Partition part_02 values less than (to_date ('1970-01-01 ', 'yyyy-mm-dd') tablespace space02,
Partition part_03 values less than (maxvalue) tablespace space03
);

Space01 \ space02 \ space03 is the three created tablespaces, which are equivalent to dividing the created large table into three different tablespaces.

Ii. Hash partitions (Hash partitions)

Hash partitioning is a type of partitioning that distributes data evenly by specifying the Partition Number, because the size of these partitions is consistent by performing hash partitioning on the I/O device. That is, only name the partition name, so that data distribution is even.

Iii. Detailed description of composite partitions

Sometimes we need to hash the data in each partition in several tablespaces Based on the range partition, so we need to use the composite partition. A composite partition is a partitioning method that first uses a range partition and then uses a hash partition within each partition.

Partition by range (indate) subpartition by hash (id)
Subpartitions 3 store in (space01, space02, space03)
(
Partition part_01 values less than (to_date ('1970-01-01 ', 'yyyy-mm-dd ')),
Partition part_02 values less than (to_date ('1970-01-01 ', 'yyyy-mm-dd ')),
Partition part_03 values less than (maxvalue)
);

4. Partition Table operations

1. insert record: insert into AAA values (1, sysdate );

2. query partition Table records: select * from AAA partition (part_01 );

3. update partition Table records: update AAA partition (part_01) t set indate = ''where id = 1; however, partitions are specified during update, data will not be updated when the queried records are not in the partition.

4. delete a partition table record: delete from AAA partition (part_02) t where id = 4. If a partition is specified and the data in the condition is not in the partition, no data will be deleted.

5. add a partition: alter table AAA add partition part_04 values less than (to_date ('2017-01-01 ', 'yyyy-mm-dd') tablespace dinya_spa ce03; when adding a partition, the conditions for the added partition must be greater than the maximum value of the existing partition, otherwise the system will prompt the ORA-14074 partition bound must collate higher than that of the last partition error.

6. merge a partition: alter table AAA merge partitions part_01, part_02 into partition part_02; if the merged partition is set to part_01 during merge, the system will prompt the ORA-14275 cannot reuse lower-bound partition as resulting partition error.

7. delete a partition: alter table AAA drop partition part_01; after deleting a partition in a partition table, when querying data in the table, it is displayed that all data in the partition has been lost, therefore, exercise caution when deleting a partition. Make sure that you back up the data before performing this operation, or merge the partitions.

5. Create an index

You can create an index for a partitioned table like a common table. You can create a local index and a global index for a partitioned table. Global indexes are used when there are many transactions in a partition and the uniqueness of data records in all partitions must be ensured.

1. create index idx_t on AAA (id)
Local
(
Partition idx_1 tablespace space01,
Partition idx_2 tablespace space02,
Partition idx_3 tablespace space03
);

2. When a global index is created, the global clause allows you to specify the index range value. The range value is the index field range value: create index idx_t on AAA (id)
Global partition by range (id)
(
Partition idx_1 values less than (1000) tablespace space01,
Partition idx_2 values less than (10000) tablespace space02,
Partition idx_3 values less than (maxvalue) tablespace space03
);

You can also create an index for the entire table without specifying the index partition name: create index idx_t on AAA (id );