Oracle Partitioned Tables perform performance

1.1 Partitioned Tables Partition table

In Oracle, if you encounter a particularly large table, you can use partitioned tables to change the performance of their applications.

1.1.1 Partition Table Establishment:

A company generates huge sales records each year, and the DBA suggests to the company that quarterly data be placed in a single partition, and the following is a demonstration of the company's 1999 data (assuming 30M of data per month), operating 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

VALUES less THAN (to_date (' 1999-07-01 ', ' yyyy-mm-dd ')

Tablespace TS_SALE1999Q2,

PARTITION sales1999_q3

VALUES less THAN (to_date (' 1999-10-01 ', ' yyyy-mm-dd ')

Tablespace ts_sale1999q3,

PARTITION Sales1999_q4

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 (' Beijing '),

Partition P2 values (' Shanghai ', ' Tianjin ', ' Chongqing '),

Partition P3 values (' Guangdong ', ' Fujian ')

Partition P0 values (default)

);

Hash partition:

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 partitions:

Range Hash Combination partition:

CREATE TABLE EMP (

Empno Number (4),

ename VARCHAR2 (30),

HireDate date)

Partition by range (HireDate)

Subpartition by Hash (empno)

Subpartitions 2

(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 Combination partition:

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

Maintenance of 1.1.2 Partition table:

Add 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 and cannot increase the partition, you can take the partitioning method to increase the partition!

To delete a partition:

ALTER TABLE sales DROP partion sales1999_q1;

Truncated partitions:

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 () into (partition p12,partition p3);

Swap partition:

ALTER TABLE x Exchange partition p0 with table Bsvcbusrundatald;

To access the specified partition:

SELECT * FROM sales partition (SALES1999_Q2)

Export specified partition:

Exp Sales/sales_password TABLES=SALES:SALES1999_Q1

File=sales1999_q1.dmp

import Specifies the partition:

Imp Sales/sales_password FILE =sales1999_q1.dmp

TABLES = (sales:sales1999_q1) ignore=y

To view partition information:

User_tab_partitions, User_segments

Note: If the partition table spans different table spaces, the target database must pre-build these table spaces when exporting and importing. The table space of each district in the table area when doing import, the target database must be pre-built these tablespace! These table spaces are not necessarily the user's default tablespace, as long as they exist. If one does not exist, it will be an error!

By default, many table maintenance operations on partitioned tables make the global index unusable and marked as unusable. Then you must rebuild the entire global index or all of its partitions. If it has been partitioned, Oracle allows the default attribute to be overloaded by specifying update global INDEXES in the ALTER TABLE statement used for maintenance operations, and specifying this clause will also tell Oracle to update the global index when it executes the DDL statement for the maintenance operation. This provides the following benefits: 1. Updating the global index while manipulating the underlying table this does not require the subsequent rebuilding of the global index separately; 2. Because it is not marked as unusable, the global index is more usable, and even if the partition's DDL statement is being executed, the index is still available to access other partitions in the table. Avoid querying the names of all defunct global indexes to rebuild them, and also consider the following performance factors before specifying update global INDEXES: 1. Because you want to update an index that was marked as unusable beforehand, the DDL statements for the partition are going to take longer, Of course this is compared to the time it takes to perform the DDL and then rebuild the index without updating the index, and one rule is that if the partition size is less than 5% of the size of the table, updating the index is a bit faster; 2.DROP TRUNCATE and exchange operations are not so fast, Again, this must be compared with the time it takes to execute the DDL and then rebuild all the global indexes; 3. To register updates to the index and generate redo and undo records, you can select nologging when rebuilding the entire index; 4. Rebuilding an entire index results in a more efficient index because it is more useful for using space, In addition, the storage options are allowed to be modified when the index is rebuilt. Note the partition index structure table does not support the update GLOBAL INDEXES clause.

1.1.3 Normal table into partitioned table

Converting a normal table with existing data into a partitioned table, without modifying the properties directly into a partitioned table, must be transformed by rebuilding, typically with three methods, depending on the scenario:

Case:

Method One: Use the original table to reconstruct the partition 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 a SELECT ID, time from T;

RENAME T to T_old;

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

Advantage: The method is simple and easy to use, because the use of DDL statements does not produce undo, and only a small amount of redo, the efficiency is relatively high, and the completion of the table after the data is distributed in the various partitions.

Insufficient: Additional considerations are required for consistency of data. Since there is almost no way to ensure consistency by manually locking the T-table, the direct modification of the CREATE TABLE statement and the rename t_new to T statement may be lost, and if consistency is to be ensured, the data will be checked after the statement is executed, and the cost is relatively large. In addition, access to t that executes between two rename statements fails.

It is suitable for the non-frequent table, in the idle operation, the table data volume should not be too large.

Method Two: The method of 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_da TE (' 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;

RENAME t_new to T;

Advantages: Only the definition of partitions and tables in the data dictionary has been modified, and no data has been modified or copied, the most efficient. If there is no further requirement for the distribution of data in the partition, the implementation is relatively straightforward. After performing the rename operation, you can check if there is data in the t_old, and if so, insert the data directly into T, and you can guarantee that the operation for T insertion will not be lost.

Insufficient: There is still a consistency issue, and before the swap partition rename t_new to T, queries, updates, and deletions can cause errors or access to data. If data is required to be distributed across multiple partitions, split operations that require partitioning can increase the complexity of operations and reduce efficiency.

Applies to the operation of a table that contains large amounts of data to go to a partition in a partitioned table. You should try to operate at leisure.

Method Three: Oracle9i above, using the online redefinition function

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;

EXEC dbms_redefinition. Can_redef_table (USER, ' T ');

The PL/SQL process has completed successfully.

CREATE TABLE t_new (ID number PRIMARY KEY, Time DATE) PARTITION by RANGE (time) (PARTITION P1 VALUES less THAN (to_da TE (' 2004-7-1 ', ' yyyy-mm-dd '), PARTITION P2 VALUES less THAN (to_date (' 2005-1-1 ', ' yyyy-mm-dd ')), PARTITION P3 V Alues less THAN (to_date (' 2005-7-1 ', ' yyyy-mm-dd '), PARTITION P4 VALUES less THAN (MAXVALUE));

The table is created.

EXEC dbms_redefinition. Start_redef_table (USER, ' T ', ' t_new ');

The PL/SQL process has completed successfully.

EXEC dbms_redefinition. Finish_redef_table (USER, ' T ', ' t_new ');

The PL/SQL process has completed successfully.

SELECT COUNT (*) from T;

COUNT (*)----------5000

SELECT COUNT (*) from T PARTITION (P3);

COUNT (*)----------1096

Pros: Data consistency is ensured, and for most of the time, the DML operation can be performed normally by the table T. Only in the momentary lock table of the switch, it has high availability. This approach is highly flexible and can be met for a variety of different needs. Furthermore, it is possible to authorize and establish various constraints before switching, so that no additional administrative action is required after the switchover is complete.

Insufficient: the implementation is slightly more complex than the above two.

Suitable for all situations.

Here is only one of the simplest examples of online redefinition tables, with detailed descriptions and examples that can be found in the following two articles.

Oracle's online redefine table feature: http://blog.itpub.net/post/468/12855

Oracle's online redefinition table function (ii): http://blog.itpub.net/post/468/12962

XSB:

To change a large table of existing data into a partitioned table:

The first type (the table is not too large):

1. Rename the original table: rename xsb1 to XSB2; 2. Creating a partition Table: Create TABLE Xsb1 PARTITION by LIST (c_test) (PARTITION xsb1_p1 values (1), PARTITION XSB1_P2 values (2), PA Rtition xsb1_p0 VALUES (default) nologging as SELECT * from XSB2; 3. Apply the trigger, primary key, index, etc. on the original table to the partition table; 4. Delete the original table: Drop table xsb2;

The second type (table is very Large):

1. Creating a partition Table: CREATE TABLE x PARTITION by LIST (c_test) [Range ()] (PARTITION p0 VALUES [Less than] (1) tablespace tbs1, parti tion P2 values (2) tablespace tbs1, PARTITION xsb1_p0 values ([Maxvalue]default)) as SELECT * from XSB2 [where 1=2];

2. Swap partition ALTER TABLE X Exchange partition p0 with table Bsvcbusrundatald;

3. The original table was renamed ALTER TABLE Bsvcbusrundatald Rename to x0;

4. Change the name of the new table ALTER TABLE x rename to Bsvcbusrundatald;

5. Delete the original table drop table x0;

6. Creating a new Table trigger and index CREATE INDEX IND_BUSRUNDATA_LP on Bsvcbusrundatald (... ) Local tablespace tbs_brd_ind;

Or:

1. Plan the boundaries of data partitioning in the original table, and in principle copy the small amount of recent data from the original table to another table;

2. Pause the relevant trigger in the original large table;

3. Delete recent data from the original large table;

4. Name of the original large table renamed;

5. Create a partitioned table;

6. Swap partitions;

7. Rebuild the relevant indexes and triggers (first remove the 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

[Rebuild Trigger]

DROP TABLE x1

1.1.4 Reference Material:

If you expect a large amount of data in a table, you usually need to consider using a partitioned table to determine what type of partition (range partition, hash partition, list partition, etc.), partition interval size, and so on, after using the partitioned table. Partition creation is best with the program has some kind of tacit understanding, I once created the partition table, according to the natural month definition partition, but the program in the query when the default start time and end time is: The current date-30 to the current date, For example, the day is No. 9.18, the query condition is generated as 8.18-9.18, the results of the partition is not not significantly improved performance, and later on the program's query date has been adjusted, according to the natural month query, the system load is much smaller.

Table partitioning is supported starting from Oracle8.0 (MSSQL2005 begins to support table partitioning).

Oracle9i partitioning can improve the manageability, performance, and availability of many applications. Partitions can further divide tables, indexes, and indexing tables, allowing for finer-grained management and access to these database objects. Oracle offers a wide range of partitioning schemes to meet all business needs. In addition, because it is completely transparent in SQL statements, partitions can be used in almost all applications.

Partitioned tables allow you to divide data into smaller, better-managed blocks called partitions or even sub-partitions. Indexes can also be partitioned in this way. Each partition can be managed separately and can function independently of other partitions, thus providing a more pro-availability and performance-based architecture.

Partitioning improves manageability, performance, and availability, which can bring great benefits to a wide variety of applications. In general, partitioning can greatly improve the performance of certain queries and maintenance operations. In addition, partitioning can greatly simplify routine management tasks. Partitioning also enables database designers and administrators to address the most difficult problems posed by cutting-edge applications. Partitioning is a key tool for building up hundreds of billions of bytes of data systems or requiring extremely high availability systems.

In a multi-CPU configuration environment, partitioning provides another way to parallelize if you intend to use parallel execution. Operations on partitioned tables and partitioned indexes can be performed in parallel by assigning different parallel execution servers to different partitions of a table or index.

The partitions and sub-partitions of a table or index share the same logical properties. For example, all partitions or sub-partitions of a table share the same column and constraint definitions, and an indexed partition or sub-partition shares the same index option. However, they can have different physical properties such as table spaces.

Although it is not necessary to place each partition or sub-partition of a table or index in a different table space, this is better. storing partitions in different tablespaces enables you to

• Reduce the likelihood of data collisions in multiple partitions

L can back up and restore each partition separately

L The mapping between the control partition and the disk drive is important for balancing the I/O load

L Improved manageability availability and performance

Partitioning operations are transparent to existing applications and to standard DML statements that run on partitioned tables. However, you can program your application to take advantage of partitioning by using the name of a partition extension table or index in DML.

You can use the Sql*loader, Import, and export tools to mount or unload data from a partitioned table. These tools are supported for partitions and sub-partitions.

Methods of Partitioning

Oracle9i provides the following 5 partitioning methods:

L Range Partition Range

L Hash Partition Hash

L List partition lists

L Combination range-Hash partition Range-hash

L Combination range-list partition range-list

Can be partitioned on indexes and tables. A global index can only be partitioned by scope, but it may be defined on any type of partition or non-partitioned table. Generally, global indexes require more maintenance than local indexes.

A local index is typically formed to reflect the structure of its underlying table. It is partitioned with the underlying table, that is, it is associated with the underlying

The table partitions on the same column, creates the same number of partitions or sub-partitions, and sets the same partition boundary that corresponds to the underlying table. For a local index, the index partition is maintained automatically when the maintenance activity affects the partition. This guarantees an equivalent partition between the index and the underlying table.

About range Partition range:

To map rows to partitions based on the range of column values, use the range partitioning method. This type of partitioning is useful when the data can be divided into logical ranges such as the months of the year. The performance is best when the data can be divided evenly across the entire range. Other partitioning methods need to be considered if the partitions in the range are significantly different in size because of unequal partitioning.

About Hash partition hash:

The hash partitioning method is used when the data is not so easily partitioned, but for performance and management reasons, when you want to partition. Hash partitioning provides a way to divide data evenly across a specified number of partitions. Maps a row to a partition based on the hash value of the partition key. Creating and using hash partitions gives you a flexible way to place data, because you can influence availability and performance by seeding (dropping) these evenly spaced partitions between I/O drives.

About List partition lists:

The list partitioning method is used when you need to explicitly control how rows are mapped to partitions. You can specify a column of discrete values for the partition column in the description of each partition, which differs from the range partition, where a range is related to a partition, which is also different from the hash partition, where the user cannot control how the row is mapped to the partition. The list partitioning method is deliberately designed for modular data partitioning that obeys discrete values. Range partitioning or hash partitioning is not so easy to do this. Further, the list partition can be very natural to group and organize the unordered and unrelated datasets.

Unlike range partitioning and hash partitioning, the list partition does not support multi-column partitioning. If you want to partition a table by column, the partition key can only consist of a single column of the table, but all the columns that can be partitioned with the range partition or hash partitioning method are partitioned using the list partitioning method.

About the combined range-hash partition:

A combination of scope and hashing techniques, first partitioning the table, and then partitioning each range partition again with hashing techniques. All child partitions of a given range partition are added together to represent a logical subset of the data.

About the combined range-list partition:

A combination of scope and list technology, first partitioning the table, and then partitioning each range partition with List technology. Unlike a composite range-hash partition, all the contents of each sub-partition represent a logical subset of the data, described by the appropriate scope and list partitioning settings.

When you create or change a partitioned table, you can specify a row move clause, either enable row movement or disable row movement, which enables or disables the migration of rows to a new partition when their keys are changed. The default value is disable ROW movement. This product (project) uses the Enable ROW movement clause.

Partitioning technology can improve the manageability of the database:

With partitioning techniques, maintenance operations can be focused on specific parts of a table. For example, a database administrator can make a backup of only a portion of a table without having to make a backup of the entire table. Maintenance operations on the entire database object can be performed on a per-partition basis, thereby breaking down maintenance efforts into smaller, more manageable chunks.

A typical use of partitioning technology to improve manageability is to support the ' scrolling windows ' loading process in the Data warehouse. Assume that the database administrator loads new data into the table weekly. The table can be a range partition so that each partition contains one week of data. The load process simply adds a new partition. Adding a new partition is much more efficient than modifying the entire table, because the database administrator does not need to modify any other partitions. The same is true for removing data from a partitioned table. You simply delete a partition with a very quick and easy data dictionary operation without having to issue a ' delete ' command that uses a lot of resources and transfers all the data you want to delete.

Partitioning technology can improve the performance of the database:

The Oracle9i partitioning feature provides performance benefits because it reduces the amount of data being inspected or manipulated while allowing parallel execution. These capabilities include:

L Partition Trimming: partition trimming is the simplest and most valuable means of using partitioning technology to raise high performance. Partition trimming can often improve query performance by several orders of magnitude. For example, suppose you have an order table with an order history in your application, and the table is partitioned by week. Querying a week's order requires access to only one partition of the order form. If the order table contains two years of history, this query only needs to access one instead of 104 partitions. The execution speed of the query may be 100 times times faster because of partition trimming. Partition trimming can collaborate with all other Oracle performance features. Oracle will use partition trimming technology in conjunction with indexing technology, linking technology, and parallel access methods.

L Partition Smart join: The partitioning feature can improve the performance of multiple table joins by means of a technique called zoning smart joins. You can use a partitioned smart join when two tables are joined together, and each table is partitioned with the Join keyword. A partitioned smart join breaks down large joins into smaller joins that occur between partitions, thus completing all joins in less time. This can lead to significant performance improvements for both serial and parallel execution.

l Parallel execution of updates and deletions: The partitioning function can execute update, delete, and MERGE statements in parallel indefinitely. When accessing a partitioned or unpartitioned database object, Oracle processes the SELECT and INSERT statements in parallel. When you do not use a bitmap index, you can also process UPDATE, DELETE, and MERGE statements in parallel to partitioned or unpartitioned database objects. In order to process those operations in parallel for objects that have bitmap indexes, the target table must be partitioned first. Parallel execution of these SQL statements can greatly improve performance, especially when the UPDATE and DELETE or MERGE operations involve large amounts of data.

Partitioning technology improves usability:

Partitioned database objects have partition independence. The partitioning independence feature may be an important part of a high availability strategy, for example, if the partition table's partitions are unavailable, but all other partitions of the table remain online and available. Then the application can continue to execute queries and transactions against the partitioned table, and the database operation will still run successfully as long as it is not accessing that unusable partition. The database administrator can specify that each partition is stored in a different table space, allowing the administrator to perform backup and restore operations on each partition independently of the other table partitions. Also, the partitioning feature can reduce planned down time. Performance is improved by partitioning functionality, enabling database administrators to perform large database object maintenance in relatively small batch processing windows.

Oracle Partitioned Tables perform performance