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

The main content of this article is to explain "what is the difference between DM7,DM8 and ORACLE on partition split". Interested friends may wish to have a look. The method introduced in this paper is simple, fast and practical. Next, let the editor take you to learn "what is the difference between DM7,DM8 and ORACLE in partition split?"

In oracle, we can go to the split partition with the following command, and we can keep the original partition name. Dameng 7 and Dameng 8 cannot keep the original partition name, so we test it with the following command:

Oracle 11g:

CREATE TABLE HC.PARTITION_HB

(PID NUMBER NOT NULL

PITEM VARCHAR2 (200)

PDATA DATE NOT NULL

)

PARTITION BY RANGE (PID)

(PARTITION PART_01 VALUES LESS THAN (5)

PARTITION PART_02 VALUES LESS THAN (10)

PARTITION PART_03 VALUES LESS THAN (20)

PARTITION PART_MAX VALUES LESS THAN (MAXVALUE))

BEGIN

FOR I IN 1..150 LOOP

INSERT INTO HC.PARTITION_HB VALUES (iMagazine AA' | | iMagneSysdate)

COMMIT

END LOOP

END

Alter table hc.partition_hb SPLIT PARTITION part_max AT (150) INTO

(PARTITION PART_04,PARTITION PART_MAX)

Select * from hc.partition_hb partition (part_max)

ALTER TABLE hc.partition_hb RENAME PARTITION part_max TO part_max1

If the execution is successful, you can use rename to rename the partition

In DM7:

Create user hc identified by dameng123

Grant dba to hc

CREATE TABLE HC.PARTITION_HB

(PID NUMBER NOT NULL ENABLE

PITEM VARCHAR2 (200)

PDATA DATE NOT NULL ENABLE

)

PARTITION BY RANGE (PID)

(PARTITION PART_01 VALUES LESS THAN (5)

PARTITION PART_02 VALUES LESS THAN (10)

PARTITION PART_03 VALUES LESS THAN (20)

PARTITION PART_MAX VALUES LESS THAN (MAXVALUE))

BEGIN

FOR I IN 1..150 LOOP

INSERT INTO HC.PARTITION_HB VALUES (iMagazine AA' | | iMagneSysdate)

COMMIT

END LOOP

END

Select count (1) from hc.partition_hb partition (part_max)

AlTer table hc.partition_hb SPLIT PARTITION part_max AT (150) INTO

(PARTITION PART_04,PARTITION PART_MAX1)

ALTER TABLE hc.partition_hb RENAME PARTITION part_max1 TO part_max

[execute statement 1]:

ALTER TABLE hc.partition_hb RENAME PARTITION part_max1 TO part_max

Execution failed (statement 1)

Line 1, error [- 2007] near column 74 [PARTITION]:

An error occurred in parsing

You can see that Dameng 7 does not support rename and object duplicate names, that is, the partition after split cannot be in the original table, so it can only be changed to part_max1.

-

Dm8:

[root@dm8os tool] #. / disql sysdba/dameng123

Disql V8

SQL > show user

Unknown SHOW option user

SQL > select user

Not connected

SQL > conn sysdba/dameng123

Server [LOCALHOST:5236]: in normal configuration state

Login usage time: 4.958 (milliseconds)

SQL > create user hc identified by dameng123

Grant dba to hc

Create user hc identified by dameng123

The system is in MOUNT state.

Elapsed time: 21.042 milliseconds. Execution number: 0.

SQL > grant dba to hc

The system is in MOUNT state.

Elapsed time: 2.532 milliseconds. Execution number: 0.

SQL >

SQL > CREATE TABLE HC.PARTITION_HB

2 (PID NUMBER NOT NULL ENABLE

3 PITEM VARCHAR2

4 PDATA DATE NOT NULL ENABLE

5)

6 PARTITION BY RANGE (PID)

7 (PARTITION PART_01 VALUES LESS THAN (5))

8 PARTITION PART_02 VALUES LESS THAN (10)

9 PARTITION PART_03 VALUES LESS THAN (20)

10 PARTITION PART_MAX VALUES LESS THAN (MAXVALUE))

CREATE TABLE HC.PARTITION_HB

(PID NUMBER NOT NULL ENABLE

PITEM VARCHAR2 (200)

PDATA DATE NOT NULL ENABLE

)

PARTITION BY RANGE (PID)

(PARTITION PART_01 VALUES LESS THAN (5)

PARTITION PART_02 VALUES LESS THAN (10)

PARTITION PART_03 VALUES LESS THAN (20)

PARTITION PART_MAX VALUES LESS THAN (MAXVALUE))

The system is in MOUNT state.

Elapsed time: 3.614 milliseconds. Execution number: 0.

SQL > alter database open

The operation has been performed

Elapsed time: 000.01.924. Execution number: 0.

SQL > create user hc identified by dameng123

Create user hc identified by dameng123

Error near line 1 [- 2124]: object [HC] already exists.

Elapsed time: 1.310 milliseconds. Execution number: 0.

SQL > grant dba to hc

The operation has been performed

Elapsed time: 11.131 milliseconds. Execution number: 4.

SQL >

SQL > CREATE TABLE HC.PARTITION_HB

2 (PID NUMBER NOT NULL ENABLE

3 PITEM VARCHAR2

4 PDATA DATE NOT NULL ENABLE

5)

6 PARTITION BY RANGE (PID)

7 (PARTITION PART_01 VALUES LESS THAN (5))

8 PARTITION PART_02 VALUES LESS THAN (10)

9 PARTITION PART_03 VALUES LESS THAN (20)

10 PARTITION PART_MAX VALUES LESS THAN (MAXVALUE))

The operation has been performed

Elapsed time: 28.160 milliseconds. Execution number: 5.

SQL >

2 BEGIN

3 FOR I IN 1..150 LOOP

4 INSERT INTO HC.PARTITION_HB VALUES (iMagnesia AA' | | iMagneSysdate)

5 COMMIT

6 END LOOP

7 END

8 /

The DMSQL process has completed successfully

Elapsed time: 257.421 milliseconds. Execution number: 6.

SQL > select count (1) from hc.partition_hb partition (part_max)

Line number COUNT (1)

--

1 131

Elapsed time: 2.695 milliseconds. Execution number: 7.

SQL > alTer table hc.partition_hb SPLIT PARTITION part_max AT (150) INTO

2 (PARTITION PART_04,PARTITION PART_MAX)

AlTer table hc.partition_hb SPLIT PARTITION part_max AT (150) INTO

(PARTITION PART_04,PARTITION PART_MAX)

Error near line 2 [- 2622]: partition name conflicts with database object name.

Elapsed time: 0.757 milliseconds. Execution number: 0.

SQL > alTer table hc.partition_hb SPLIT PARTITION part_max AT (150) INTO

2 (PARTITION PART_04,PARTITION PART_MAX1)

The operation has been performed

Elapsed time: 90.611 milliseconds. Execution number: 8.

SQL > ALTER TABLE hc.partition_hb RENAME PARTITION part_max1 TO part_max

The operation has been performed

Elapsed time: 117.870 milliseconds. Execution number: 9.

SQL >

At this point, I believe you have a deeper understanding of "what is the difference between DM7,DM8 and ORACLE on partition split". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

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.