Partition management for ORACLE8
- 2020-05-10 23:05:44
- OfStack
The ORACLE tutorial you are looking at is: partition management for ORACLE8. Abstract: this article introduces a new feature of ORACLE database, partition management, and gives an example of how to use it.
Key words: ORACLE, partition
1. Partition overview:
To simplify the management of database large tables, ORACLE8 introduced partitioning options. Partitioning separates the tables into several different table Spaces, using a divide-and-conquer approach to support infinitely expanding large tables, giving large tables manageability at physical level 1. Splitting large tables into smaller partitions improves table maintenance, backup, recovery, transaction, and query performance. This option of ORACLE8 can be recommended for the current social security and telecommunications industries with a large amount of daily business data.
2. Advantages of partitioning:
1. Enhanced availability: if one partition of the table cannot be used due to system failure, the remaining good partitions of the table can still be used;
2. Reduce shutdown time: if the system failure only affects part 1 of the table partition, then only this part of the partition needs to be repaired, so it can take less time to repair than the whole big table;
3. Easy maintenance: if you need to rebuild the tables, it is much easier to manage each partition independently than to manage a single large table;
4. Balanced I/O: different partitions of the table can be allocated to different disks to balance I/O to improve performance;
5, improve performance: query, add, modify and other operations of the large table can be decomposed into different partitions of the table for parallel execution, which can make the running speed faster;
6. The partition is transparent to users, and the end user cannot feel the existence of the partition.
3. Partition management:
1. Establishment of partition table:
A company's annual sales record is huge. DBA suggests to the company that the quarterly data be placed in one partition. The following is the company's 1999 data (assuming that 30M data are generated every month).
STEP1. Create table Spaces for each partition of the table:
CREATE TABLESPACE ts_sale1999q1
DATAFILE '/ u1 oradata/sales/sales1999_q1 dat'
SIZE 100M
DEFAULT STORAGE (INITIAL 30m NEXT 30m MINEXTENTS 3 PCTINCREASE 0)
CREATE TABLESPACE ts_sale1999q2
DATAFILE '/ u1 oradata/sales/sales1999_q2 dat'
SIZE 100M
DEFAULT STORAGE (INITIAL 30m NEXT 30m MINEXTENTS 3 PCTINCREASE 0)
CREATE TABLESPACE ts_sale1999q3
DATAFILE '/ u1 oradata/sales/sales1999_q3 dat'
SIZE 100M
DEFAULT STORAGE (INITIAL 30m NEXT 30m MINEXTENTS 3 PCTINCREASE 0)
CREATE TABLESPACE ts_sale1999q4
DATAFILE '/ u1 oradata/sales/sales1999_q4 dat'
SIZE 100M
DEFAULT STORAGE (INITIAL 30m NEXT 30m MINEXTENTS 3 PCTINCREASE 0)
STEP2, create a partition-based 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 );
2. Capacity expansion of partition table:
By the end of 1999, DBA should add the year 2000 table space to the table, which is also one table space per quarter. Due to the booming business of the company, it is expected that each partition is 40M. The operation is as follows.
STEP1, establish a table space:
CREATE TABLESPACE ts_sale2000q1
DATAFILE '/ u1 oradata/sales/sales2000_q1 dat'
SIZE 130M
DEFAULT STORAGE (INITIAL 40m NEXT 40m MINEXTENTS 3 PCTINCREASE 0)
Other table space ts_sale2000q2 ts_sale2000q3, ts_sales2000q4 do the same thing.
STEP2, add a table space for the table:
ALTER TABLE sales
ADD PARTITION sales2000_q1
VALUES LESS THAN (TO_DATE (' 2000-04-01 ', 'YYYY MM - DD')
TABLESPACE ts_sale2000q1;
Other partitions sales2000_q1, sales2000_q1, sales2000_q1 do the same.
3. Delete unnecessary partitions:
Company rules: sales details must be kept online within two years. By 2001, DBA had to back up the 1999 data (see 5, EXPORT partition for the backup method), delete the 1999 partition, and make the space available for later data. Such a cycle, always maintain two years of sales data online.
STEP1, DROP partition:
ALTER TABLE sales
DROP PARTION sales1999_q1;
ALTER TABLE sales
DROP PARTION sales1999_q2;
ALTER TABLE sales
DROP PARTION sales1999_q3;
ALTER TABLE sales
DROP PARTION sales1999_q4;
STEP2. Use the tools of the operating system to delete the files occupied by the above table space (the table space is based on the naked device without the next step). UNIX system is an example:
oracle$ rm /u1/oradata/sales/sales1999_q1.dat
oracle$ rm /u1/oradata/sales/sales1999_q2.dat
oracle$ rm /u1/oradata/sales/sales1999_q3.dat
oracle$ rm /u1/oradata/sales/sales1999_q4.dat
4. Other operations of partition:
Other partitioning operations include truncating partitions (truncate), dividing existing partitions into multiple partitions (split), swapping partitions (exchange), renaming partitions (rename), indexing partitions, and so on. DBA can be used as appropriate.
For example, the company's sales details for the fourth quarter of 1999 increased sharply (to celebrate National Day, welcome the millennium, and congratulate the return). DBA suggested to the company that the fourth quarter of the partition be divided into two partitions, and each partition should have data of two months. The operation is as follows:
STEP1, according to the method of (1) to establish two partition table space ts_sales1999q4p1,
ts_sales1999q4p2;
STEP2, add two partitions to the table sales1999_q4_p1,sales1999_q4_p2;
STEP3, 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)
5. View the partition information:
To view the partition information of the table, DBA can view the data dictionary USER_EXTENTS as follows:
SVRMGRL > SELECT * FROM user_extents WHERE SEGMENT_NAME='SALES';
SEGMENT_NA PARTITION_ SEGMENT_TYPE TABLESPACE
---------- ------------ --------------- --------------
SALES SALES1999_Q1 TABLE PARTITION TS_SALES1999Q1
SALES SALES1999
SALES SALES1999_Q3 TABLE PARTITION TS_SALES1999Q3
SALES SALES1999_Q4 TABLE PARTITION TS_SALES1999Q4
SALES SALES2000_Q1 TABLE PARTITION TS_SALES1999Q1
SALES SALES2000_Q2 TABLE PARTITION TS_SALES1999Q2
SALES SALES2000_Q3 TABLE PARTITION TS_SALES1999Q3
SALES SALES2000_Q4 TABLE PARTITION TS_SALES1999Q4
5. EXPORT partition:
The EXPORT tool of ORACLE8 can partition and export the data in the table. For example, by 2001, DBA must export the data in 1999 by partition. The operation is as follows:
oracle$ exp sales/sales_password tables=sales:sales1999_q1 rows=Y
file=sales1999_q1.dmp
oracle$ exp sales/sales_password tables=sales:sales1999_q2 rows=Y
file=sales1999_q2.dmp
oracle$ exp sales/sales_password tables=sales:sales1999_q3 rows=Y
file=sales1999_q3.dmp
oracle$ exp sales/sales_password tables=sales:sales1999_q4 rows=Y
file=sales1999_q4.dmp
6. IMPORT partition:
The IMPORT tool of ORACLE8 can partition and import data in the table. For example, in 2001, users want to view the data of 1999, and DBA must import the data of 1999 to make it online. The operation is as follows:
STEP1. Four table Spaces and corresponding partitions were established in 1999, refer to (2);
STEP2. Imported data:
oracle$ imp sales/sales_password FILE =sales1999_q1.dmp
TABLES = (sales:sales1999_q1) IGNORE=y
oracle$ imp sales/sales_password FILE =sales1999_q2.dmp
TABLES = (sales:sales1999_q2) IGNORE=y
oracle$ imp sales/sales_password FILE =sales1999_q3.dmp
TABLES = (sales:sales1999_q3) IGNORE=y
oracle$ imp sales/sales_password FILE =sales1999_q4.dmp
TABLES = (sales:sales1999_q4) IGNORE=y
Key words: ORACLE, partition
1. Partition overview:
To simplify the management of database large tables, ORACLE8 introduced partitioning options. Partitioning separates the tables into several different table Spaces, using a divide-and-conquer approach to support infinitely expanding large tables, giving large tables manageability at physical level 1. Splitting large tables into smaller partitions improves table maintenance, backup, recovery, transaction, and query performance. This option of ORACLE8 can be recommended for the current social security and telecommunications industries with a large amount of daily business data.
2. Advantages of partitioning:
1. Enhanced availability: if one partition of the table cannot be used due to system failure, the remaining good partitions of the table can still be used;
2. Reduce shutdown time: if the system failure only affects part 1 of the table partition, then only this part of the partition needs to be repaired, so it can take less time to repair than the whole big table;
3. Easy maintenance: if you need to rebuild the tables, it is much easier to manage each partition independently than to manage a single large table;
4. Balanced I/O: different partitions of the table can be allocated to different disks to balance I/O to improve performance;
5, improve performance: query, add, modify and other operations of the large table can be decomposed into different partitions of the table for parallel execution, which can make the running speed faster;
6. The partition is transparent to users, and the end user cannot feel the existence of the partition.
3. Partition management:
1. Establishment of partition table:
A company's annual sales record is huge. DBA suggests to the company that the quarterly data be placed in one partition. The following is the company's 1999 data (assuming that 30M data are generated every month).
STEP1. Create table Spaces for each partition of the table:
CREATE TABLESPACE ts_sale1999q1
DATAFILE '/ u1 oradata/sales/sales1999_q1 dat'
SIZE 100M
DEFAULT STORAGE (INITIAL 30m NEXT 30m MINEXTENTS 3 PCTINCREASE 0)
CREATE TABLESPACE ts_sale1999q2
DATAFILE '/ u1 oradata/sales/sales1999_q2 dat'
SIZE 100M
DEFAULT STORAGE (INITIAL 30m NEXT 30m MINEXTENTS 3 PCTINCREASE 0)
CREATE TABLESPACE ts_sale1999q3
DATAFILE '/ u1 oradata/sales/sales1999_q3 dat'
SIZE 100M
DEFAULT STORAGE (INITIAL 30m NEXT 30m MINEXTENTS 3 PCTINCREASE 0)
CREATE TABLESPACE ts_sale1999q4
DATAFILE '/ u1 oradata/sales/sales1999_q4 dat'
SIZE 100M
DEFAULT STORAGE (INITIAL 30m NEXT 30m MINEXTENTS 3 PCTINCREASE 0)
STEP2, create a partition-based 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 );
2. Capacity expansion of partition table:
By the end of 1999, DBA should add the year 2000 table space to the table, which is also one table space per quarter. Due to the booming business of the company, it is expected that each partition is 40M. The operation is as follows.
STEP1, establish a table space:
CREATE TABLESPACE ts_sale2000q1
DATAFILE '/ u1 oradata/sales/sales2000_q1 dat'
SIZE 130M
DEFAULT STORAGE (INITIAL 40m NEXT 40m MINEXTENTS 3 PCTINCREASE 0)
Other table space ts_sale2000q2 ts_sale2000q3, ts_sales2000q4 do the same thing.
STEP2, add a table space for the table:
ALTER TABLE sales
ADD PARTITION sales2000_q1
VALUES LESS THAN (TO_DATE (' 2000-04-01 ', 'YYYY MM - DD')
TABLESPACE ts_sale2000q1;
Other partitions sales2000_q1, sales2000_q1, sales2000_q1 do the same.
3. Delete unnecessary partitions:
Company rules: sales details must be kept online within two years. By 2001, DBA had to back up the 1999 data (see 5, EXPORT partition for the backup method), delete the 1999 partition, and make the space available for later data. Such a cycle, always maintain two years of sales data online.
STEP1, DROP partition:
ALTER TABLE sales
DROP PARTION sales1999_q1;
ALTER TABLE sales
DROP PARTION sales1999_q2;
ALTER TABLE sales
DROP PARTION sales1999_q3;
ALTER TABLE sales
DROP PARTION sales1999_q4;
STEP2. Use the tools of the operating system to delete the files occupied by the above table space (the table space is based on the naked device without the next step). UNIX system is an example:
oracle$ rm /u1/oradata/sales/sales1999_q1.dat
oracle$ rm /u1/oradata/sales/sales1999_q2.dat
oracle$ rm /u1/oradata/sales/sales1999_q3.dat
oracle$ rm /u1/oradata/sales/sales1999_q4.dat
4. Other operations of partition:
Other partitioning operations include truncating partitions (truncate), dividing existing partitions into multiple partitions (split), swapping partitions (exchange), renaming partitions (rename), indexing partitions, and so on. DBA can be used as appropriate.
For example, the company's sales details for the fourth quarter of 1999 increased sharply (to celebrate National Day, welcome the millennium, and congratulate the return). DBA suggested to the company that the fourth quarter of the partition be divided into two partitions, and each partition should have data of two months. The operation is as follows:
STEP1, according to the method of (1) to establish two partition table space ts_sales1999q4p1,
ts_sales1999q4p2;
STEP2, add two partitions to the table sales1999_q4_p1,sales1999_q4_p2;
STEP3, 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)
5. View the partition information:
To view the partition information of the table, DBA can view the data dictionary USER_EXTENTS as follows:
SVRMGRL > SELECT * FROM user_extents WHERE SEGMENT_NAME='SALES';
SEGMENT_NA PARTITION_ SEGMENT_TYPE TABLESPACE
---------- ------------ --------------- --------------
SALES SALES1999_Q1 TABLE PARTITION TS_SALES1999Q1
SALES SALES1999
[1] [2] next page
The ORACLE tutorial you are looking at is: partition management for ORACLE8. _Q2 TABLE PARTITION TS_SALES1999Q2SALES SALES1999_Q3 TABLE PARTITION TS_SALES1999Q3
SALES SALES1999_Q4 TABLE PARTITION TS_SALES1999Q4
SALES SALES2000_Q1 TABLE PARTITION TS_SALES1999Q1
SALES SALES2000_Q2 TABLE PARTITION TS_SALES1999Q2
SALES SALES2000_Q3 TABLE PARTITION TS_SALES1999Q3
SALES SALES2000_Q4 TABLE PARTITION TS_SALES1999Q4
5. EXPORT partition:
The EXPORT tool of ORACLE8 can partition and export the data in the table. For example, by 2001, DBA must export the data in 1999 by partition. The operation is as follows:
oracle$ exp sales/sales_password tables=sales:sales1999_q1 rows=Y
file=sales1999_q1.dmp
oracle$ exp sales/sales_password tables=sales:sales1999_q2 rows=Y
file=sales1999_q2.dmp
oracle$ exp sales/sales_password tables=sales:sales1999_q3 rows=Y
file=sales1999_q3.dmp
oracle$ exp sales/sales_password tables=sales:sales1999_q4 rows=Y
file=sales1999_q4.dmp
6. IMPORT partition:
The IMPORT tool of ORACLE8 can partition and import data in the table. For example, in 2001, users want to view the data of 1999, and DBA must import the data of 1999 to make it online. The operation is as follows:
STEP1. Four table Spaces and corresponding partitions were established in 1999, refer to (2);
STEP2. Imported data:
oracle$ imp sales/sales_password FILE =sales1999_q1.dmp
TABLES = (sales:sales1999_q1) IGNORE=y
oracle$ imp sales/sales_password FILE =sales1999_q2.dmp
TABLES = (sales:sales1999_q2) IGNORE=y
oracle$ imp sales/sales_password FILE =sales1999_q3.dmp
TABLES = (sales:sales1999_q3) IGNORE=y
oracle$ imp sales/sales_password FILE =sales1999_q4.dmp
TABLES = (sales:sales1999_q4) IGNORE=y
Previous page [1] [2]