Shulou(Shulou.com)05/31 Report--

What is the function of oracle partition table? I believe many inexperienced people don't know what to do about it. Therefore, this paper summarizes the causes and solutions of the problem. Through this article, I hope you can solve this problem.

1. The concept of table space and partition table

Tablespace: a collection of one or more data files. All data objects are stored in a specified tablespace, but tables are mainly stored, so they are called tablespaces.

Partitioned tables: as the amount of data in the table increases, the speed of querying data slows down and the performance of the application degrades, so you should consider partitioning the table. After the table is partitioned, the table is still logically a complete table, but the data in the table is physically stored in multiple tablespaces (physical files), so that when querying data, the entire table is not scanned every time.

two。 The specific role of table partitions

Oracle's table partitioning features bring great benefits to a wide range of applications by improving manageability, performance, and availability. In general, partitioning can greatly improve the performance of some queries and maintenance operations. In addition, partitioning can greatly simplify common administrative tasks, and partitioning is a key tool for building gigabyte data systems or ultra-high availability systems.

The partitioning function can further subdivide tables, indexes, or index organization tables into segments, and the segments of these database objects are called partitions. Each partition has its own name, and you can choose its own storage characteristics. From the point of view of the database administrator, the object after a partition has multiple segments, which can be managed either collectively or individually, which gives the database administrator considerable flexibility in managing the partitioned objects. However, from the application's point of view, the partitioned table is exactly the same as the non-partitioned table, and no modification is required when accessing the partitioned table using the SQL DML command.

When to use partition tables, the official advice is:

a. The size of the table exceeds that of 2GB.

b. The table contains historical data, and the new data is added to the new partition.

3. Advantages and disadvantages of table partitioning

Advantages:

a. Improve query performance: the query of partition objects can only search the partitions you care about, and improve the retrieval speed.

b. Enhanced availability: if one partition of the table fails, the data of the table in other partitions is still available.

c. Easy to maintain: if a partition of the table fails, you need to repair the data, only the partition can be repaired.

d. Balanced Icano: you can map different partitions to disk to balance Icano and improve the performance of the entire system.

Disadvantages:

Partitioned tables are related, and there is no way for existing tables to be converted directly to partitioned tables. However, oracle provides the ability to redefine tables online.

4. Several types and Operation methods of Table Partition

4.1 range Partition (range) maxvalue

Range partitions map data to each partition based on scope, which is determined by the partition key you specified when you created the partition. This partitioning method is the most commonly used, and the partitioning key often uses a date. For example: you might break down sales data by month.

When using range partitioning, consider the following rules:

a. Each partition must have a VALUES LESS THEN clause that specifies an upper bound value that is not included in the partition. Any record whose value of the partition key is equal to or greater than this upper limit is added to the next higher partition.

b. All partitions, except the first, have an implicit lower limit, which is the upper limit of the previous partition for this partition.

c. If some records cannot predict the range for the time being, you can create a maxvalue partition, and all records that are not within the specified range will be stored in the same partition as maxvalue.

Example 1: suppose you have a test table with 200000 rows of data. We partition the table through id, each partition stores 100000 rows, and we save each partition to 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 multiple test tablespaces first

Sys@ORCL > create tablespace test_ts01 datafile'/ home/oracle/test_01.dbf' size 32m extent management local autoallocate

Tablespace created.

Sys@ORCL > create tablespace test_ts02 datafile'/ home/oracle/test_02.dbf' size 32m extent management local autoallocate

Tablespace created.

Sys@ORCL > create tablespace test_ts03 datafile'/ home/oracle/test_03.dbf' size 32m extent management local autoallocate

Tablespace created.

-create a test partition table

Create table test

(id number not null

First_name varchar2 (30) not null

Last_name varchar2 (30) not null

Phone varchar2 (30) not null

Email varchar2 (80)

Status char (1)

Constraint test_id primary key (id)

)

Partition by range (id)

(partition test_part1 values less than (100000) tablespace test_ts01

Partition test_part2 values less than (200000) tablespace test_ts02

Partition test_part3 values less than (maxvalue) tablespace test_ts03

)

Example 2: divided by time

Create table order_time

