[DB] [Oracle] [partition] Big Data Partitioning technology

[Oracle] [partition] Big Data Partitioning technology

I. Introduction to Oracle partitioning
Oracle partitioning is a technology that processes ultra-large tables and indexes. Partitioning is a "divide and conquer" technology. By dividing large tables and indexes into small pieces that can be managed, you can avoid managing each table as a large and separate object, it provides Scalable Performance for a large amount of data. By allocating operations to smaller storage units, partitions reduce the time required for management operations, and improve performance through enhanced parallel processing. By shielding the partitions of faulty data, availability is also increased.

Ii. Advantages and Disadvantages of Oracle partitioning
Advantages:
Enhanced availability: If a table partition fails, data in other partitions is still available;
Easy Maintenance: If a partition in the table fails, you only need to repair the data;
Balanced I/O: You can map different partitions to disks to balance I/O and improve the overall system performance;
Query performance improvement: You can query partition objects by searching only the partitions you are concerned about, improving the search speed.
Disadvantages:
Partition Table: an existing table cannot be directly converted to a partition table. However, Oracle provides the online table redefinition function.
Iii. Oracle Partitioning Method
Range partition:
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 based on the creation date of business records.
Hash partition (hash partition ):
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.
List partition (list partition ):
When you need to explicitly control how to map rows to partitions, use the list partition method. Unlike range partitions and hash partitions, list partitions do not support multiple column partitions. If you want to partition a table by column, the partition key can only be composed of a single column in the table. However, you can use the range partition or hash partition method to partition all columns, you can use the list partition method to partition.

Range-Hash partition (composite partition ):
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 uses a range partition first, and then uses a hash partition within each partition (Note: you must first perform a range partition)

Range-list partition (composite partition ):
The combination of range and list technology first partitions the table range, and then uses list technology to partition each range partition again. Unlike the combined range-Hash partition, all content of each subpartition represents the logical subset of data, which is described by the appropriate range and list partition settings. (Note: you must first perform range partitioning)

4. Oracle Table Partitioning
-- Whether partitioning is true
Select * from V $ option s order by S. Parameter DESC

-- Create a tablespace
Create tablespace "partion_03"
Logging
Datafile 'd: \ oracle \ oradata \ jzhua \ partion_03.dbf' size 50 m
Extent management local segment space management auto

-- Delete a tablespace
Drop tablespace partion_01

-- Range Partitioning technology
Create Table partition_test
(
PID Number not null,
Pitem varchar2 (200 ),
Pdata date not null
)
Partition by range (PID)
(
Partition part_01 values less than (50000) tablespace dinya_space01,
Partition part_02 values less than (100000) tablespace dinya_space02,
Partition part_03 values less than (maxvalue) tablespace dinya_space03
)

Create Table partition_ttest
(
PID Number not null,
Pitem varchar2 (200 ),
Pdata date not null
)
Partition by range (pdata)
(
Partition part_t01 values less than (to_date ('1970-01-01 ', 'yyyy-mm-dd') tablespace dinya_space01,

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

Partition part_t03 values less than (maxvalue) tablespace dinya_space03
)

Insert into partition_test (PID, pitem, pdata) select H. ID, H. userid, H. rectime from st_handle H

Select * From partition_test partition (part_01) t where T. PID = '20170101'

-- Hash Partitioning technology
Create Table partition_hashtest
(
PID Number not null,
Pitem varchar2 (200 ),
Pdata date not null
)
Partition by hash (PID)
(
Partition part_h01 tablespace dinya_space01,
Partition part_h02 tablespace dinya_space02,
Partition part_h03 tablespace dinya_space03
)

Insert into partition_hashtest (PID, pitem, pdata) select H. ID, H. userid, H. rectime from st_handle H

Select * From partition_hashtest partition (part_h03) t where T. PID = '20170101'

-- Composite Partitioning technology
Create Table partition_fhtest
(
PID Number not null,
Pitem varchar2 (200 ),
Pdata date not null
)
Partition by range (pdata) subpartition by hash (PID) subpartitions 3 store in (dinya_space01, dinya_space02, dinya_space03)

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

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

Partition part_fh03 values less than (maxvalue) tablespace dinya_space03
)

