Oracle Partition summary, oraclepartition

Oracle Partition summary, oraclepartition

Table partition http://www.cnblogs.com/ggjucheng/archive/2013/01/03/2843393.html

Http://wenku.baidu.com/link? Url = SE-XAChaTsqht6ddSHI57dV4FfD0pL4h_pXt9vXxU5sA71CkAqihtbzdRuDWexNfv8rvYXs9rv17C8O5g_jK6VrbGVqoJetQbotFB4Pd4P7

Add Partition

Alter table table_name add partition (partCol = 'value1') location 'loc1'; // example

Alter table table_name add if not exists partition (dt = '000000') LOCATION '/user/hadoop/warehouse/table_name/dt = 100'; // ADD a PARTITION at a time

Alter table page_view add partition (dt = '2017-08-08 ', country = 'us ') location '/path/to/us/part080808' PARTITION (dt = '2017-08-09 ', country = 'use') location'/path/to/us/part080809 '; // Add multiple partitions at a time

 

Delete Partition

Alter table login drop if exists partition (dt = '2017-08-08 ');

Alter table page_view drop if exists partition (dt = '2017-08-08 ', country = 'us ');

Modify Partition

Alter table table_name PARTITION (dt = '1970-08-08 ') set location "new location ";

Alter table table_name PARTITION (dt = '1970-08-08 ') rename to partition (dt = '20160901 ');

Query partitions

Select * from user_tables a where a. partitioned = 'yes'

This article describes the concepts and operations of partition tables from the following aspects: 1. table space and Partition Table concept 2. table Partition Function 3. advantages and disadvantages of Table Partitioning 4. table partition types and operation methods 5. maintainability of table partitions.

(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.

(2 ). The specific role of Table Partitioning Oracle's Table Partitioning function brings great benefits to various applications by improving manageability, performance, and availability. 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 partition tables: 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 have the following advantages: 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: Partition Table: existing tables cannot be directly converted to partition tables. However, Oracle provides the online table redefinition function.

(4 ). Table partition types and operation method 1. Range partition: A range partition maps data to each partition based on the range. This range 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 that 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 ORDER_ACTIVITIES (ORDER_ID NUMBER (7) not null, ORDER_DATE DATE, TOTAL_AMOUNT NUMBER, CUSTOTMER_ID NUMBER (7), paid char (1) by Time ))

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 than (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 );

II. 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 partitions is to calculate the partition to which a specific record should be inserted based on the hash algorithm. 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)

(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 ('2017-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 ('1970-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 ). Some 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 alter table sales modify partition P3 add subpartition P3SUB1 VALUES ('complete') to the P3 PARTITION of the sales table ');

Ii. Run the following code to delete a PARTITION: alter table sales drop partition P3; run the following code to delete a P4SUB1 SUBPARTITION: 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. truncation a partition refers to the deletion of data in a partition, which does not delete or delete 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 lower boundaries. Run the following code to MERGE P1 P2 PARTITIONS: alter table sales merge partitions P1, P2 into partition P2;

5. Split a partition. Split a partition into two new partitions. After the partition is split, 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 perform join partitioning: alter table sales coalesca partition;

7. RENAME a table partition the following code changes p21-alter table sales rename partition p21-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); queries the number of partitions in the Table: SELECT * FROM useR_TAB_PARTITIONS WHERE TABLE_NAME = 'tablename' queries the 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

-- 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 of all the 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

-- Display detailed partition information of all partition tables of the current user: select * from USER_TAB_PARTITIONS

-- Display the subpartition information to display the subpartition information of all the combined partition tables of the database: select * from DBA_TAB_SUBPARTITIONS

-- Display the subpartition information of all the combined partition tables accessible to the current user: select * from ALL_TAB_SUBPARTITIONS

-- Display the subpartition information of all the combined partition tables of the current user: select * from USER_TAB_SUBPARTITIONS

-- Display partition column information of all database partition tables: 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 partition tables of the current user: select * from USER_PART_KEY_COLUMNS

-- Display the subpartition column information of all database partition tables: select * from DBA_SUBPART_KEY_COLUMNS

-- Display the subpartition column information of all partition tables accessible to the current user: select * from ALL_SUBPART_KEY_COLUMNS

-- Display the subpartition column information 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 a. partitioned = 'yes'

-- Truncate table table_name is used to delete data from a table;

-- To delete a partition table, the data in a partition is alter table table_name truncate partition p5;

"PS": When update changes the partition to which the data belongs, the row movement permission must be granted to the table.

Alter table test_partition_tab enable/disable row movement; update the partition field and change the partition. The internal change of data goes through the steps of inserting new partitions, updating new data, and deleting old source data from the source data, therefore, the rowid will change, and the data index will also be removed.