Oracle Table Partitioning Detailed __oracle

Introduction of Oracle Partition

Oracle partitioning is a technique for handling oversized tables, indexes, and so on. Partitioning is a "divide and conquer" technique that provides scalable performance for large amounts of data by dividing large tables and indexes into manageable chunks, thus avoiding the management of each table as a large, separate object. Partitioning reduces the time required for administrative operations by assigning operations to smaller storage units, improves performance through enhanced parallelism, and increases availability by shielding partitions of failed data.

Ii. Advantages and disadvantages of Oracle Partitioning

 Advantages:

Enhanced availability: If one of the table's partitions fails, the table's data in other partitions is still available;

Easy maintenance: If a partition of the table fails, the data needs to be repaired, only the partition can be repaired;

Balanced I/O: can map different partitions to disk to balance I/O, improve the overall system performance;

Improved query performance: Queries on partitioned objects can search only for areas of interest and improve retrieval speed.

 Disadvantages:

Partition Table correlation: Existing tables do not have methods that can be directly converted into partitioned tables. However, Oracle provides the ability to redefine tables online.

Iii. Oracle Partitioning method

 Range Zoning:

A range partition is a range of values in a datasheet that determines which partition the data is stored on, depending on the scope of the value. If according to the ordinal partition, according to the business record creation date carries on the partition and so on.

hash partition (hash partition):

A hash partition is a type of partition that distributes data evenly by specifying the partition number, because these partitions are identical in size by hashing partitions on the I/O device.

list partition (list partition):

The list partitioning method is used when you need to explicitly control how rows are mapped to partitions. Unlike range and hash partitions, the list partition does not support multiple-column partitions. If you want to partition a table by column, the partitioning key can only be made up of a single column of the table, but all columns that can be partitioned with a range or hash partitioning method can be partitioned using a list partitioning method.

 Range-Hash partition (composite partition):

Sometimes when we need to partition the range, the data in each partition is scattered in several tablespaces, so we use a composite partition. A composite partition is a partitioning method that uses a range partition first and then uses a hash partition within each partition (note: Make sure the range partition is first)

 Range-List partitioning (composite partition):

A combination of scope and list technology, first partition the table, and then partition each range partition again with the list technology. Unlike a combo range-hash partition, all of the content of each child partition represents a logical subset of the data, as described by the appropriate scope and list partitioning settings. (Note: Be sure to do range partitioning first)

-------------------------------

Oracle table partitioning and index partition rollup

In order to simplify the management of database large tables, such as in the Data warehouse is generally a terabytes of the order of magnitude. ORACLE8 later introduced the partitioning option. Partitions separate the tables into large tables that support the expansion of the element in a divide-and-conquer way in different tablespace spaces, The manageability of a large table at the physical level. Splitting a large table into smaller partitions can improve the maintenance, backup, recovery, transaction, and query performance of the table.

Advantages of Partitioning:

1. Enhance usability: If one partition of a table is not usable due to system failure, the rest of the table can still be used;

2, reduce the shutdown time: If the system failure affects only a part of the table partition, then only this part of the partition needs to be repaired, the mine can be repaired than the entire large table to spend less time;

3, Maintenance Easy: If you need to build a table, the single production management of each public area than the management of a single large table is much easier;

4, balanced I/O: The table can be divided into different partitions to separate disk to balance I/O performance improvement;

5, improve performance: the large table query, add, modify and other operations can be broken down to the table of different partitions to execute in parallel, can make running faster, in the Data Warehouse TP query is particularly useful.

6, the partition is transparent to the user, the end user does not feel the existence of the partition.

