Oracle Table Partitioning details the concept and operation of partitioned tables from the following aspects: Table space and partitioned table concept table partitioning specific role table partitioning several types of table partitioning and how to maintain operations on table partitioning
1. tablespace and partitioned Table concept table space: is a collection of one or more data files, all of which are stored in the specified table space, but are primarily tables, so called table spaces. Partitioned tables: When the amount of data in a table grows, the query data slows down and the performance of the application degrades, so 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 table spaces (physical files) so that querying the data does not always scan the entire table. www.2cto.com 2. Table partitioning's specific role Oracle's table partitioning capabilities bring great benefits to a wide variety of applications by improving manageability, performance, and availability. In general, partitioning can greatly improve the performance of certain queries and maintenance operations. In addition, partitioning can greatly simplify common administrative tasks, and partitioning is a key tool for building gigabytes of data systems or ultra-high availability systems. The partitioning feature enables you to further subdivide a table, index, or index organization table into segments, which are called partitions. Each partition has its own name, and you can choose your own storage features. From the database administrator's point of view, a partitioned object has multiple segments that can be collectively managed or managed separately, which gives the database manager considerable flexibility in managing the objects after the partition. However, from the application's point of view, the partitioned table is exactly the same as the non-partitioned table, and no modifications are required to access the partitioned table using the SQL DML command. When to use partition table: 1) The size of the table is more than 2GB. 2) The table contains historical data, and new data is added to the new partition. 3. Advantages and disadvantages of table partitioning: 1) Improve query performance: Queries on partitioned objects can search only the partitions they care about, improving retrieval speed. 2) Enhanced usability: If one partition of the table fails, the data of the table on the other partition is still available; 3) Maintenance is convenient: If a partition of the table fails, repair the data, repair the partition only; 4) Balanced i/ O: Different partitions can be mapped to disk to balance I/O and improve overall system performance. Cons: www.2cto.com Partitioned table related, existing tables no method can be converted directly into a partitioned table. However Oracle provides the ability to redefine tables online. 4. Several types of table partitioning and how to operate 1. The range partition range maps the data to each partition based on the range, which is the partition key you specify when you create the partitionOf This partitioning method is most commonly used, and the partitioning key often takes the date. For example, you might partition sales data by month. When using range partitioning, consider the following rules: 1) Each partition must have a values less then clause that specifies an upper value that is not included in the partition. Any record of the partition key that is equal to or greater than this upper value is added to the next higher partition. 2) All partitions, except the first one, will have an implicit lower value, which is the upper value of the previous partition of this partition. 3) in the highest partition, MaxValue is defined. The MaxValue represents an indeterminate value. This value is higher than the value of any partition key in the other partition, and it can be understood to be higher than the value less or less that is specified in any partition, including null values. Example 1: Suppose there is a customer table with data 200000 rows, we partition this table through CUSTOMER_ID, each partition stores 100000 rows, we save each partition in a separate tablespace so that the data file can span multiple physical disks. Here is the code to create the table and partition, as follows: create table CUSTOMER ( customer_id number not NULL PRIMARY key, First_nam E varchar2 (+) not null, last_name VARCHAR2 (in) not null, PHONE &NBSP ;  VARCHAR2 () not null, EMAIL  VARCHAR2 (+), STATUS CHAR (1)) PARTITION by RANGE (customer_id) ( PARTITION cus_part1 VALUES less THAN (100000) tablespace CU s_ts01, PARTITION cus_part2 VALUES less THAN (200000) tablespace CUS_TS02) Example 2: divided by Time create TABLE 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_ac t_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 THAN (to_date (' 01-jul-2003 ', ' dd-mon-yyyy ') tablespace ord_ts03 ) Example 3:maxvalue CREATE TABLE rangetable ( IDD INT PRIMARY KEY, iname VARCHAR (Ten), grade int ) PARTITION   ; by range (grade) ( PARTITION part1 VALUES less then (+) tablespace par t1_tb, PARTITION part2 VALUES less then (MAXVALUE) tablespace part2_tb); www.2cto.com 2. List partition: This partition is characterized by a column with only a few values, Based on this feature, we can use list partitioning. Example 1 create TABLE problem_tickets ( problem_id Number (7) not NULL PRIMARY key, descript ION varchar2 (, customer_id number (7) Not null, date_entered DATE not Null,&nbs P Status VARCHAR2 (PARTITION) by LIST (status) ( PARTITION prob_active &nbs P VALUES (' ACTIVE ') tablespace prob_ts01, PARTITION prob_inactive VALUES (' INACTIVE ') tablespace prob_t s02 Example 2 create table listtable ( ID int primary key, & nbsp Name varchar, Area varchar (Ten)) PARTITION by list (area) ( Partitio N part1 values (' Guangdong ', ' Beijing ') tablespace Part1_tb, PARTITION part2 values (' Shanghai ', ' Nanjing ') tablespace part2_tb); ) 3. Hash partition: This type of partition uses a hash algorithm on the column values to determine which partition the row is placed in. Hash partitioning is recommended when the value of the column does not have an appropriate condition. A hash partition is a type of partition that distributes data evenly by specifying the partition number, because the partitions are identical in size by hashing on the I/O device. Example 1: create TABLE hash_table ( col number (8), INF VARCHAR2 (+)) PARTITION by HASH (COL) ( PARTITION P ART01 tablespace hash_ts01, PARTITION PART02 tablespace hash_ts02, PARTITION PART03 tablespace hash_ts03) & nbsp; Shorthand: create TABLE emp ( EMPNO number (4), ename VARCHAR2 (in), sal N umber) PARTITION by hash (empno) partitions 8STORE in (EMP1,EMP2,EMP3,EMP4,EMP5,EMP6,EMP7,EMP8); Hash partition The main mechanism is based on the hash algorithm to calculate the specific record should be inserted into which partition, hash algorithm is the most important hash function, Oracle if you want to use the hash partition, only specify the number of partitions. It is recommended that the number of partitions be 2 N, which makes the data distribution more evenly between partitions. 4. Combined range hash partition this partition is based on the range partition and the list partition, where the table is first scoped by a column and then partitioned by a column, and the partitions in the partition are called sub-partitions. create TABLE SALES ( product_id VARCHAR2 (5), sales_date date, sales_cost number (Ten), STATUS VARCHAR2 ) partition by RANGE (SALES_date) Subpartition by LIST (STATUS) www.2cto.com ( partition P1 VALUES less THAN (to_date ( ' 2003-01-01 ', ' yyyy-mm-dd ')) tablespace rptfact2009 ( & nbsp subpartition p1sub1 VALUES (' ACTIVE ') tablespace rptfact2009, SUBPA Rtition p1sub2 VALUES (' INACTIVE ') tablespace rptfact2009 ), partition P2 VA Lues less THAN (to_date (' 2003-03-01 ', ' yyyy-mm-dd ')) tablespace rptfact2009 ( &nbs P subpartition p2sub1 VALUES (' ACTIVE ') tablespace rptfact2009, &NBSP ; subpartition p2sub2 VALUES (' INACTIVE ') tablespace rptfact2009 )) &N Bsp;5. Compound-scoped Hash partition: This partition is based on a range partition and a hash partition, and the table is first scoped by a column and then hashed by a column. create table dinya_test ( transaction_id number primary Key,&nbsP;ITEM_ID Number (8) Not null, item_description varchar2 (+), 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 (' 2006-01-01 ', ' Yyyy-mm-dd '), partition part_02 values less than (to_date (' 2010-01-01 ', ' yyyy-mm-dd ')), partition part_03 values less than (maxvalue) ) 5. Some Maintenance operations on table partitioning
1) Add partition The following code adds a P3 partition to the Sales table ALTER TABLE sales add PARTITION P3 VALUES less THAN (to_date (' 2003-06-01 ', ' Yyyy-mm-dd '); Note: The above partition boundaries should be added above the last partition bounds. The following code adds a P3SUB1 sub-partition to the P3 partition of the Sales table ALTER TABLE sales MODIFY PARTITION P3 add subpartition p3sub1 VALUES (' complete '); & nbsp;www.2cto.com 2) Delete partition The following code removes the P3 table partition: ALTER TABLE SALES drop PARTITION p3; removed the P4SUB1 sub-partition in the following code: ALTER Table SALES Drop subpartition p4sub1; Note: If the deleted partition is the only partition in the table, then this partition will not be deleted and the table must be deleted if you want to delete this partition. 3) TRUNCATE partition truncation of a partition refers to the deletion of data from a partition, does not delete the partition, and does not delete data from other partitions. You can truncate a table even if it has only one partition. Truncate the partition with the following code: ALTER TABLE sales TRUNCATE PARTITION p2; truncate the sub-partition with the following code: ALTER TABLE sales TRUNCATE subpartition p2sub2;& nbsp;4) Merge partitioned partitions merge contiguous partitions into a single partition, resulting in partitions with higher partitions, and it is worth noting that partitions cannot be merged into lower-bound partitions. The following code implements the merge of the P1 P2 partition: ALTER TABLE SALES Merge partitions p1,p2 into PARTITION p2; 5) split partition split partition divide a partition into two new partitions, split the original partition No longer exists. Note You cannot split a hash-type partition. ALTER TABLE SALES sblit PARTITION P2 at (to_date (' 2003-02-01 ', ' yyyy-mm-dd ')) into (PARTITION P21,PARTITION P22), 6) bonded Partitioning (COALESCA) in conjunction with partitioning is the data in the hash partition is joined to other partitions, when the data in the hash partition is larger, you can increase the hash partition, and then engage, it is worth noting that A bonded partition can only be used in a hash partition. Join the partition with the following code: ALTER table Sales Coalesca partition; 7) Rename table partition the following code changes P21 to P2alter table sales RENAME PARTITION P P2; 8) Related queries 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) ); How many partitions are on the query 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 --displays information for all partitioned tables in the database: SELECT * FROM dba_part_tables -- Displays all partition table information that the current user can access: SELECT * from all_part_tables --displays information for all partition tables for the current user: select * from User_part_tables --Display Table partition information displays detailed partitioning information for all partitioned tables in the database: SELECT * FROM dba_tab_partitions -- Displays detailed partitioning information for all partitioned tables that the current user can access: SELECT * from all_tab_partitions --displays detailed partitioning information for all partitioned tables for the current user: SELECT * from User_tab_ partitions --Display sub-partition information displays sub-partition information for all combined partitioned tables in the database: SELECT * FROM dba_tab_subpartitions -- Displays sub-partition information for all combined partitioned tables that the current user can access: select * from all_tab_subpartitions --displays sub-partition information for all combined partitioned tables for the current user: select * from User_tab_subpartitions --Display partition column displays the partition column information for all partitioned tables in the database: SELECT * from dba_part_key_columns --displays the partition column information for all partitioned tables that the current user can access: SELECT * from All_part _key_columns --Displays the partition column information for all partitioned tables for the current user: SELECT * FROM user_part_key_columns --display sub-partition columns Displays sub-partition column information for all partitioned tables in the database: SELECT * FROM dba_subpart_key_columns --displays sub-partition column information for all partitioned tables that the current user can access: SELECT * from All_subpart_key_ columns www.2cto.com --Displays sub-partition column information for all partitioned tables for the current user: SELECT * from user_subpart_key_columns --How to query out all the partition tables in the Oracle database SELECT * FROM User_tables a where a.partitioned= ' YES ' --delete data from a table is truncate Table table_name; --Delete partition table data for a partition is ALTER TABLE TABLE_NAME TRUNCATE PARTITION p5;
Detailed Oracle Table partitioning