Insert into partition_fhtest (PID, pitem, pdata) select H. ID, H. userid, H. rectime from st_handle H

Select * From partition_fhtest partition (part_fh02) t where T. PID = '20170101'

Select * From partition_fhtest partition (part_fh03) T

-- Speed comparison
Select * From st_handle H where H. rectime> to_date ('2017-01-01 ', 'yyyy-mm-dd ');

Select * From partition_fhtest partition (part_fh03) t where T. pdata> to_date ('2017-01-01 ', 'yyyy-mm-dd ');

-- Partition Table operation

-- Add a partition
Alter table partition_test add partition part_05 values less than (10020) tablespace dinya_space03

-- Query partition data
Select * From partition_fhtest partition (part_fh02) T

-- Modify data in a partition
Update partition_fhtest partition (part_fh02) t set T. pitem = 'jzhua' where T. PID = '20140901'

-- Delete data in a partition
Delete from partition_fhtest partition (part_fh02) t where T. PID = '20170101'

-- Merge partitions
Create Table partition_hb
(
PID Number not null,
Pitem varchar2 (200 ),
Pdata date not null
)
Partition by range (PID)
(
Partition part_01 values less than (50000) tablespace dinya_space01,
Partition part_02 values less than (100000) tablespace dinya_space02,
Partition part_03 values less than (maxvalue) tablespace dinya_space03
)

Insert into partition_hb (PID, pitem, pdata) select H. ID, H. userid, H. rectime from st_handle H

Select * From partition_hb partition (part_03) t where T. PID = '20170101'

Alter table partition_hb merge partitions part_01, part_02 into partition part_02;

-- Shard
-- Spilt partition name at (this is a critical section, for example, 50000 means that less than 50000 is placed in part_01, and more than 50000 is placed in part_02)

Alter table partition_hbsplit partition part_02 at (50000) into (partition part_01 tablespace dinya_space01, partition part_02 tablespace dinya_space02 );

-- Change partition name
Alter table partition_hb rename partition part_01_test to part_02;
V. Oracle index Partition Table operations
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. When a global index is created, the global clause allows you to specify the index range value, which is the range value of the index field. In theory, there are three partition indexes.

Global index ):
For a global index, you can select whether to partition, and the index partition does not correspond to the table partition. During partition maintenance, the invalid of the global index is usually caused and must be rebuilt after the operation is completed. Oracle9i provides the update global indexes statement, which allows you to re-create a global index while maintaining partitions.

1: The storage location of the index information is completely irrelevant to the partition (partition) information of the parent table. It doesn't even matter if the parent table is a partition table.

Create index dinya_idx_t on dinya_test (item_id) global partition by range (item_id )(

Partition idx_1 valueless than (1000) tablespace dinya_space01,
Partition idx_2 values less than (10000) tablespace dinya_space02,
Partition idx_3 values less than (maxvalue) tablespace dinya_space03
);
2: In this case, if the parent table is a partition table, you must update global index to delete all partitions of the parent table. Otherwise, the index information is incorrect.
Alter table tablename drop partition partitionname update global indexes

Local index ):

For a local index, each table partition corresponds to one index partition (that is, only one local index can be created for a partition table field). When the table partition changes, oracle automatically maintains indexes;

1: The storage location of the index information depends on the partition (partition) information of the parent table. In other words, to create such an index, you must ensure that the parent table is a partition ), the index information is stored in the tablespace where the partition of the parent table is located.

2: however, it can only be created in a partition table with the parent table hashtable or composite.
3: You can only create a hashtable or composite partition table in the parent table. The specified number of partitions must be the same as the number of partitions in the parent table.

Create index dinya_idx_t on dinya_test (item_id) Local (
Partition idx_1 tablespace dinya_space01,
Partition idx_2 tablespace dinya_space02,
Partition idx_3 tablespace dinya_space03
);

Index the entire table directly without specifying the index partition name
Create index dinya_idx_t on dinya_test (item_id );