Use and Management of Oracle partition tables

The use and management of Oracle partition tables use very large tables, such as demographic statistics tables. If a table is large, the query speed is reduced and the management difficulty is increased. In the event of disk damage, data in the entire table may be lost, making it difficult to recover. Based on this situation, you can create a partition table and divide a large table into Several partitions (small segments). Data operations and management can be performed on partitions, which improves the database operation efficiency. Partitions can exist in different tablespaces, improving data availability. 1. advantages of using partitions: * Enhanced availability: If a partition in the table fails, data in other partitions is still available. * easy maintenance: If a partition in the table fails, you only need to repair the data in this partition. * balanced I/O: You can map different partitions to the disk to balance I/O and improve the overall system performance. * improved query performance: to query partition objects, you can only search for the partitions you are concerned about, improving the retrieval speed. 2. table partitions can be based on the values of one or more columns. This column or multiple columns are called partition keywords or partition columns. All partitions have the same logical attributes (column names, data types, constraints, and so on), but each partition can have its own physical attributes (tablespace, storage parameters, and so on ). There are four types of partitions: range partition, hash partition, list partition, and combined partition. Range partition: partitions are created based on the range of the partition key value, and records are stored in the range partition where the column value is located. For example, create a partition by time. Create tablespace test_space001 datafile 'd: \ tbs001.dbf 'size20m; create tablespace test_space002 datafile 'd: \ tbs002.dbf 'size20m; create tablespace test_space003 datafile 'd: \ tbs003.dbf 'size20m; Create a Partition table: Create table range_example (Range_key_column date, Dat varchar2 (20), Id integer) Partition by range (range_key_column) (Partition part01 values less than (to_date ('2017-8-28 ', 'yyyy-mm-dd') tablesp Ace test_space01, Partition part02 values less than (to_date ('2017-9-28 ', 'yyyy-mm-dd') tablespace test_space02Partition part03 values less than (maxvalue) tablespace test_space03) if some records cannot predict the range, you can create a maxvalue partition, all records not within the specified range will be stored in the partition where Maxvalue is located for query on the table partition Select * from range_example partition (part01); hash partition: for tables that cannot effectively divide the range, use the HASH algorithm to partition the partition column, which will help improve performance. Hash partitions evenly distribute data in the table to the specified partitions. The partition where the column is located is automatically allocated based on the hash value of the partition column, therefore, you cannot control or know which partition the record will be put in. create table test (transaction_id number primary key, item_id number (8) not null) partition by hash (transaction_id) (partition part_01 tablespace tablespace01, partition part_02 tablespace tablespace02, partition part_03 tablespace partition ); list partition: determines the partition to which the record is allocated based on the predefined partition key value. List partitions also need to specify the column value. The partition value must be explicitly specified. Only one partition column can be specified. Multiple Columns cannot be specified as partition dependency columns in the range or hash partition, however, the corresponding values of a single partition can be multiple. When partitioning, you must determine the values that may exist in the partition column. Once the inserted column value is not within the partition range, insertion/update will fail. Therefore, we recommend that you use list partitioning, create a default partition to store records that are not within the specified range, similar to the maxvalue partition in the range partition. Create table custaddr (id varchar2 (15 byte) not null, areacode varchar2 (4 byte) partition by list (areacode) (partition t_list025 values ('025 '), partition t_list372 values ('000000'), partition t_list510 values ('000000'), partition p_other values (default) combined partition: If a table is partitioned by a column, it is still relatively large, or there are some other requirements. You can also re-create a sub-partition in the partition to re-partition the partition, that is, the method of combining partitions. There are two types of combined partitions in 10 GB: range-hash and range-list. Note that the root partition can only be a range partition, but a subpartition can be a hash partition or a list partition. In Oracle 11g, the combined partitioning function adds range-range, list-range, list-list, list-hash, and supports Interval partitions and virtual column partitions in 11g. Syntax: partition by range (column_name1) subpartition by hash (column_name2) SUBPARTITIONS number_of_partitions (PARTITION part1 value less than (range1), PARTITION part2 value less than (range2 ),... PARTITION partN value less than (MAXVALUE); Application: after the time field is partitioned by range, there are still a large number of records for each PARTITION, which can be based on columns with few duplicates (months) create table sales (PRODUCT_ID VARCHAR2 (5), SALES_DATE date not null, SALES_COST NUMBER (10 )) partition by range (SALES_DATE) subpartition by hash (PRODUCT_ID) subpartitions 3 store in (tablespace01, tablespace02, tablespace03) (PARTITION S1 values less than (TO_DATE ('1949th/100 ', 'dd/MON/yyyy'), PARTITION S2 values less than (TO_DATE ('1949th month/123', 'dd/MON/yyyy ')), PARTITION S3 values less than (MAXVALUE ));