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

This article introduces the knowledge of "the advantages, disadvantages and usage of oracle partition table". Many people will encounter this dilemma in the operation of actual cases, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

Summary of I partition table:

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.

1. Advantages of partitioned tables:

(1) the possibility of data corruption is reduced by dispersing the data into various partitions.

(2) individual partitions can be backed up and restored

(3) you can map partitions to different physical disks to disperse the IO

(4) improve manageability, availability and performance.

2. When to use the partition table

(1) if a single table is too large and exceeds a certain range, it is recommended to use g to calculate the table, and partition can be considered.

(2) Historical data should be stripped off as needed.

(3) query features are very obvious, such as by the whole year, the whole month or by a certain range!

3. Type of partition table

1. Range partition, by range

2. List partition, enumerate partition

3. Hash partition, hash partition based on hash value

4. Compound partition. Starting from 9i, Oracle includes two kinds of compound partition, RANGE-HASH and RANGE-LIST. At 11g Oracle, four composite partitions are available: RANGE-RANGE, LIST-RANGE, LIST-HASH, and LIST-LIST.

An example of creating Partition by II

11g automatic zoning technology

CREATE TABLE auto_partition (

OBJECT_ID NUMBER

OBJECT_TYPE VARCHAR2 (128)

CREATED DATE)

PARTITION BY RANGE (CREATED)

INTERVAL (NUMTOYMINTERVAL (1, 'month'))

(PARTITION P0 VALUES LESS THAN (TO_DATE ('1-1-2011, 'dd-mm-yyyy')

-- create create table test_jia (ID NUMBER (20) not null, REMARK VARCHAR2 (1000)) PARTITION BY RANGE (CREATE_TIME) INTERVAL (numtoyminterval (1, 'year')) (partition part_t01 values less than (' 2020-01-01), 'yyyy-mm-dd') on an annual basis

In this way, a partition is automatically generated by the system each year

-- INTERVAL (NUMTODSINTERVAL (1 minute daylight) means every day

-- INTERVAL (NUMTOYMINTERVAL (1) means monthly

-- INTERVAL (NUMTODSINTERVAL (7 minutes daylight)) means every week

III partition operation

-- add a partition

Add a partition

Alter table test_jia add partition P3 valuse less than (to_date ('2020-01-10)

-- Delete partition

Delete partition

Alter table test_jia drop partition P3

Note that if there is only one partition left in the table, you cannot drop, but you should drop the table. In a compound partition, if there is only one subpartition left in a partition, you cannot drop

-- truncate partition

Truncate partition

Alter table test_jia truncate partition P4

-- merge partitions

Alter table test_jia merge partition P1,P2 into partition P2

Note: you cannot merge partitions into lower-bounded partitions. The above code implements the merging of P1 and P2 partitions.

-- split partition

Alter table test_jia split partition P2 at (to_date ('2013-02-01)) into (partition P21 ~ partition P22)

-rename the partition

Alter table test_jia RENAME partition P21 to P2

-- query for partition tables

SQL > select * from test_jia partition (p1)

No rows selected

-create a partitioned table index

Create index ind_id on test_jia (id) local

-- partitioned table index reconstruction

Alter index ind_test_jia rebuild partition p1

This is the end of the content of "advantages and disadvantages and usage of oracle partition table". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

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.