---------------------------------This article from the following aspects to collate the concept and operation of the partition table: 1. Table space and the concept of partitioned Tables 2. The specific role of table partitioning 3. The advantages and disadvantages of table partitioning 4. Table partitioning of several types and operating methods 5. Maintenance Operations on table partitions. (1) Table space and the concept table space of the partition table:
is a collection of one or more data files, all of which are stored in a specified table space, but are primarily stored as tables, so they are called table spaces. Partition table:
When the amount of data in a table increases, the query data slows down, and the application's performance drops, 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 when querying the data, it does not scan the entire table every time. (2). Table Partitioning's specific role Oracle's table partitioning feature provides great benefits for 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 a gigabit data system or a hyper-high-availability system. Partitioning enables you to further subdivide a table, index, or index organization table into segments, which are sections of the database objects called partitions. Each partition has its own name, and you can choose your own storage attributes. From a database administrator's point of view, an object with a partition has multiple segments, which can be managed collectively or separately, giving the database administrator considerable flexibility in managing the objects after the partition. However, from an application perspective, the partitioned table is exactly the same as a partitioned table, and the SQL DML command is used to access the partitioned table without any modification. When to use partitioned tables:
1, the size of the table more than 2GB. 2, the table contains historical data, new data is added to the new partition. (3). Table partitioning advantages and disadvantages table partitioning has the following benefits:
1, improve query performance: The query on the partition object can only search their own care, improve the speed of retrieval. 2. Enhance usability: If a partition of a table fails, tables are still available in other partitions; 3, easy to maintain: if a partition of a table fails, you need to repair the data, only repair the partition; 4, balanced I/O: You can map different partitions to disk to balance I/O and improve overall system performance. Disadvantages:
Partition Table correlation: Existing tables do not have methods that can be directly converted into partitioned tables. However, Oracle provides the ability to redefine tables online. (4). Several types of table partitioning and their operating methods I. Range Partitioning:
A range partition maps data to each partition based on scope, which is determined by the partitioning key that you specify when you create the partition. This partitioning method is most commonly used, and the partitioning key often takes a date. For example, you might be able to 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 bound value that is not included in the partition. Any record of a partition key that is equal to or greater than this upper bound value is added to the next higher partition. 2. All partitions, except the first one, will have an implicit lower bound value, which is the upper bound 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 any partition key in other partitions, and can be understood to be higher than the value less then specified in any partition, including null values. Example one: Suppose there is a customer table with 200000 rows of data, we partition the table through customer_id, each partition stores 100000 rows, and 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 tables and partitions, as follows: Create TABLE CUSTOMER
(
customer_id number not NULL PRIMARY KEY,
First_Name VARCHAR2 () not NULL,
Last_Name VARCHAR2 () not NULL,
PHONE VARCHAR2 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 two: CREATE TABLE order_activities by Time
(
ORDER_ID Number (7) is 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 VALUE S less THAN (to_date (' 01-jul-2003 ', ' dd-mon-yyyy ') tablespace ord_ts03) case three: 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
); Two. List partition: This partition is characterized by a column of only a few values, based on this feature we can use the list partition. Example a CREATE TABLE problem_tickets
(
PROBLEM_ID Number (7) is not NULL PRIMARY KEY,
DESCRIPTION VARCHAR2 (2000),
CUSTOMER_ID Number (7) is 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 case two CREATE TABLE listtable
(
ID INT PRIMARY KEY,
Name VARCHAR (20),
Area VARCHAR (10)
)
PARTITION by LIST (area)
(
PARTITION part1 VALUES (' Guangdong ', ' Beijing ') tablespace PART1_TB,
PARTITION part2 VALUES (' Shanghai ', ' Nanjing ') tablespace PART2_TB
); ) three. Hash partition:
This type of partition uses a hashing algorithm on the column value to determine which partition to put the row into. A hash partition is recommended when the value of a 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 these partitions are identical in size by hashing partitions on the I/O device. Example one: 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
) Shorthand: 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 main mechanism of the hash partition is based on the hash algorithm to calculate a specific record should be inserted into which partition, the hash algorithm is the most important hash function, Oracle if you want to use the hash partition, you can only specify the number of partitions. It is recommended that the number of partitions should be 2 n-th, so that the data distribution among the partitions is more uniform. Four. Combined range hash Partition
This partition is based on a range partition and a list partition, where the table is first scoped by a column, and then the list is 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 (a), STATUS VARCHAR2 (20)) PARTITION by RANGE (sales_date) subpartition by LIST (STATUS) (PARTITION P1 VALUES less THAN (' to_date ', ' 2003-01-01 -mm-dd ')) tablespace rptfact2009
(
Subpartition p1sub1 VALUES (' ACTIVE ') tablespace rptfact2009,
Subpartition p1sub2 VALUES (' INACTIVE ') tablespace rptfact2009
),
PARTITION P2 VALUES Less THAN (to_date (' 2003-03-01 ', ' yyyy-mm-dd ')) tablespace rptfact2009
(
Subpartition p2sub1 VALUES (' ACTIVE ') tablespace rptfact2009,
Subpartition p2sub2 VALUES (' INACTIVE ') tablespace rptfact2009
)
Five. Composite range hash Partition: This partition is based on a range partition and a hash partition, the table is first scoped by a column, and then a column is partitioned. CREATE TABLE Dinya_test
(
TRANSACTION_ID Number primary Key,
ITEM_ID Number (8) is 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 (' 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 actions on table partitioning:
One, add partitions
The following code adds a P3 partition ALTER table sales add PARTITION P3 VALUES less THAN (to_date (' 2003-06-01 ', ' yyyy-mm-dd ') to the sales table); Note: The partition boundaries added above should be higher than the last partition boundary. The following code adds a P3SUB1 sub partition ALTER table sales MODIFY PARTITION P3 add subpartition VALUES (' p3sub1 ') to the P3 partition of the SALES table; Second, delete the partition
The following code deletes the P3 table partition: ALTER table SALES DROP PARTITION P3; The P4SUB1 sub partition was deleted in the following code: ALTER TABLE SALES DROP subpartition p4sub1; Note: If the deleted partition is the only partition in the table, then the partition will not be deleted and the table must be deleted to remove the partition. Iii. Truncation of partitions
Truncating a partition means deleting the data from a partition and does not delete the partition, nor does it delete the data from other partitions. You can truncate a table even if it has only one partition. Truncate the partition by using the following code: ALTER TABLE SALES TRUNCATE PARTITION P2; Truncate the child partition by using the following code: ALTER TABLE SALES TRUNCATE subpartition p2sub2; Iv. Merging of partitions
Merge partitions combine adjacent partitions into one partition, resulting partitions with higher partition boundaries, and it is noteworthy 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; V. Split Division
A split partition splits a partition into two new partitions, and the original partition does not exist until it is split. 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); Vi. junction Zoning (Coalesca)
Combined partitioning is the integration of data from the hash partition into other partitions, and when the data in the hash partition is larger, the hash partition can be added and then jointed, and it is noteworthy that the junction partition can only be used in the hash partition. Splice partitions through the following code: ALTER TABLE SALES Coalesca PARTITION; Vii. Renaming table Partitions
The following code changes P21 to P2 ALTER TABLE, SALES RENAME PARTITION P21 to P2; Viii. Related Inquiries
Cross-partition Query select SUM (*) from (SELECT COUNT (*) cn to T_TABLE_SS PARTITION (p200709_1) union ALL SELECT COUNT (*) cn from t_t Able_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 ' 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 partitioned table information accessible to the current user: SELECT * from All_part_tables--Displays information for all partitioned tables for the current user: SELECT * from User_part _tables--Display table partition information displays detailed partition information for all partitioned tables in the database: SELECT * FROM Dba_tab_partitions--Displays detailed partition information for all partitioned tables accessible to the current user: SELECT * from All_tab_par Titions--Displays detailed partition information for all partitioned tables for the current user: SELECT * from User_tab_partitions--Displays child partition information displays child partition information for all combined partition tables in the database: SELECT * FROM DBA_TAB_SUBPA Rtitions--sub-partition information for all combined partitioned tables accessible to the current user: SELECT * from All_tab_subpartitions--Displays child partition information for all grouped partitioned tables for the current user: SELECT * from User_tab_ Subpartitions--ShowThe partitioning 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 accessible to the current user: SELECT * FROM All_part_key_columns-Display Partition column information for all partitioned tables for the current user: SELECT * from User_part_key_columns--Displays the child partition column displays the child partition column information of all partitioned tables in the database: SELECT * FROM Dba_subpart_key_columns- Displays the child partition column information for all partitioned tables accessible to the current user: SELECT * from All_subpart_key_columns--Displays the child partition column information for all the current user's partitioned tables: SELECT * from User_subpart_key_ COLUMNS-How to query out all of the partitioned tables in the Oracle database SELECT * FROM User_tables a where a.partitioned= ' yes '--the data that deletes one table is truncate TABLE Table_na Me --The data that deletes one partition of the partition table is ALTER TABLE TABLE_NAME TRUNCATE PARTITION P5;

Oracle Table partitioning Combat: http://love-flying-snow.iteye.com/blog/573303