If you encounter a particularly large table in ORACLE, you can use the partition table to change the performance of their applications.
If you encounter a particularly large table in ORACLE, you can use the partition table to change the performance of their applications.
A company’s annual sales have a tremendous record, DBA recommendations to the company quarterly data on a partition, the following is an example of the1999 data (assuming monthly data generated 30M), as follows:
Range partition table:
CREATE TABLE Sales
(Invoice_no Number
…
sale_date DATE NOT NULL)
PARTITION BY RANGE (sale_date)
(PARTITION sales1999_q1
VALUES LESS THAN (TO_DATE (‘1999-04-01’, ‘YYYY-MM-DD’)
TABLESPACE ts_sale1999q1,
PARTITION sales1999_q2 is
VALUES LESS THAN (TO_DATE (‘1999-07-01’, ‘YYYY-MM-DD’)
TABLESPACE ts_sale1999q2,
PARTITION sales1999_q3 is
VALUES LESS THAN (TO_DATE (‘1999-10-01’, ‘YYYY-MM-DD’)
TABLESPACE ts_sale1999q3,
PARTITION sales1999_q4 is
VALUES LESS THAN (TO_DATE (‘2000-01-01’, ‘YYYY-MM-DD’)
TABLESPACE ts_sale1999q4);
– Values ??less than (maxvalue)
List partition table:
create table emp (
empno number (4),
ename varchar2 (30),
location varchar2 (30))
partition by list (location)
(Partition p1 values ??(”),
partition p2 values ??(‘Shanghai’, ‘Tianjin’, ‘Chongqing’),
partition p3 values ??(‘Guangdong’, ‘Fujian’)
partition p0 values ??(default)
);
Hash partitioning:
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);
Combined partition:
Range hash composite partitions:
create table emp (
empno number (4),
ename varchar2 (30),
hiredate date)
partition by range (hiredate)
subpartition by hash (empno)
subpartitions
(Partition e1 values ??less than (to_date (‘20020501 ‘,’ YYYYMMDD ‘)),
partition e2 values ??less than (to_date (‘20021001 ‘,’ YYYYMMDD ‘)),
partition e3 values ??less than (maxvalue));
Range list composite partitions:
The CREATE TABLE customers_part (
CUSTOMER_ID NUMBER (6),
cust_first_name VARCHAR2 (20),
cust_last_name VARCHAR2 (20),
nls_territory VARCHAR2 (30),
credit_limit NUMBER (9,2))
PARTITION BY RANGE (credit_limit)
SUBPARTITION BY LIST (nls_territory)
SUBPARTITION Template
(SUBPARTITION east VALUES (‘CHINA’, ‘JAPAN’, ‘INDIA’, ‘THAILAND’),
SUBPARTITION west VALUES (‘AMERICA’, ‘GERMANY’, ‘ITALY’, ‘SWITZERLAND’),
SUBPARTITION other VALUES (DEFAULT))
(PARTITION p1 VALUES LESS THAN (1000),
PARTITION p2 VALUES LESS THAN (2500),
PARTITION p3 VALUES LESS THAN (MAXVALUE));
create table t1 (id1 number, id2 number)
partition by range (id1) subpartition by list (id2)
(Partition p11 values ??less than (11)
(Subpartition subp1 values ??(1))
);
Index partition:
CREATE INDEX month_ix ON sales (sales_month)
GLOBAL PARTITION BY RANGE (sales_month)
(PARTITION pm1_ix VALUES LESS THAN (2)
PARTITION pm12_ix VALUES LESS THAN (MAXVALUE));
Add a new partition:
ALTER TABLE sales ADD PARTITION sales2000_q1
VALUES LESS THAN (TO_DATE (‘2000-04-01’, ‘YYYY-MM-DD’)
TABLESPACE ts_sale2000q1;
If you already have a maxvalue partition, can not increase the partition, split partition can be taken to increase partition!
To delete a partition:
ALTER TABLE sales DROP PARTION sales1999_q1;
Truncated partition:
alter table sales truncate partiton sales1999_q2;
Merge partitions:
alter table sales merge partitons sales1999_q2, sales1999_q3 into sales1999_q23;
alter index ind_t2 rebuild partition p123 parallel 2;
Split partition:
ALTER TABLE sales
SPLIT PARTITON sales1999_q4
AT TO_DATE (‘1999-11-01’, ‘YYYY-MM-DD’)
INTO (partition sales1999_q4_p1, partition sales1999_q4_p2);
alter table t2 split partition p123 values ??(1,2) into (partition p12, partition p3);
Swap partition:
alter table x exchange partition p0 with table bsvcbusrundatald;
Access to the specified partition:
select * from sales partition (sales1999_q2)
EXPORT specify the partition:
exp sales / sales_password tables = sales: sales1999_q1
file = sales1999_q1.dmp
IMPORT specify the partition:
imp sales / sales_password FILE = sales1999_q1.dmp
TABLES = (sales: sales1999_q1) IGNORE = y
View the partitions Information:
user_tab_partitions, user_segments
Note: If the partition table across different table space, do the export, import target database must be pre-built table space. Score sheet where the district table space to do the import target database must be pre-built table space! These table spaces are not necessarily the user’s default table space, as long as there can be. If you have a non-existent, will give an error!
Default partition table table maintenance operation causes the global index is not available, marked as in the UNUSABLE. Then you must rebuild the entire global indexes, or all partitions. If partition, Oracle allow for maintenance operations ALTER TABLE statement to specify UPDATE GLOBAL INDEXES to override this default behavior, you specify this clause will tell Oracle when it performs maintenance operations DDL statements update the global index, which provides the following benefits:
This eliminates the need for separate later to rebuild the global index in the operation of the underlying table to update the global index;
(2) because the index is not marked as in the UNUSABLE global index higher availability, and even being executed partition DDL statements are still available to access other partitions in the table, to avoid the query name of the failure of the global index in order to rebuild them ;
In addition, the following performance factors should also be considered before you specify the UPDATE GLOBAL INDEXES:
Because you want to update the advance is marked UNUSABLE index partition DDL statements to be executed more, of course, to execute DDL and the first does not update the index and then rebuild the index the time it takes to make a comparison, one for the The rule is if the partition size is less than 5% of the size of the table, the index is updated a little faster;
Not so fast 2.DROP truncate and Exchange operations, the same must first perform DDL and then rebuild all the time spent by the global index to be compared;
3 To register the index is updated and generates redo records and undo records to rebuild the entire index optional inclusion of the NOLOGGING;
Rebuild the entire index to produce a more effective index, because it is more conducive to the use of space, Furthermore rebuild the index allows you to modify storage options.
Note that the the partition index structure table does not support UPDATE GLOBAL INDEXES clause.
Data already exists in ordinary table into a partition table, there is no way to modify the properties directly into the partition table, must shift by reconstruction, generally there are three ways, depending on scene:
Use cases:
Method: rebuild the partition table of the original table.
CREATE TABLE T (ID NUMBER PRIMARY KEY, TIME DATE);
INSERT INTO T
SELECT ROWNUM, SYSDATE – ROWNUM FROM DBA_OBJECTS WHERE ROWNUM <= 5000;
COMMIT;
CREATE TABLE T_NEW (ID, TIME) PARTITION BY RANGE (TIME)
(PARTITION P1 VALUES LESS THAN (TO_DATE (‘2000-1-1 ‘,’ YYYY-MM-DD ‘)),
PARTITION P2 VALUES LESS THAN (TO_DATE (‘2002-1-1 ‘,’ YYYY-MM-DD ‘)),
PARTITION P3 VALUES LESS THAN (TO_DATE (‘2005-1-1 ‘,’ YYYY-MM-DD ‘)),
PARTITION P4 VALUES LESS THAN (MAXVALUE))
AS SELECT ID, time FROM T;
RENAME T TO T_OLD;
The RENAME T_NEW TO T;
SELECT COUNT (*) FROM T;
COUNT (*)
———-
5000
SELECT COUNT (*) FROM T partition (P1);
COUNT (*)
———-
2946
SELECT COUNT (*) FROM T partition (P2);
COUNT (*)
———-
731
SELECT COUNT (*) FROM T partition (P3);
COUNT (*)
———-
1096
Advantages: simple to use DDL statements, does not produce the undo, and only a small amount of redo, the efficiency is relatively high, and the completion of the construction of the table after the data has been distributed to each partition.
Inadequate: the consistency of the data also require additional consideration. Since almost no way to manually lock the T table to ensure consistency in theCREATE TABLE statement and RENAME T_NEW TO T statement directly modifications may be lost if you want to ensure consistency, you need to check the data after executing the statement, and this price is relatively large. In addition, access to the executed between the execution of two RENAME statement will fail.
Apply to the modified the infrequent table at leisure operation, the amount of data of the table should not be too much.
Method 2: using the swap partition.
DROP TABLE t;
CREATE TABLE T (ID NUMBER PRIMARY KEY, TIME DATE);
INSERT INTO T
SELECT ROWNUM, SYSDATE – ROWNUM FROM DBA_OBJECTS WHERE ROWNUM <= 5000;
COMMIT;
CREATE TABLE T_NEW (ID NUMBER PRIMARY KEY, TIME DATE) PARTITION BY RANGE (TIME)
(PARTITION P1 VALUES LESS THAN (TO_DATE (‘2005-9-1 ‘,’ YYYY-MM-DD ‘)),
PARTITION P2 VALUES LESS THAN (MAXVALUE));
ALTER TABLE T_NEW EXCHANGE PARTITION P1 WITH TABLE T;
RENAME T TO T_OLD;
The RENAME T_NEW TO T;
Advantages: only partitions in the data dictionary and the definition of the table has been modified, modify, or copy data, the highest efficiency. If the distribution of the data in the partition no further requirements to achieve relatively simple. RENAME operation is executed, it is possible to check whether data existsT_OLD, if present, these data directly inserted into the T the T insertion operation can be guaranteed not lost.
Inadequate: consistency problem persists, swap partition before RENAME T_NEW TO T, query, update, and delete errors or not access data. If the data distribution to multiple partitions, you need to partition SPLIT operation, will increase the complexity of the operation and efficiency will be reduced.
Applicable to contain large amounts of data table to go to the operation of a partition in the partition table. Should be at leisure.
Method three: Oracle9i or later, redefined online
DROP TABLE t;
CREATE TABLE T (ID NUMBER PRIMARY KEY, TIME DATE);
INSERT INTO T
SELECT ROWNUM, SYSDATE – ROWNUM FROM DBA_OBJECTS WHERE ROWNUM <= 5000;
COMMIT;
To EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE (USER, ‘T’);
PL / SQL procedure successfully completed.
CREATE TABLE T_NEW (ID NUMBER PRIMARY KEY, TIME DATE) PARTITION BY RANGE (TIME)
(PARTITION P1 VALUES LESS THAN (TO_DATE (‘2004-7-1 ‘,’ YYYY-MM-DD ‘)),
PARTITION P2 VALUES LESS THAN (TO_DATE (‘2005-1-1 ‘,’ YYYY-MM-DD ‘)),
PARTITION P3 VALUES LESS THAN (TO_DATE (‘2005-7-1 ‘,’ YYYY-MM-DD ‘)),
PARTITION P4 VALUES LESS THAN (MAXVALUE));
The table has been created.
EXEC DBMS_REDEFINITION.START_REDEF_TABLE (USER, ‘T’, ‘T_NEW’);
PL / SQL procedure successfully completed.
EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE (USER, ‘T’, ‘T_NEW’);
PL / SQL procedure successfully completed.
SELECT COUNT (*) FROM T;
COUNT (*)
———-
5000
SELECT COUNT (*) FROM T partition (P3);
COUNT (*)
———-
1096
Advantages: to ensure data consistency, most of the time, the table T can be normal DML operations. Only switch instantly lock table, with high availability. This method has great flexibility and can meet various needs. Moreover, before switching to the corresponding authorization to create a variety of constraints, do not need any additional management operation after the completion of the handover.
Enough: to achieve a little more complicated than the above two.
Applicable in all situations.
Given here only redefined online form one of the most simple example, detailed descriptions and examples can refer to the following two articles.
Oracle’s online redefinition table function:
Oracle’s online redefinition table function (b):
XSB:
An existing data table into a partitioned table:
The first (the table is not too large):
1 to rename the original table:
rename xsb1 to xsb2;
Create the partition table:
CREATE TABLE xsb1
PARTITION BY LIST (c_test)
(The PARTITION xsb1_p1 Values ??(1),
The PARTITION xsb1_p2 VALUES (2),
The PARTITION xsb1_p0 VALUES (default))
nologging AS SELECT * FROM xsb2;
The original triggers on a table, primary keys, indexes, and so applied to the partition table;
Delete the original table:
The drop table xSb2;
The second (table):
1. To create a partition table:
CREATE TABLE x PARTITION BY LIST (c_test) [range ()]
(PARTITION p0 VALUES [less than] (1) tablespace tbs1,
PARTITION p2 VALUES (2) tablespace tbs1,
The PARTITION xsb1_p0 VALUES (the [maxvalue] Default))
AS SELECT * FROM xsb2 [where 1 = 2];
2. Exchange the partition alter table x exchange partition p0 with table bsvcbusrundatald;
3. Rename the original table alter table bsvcbusrundatald rename to x0;
4. Rename the new table alter table x rename to bsvcbusrundatald;
5. Delete the original table drop table x0;
6. Create a new table triggers and indexes create the index ind_busrundata_lp on bsvcbusrundatald () local tablespace tbs_brd_ind to;
Or:
1. Planning the partition boundaries of the original data in the table, in principle, the original table a small amount of data copied to another table;
2. Suspension of the trigger in the original large table;
3. Delete recent data in the original table;
4. Changed its name to the original name of the table;
5. Create a partition table;
6. Swap partition;
7. Rebuild the indexes and triggers (first remove and then rebuild).
Reference script:
select count (*) from t1 where recdate> sysdate-2
create table x2 nologging as select * from t1 where recdate> trunc (sysdate-2)
alter triger trg_t1 disable
delete t1 where recdate> sysdate-2
commit
rename t1 to x1
create table t1 [nologging] partition by range (recdate)
(Partition pbefore values ??less than (trunc (sysdate-2)),
partition pmax values ??less than (maxvalue))
as select * from x1 where 1 = 2
alter table t1 exchange partition pbefore with table x1
alter table t1 exchange partition pmax with table x2
drop table x2
[Rebuilding trigger]
drop table x1
If the expected amount of data in the table, usually need to consider the use of the partition table to determine the partition table, but also determine what type of partition (range partition hash partition, the list partition, etc.), the size of the partition interval. Partition to create the best some kind of understanding with the program, even had to create a partition table, partition defined by natural month, but the program is in the query, the default start time and end time: the current date -30 to the current date, such as the day 9.18, that query is generated 8.18-9.18 partition does not significantly improve performance, and later on the date of the program queries made ??an adjustment by natural month for system load.
The support table partition from Oracle8.0 start (the MSSQL2005 began to support table partitioning).
Oracle9 i partition can improve application manageability, performance, and availability. Partition tables, indexes and index-organized tables can be further divided into finer management and access to these database objects. Oracle provides a wide variety of partitioning scheme to meet all business needs. In addition, because the SQL statement is completely transparent, the partition can be used for almost all applications.
The partition table allows the data into is called the partition or sub-partition smaller more manageable blocks. The index can also be such a partition. Each partition can be managed separately, can not rely on other partitions alone play a role, thus providing a structure that is more conducive to the availability and performance.
The partition can improve the manageability, performance and availability, and bring great benefits to a wide variety of applications. Typically, the partition can make some queries, and greatly improve the performance of maintenance operations. In addition, the partition can greatly simplify the day-to-day management tasks.The partition also enables database designers and administrators to be able to solve the most difficult problem of cutting-edge applications bring. Partition is built on hundreds of millions of bytes of data system or need a key tool for high-availability system.
In a multi-CPU configuration environment, if you intend to use parallel execution, partitions provide another parallel. , You can perform operations on partitioned tables and indexes in parallel by different parallel execution servers to different partitions of the table or index allocation.
Table or index and child partitions share the same logical attributes. Such as table partitions or subpartitions share the same column and constraint definitions, an index partition or subpartition share the same index options. However, they may have different physical properties such as tablespaces.
Although you do not need each partition or subpartition of a table or index on a different table space, but doing better. Partition storage to a different table space can
l Reduce the possibility of conflicting data in multiple partitions
l Each partition can separate backup and recovery
l Control the mapping between the partition and the disk drive is important to balance I / O load
l To improve the manageability, availability and performance
Partition operation is transparent to existing applications and standard DML statements running in the partition table. Extended table or index partition name byDML application programming, making use of the advantages of the partition.
You can use SQL * Loader, Import and Export tool to load or unload the data in the partition table. These tools are support and child partitions.
This partitioning method
Oracle9i provides the following five kinds of partitioning methods:
l Range partitioning Range
l Hash partitioning Hash
l List Partitioning List
l Bands – hash partitioning Range-Hash
l The combinations range of – list partition Range-List
Index and table partitioning. Global index can only be range-partitioned, but it can be defined in any type of partition or non-partitioned tables. Global index usually require more maintenance than the local index.
Generally the formation of a local index, in order to reflect the structure of the underlying table. Underlying table is equivalent to the partition that basis
The same table in the same column, partition, create the same number of partitions or subpartitions, set the basis of appearances corresponding partition boundaries. Local index maintenance activities affect the partition will be automatically maintains the index partitions. This ensures that the the equivalent partition between the index and the underlying table.
About range partitioning Range:
Line in order to be mapped to the partition based on the range of column values, the use of range partitioning method. Such as the month of the year when the data can be divided into logical partitions of this type there. The best performance when the data in the entire range can be equally divided. If the partition by range due to the unequal division lead to the partition size is obviously not at the same time, we need to consider other partitioning methods.
About a hash partitions Hash:
If the data is not so easy to range partitioning, but want to partition for performance and management reasons, the use of hash partitioning method. Hash partitioning provides a specified number of partitions equally divided data. Based on the hash value of the partitioning key row is mapped to the partition. Create and use hash partitioning will provide you with a very flexible placement of data, because you can sow (removable) equal quantitative partition between the I / O driver to influence the availability and performance .
On the list of partitions List:
When you need to explicitly control how the rows are mapped to the partition, use the list partitioning method. Description of each partition for the partition column specifies a discrete value, which is different from range partitioning, where a range with a partition, which is also different from hash partitioning, where the user can not control how the line mapped to partition. The list partitioning method is specially designed modular data into compliance with discrete values ??of. Range partitioning or hash partitioning is not so easy to do this. Further list of partitions can be very natural disorder and related data sets are grouped and organized together.
Range partitioning and hash partitioning, list partitioning does not support multi-column partition. If you want to table column-wise, the partitioning key can only be a separate table columns, however, can use range partitioning or hash partitioning all columns of the partition, you can use the list partitioning method partitions.
Bands – hash partitioning:
Combination of range and hash, the first range-partitioned table, and then use the hashing technique for each range partition partition again. To all children of the given range partitioning partition together represents a logical subset of the data.
Bands – list partition:
Scope and list a combination of technologies, the first range-partitioned table, then use the list on each range partition partition again. Bands – hash partitioning, each sub-partition represents a logical subset of the data described by the appropriate range and list partitioning settings.
Create or change a partition table, you can specify row movement clause ENABLE ROW MOVEMENT or DISABLE ROW MOVEMENT, when the key is changed, the clause to enable or disable the line to migrate to a new partition. The default value is Disable Row Movement. This product (item) ENABLE ROW MOVEMENT clause.
Partitioning technology can improve the manageability of the database:
Partitioning technology, a specific part of maintenance operations can be focused on the table. For example, the database administrator can only do a backup on the part of the table without having to do a backup of the entire table. Of the entire database object maintenance operations, on a per-partition basis, so that maintenance work broken down into more manageable pieces.
Partitioning technology to improve the manageability of a typical use is to support the ‘rolling window’ in the data warehouse load process. Assume that a database administrator to load new data to the table every week. The table can be range-partitioned so that each partition contains one week of data. The loading process simply add a new partition. Than they are modified to add a new partition table is much more efficient, because the database administrator does not need to modify any other partitions. Data is removed from the partition table. As long as you use a very simple and quick data dictionary operation to delete a partition, without having to issue a lot of resources and the mobilization of all data to be deleted ‘DELETE’ command.
Partitioning technology can improve the performance of the database:
Due to the reduced number of check or operating data, while allowing parallel execution Oracle9 i partition function provides a performance advantage. These properties include:
l Partition pruning: Partition pruning is the simplest and most valuable means of partitioning technology to improve performance. Partition pruning can often improve query performance by several orders of magnitude. For example, assume that the application in the order table contains the order history, the table with the periphery of the partition. Query-week order just visit a partition of the orders table. If the order table contains two years of history, this query needs to access only one instead of one hundred and four partitions. The query execution speed because of partition pruning may be a hundred times faster. Partition pruning collaboration with all otherOracle performance characteristics. Oracle will partition pruning index of technology, links and parallel access method to be used in conjunction.
l Partition-wise join: partition function can be called partition intelligent connection technology to improve performance of multi-table joins. When the two tables to be joined together, and each table join keyword to partition, you can use partition-wise join. Partition-wise joins the decomposition of large join into smaller occur in the connection between each partition, and thus less time to complete a full connection. This can bring significant performance improvements to the serial and parallel execution.
l Update, and delete the parallel execution: the partition function can be infinite parallel UPDATE, DELETE, and MERGE statement. When access the partition or partition database objects Oracle parallel processing SELECT and INSERT statements. Do not use a bitmap index on the partition or non-partitioned database objects parallel processing UPDATE, DELETE, and MERGE statements. Parallel processing for object bitmap index, the target table must first partition. Parallel execution of these SQLstatements can greatly improve performance, especially to improve the performance UPDATE, DELETE, or MERGE operation involving large amounts of data.
Partitioning technology to improve availability:
The partition database objects with the independence of the partition. The characteristics of the partition independence may be an important part of high availability strategy, for example, if the partition of the partition table can not be used, but all other partitions of the table remain online and available. Then this application can continue to execute the query and transaction processing for the partition table, do not have access to that partition that is unavailable, database operations will still be able to run successfully. The database administrator can specify that each partition is stored in a different table space, which allows administrators to backup and recovery operations independent of the other table partition for each partition. Also, the partitioning can reduce scheduled downtime.Performance due to the partition function has been improved, so that the database administrator to complete the maintenance of large database objects in relatively small batch window.