Oracle Table Partitioning

The following describes the concepts and operations of partition tables in oracle table partitions: concept of table partitions in Table spaces and partition tables specific roles of table partitions Advantages and Disadvantages of table partitions several types of table partitions and operation methods of table partition maintainability
1. concept table space for tablespaces and partition tables: it is a collection of one or more data files. All data objects are stored in the specified tablespace, but they are mainly stored in tables, so they are called tablespaces. Partitioned table: as the data volume in the table increases, the data query speed slows down and the performance of the application degrades. In this case, you should consider partitioning the table. After the table is partitioned, the logical table is still a complete table, but the data in the table is physically stored in multiple tablespaces (physical files, it does not scan the entire table every time. Www.2cto.com 2. Specific role of Table Partitioning Oracle's Table Partitioning function improves manageability, performance, and availability, which brings great benefits to various applications. In general, partitions can greatly improve the performance of some queries and maintenance operations. In addition, partitioning can greatly simplify common management tasks. Partitioning is a key tool for building a gigabit data system or a super high availability system. The partition function further segments a table, index, or index organization table into segments. The segments of these database objects are called partitions. Each partition has its own name, and you can select its own storage features. From the perspective of the database administrator, the objects in a partition have multiple segments, which can be managed collectively or separately, this makes the database administrator quite flexible in managing the objects after the partition. However, from the perspective of the application, the partitioned table is exactly the same as the non-partitioned table. You do not need to modify it when using the SQL DML command to access the partitioned table. When to use a partition table: 1) The table size exceeds 2 GB. 2) The table contains historical data. New data is added to new partitions. 3. Advantages and Disadvantages of table partitions: 1) Improved query performance: You can query partition objects by searching only the partitions you are concerned about, improving the search speed. 2) Enhanced availability: If a partition of the table fails, data in other partitions is still available. 3) Easy Maintenance: If a partition of the table fails, data must be repaired, only the partition can be repaired. 4) Balanced I/O: You can map different partitions to the disk to balance I/O and improve the overall system performance. Disadvantages: www.2cto.com is related to partition tables. Existing tables cannot be directly converted to partition tables. However, Oracle provides the online table redefinition function. 4. Several table partition types and operation methods 1. Range partition range partitions map data to each partition based on the range, which is determined by the partition key you specified when creating the partition. This partitioning method is the most commonly used, and the partition key usually uses the date. For example, you may partition the sales data by month. When using range partitions, consider the following rules: 1) Each partition must have a values less then clause, which specifies an upper limit not included in the partition. Any record with the partition key value equal to or greater than the upper limit will be added to the next higher partition. 2) all partitions except the first partition have an implicit lower limit. This value is the upper limit of the previous partition. 3) MAXVALUE is defined in the highest partition. MAXVALUE indicates an uncertain value. This VALUE is higher than the VALUE of any partition key in other partitions. It can also be understood as a VALUE higher than the value less then specified in any partition and a null VALUE. Example 1: assume there is a CUSTOMER table with 200000 rows of data. We partition the table using mermer_id, and store 100000 rows for each partition, we save each partition to a separate tablespace so that data files can span multiple physical disks. The following code creates tables and partitions: create table customer (CUSTOMER_ID number not null primary key, FIRST_NAME VARCHAR2 (30) not null, LAST_NAME VARCHAR2 (30) not null, PHONE VARCHAR2 (15) not null, EMAIL VARCHAR2 (80), status char (1) partition by range (CUSTOMER_ID) (PARTITION CUS_PART1 values less than (100000) TABLESPACE CUS_TS01, PARTITION CUS_PART2 values less than (200000) TABLESPACE CUS_TS02) Example 2: create table by Time ORDER_ACTIVITIES (ORDER_ID NUMBER (7) not null, ORDER_DATE DATE, TOTAL_AMOUNT NUMBER, CUSTOTMER_ID NUMBER (7), paid char (1) partition by range (ORDER_DATE) (PARTITION ORD_ACT_PART01 values less than (TO_DATE ('01-MAY-2003 ', 'dd-MON-YYYY') TABLESPACEORD_TS01, PARTITION ORD_ACT_PART02 values less than (TO_DATE ('01-JUN-2003 ', 'dd-MON-YYYY ') TABLESPACE ORD_TS02, PARTITION ORD_ACT_PART02 VALUES LESS T HAN (TO_DATE ('01-JUL-2003 ', 'dd-MON-YYYY') TABLESPACE ORD_TS03) Example 3: maxvalue create table RangeTable (idd int primary key, iNAME VARCHAR (10 ), grade INT) partition by range (grade) (PARTITION part1 values less then (1000) TABLESPACE Part1_tb, PARTITION part2 values less then (MAXVALUE) TABLESPACE Part2_tb); www.2cto.com 2. list partition: This partition has only a few values in a column. Based on this feature, we can use list partitions. Example 1 create table PROBLEM_TICKETS (PROBLEM_ID NUMBER (7) not null primary key, DESCRIPTION VARCHAR2 (2000), CUSTOMER_ID NUMBER (7) not null, DATE_ENTERED date not null, STATUS VARCHAR2 (20) partition by list (STATUS) (PARTITION PROB_ACTIVE VALUES ('active') TABLESPACE PROB_TS01, PARTITION PROB_INACTIVE VALUES ('inactive ') TABLESPACE PROB_TS02 Example 2 create table ListTable (id int primary key, name VARCHAR (20), area VARCHAR (10) partition by list (area) (PARTITION part1 VALUES ('guangdong', 'beijinging') TABLESPACE Part1_tb, PARTITION part2 VALUES ('shanghai ', 'nanjing') TABLESPACE Part2_tb);) 3. hash partition: This type of partition uses the hash algorithm on the column value to determine which partition the row is placed in. When the column value does not have an appropriate condition, hash partitions are recommended. 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. Example 1: create table HASH_TABLE (col number (8), INF VARCHAR2 (100) partition by hash (COL) (PARTITION PART01 TABLESPACE HASH_TS01, PARTITION PART02 TABLESPACE HASH_TS02, PARTITION PART03 TABLESPACE HASH_TS03) Abbreviation: create table emp (empno NUMBER (4), ename VARCHAR2 (30), sal NUMBER) partition by hash (empno) PARTITIONS 8 store in (emp1, emp2, emp3, emp4, emp5, emp6, emp7, emp8); the primary mechanism of hash partitioning is to calculate a specific record based on the hash algorithm and insert it In which partition is the most important part of the hash algorithm is the hash function. If you want to use hash partitions in Oracle, you only need to specify the number of partitions. We recommend that the number of partitions use the N power of 2 to make the data distribution in each shard more even. 4. combined range hash partitions are based on range partitions and list partitions. A table first partitions a range by a column and then partitions a list by a column, A partition is called a subpartition. Create table sales (PRODUCT_ID VARCHAR2 (5), SALES_DATE DATE, SALES_COST NUMBER (10), STATUS VARCHAR2 (20) partition by range (SALES_DATE) subpartition by list (STATUS) www.2cto.com (PARTITION P1 values less than (TO_DATE ('1970-01-01 ', 'yyyy-MM-DD') TABLESPACE rptfact2009 (SUBPARTITION P1SUB1 VALUES ('active') TABLESPACE rptfact2009, SUBPARTITION P1SUB2 VALUES ('inactive') TABLESPACE rptfact2009 ), PARTITION P2 values less than (TO_DATE ('1970-03-01 ', 'yyyy-MM-DD') TABLESPACE rptfact2009 (SUBPARTITION P2SUB1 VALUES ('active') TABLESPACE rptfact2009, SUBPARTITION P2SUB2 VALUES ('inactive') TABLESPACE rptfact2009) 5. composite range hash partition: This type of partition is based on range partitions and hash partitions. the table first partitions the range by a column, and then hash partitions by a column. Create table dinya_test (transaction_id number primary key, item_id number (8) not null, item_description varchar2 (300), transaction_date date) partition by range (transaction_date) subpartition by hash (transaction_id) subpartitions 3 store in (dinya_space01, dinya_space02, dinya_space03) (partition part_01 values less than (to_date ('2017-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); 5. maintenance operations on table partitions
1) add partition the following code adds a P3 partition alter table sales add partition P3 values less than (TO_DATE ('2017-06-01 ', 'yyyy-MM-DD') to the sales table ')); note: The partition limit added above should be higher than the last partition limit. The following code adds an P3SUB1 SUBPARTITION to the P3 PARTITION of the sales table alter table sales modify partition P3 add subpartition P3SUB1 VALUES ('complete'); www.2cto.com 2) delete PARTITION the following code deletes the PARTITION of the P3 TABLE: alter table sales drop partition P3; Delete the P4SUB1 SUBPARTITION in the following code: alter table sales drop subpartition P4SUB1; note: if the partition to be deleted is the only partition in the table, the partition cannot be deleted. to delete the partition, you must delete the table. 3) truncating a partition refers to deleting data in a partition, neither deleting the partition nor deleting data in other partitions. If a table has only one partition, it can be truncated. Run the following code to PARTITION: alter table sales truncate partition P2; run the following code to PARTITION: alter table sales truncate subpartition P2SUB2; 4) merge partitions to combine adjacent partitions into one partition. The result partition uses the boundary of High-score partitions. It is worth noting that partitions cannot be merged into partitions with low boundaries. Run the following code to MERGE P1 P2 PARTITIONS: alter table sales merge partitions P1, P2 into partition P2; 5) Split PARTITIONS split one partition into two new PARTITIONS, after splitting, the original partition no longer exists. Note that HASH partitions cannot be split. Alter table sales sb1_partition P2 AT (TO_DATE ('1970-02-01 ', 'yyyy-MM-DD') INTO (PARTITION p21. PARTITION P22); 6) join PARTITION (coalesca) A combined partition joins data in a hash partition to another partition. When the data in the hash partition is large, you can add a hash partition and then join the partition. It is worth noting that, join partitions can only be used in hash partitions. Run the following code TO create a join PARTITION: alter table sales coalesca partition; 7) RENAME a table partition: run the following code TO change P2ALTER table sales rename partition p22to P2; 8) related query cross-PARTITION query select sum (*) from (select count (*) cn from t_table_SS PARTITION (P200709_1) union all select count (*) cn from t_table_SS PARTITION (P200709_2 )); query the partitions in a table.
SELECT * FROM useR_TAB_PARTITIONS WHERE TABLE_NAME = 'tablename' query index information select object_name, object_type, tablespace_name, sum (value) from v $ segment_statistics where statistic_name IN ('physical reads ', 'Physical write', 'logical reads') and object_type = 'index' www.2cto.com group by object_name, object_type, tablespace_name order by 4 desc -- display information of all database partition tables: select * from DBA_PART_TABLES -- display information about all partition tables accessible to the current user: select * from ALL_PART_TABLES -- display information about all partition tables of the current user: select * from USER_PART_TABLES -- display table partition information display detailed partition information of all database partition tables: select * from DBA_TAB_PARTITIONS -- display detailed partition information of all partition tables accessible to the current user: select * from ALL_TAB_PARTITIONS -- displays the detailed partition information of all the current user's partition tables: select * from USER_TAB_PARTITIONS -- displays the subpartition information of all the combined partition tables of the database: select * from DBA_TAB_SUBPARTITIONS -- displays the information about all the sub-partitions of all the combined partition tables that the current user can access: select * from ALL_TAB_SUBPARTITIONS -- displays the information about the sub-partitions of all the combined partition tables of the current user: select * from USER_TAB_SUBPARTITIONS -- display the partition column information of all database partition tables in the partition column: select * from DBA_PART_KEY_COLUMNS -- display the partition column information of all partition tables accessible to the current user: select * from ALL_PART_KEY_COLUMNS -- display the partition column information of all the current user's partition tables: select * from USER_PART_KEY_COLUMNS -- display the subpartition column information of all database partition tables: select * from DBA_SUBPART_KEY_COLUMNS -- displays information about the subpartition columns of all partition tables that the current user can access: select * from ALL_SUBPART_KEY_COLUMNS www.2cto.com -- displays information about subpartition columns of all partition tables of the current user: select * from USER_SUBPART_KEY_COLUMNS -- How to query all the partition tables in the oracle database select * from user_tables a where. partitioned = 'yes' -- the data for deleting a table is truncate table table_name; -- the data for deleting a partition in a partition table is alter table table_name truncate partition p5; leiOOlei