As the number of rows in the table increases, so does the impact of management and performance performance. Backups will take more time, recovery will take more time, and queries over the entire data table would take more time. By dividing the rows in a table into sections, you can reduce the management and performance problems of large tables, and the method of partitioning the publication data is called partitioning the table. Advantages of Partitioned Tables: (1) Improve query performance: Queries on partitioned objects can search only the partitions they care about, improve retrieval speed, and (2) facilitate data management: Because partitioned table data is stored in multiple parts, it is easier to load and delete data by partition than to load and delete data in large tables ; (3) Convenient backup recovery: Because the partition is smaller than the partitioned table, the backup and restore method for the partition is more than the way to back up and restore the entire table. One, Oracle database provides thetable or IndexPartitioning methods are several: 1) range partition 2) list partition 3) hash partition (hash partition) 4) composite partition (sub-partition) Two, the example demonstrates Oracle partition operation on a table or index 1, create 4 test tablespace, each table space as a separate partition (given the way that partition mappings are implemented in Oracle, it is recommended that you set the number of partitions in the table to 2 to make the data evenly distributed) sys>create tablespace partition1 datafile '/home/oracle/app/oradata/orcl/partition1.dbf ' size 20m; Sys>create tablespace partition2 datafile '/home/oracle/app/oradata/orcl/partition2.dbf ' size 20m; Sys>create tablespace partition3 datafile '/home/oracle/app/oradata/orcl/partition3.dbf ' size 20m; Sys>create tablespace partition4 datafile '/home/oracle/app/oradata/orcl/partition4.dbf ' size 20m; 2, a range partition range partition is a range of values in a data table that is partitioned, depending on the extent of a value, to decide which partition to store the data on. such as according to the serial number partition, according to the business record creation date to partition and so on (Unicom each month's bill record uses the partition table storage). Requirements Description: There is an item Trading table, table name: Material_transactions. The table may have a tens number of data records in the future. The partition table is required when the table is under construction. At this time we can use the ordinal partition three zones, each region is expected to store 30 million of the data, you can also use the date partition,such as every five years of data stored on a partition。 Build a table based on the ordinal partition of the transaction:----in order to test the need to make the following modifications: The transaction number less than 2 is stored on partition 1, the transaction number greater than or equal to 2 and less than 4 is stored on partition 2, the trade number greater than or equal to 4 and less than 6 is stored on partition 3, and the trade number greater (instead of specifying the maximum value for the last partition, the MaxValue keyword tells Oracle to use this partition to store data that cannot be stored in the previous partitions). 2.1, insert data into the partition table: 2.2, query table data does not specify partition: Specify partition: 2.3, change table data 2.4, delete table data 3, hash partition (hash partition) In addition to the range partition, Oracle also supports hash partitioning. A hash partition determines the physical location of the data by executing a hash function on the partition key value. In a range partition, the contiguous values of the partition keys are usually stored in the same partition. In a hash partition, contiguous partition key values do not have to be stored in the same partition. Hash partitioning distributes records across more partitions than the range partition, which reduces the likelihood of I/O contention. There is also a way to define a hash partition: partition by hash (column) partition n store in (tbs1,,, TBSM). The number of tablespace does not have to be equal to the number of partitions, that is, n is not necessarily equal to M, and if the number of partitions specified is greater than the number of tablespaces, then the partitions are allocated in a circular manner to the tablespace.A table space can contain multiple partitions:4, the list partition list partition tells Oracle all possible values and specifies the partition where the corresponding row should be inserted, and the amount of data it applies to the table is large but there are only a few values for a column. In a list partition, you can use the keyword default to specify all cases that are not listed. (The above 4 partitions are created in a table space) 5, composite partitions (sub-partitions) Sometimes we need to partition the data in each partition in a few table spaces, so that we are going to use a composite partition.a composite partition is a partitioning method that uses the range partition first and then uses the hash partition/list partition within each partition。 If the records of the item transactions are partitioned by time, and then the data in each partition is divided into three sub-partitions, the data is hashed to the three specified tablespace: P1:sales_cost less than 1 p1sub1:sales_cost less than 1, And the status is active p1sub2:sales_cost less than 1, and the status is inactivep2:sales_cost greater than or equal to 1, less than 3 p2sub1:sales_ Cost greater than or equal to 1, less than 3, and status active p2sub2:sales_cost is greater than or equal to 1, less than 3, and status is inactive operation Test: Add: If the inserted value is not within the scope of all custom partitions, it will be assigned by Oracle. Three, some maintenance operations on table partitioning: 1, adding a 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;2, delete partition the following code removed the P3 table partition: ALTER table SALES DROP PARTITION p3; encountered such a situation in the test. If the table creates a partition, if you want to delete the data file (tablespace file), delete the partition before you can delete the data file (but when you delete the data file, you must keep a partition to eventually delete the data file & tablespace file). Of course, you can also directly delete the table is also OK, just all the full delete, but the tablespace file is still in. Removed the P4SUB1 sub-partition in the following code: ALTER TABLE SALES DROP subpartition p4sub1; NOTE: if you want to delete the partition. 。 3, truncating partitions truncating a partition means deleting data from a partition and not deleting the partition or deleting 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 partitions Merge partitions merge adjacent partitions into one 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 splits a partition by two A new partition, the original partition no longer exists after splitting. 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); Nbsp;6, junction Zoning (Coalesca) Associative partitioning is the data in the hash partition that is bonded to other partitions, and when the data in the hash partition is larger, you can increase the hash partition and then engage, notably, 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 P21 to P2 8, cross-partition query select SUM (*) from ( select COUNT (*) cn from T_TABLE_SS PARTITION (p200709_1) Union ALL&N Bsp select COUNT (*) cn from T_TABLE_SS PARTITION (P200709_2)); 9, partition-related tables and views:
| Partition |
--How many partitions are on the query table: SELECT * from user_tab_partitions WHERE table_name= ' tableName '--Display table partition information displays a detailed partition of all partitioned tables in the database Info: 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 partition information for all partition tables of the current user: SELECT * from user_tab_partitions |
| Sub-partition |
--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 partition tables of the current user: SELECT * from user_tab_subpartitions |
| Partition table |
--Displays information for all partitioned tables in the database: SELECT * from dba_part_tables where Table_name=upper (' dinya_test ')-- Displays all partition table information that is accessible to the current user: SELECT * from all_part_tables--Displays information about all partition tables for the current user: SELECT * from user_part_tables |
| Partition column |
--Show partition column displays 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 |
| Sub-partition column |
--Show 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 accessible by the current user: SELECT * from All_subpart_key_columns--Displays sub-partition column information for all partitioned tables of the current user: SELECT * from user_subpart_key_columns |
| Exception |
--How to query out all partition tables in the Oracle database: SELECT * from user_tables a where a.partitioned= ' YES ' |
--deleting data from a table is
TRUNCATE TABLE table_name; --Delete partition table The data for a partition is ALTER TABLE TABLE_NAME TRUNCATE PARTITION p5; NOTE: The partition is selected according to the specific situation. Table partitioning has the following advantages: 1, data query: Data is stored on multiple files, reducing the I/O load, query speed increased. 2, Data pruning: the preservation of historical data is very ideal. 3, Backup: The large table of data into multiple files, easy to backup and recovery. 4. Parallelism: DML operations can be performed at the same time in the table, and the performance of parallelism is improved. ================================================ Four, partition index: 1, General index: CREATE index index_name on table ( Col_name); 2, Oracle partition index detailed syntax: Table indexcreate [unique| BITMAP] INDEX [schema.] Index_nameon [schema.] table_name [Tbl_alias] (col [ASC | DESC]) Index_clause index_attribsindex_clauses: divided into the following two cases 1. The Local index is where the index information is stored depending on the partition information of the parent table, in other words, creating such an index must ensure that the parent table is the tablespace where the Partition1.1 index information resides in the parent table's partition. However, only the parent table can be created as a Hashtable or composite partition table. LOCAL STORE in (tablespace) 1.2 can only be created in the parent table for Hashtable or composite partitioned tables. and specify the number of partitions to be the same as the number of partitions of the parent table local STORE in (tablespace) (PARTITION [PARTITION [Logging| Nologging] [tablespace {tablespace| DEFAULT}] [PCTFREE int] [pctused int] [Initrans int] [Maxtrans int] [STORAGE storage_clause] [STORE in {tablespace_name|] DEFAULT] [subpartition [subpartition [Tablespace tablespace]]] 1.3 index information is stored in the table space of the parent table's partition, which is the simplest syntax and is the most common way to create a partitioned index. local 1.4 and specifies the number of PARTITION to be consistent with the PARTITION of the parent table local (PARTITION [partition[logging| Nologging][tablespace {tablespace| Default}][pctfree int][pctused Int][initrans Int][maxtrans int][storage storage_clause][store in {tablespace_name| default][subpartition [subpartition [tablespace tablespace]]] Global index The location of the index information is completely irrelevant to the partition information of the parent table. Even if the parent table is not a partitioned table, it doesn't matter. The syntax is as follows: GLOBAL PARTITION by RANGE (col_list) (PARTITION PARTITION VALUES less THAN (value_list) [logging| Nologging][tablespace {tablespace| Default}][pctfree int][pctused Int][initrans Int][maxtrans int][storage Storage_clause]) But in this case, if the parent table is a partitioned table, To delete a partition of the parent table, you must update the global index, otherwise the index information is incorrect ALTER TABLE TableName DROP PARTITION partitionname Update Global indexes -- Query index Select Object_name,object_type,tablespace_name,sum (value) from V$segment_statisticswhere statistic_name in (' Physical reads ', ' physical write ', ' logical reads ') and Object_type= ' INDEX ' GROUP by Object_name,object_type,tablespace_nameorder by 4 desc Note: Global indexes cannot be created for hash partitions or sub-partitions.
Reprint Please specify source: http://www.cnblogs.com/pengineer/p/4393988.html
Detailed Oracle Partition partition table