(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 ora_time_part01 values less than (to_date ('2016-06-01)) tablespace test_ts01

Partition ora_time_part02 values less than (to_date ('2016-07-01)) tablespace test_ts02

Partition ora_time_part03 values less than (to_date ('2016-08-01)) tablespace test_ts03

)

Example 3:maxvalue

Create table rangetable

(rt_id number (8) not null

Name varchar (10)

Grade int

Constraint ranget_id primary key (rt_id)

)

Partition by range (grade)

(partition part1 values less than (1000) tablespace test_ts01

Partition part2 values less than (2000) tablespace test_ts02

Partition part3 values less than (maxvalue) tablespace test_ts03

)

4.2 list partition (list) default

The List partition also needs to specify the value of the column, and its partition value must be specified explicitly. There can be only one partition column, and multiple columns can not be specified as partition dependent columns at the same time as range or hash partitions, but its corresponding values for a single partition can be multiple.

When partitioning, you must determine the possible values of the partition column, and once the inserted column value is not within the partition range, the insert / update will fail, so it is generally recommended that when using a list partition, create a default partition to store records that are not within the specified range, similar to the maxvalue partition in the range partition.

When partitioning according to a field, such as a city code, you can specify default to put all data from non-partitioning rules into this default partition. The characteristic of this partition is that there are only a few values for a column, based on which we can use list partitioning.

Create tablespace test_ts04 datafile'/ home/oracle/test_04.dbf' size 32m extent management local autoallocate

Create tablespace test_ts05 datafile'/ home/oracle/test_05.dbf' size 32m extent management local autoallocate

Create tablespace test_ts06 datafile'/ home/oracle/test_06.dbf' size 32m extent management local autoallocate

Alter database datafile'/ home/oracle/test_06.dbf' resize 100m

Example 1:

Create table problem_tickets

(problem_id number (7) not null

Description varchar2 (2000)

Customer_id number (7) not null

Date_entered date not null

Status varchar2 (20)

Constraint problem_tic_id primary key (problem_id)

)

Partition by list (status)

(partition prob_active values ('active') tablespace test_ts04)

Partition prob_inactive values ('inactive') tablespace test_ts05

Partition prob_other values (default) tablespace test_ts06

)

Example 2:

Create table ListTable

(id int

Name varchar2 (20)

Area varchar2 (10)

Constraint ListTable_id primary key (id)

)

Partition by list (area)

(partition part1 values ('SH','BJ') tablespace test_ts04)

Partition part2 values ('SC','CQ') tablespace test_ts05

Partition part3 values ('SD') tablespace test_ts06

)

4.3 Hash Partition (hash)

For tables that cannot be effectively scoped, you can use hash partitioning, which is still helpful to improve performance. Hash partition will evenly distribute the data in the table to several partitions you specify, and the partition where the column is located is automatically allocated according to the hash value of the partition column, so you cannot control or know which record will be placed in which partition. Hash partition can also support multiple dependent columns.

Example 1:

Create table hash_table

(col number (8)

Inf varchar2 (100)

)

Partition by hash (col)

(partition part01 tablespace test_ts04

Partition part02 tablespace test_ts05

Partition part03 tablespace test_ts06

)

Abbreviation:

Create tablespace test_ts07 datafile'/ home/oracle/test_07.dbf' size 32m extent management local autoallocate

Create tablespace test_ts08 datafile'/ home/oracle/test_08.dbf' size 32m extent management local autoallocate

Create tablespace test_ts09 datafile'/ home/oracle/test_09.dbf' size 32m extent management local autoallocate

Create table emp

(empno number (4)

Ename varchar2 (30)

Sal number

)

Partition by hash (empno) partitions 4

Store in (test_ts06,test_ts07,test_ts08,test_ts09)

4.4 combined Partition

If a table is still large after partitioning according to a column, or for some other requirements, the partition can also be re-partitioned by creating subpartitions within the partition, that is, by combining partitions.

There are two main types of combined partitions in 10g: range-hash,range-list. Range-range,list-range,list-list,list-hash is added to 11g, and Interval partitions and virtual column partitions are also supported in 11g. Note the order that the root partition can only be a range partition, and the subpartition can be a hash partition or a list partition.

-introduction to the new features of oracle 11g:

Http://blog.csdn.net/tianlesoftware/article/details/5134819

-Interval partitions and virtual columns of partitioned tables partitioned by week

Http://blog.csdn.net/tianlesoftware/article/details/5662337

4.4.1 range-list compound partition (range-list)

This kind of partition is based on range partition and list partition. The table is partitioned first by a column and then by a column. The partitions in the partition are called sub-partitions.

Create table sales

(product_id varchar2 (5)

Sales_date date

Sales_cost number (10)

Status varchar2 (30)

)

Partition by range (sales_date) subpartition by list (status)

(partition p1 values less than (to_date ('2016-06-01)) tablespace test_ts07

(subpartition p1sub1 values ('active') tablespace test_ts07)

Subpartition p1sub2 values ('inactive') tablespace test_ts07

),

Partition p2 values less than (to_date ('2016-07-01)) tablespace test_ts08

(subpartition p2sub1 values ('active') tablespace test_ts08)

Subpartition p2sub2 values ('inactive') tablespace test_ts08

)

)

4.4.2 range-hash composite partition (range-hash)

This kind of partitioning is based on range partitioning and hash partitioning. The table is partitioned first by a column and then by a column.

Create tablespace test_ts11 datafile'/ home/oracle/test_11.dbf' size 32m extent management local autoallocate

Create tablespace test_ts12 datafile'/ home/oracle/test_12.dbf' size 32m extent management local autoallocate

Create tablespace test_ts13 datafile'/ home/oracle/test_13.dbf' size 32m extent management local autoallocate

Create table dinya_test

(transaction_id number

Item_id number (8) not null

Item_description varchar2 (300)

Transaction_date date

Constraint dinya_test_id primary key (transaction_id)

)

Partition by range (transaction_date) subpartition by hash (transaction_id) subpartitions 3

Store in (test_ts11,test_ts12,test_ts13)

(partition part_01 values less than (to_date ('2016-06-01)

Partition part_02 values less than (to_date ('2016-12-01)

Partition part_03 values less than (maxvalue)

)

5. Maintenance operation of partition table

5.1 add Partition (add)

-there are 2 situations when adding a new partition:

(1) the boundary in the original partition is maxvalue or default. In this case, we need to remove the boundary partition drop, add a new partition, and then add a new partition. Or split the boundary zone with split (the range type uses at,list and values).

(2) there is no boundary zone. In this case, just add the partition.

-the following code adds a partition to the test table

Alter table test add partition test_part4 values less than (400000)

Create tablespace test_ts14 datafile'/ home/oracle/test_14.dbf' size 32m

Alter table test add partition test_part5 values less than (500000) tablespace test_ts14

-Note: the partition boundary added above should be higher than the last partition boundary.

-the following code adds a p2sub3 subpartition to the p2 partition of the sales table

Create tablespace test_ts15 datafile'/ home/oracle/test_15.dbf' size 3G

Alter table sales modify partition p2 add subpartition p2sub3 values ('complete') tablespace test_ts15

-add a new zone with a bounded zone:

-1 > create partition tables and indexes

Create table custaddr

(id varchar2 (15 byte) not null

Areacode varchar2 (4 byte)

)

Partition by list (areacode)

(partition t_list555 values ('555') tablespace test_ts15

Partition p_other values (default) tablespace test_ts15)

Create index ix_custaddr_id on custaddr (id)

Local (partition t_list555 tablespace test_ts15

Partition p_other tablespace test_ts15)

-2 > insert test data

Insert into custaddr values ('1m. 555')

Insert into custaddr values ('2cm, 552')

Insert into custaddr values ('3cm, 554')

Commit

Select * from custaddr

ID AREACODE

1 555

2 552

3 554

Select * from custaddr partition (t_list555)

ID AREACODE

1 555

-3 > Delete default partition

Shall@ORCL > alter table custaddr drop partition p_other

Table altered.

Shall@ORCL > select * from custaddr

ID AREACODE

1 555

Shall@ORCL > select table_name,partition_name from user_tab_partitions where table_name='CUSTADDR'

TABLE_NAME PARTITION_NAME

CUSTADDR T_LIST555

-4 > add new partition, default partition

Shall@ORCL > alter table custaddr add partition t_list551 values ('551') tablespace test_ts15

Shall@ORCL > alter table custaddr add partition p_other values (default) tablespace test_ts15

Shall@ORCL > select table_name,partition_name from user_tab_partitions where table_name='CUSTADDR'

TABLE_NAME PARTITION_NAME

CUSTADDR P_OTHER

CUSTADDR T_LIST551

CUSTADDR T_LIST555

-5 > for local indexes, oracle automatically adds a local partitioned index

Shall@ORCL > select index_name,table_name,partitioning_type from user_part_indexes where index_name='IX_CUSTADDR_ID'

INDEX_NAME TABLE_NAME PARTITIONING_TYPE

-

IX_CUSTADDR_ID CUSTADDR LIST

Shall@ORCL > select index_name,partition_name from user_ind_partitions where index_name='IX_CUSTADDR_ID'

INDEX_NAME PARTITION_NAME

-

IX_CUSTADDR_ID P_OTHER

IX_CUSTADDR_ID T_LIST551

IX_CUSTADDR_ID T_LIST555

-use split partition split method, then connect to-2 > above for the following test

-3 > use split to add partitions

Alter table custaddr split partition p_other values ('552') into (partition t_list552 tablespace test_ts15, partition p_other tablespace test_ts15)

-Note that if the type is range, use at,list and use values

Select table_name,partition_name from user_tab_partitions where table_name='CUSTADDR'

TABLE_NAME PARTITION_NAME

CUSTADDR P_OTHER

CUSTADDR T_LIST552

CUSTADDR T_LIST555

Select index_name,partition_name from user_ind_partitions where index_name='IX_CUSTADDR_ID'

INDEX_NAME PARTITION_NAME

-

IX_CUSTADDR_ID P_OTHER

IX_CUSTADDR_ID T_LIST552

IX_CUSTADDR_ID T_LIST555

-Note: partitioned tables automatically maintain local partitioned indexes. The global index will be invalidated and rebuild is required

Shall@ORCL > Select index_name,status From user_indexes Where table_name='CUSTADDR'

INDEX_NAME STATUS

IX_CUSTADDR_ID N/A

-View data

Shall@ORCL > select * from custaddr

ID AREACODE

1 555

2 552

3 554

Shall@ORCL > select * from custaddr partition (t_list552)

ID AREACODE

2 552

Shall@ORCL > select * from custaddr partition (t_list555)

ID AREACODE

1 555

Shall@ORCL > select * from custaddr partition (p_other)

ID AREACODE

3 554

5.2 delete partition (drop)

-the following code deletes the p2 partition of sales table

Alter table sales drop partition p2

-alter table sales add partition p2 values less than (to_date ('2016-07-01)) tablespace test_ts08

(subpartition p2sub1 values ('active') tablespace test_ts08)

Subpartition p2sub2 values ('inactive') tablespace test_ts08

Subpartition p2sub3 values ('complete') tablespace test_ts15

)

-delete sales table p2sub3 subpartition

Alter table sales drop subpartition p2sub3

-Note: if the deleted partition is the only partition in the table, the partition cannot be deleted. To delete this partition, the table must be deleted.

-it also automatically maintains the local partitioned index and makes the global index unusable, which needs to be rebuilt

5.3 truncated partition (truncate)

Truncating a partition means that the data in one partition is deleted, and the partition is not deleted, nor the data in other partitions. You can truncate a partition even if there is only one partition in the table. Truncate the partition with the following code:

Alter table sales truncate partition p2

-of course, you can also truncate subpartitions.

Alter table sales truncate subpartition p2sub2

Truncate operates faster than delete, and bulk data loading of a large amount of data in the data warehouse may be useful; truncated partitions also automatically maintain the local partition index, while making the global index unusable, which needs to be rebuilt

5.4 merge Partition (merge)

The adjacent partition can be merge as a partition, the lower boundary of the new partition is the partition with lower boundary value, and the upper boundary is the partition with higher boundary value. The original local index will also be merged accordingly, and the global index will be invalid, which requires rebuild.

Alter database datafile'/ home/oracle/test_08.dbf' resize 500m

Alter table sales merge partitions p1,p2 into partition p2

5.5 split Partition (split)

Split partition splits a partition into two new partitions. after the split, the original partition no longer exists. Note that partitions of type HASH cannot be split.

Alter table sales split partition p2 at (to_date ('2016-06-01)) into (partition p3meme partition p4)

5.6 rename partition (rename)

Alter table sales rename partition p3 to p13

5.7 Mobile Partition (move)

Alter table test move partition test_part1 tablespace test_ts15

Alter table test move partition test_part1 tablespace test_ts01

Note: partition movement will automatically maintain the local partition index, not the global index, so we need to re-rebuild the partition index. Which indexes we need to rebuild can be judged by dba_part_indexes,dba_ind_partitions.

Shall@ORCL > Select index_name,status From user_indexes Where table_name='CUSTADDR'

INDEX_NAME STATUS

IX_CUSTADDR_ID N/A

6. Related query

6.1 query how many partitions are on the table

Select * from user_tab_partitions where table_name='SALES'

Select * from dba_tab_partitions where table_name='SALES'

6.2 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

6.3 query all partition table information

Select * from dba_part_tables

Select * from all_part_tables;-all partition table information that can be accessed by the current user

Select * from user_part_tables;-all partition table information of the current user

6.4 query subpartition information

Select * from dba_tab_subpartitions

Select * from all_tab_subpartitions

Select * from user_tab_subpartitions

6.5 query partition column information

Select * from dba_part_key_columns

Select * from all_part_key_columns

Select * from user_part_key_columns

6.6 query subpartition column information

Select * from dba_subpart_key_columns

Select * from all_subpart_key_columns

Select * from user_subpart_key_columns

6.7 query all partition tables

Select * from dba_tables where partitioned='YES'

Select * from all_tables where partitioned='YES'

Select * from user_tables where partitioned='YES'

7. The method of transferring ordinary table to partitioned table

Reference http://blog.csdn.net/tianlesoftware/article/details/6218704

There are four ways to convert a regular table to a partitioned table:

1. Export/import method

2. Insert with a subquery method

3. Partition exchange method

4. DBMS_REDEFINITION

7.1 Export/import method (Import and Export)

-create a normal table:

Sys@ORCL > create table shall (id int,name varchar2 (20))

Sys@ORCL > insert into shall values (100th)

Sys@ORCL > insert into shall values (101 jacks)

Sys@ORCL > insert into shall values (204)

Sys@ORCL > commit

-Export table

[oracle@zyx ~] $exp\'/ as sysdba\ 'tables=shall file=shall.dmp

Export: Release 11.2.0.4.0-Production on Tue Jun 28 12:32:03 2016

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0-64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

About to export specified tables via Conventional Path...

. . Exporting table SHALL 3 rows exported

Export terminated successfully without warnings.

[oracle@zyx ~] $

-delete the original regular table

Sys@ORCL > drop table shall

-re-create as a partition table

Create table shall (id int,name varchar2 (20))

Partition by range (id)

(partition shall_part1 values less than)

Partition shall_part2 values less than (200)

Partition shall_part3 values less than (maxvalue)

)

-Import data

[oracle@zyx ~] $imp\'/ as sysdba\ 'file=shall.dmp tables=shall fromuser=sys touser=sys ignore=y

Import: Release 11.2.0.4.0-Production on Tue Jun 28 12:38:02 2016

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0-64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Export file created by EXPORT:V11.02.00 via conventional path

. Importing SYS's objects into SYS

. . Importing table "SHALL" 3 rows imported

Import terminated successfully without warnings.

-Test

Sys@ORCL > select * from shall

ID NAME

100 zhong

101 Jack

204 shell

Sys@ORCL > select * from shall partition (shall_part1)

No rows selected

Sys@ORCL > select * from shall partition (shall_part2)

ID NAME

100 zhong

101 Jack

Sys@ORCL > select * from shall partition (shall_part3)

ID NAME

204 shell

Sys@ORCL > select * from shall partition (shall_part3) union all select * from shall partition (shall_part2)

ID NAME

204 shell

100 zhong

101 Jack

Sys@ORCL > select table_name,partition_name from user_tab_partitions where table_name='SHALL'

TABLE_NAME PARTITION_NAME

--

SHALL SHALL_PART1

SHALL SHALL_PART2

SHALL SHALL_PART3

7.2 Insert with a subquery method (insert query)

This method is implemented using insert. Of course, you can insert data together when you create a partition table, or you can enter it in insert after you create it. This method uses DDL statement, does not generate UNDO, only produces a small amount of REDO, after the completion of the table, the data has been distributed to each partition.

-create a normal table:

Sys@ORCL > create table test2 (id int,name varchar2 (20))

Sys@ORCL > insert into test2 values (100th)

Sys@ORCL > insert into test2 values (140)

Sys@ORCL > insert into test2 values (240)

Sys@ORCL > commit

-create a partition table

Create table part (id int,name varchar2 (20))

Partition by range (id)

(partition part1 values less than)

Partition part2 values less than (200)

Partition part3 values less than (maxvalue)

)

-insert data

Sys@ORCL > insert into part select * from test2

Sys@ORCL > commit

Sys@ORCL > select * from part

-delete the original regular table and rename the partitioned table to the original ordinary table

Sys@ORCL > drop table test2;-delete if you don't need it, rename to old if you are not sure

Sys@ORCL > alter table part rename to test2

-check the test

Sys@ORCL > select * from test2

Sys@ORCL > select * from test2 partition (part1)

No rows selected

Sys@ORCL > select * from test2 partition (part2)

ID NAME

100 zhong

140 Jack

Sys@ORCL > select * from test2 partition (part3)

ID NAME

240 shell

Sys@ORCL > select table_name,partition_name from user_tab_partitions where table_name='TEST2'

TABLE_NAME PARTITION_

--

TEST2 PART1

TEST2 PART2

TEST2 PART3

7.3 Partition exchange method (swap partition)

This method only modifies the definition of partitions and tables in the data dictionary, without data modification or replication, and is the most efficient. It is suitable for the operation that a table containing a large amount of data is transferred to a partition in a partitioned table. Try to operate in your spare time.

The steps for swapping partitions are as follows:

1. Create a partition table, assuming that there are 2 partitions, P 1 and P 2.

two。 Create Table A to store the data of the P1 rule.

3. Create Table B to store the data of the P2 rule.

4. Swap with tables An and P1. Put the data from Table An into the P1 partition

5. Swap with table B and p2. Store the data from Table B in the P2 partition.

-create a partition table

Create table p_emp (sal number (7 and 2))

Partition by range (sal)

(partition emp_p1 values less than (2000)

Partition emp_p2 values less than (4000)

)

-create a test table

Sys@ORCL > create table emp1 as select sal from scott.emp where salselect count (*) from emp1

COUNT (*)

-

eight

Sys@ORCL > create table emp2 as select sal from scott.emp where sal between 2000 and 3999

Sys@ORCL > select count (*) from emp2

COUNT (*)

-

five

-swap two basic tables with two partitions

-if the inserted data does not meet the partition rules, an ORA-14400 error will be reported.

Sys@ORCL > alter table p_emp exchange partition emp_p1 with table emp1

Sys@ORCL > select count (*) from emp1

COUNT (*)

-

0

Sys@ORCL > select count (*) from p_emp

COUNT (*)

-

eight

Sys@ORCL > alter table p_emp exchange partition emp_p2 with table emp2

Sys@ORCL > select count (*) from p_emp

COUNT (*)

-

thirteen

Sys@ORCL > select count (*) from emp2

COUNT (*)

-

0

-check the test

Sys@ORCL > select count (*) from p_emp partition (emp_p1)

COUNT (*)

-

eight

Sys@ORCL > select table_name,partition_name from user_tab_partitions where table_name='P_EMP'

TABLE_NAME PARTITION_NAME

--

P_EMP EMP_P1

P_EMP EMP_P2

7.4 DBMS_REDEFINITION (online redefinition)

Online redefinition ensures data consistency, and tables can operate DML normally most of the time. Lock the table only at the moment of switching, with high availability. This method has strong flexibility and can meet a variety of different needs. Moreover, the corresponding authorization and various constraints can be established before the handover, so that no additional management operations are needed after the handover is completed.

Reference http://blog.csdn.net/tianlesoftware/article/details/6218693

This feature is available only in versions after 9.2.0.4, and the online redefinition table has the following features:

(1) modify the storage parameters of the table

(2) transfer the table to another tablespace

(3) add parallel query options.

(4) add or delete partitions

(5) rebuild the table to reduce fragmentation

(6) change the heap table into an index to organize the table or vice versa.

(7) add or delete a column.

Use some of the constraints that are redefined online:

(1) There must be enough space to hold two copies of the table.

(2) Primary key columns cannot be modified.

(3) Tables must have primary keys.

(4) Redefinition must be done within the same schema.

(5) New columns added cannot be made NOT NULL until after the redefinition operation.

(6) Tables cannot contain LONGs, BFILEs or User Defined Types.

(7) Clustered tables cannot be redefined.

(8) Tables in the SYS or SYSTEM schema cannot be redefined.

(9) Tables with materialized view logs or materialized views defined on them cannot be redefined.

(10) Horizontal sub setting of data cannot be performed during the redefinition.

Under Oracle 10.2.0.4 and 11.1.0.7 versions, online redefinition may encounter the following bug:

Bug 7007594-ORA-600 [12261]

Http://blog.csdn.net/tianlesoftware/archive/2011/03/02/6218681.aspx

The general operation flow of online redefinition is as follows:

(1) create the underlying table A, if it exists, no action is required.

(2) create a temporary partition table B.

(3) start redefining and import the data from base table An into temporary partition table B.

(4) end the redefinition. At this time, the two tables have been exchanged in the Name Directory of DB. That is, at this point, base table A becomes a partition table, and the temporary partition table B we created becomes a regular table. At this point we can delete the temporary table B we created. It's already a regular watch.

-create a test table

Sys@ORCL > create user shall identified by shall

Sys@ORCL > grant connect,resource to shall

Sys@ORCL > grant select on dba_objects to shall

Shall@ORCL > create table zhong (id number (10) primary key,z_date date)

Shall@ORCL > insert into zhong select rownum,created from dba_objects

86435 rows created.

Shall@ORCL > create index ind_zhong_z_date on zhong (z_date)

-collect statistics

Sys@ORCL > exec dbms_stats.gather_table_stats ('shall','zhong',cascade = > true)

-create a temporary partition table

Create table par_table (id number primary key,z_time date)

Partition by range (z_time)

(partition part1 values less than (to_date ('2013-7-1)

Partition part2 values less than (to_date ('2014-7-1)

Partition part3 values less than (maxvalue)

)

-redefine operation

-check the reasonableness of redefinition

Sys@ORCL > exec dbms_redefinition.can_redef_table ('shall','zhong')

PL/SQL procedure successfully completed.

-if there is no problem, start redefining, the process may take a while

-Note here: if the column name of the partition table is the same as that of the original table, it can be done as follows:

Begin

Dbms_redefinition.start_redef_table

(uname = > 'SHALL'

Orig_table = > 'zhong'

Int_table = > 'par_table')

End

/

-if the column name of the partition table is not the same as the original, you need to re-specify the mapping relationship when you start the redefinition:

Exec dbms_redefinition.start_redef_table ('SHALL',' zhong', 'par_table',' id id,z_date zonal timekeeping, dbms_redefinition.cons_use_pk)

-after this step, the data has been synchronized to the temporary partition table

Shall@ORCL > select count (*) from par_table partition (part2)

COUNT (*)

-

86198

-synchronize the new table, which is optional

Begin

Dbms_redefinition.sync_interim_table

(uname = > 'SHALL'

Orig_table = > 'zhong'

Int_table = > 'par_table')

End

/

-create an index (after the data is redefined online, the index needs to be created separately)

Shall@ORCL > create index ind_par_date on par_table (z_time)

-collect new table statistics

Sys@ORCL > exec dbms_stats.gather_table_stats ('shall','par_table',cascade = > true)

-end redefinition

Begin

Dbms_redefinition.finish_redef_table

(uname = > 'SHALL'

Orig_table = > 'zhong'

Int_table = > 'par_table')

End

/

-the meaning of ending the redefinition: the base table zhong and the temporary partition table par_table are exchanged. At this point, the temporary partition table par_table becomes a regular table, and our base table, zhong, becomes a partition table. When we redefine, the base table zhong can perform DML operations. There is only a brief lock on the table when the two tables are switched.

-verify:

Shall@ORCL > select count (*) from par_table partition (part2)

Select count (*) from par_table partition (part2)

*

ERROR at line 1:

ORA-14501: object is not partitioned

Shall@ORCL > select count (*) from par_table

COUNT (*)

-

86435

Shall@ORCL > drop table par_table

Shall@ORCL > alter index ind_par_date rename to ind_zhong_z_date

Shall@ORCL > select table_name,partition_name from user_tab_partitions where table_name='ZHONG'

TABLE_NAME PARTITION_NAME

ZHONG PART1

ZHONG PART2

ZHONG PART3

Shall@ORCL > select count (*) from zhong

COUNT (*)

-

86435

Shall@ORCL > select count (*) from zhong partition (part1)

COUNT (*)

-

0

Shall@ORCL > select count (*) from zhong partition (part2)

COUNT (*)

-

86198

Shall@ORCL > select count (*) from zhong partition (part3)

COUNT (*)

-

two hundred and thirty seven

8. Partition table index

Partitioned indexes are divided into local (local index) indexes and global indexes (global index). Local indexes are easier to manage than global indexes, while global indexes are faster.

Tables related to the index:

Dba_part_indexes: summary statistics of partitioned indexes. You can know which partitioned indexes are on each table and the type of partitioned index (local/global).

Dba_ind_partitions: partition-level statistics for each partition index

Dba_indexes/dba_part_indexes: you can find out which non-partitioned indexes are on each table

The Local index must be a partitioned index. The Global index can choose whether or not to partition. If partitioned, it can only be a partitioned index with a prefix.

Partitioned indexes are divided into two categories: partitioned indexes with prefix (prefix) and partitioned indexes without prefix (nonprefix):

(1) the prefixed partition index contains only the partition key and uses it as the index of the boot column.

Such as:

Create index i_id_global on PDBA (id) global-Boot column

Partition by range (id)-Partition key

(partition p1 values less than)

Partition p2 values less than (maxvalue)

)

The ID here is the partitioning key, and the partitioning key id is also the boot column of the index.

(2) the column of an unprefixed partition index does not begin with a partition key or does not contain a partition key column.

Such as:

Create index ix_custaddr_local_id_p on custaddr (id)

Local (partition t_list556 tablespace test_ts15

Partition p_other tablespace test_ts15

)

This partition is based on areacode. But the leading column of the index is ID. So it is a non-prefix partitioned index.

Global partition indexes do not support non-prefixed partition indexes. If created, the error is as follows:

Create index i_time_global on PDBA (id) global-Index Boot column

Partition by range (time)-Partition key

(partition p1 values less than (TO_DATE ('2010-12-1,' YYYY-MM-DD')

Partition p2 values less than (maxvalue)

)

Partition by range (time)

*

An error occurred on line 2:

ORA-14038: GLOBAL partitioned indexes must be prefixed

8.1 Local local index

For local indexes, when the partition of the table changes, the index is maintained automatically by Oracle.

Note:

1 > the local index must be a partitioned index, and the partitioning key is equivalent to the partitioning key of the table.

2 > both prefix and non-prefix indexes can support index partition elimination, as long as the condition of the query contains the index partition key.

3 > the local index only supports the uniqueness within the partition, but cannot support the uniqueness on the table, so if you want to use the local index to restrict the uniqueness of the table, the partition key columns must be included in the constraint.

4 > the local partition index is for a single partition, and each partition index only points to one table partition; otherwise, a partition index can point to n table partitions, and at the same time, a table partition may also point to n index partitions. Doing truncate or move,shrink on a partition in the partition table may affect n global index partitions. Because of this, the local partition index has higher availability.

5 > the bitmap index must be a local partition index.

6 > Local index is mostly used in data warehouse environment.

7 > B-tree indexes and bitmap indexes can be partitioned, but HASH indexes cannot be partitioned.

Example:

Shall@ORCL > drop index IX_CUSTADDR_ID

Shall@ORCL > create index ix_custaddr_local_id on custaddr (id) local

The effect is the same as the following SQL, because the local index is a partitioned index:

Shall@ORCL > Select index_name,status From user_indexes Where table_name='CUSTADDR'

Shall@ORCL > drop index IX_CUSTADDR_LOCAL_ID

Create index ix_custaddr_local_id_p on custaddr (id)

Local (

Partition t_list555 tablespace test_ts15

Partition p_other tablespace test_ts15

)

Create index ix_custaddr_local_id_p on custaddr (id)

*

ERROR at line 1:

ORA-14024: number of partitions of LOCAL index must equal that of the underlying table

Shall@ORCL > select partition_name from user_tab_partitions where table_name=upper ('custaddr')

PARTITION_NAME

P_OTHER

T_LIST552

T_LIST555

Create index ix_custaddr_local_id_p on custaddr (id)

Local (

Partition t_list552 tablespace test_ts15

Partition t_list555 tablespace test_ts15

Partition p_other tablespace test_ts15

)

Create index ix_custaddr_local_areacode on custaddr (areacode) local

Verify the type of 2 indexes:

Select index_name,table_name,partitioning_type,locality,ALIGNMENT from user_part_indexes where table_name='CUSTADDR'

Index_name table_name partition locali alignment

Ix_custaddr_local_areacode custaddr list local prefixed

Ix_custaddr_local_id custaddr list local non_prefixed

Because our custaddr table is partitioned by areacode, the index ix_custaddr_local_areacode is a prefixed index (prefixed). Ix_custaddr_local_id is a non-prefix index.

4.2 Global Index

For global indexes, you can choose whether or not to partition, and the partition of the index may not correspond to the table partition. Global partitioned indexes can only be B-tree indexes. So far (10gR2), oracle only supports prefixed global indexes.

In addition, oracle will not automatically maintain the global partition index, when we modify the partition of the table, if we do not add update global indexes to the partition maintenance operation, it will usually lead to the INVALDED of the global index, which must be REBUILD after the operation.

Note:

1 > the global index can be partitioned or unpartitioned, and the global index must be a prefix index, that is, the index column of the global index must have the index partitioning key as its first few columns.

2 > Global indexes can be attached to either partitioned or non-partitioned tables.

3 > Index entries of a global partitioned index may point to several partitions, so for a global partitioned index, even truncating data in one partition requires rebulid several partitions or even the entire index.

4 > Global index is mostly used in oltp system.

5 > Global partition indexes are only partitioned by range or hash, and hash partitions are only supported after 10g.

6 > oracle9i when you move or truncate a partition table later, you can use the update global indexes statement to synchronously update the global partition index, consuming certain resources in exchange for a high degree of availability.

7 > the table uses column an as the partition, and references b as the local partition index. if b is used in the where condition, then oracle will scan the partitions of all tables and indexes, and the cost will be higher than the partition. At this time, you can consider using b as the global partition index.

Note: Oracle only supports global partitioned indexes of type 2:

Range partitioned and Hash Partitioned.

The official website says as follows:

Global Partitioned Indexes

Oracle offers two types of global partitioned index: range partitioned and hash partitioned.

(1) Global Range Partitioned Indexes

Global range partitioned indexes are flexible in that the degree of partitioning and the partitioning key are independent from the table's partitioning method. They are commonly used for OLTP environments and offer efficient access to any individual record.

The highest partition of a global index must have a partition bound, all of whose values are MAXVALUE. This ensures that all rows in the underlying table can be represented in the index. Global prefixed indexes can be unique or nonunique.

You cannot add a partition to a global index because the highest partition always has a partition bound of MAXVALUE. If you wish to add a new highest partition, use the ALTER INDEX SPLIT PARTITION statement. If a global index partition is empty, you can explicitly drop it by issuing the ALTER INDEX DROP PARTITION statement. If a global index partition contains data, dropping the partition causes the next highest partition to be marked unusable. You cannot drop the highest partition in a global index.

(2) Global Hash Partitioned Indexes

Global hash partitioned indexes improve performance by spreading out contention when the index is monotonically growing. In other words, most of the index insertions occur only on the right edge of an index.

(3) Maintenance of Global Partitioned Indexes

By default, the following operations on partitions on a heap-organized table mark all global indexes as unusable:

ADD (HASH)

COALESCE (HASH)

DROP

EXCHANGE

MERGE

MOVE

SPLIT

TRUNCATE

-example 1 Global index, which is supported for all partition types:

Select index_name,status From user_indexes Where table_name='CUSTADDR'

Drop index IX_CUSTADDR_LOCAL_ID_P

Create index ix_custaddr_global_id on custaddr (id) global

-example 2: global partition index, which only supports Range partition and Hash partition:

1 > create 2 test partition tables:

Create table pdba (id number, time date)

Partition by range (time)

(partition p1 values less than (to_date ('2016-1-1,' yyyy-mm-dd')

Partition p2 values less than (to_date ('2016-6-1,' yyyy-mm-dd'))

Partition p3 values less than (to_date ('2016-12-1,' yyyy-mm-dd'))

Partition p4 values less than (maxvalue)

)

Create table Thash

(id number primary key

Item_id number (8) not null

)

Partition by hash (id)

(partition part_01

Partition part_02

Partition part_03

)

2 > create a partitioned index

-example 2: global partition index

Create index i_id_global on PDBA (id) global

Partition by range (id)

(partition p1 values less than)

Partition p2 values less than (maxvalue)

)

-this is a prefixed partition index.

Create index i_time_global on PDBA (id) global

Partition by range (time)

(partition p1 values less than (TO_DATE ('2010-12-1,' YYYY-MM-DD')

Partition p2 values less than (maxvalue)

)

Partition by range (time)

*

An error occurred on line 2:

ORA-14038: GLOBAL partitioned indexes must be prefixed

Create index i_time_global on PDBA (time) global

Partition by range (time)

(partition p1 values less than (TO_DATE ('2016-12-1,' YYYY-MM-DD')

Partition p2 values less than (maxvalue)

)

-prefixed partition index

Select index_name,table_name,partitioning_type,locality,ALIGNMENT from user_part_indexes where table_name='PDBA'

Index_name table_name partition locali alignment

I_id_global pdba range global prefixed

I_time_global pdba range global prefixed

CREATE INDEX ix_hash ON PDBA (id,time) GLOBAL

PARTITION BY HASH (id)

(PARTITION p1

PARTITION p2

PARTITION p3

PARTITION p4)

-as long as the boot column of the index contains a partition key, it is a prefixed partition index.

Select index_name,table_name,partitioning_type,locality,ALIGNMENT from user_part_indexes where table_name='PDBA'

Index_name table_name partition locali alignment

I_id_global pdba range global prefixed

I_time_global pdba range global prefixed

Ix_hash pdba hash global prefixed

4.3 Index Reconstruction issu

1 > Partition index reconstruction

For partitioned indexes, you cannot rebuild as a whole, you can only rebuild a single partition. The syntax is as follows:

Alter index idx_name rebuild partition index_partition_name [online nologging]

Description:

Online: indicates that the table will not be locked when rebuilding.

Nologging: indicates that no log is generated when the index is built, which speeds up the speed.

Select index_name,status From user_indexes Where table_name='PDBA'

Select PARTITION_NAME from user_tab_partitions where table_name='PDBA'

Select partition_name from user_ind_partitions where index_name='I_ID_GLOBAL'

Alter index I_ID_GLOBAL rebuild partition p1

Alter index I_TIME_GLOBAL rebuild partition p2 online

If you want to rebuild the entire partitioned index, you can only drop the original table index before recreating:

Drop index I_ID_GLOBAL

Create index i_id_global on PDBA (id) local tablespace test_ts15

Select partition_name,tablespace_name from user_ind_partitions where index_name='I_ID_GLOBAL'

-online reconstruction operation requires large temporary tablespaces and sorting areas:

Select index_name,partition_name from user_ind_partitions where index_name='I_TIME_GLOBAL'

INDEX_NAME PARTITION_NAME

I_TIME_GLOBAL P1

I_TIME_GLOBAL P2

Alter index I_TIME_GLOBAL rebuild partition p1 online nologging

Alter index I_TIME_GLOBAL rebuild partition p2 online nologging

2 > Global index reconstruction

Oracle automatically maintains partitioned indexes. For global indexes, if update index is not specified when operating on partitioned tables, it will invalidate the global index and need to be rebuilt.

Select index_name,status From user_indexes Where table_name='PDBA'

Select index_name,table_name,status from user_indexes where INDEX_NAME='I_ID_GLOBAL'

Index_name table_name status

-

I_ID_GLOBAL pdba valid

-delete a partition:

Select PARTITION_NAME from user_tab_partitions where table_name='PDBA'

Select partition_name from user_ind_partitions where index_name='I_ID_GLOBAL'

Alter table pdba drop partition p2

Select index_name,table_name,status from user_indexes where INDEX_NAME='I_ID_GLOBAL'

Index_name table_name status

-

I_ID_GLOBAL pdba valid

-split partition:

Alter table pdba split partition P4 at (TO_DATE (partition P4, partition P5) (partition P4, partition P5)

Select PARTITION_NAME from user_tab_partitions where table_name='PDBA'

Select partition_name from user_ind_partitions where index_name='I_ID_GLOBAL'

Select index_name,table_name,status from user_indexes where INDEX_NAME='I_ID_GLOBAL'

Index_name table_name status

-

I_ID_GLOBAL pdba valid

-use update indexes when partitioning drop

Alter table pdba drop partition P4 UPDATE INDEXES

Select index_name,table_name,status from user_indexes where INDEX_NAME='I_ID_GLOBAL'

Index_name table_name status

-

I_ID_GLOBAL pdba valid

-several drop partitioning operations have been done, and it is a bit strange that the global index does not fail. But if you are in a production environment, be careful.

The command to rebuild the global index is as follows:

Alter index idx_name rebuild [online nologging]

Example:

Select index_name,status From user_indexes Where table_name='PDBA'

Select PARTITION_NAME from user_tab_partitions where table_name='PDBA'

Select partition_name from user_ind_partitions where index_name='I_ID_GLOBAL'

SQL > Alter index I_ID_GLOBAL rebuild online nologging

The index has changed.

Select table_name,partition_name,tablespace_name from user_tab_partitions where table_name='PDBA'

Through the user_tab_partitions table, you can view the corresponding tablesapce_name for each partition. However, if you go through the all_tables table, you cannot find the information about the corresponding table space of the partitioned table.

Partition table: tablespace_name can not be seen

SQL > select owner,table_name,tablespace_name,cluster_name from all_tables where table_name='PDBA'

Regular table: you can see the tablespace_name

SQL > select owner,table_name,tablespace_name,cluster_name from all_tables where table_name='test1'

3 > case 2 of partitioned index and global index reconstruction

Reference http://blog.csdn.net/weiwangsisoftstone/article/details/37615245

-create a partition table:

Create table test (

Id number

Name varchar2 (20)

)

Partition by range (id)

(partition p1 values less than (1000)

Partition p2 values less than (2000)

Partition p3 values less than (maxvalue)

)

-create a partitioned index

-LOCAL index structure

Create index ind_test_id_local on test (id) local

-re-create an index of GLOBAL on the name column

Create index ind_test_name_global on test (name) global

Select index_name,status From user_indexes Where table_name='TEST'

Select PARTITION_NAME from user_tab_partitions where table_name='TEST'

Select partition_name from user_ind_partitions where index_name='IND_TEST_ID_LOCAL'

Select partition_name from user_ind_partitions where index_name='IND_TEST_NAME_GLOBAL'

Insert into test values (999 and p1')

Insert into test values (1999 meme p2')

Insert into test values (2999 and p3')

SQL > select * from test

ID NAME

--

999 p1

1999 p2

2999 p3

-query which partition tables are under the current user:

SELECT table_name, partitioning_type,partition_count FROM USER_PART_TABLES

TABLE_NAME PARTITION PARTITION_COUNT

TEST RANGE 3

-query which partition indexes are available under the current user:

SELECT index_name,table_name FROM USER_PART_INDEXES

INDEX_NAME TABLE_NAME

ID_LOCAL TEST

-the partition corresponding to the index and the status of the index

Select index_name,partition_name,status from user_ind_partitions

INDEX_NAME PARTITION_NAME STATUS

-

ID_LOCAL P2 USABLE

ID_LOCAL P1 USABLE

ID_LOCAL P3 USABLE

-move the partition table to invalidate the index

Alter table test move partition p1 tablespace users

Alter table test move partition p2 tablespace users

-Local partition invalidation

Select index_name,partition_name,status from user_ind_partitions

INDEX_NAME PARTITION_NAME STATUS

IND_TEST_ID_LOCAL P3 USABLE

IND_TEST_ID_LOCAL P2 UNUSABLE

IND_TEST_ID_LOCAL P1 UNUSABLE

-rebuild the partition index

Alter index IND_TEST_ID_LOCAL rebuild partition p1

-the index status has changed.

Select index_name,partition_name,status from user_ind_partitions

INDEX_NAME PARTITION_NAME STATUS

IND_TEST_ID_LOCAL P1 USABLE

IND_TEST_ID_LOCAL P3 USABLE

IND_TEST_ID_LOCAL P2 UNUSABLE

Alter table test modify partition p2 rebuild unusable local indexes

SQL > select index_name,partition_name,status from user_ind_partitions

INDEX_NAME PARTITION_NAME STATUS

IND_TEST_ID_LOCAL P2 USABLE

IND_TEST_ID_LOCAL P1 USABLE

IND_TEST_ID_LOCAL P3 USABLE

-status of the global index:

Select index_name,table_name,status from user_indexes where index_name='IND_TEST_NAME_GLOBAL'

INDEX_NAME TABLE_NAME STATUS

-

IND_TEST_NAME_GLOBAL TEST UNUSABLE

-rebuild the global partition index

Alter index IND_TEST_NAME_GLOBAL rebuild

-the index status has changed.

Select index_name,table_name,status from user_indexes where index_name='IND_TEST_NAME_GLOBAL'

INDEX_NAME TABLE_NAME STATUS

-

IND_TEST_NAME_GLOBAL TEST VALID

-deleting a partition will also invalidate the global partition index

Alter table test truncate partition p1

Select index_name,partition_name,status from user_ind_partitions

INDEX_NAME PARTITION_NAME STATUS

IND_TEST_ID_LOCAL P2 USABLE

IND_TEST_ID_LOCAL P1 USABLE

IND_TEST_ID_LOCAL P3 USABLE

Select index_name,table_name,status from user_indexes where index_name='IND_TEST_NAME_GLOBAL'

INDEX_NAME TABLE_NAME STATUS

-

IND_TEST_NAME_GLOBAL TEST UNUSABLE

After reading the above, have you mastered the method of what is the function of oracle partition table? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel, thank you for reading!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.