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

This article mainly explains the "oracle partition tables can be divided into several categories", the article explains the content is simple and clear, easy to learn and understand, the following please follow the editor's ideas slowly in depth, together to study and learn "oracle partition tables can be divided into several categories" bar!

Oracle partition tables are divided into four categories: range partition tables, list partition tables, hash partition tables, and combined partition tables.

1. List partition table (list)

CREATE TABLE list_example (

Dname VARCHAR2 (10)

DATA VARCHAR2 (20)

)

PARTITION BY LIST (dname)

(

PARTITION part01 VALUES ('initial registration', 'transfer registration')

PARTITION part02 VALUES ('renaming registration', 'real estate change')

PARTITION part03 VALUES ('Mortgage Registration')

PARTITION part04 VALUES ('restricted registration')

);

two。 Range partition

CREATE TABLE example

(

CUSTOMER_ID NUMBER NOT NULL PRIMARY KEY

FIRST_NAME VARCHAR2 (30) NOT NULL

LAST_NAME VARCHAR2 (30) NOT NULL

PHONE VARCHAR2 (15) NOT NULL

EMAIL VARCHAR2 (80)

STATUS CHAR (1)

)

PARTITION BY RANGE (CUSTOMER_ID)

(

PARTITION exam1 VALUES LESS THAN (100000) TABLESPACE arsystem

PARTITION exam2 VALUES LESS THAN (200000) TABLESPACE arsystem

PARTITION exam3 VALUES LESS THAN (300000) TABLESPACE arsystem

);

Time division

CREATE TABLE part_date

(

ORDER_ID NUMBER (7) NOT NULL

ORDER_DATE DATE

TOTAL_AMOUNT NUMBER

CUSTOTMER_ID NUMBER (7)

PAID CHAR (1)

)

PARTITION BY RANGE (ORDER_DATE)

(

PARTITION ORD_ACT_PART01 VALUES LESS THAN (TO_DATE ('2015-07-01 00 hh34:mi:ss')) TABLESPACE arsystem

PARTITION ORD_ACT_PART02 VALUES LESS THAN (TO_DATE ('2015-08-01 00 hh34:mi:ss')) TABLESPACE arsystem

PARTITION ORD_ACT_PART03 VALUES LESS THAN (TO_DATE ('2015-09-01 00 hh34:mi:ss')) TABLESPACE arsystem

PARTITION ORD_ACT_PART04 VALUES LESS THAN (MAXVALUE) TABLESPACE arsystem

);

Hash partition

Create table HASH_PART

(

Transaction_id number primary key

Item_id number (8) not null

)

Partition by hash (transaction_id)

(

Partition part_01 tablespace ARSYSTEM

Partition part_02 tablespace ARSYSTEM

Partition part_03 tablespace ARSYSTEM

);

Abbreviated

CREATE TABLE emp_hash

(

Empno NUMBER (4)

Ename VARCHAR2 (30)

Sal NUMBER

)

PARTITION BY HASH (empno) PARTITIONS 8

STORE IN (arsystem1,arsystem2)

The main mechanism of hash partition is to calculate which partition a record should be inserted into according to hash algorithm. The most important thing in hash algorithm is the hash function. If you want to use hash partition in Oracle, you only need to specify the number of partitions. It is suggested that the number of partitions should be 2 to the n-th power, which can make the data distribution of each interval more uniform.

Combined partition

Range-Hash Partition

CREATE TABLE SALES

(

PRODUCT_ID VARCHAR2 (5)

SALES_DATE DATE

SALES_COST NUMBER (10)

STATUS VARCHAR2 (20)

)

PARTITION BY RANGE (SALES_DATE) SUBPARTITION BY LIST (STATUS)

(

PARTITION P1 VALUES LESS THAN (TO_DATE ('2003-01-01-01-01)) TABLESPACE ARSYSTEM

(

SUBPARTITION P1SUB1 VALUES ('ACTIVE') TABLESPACE ARSYSTEM

SUBPARTITION P1SUB2 VALUES ('INACTIVE') TABLESPACE ARSYSTEM

),

PARTITION P2 VALUES LESS THAN (TO_DATE ('2003-03-01)) TABLESPACE ARSYSTEM

(

SUBPARTITION P2SUB1 VALUES ('ACTIVE') TABLESPACE ARSYSTEM

SUBPARTITION P2SUB2 VALUES ('INACTIVE') TABLESPACE ARSYSTEM

)

);

Range-Hash Partition

Create table dinya_test

(

Transaction_id number primary key

Item_id number (8) not null

Item_description varchar2 (300)

Transaction_date date

)

Partition by range (transaction_date) subpartition by hash (transaction_id) subpartitions 3 store in (arsystem,users)

(

Partition part_01 values less than (TO_DATE ('2015-07-01 00 hh34:mi:ss'))

Partition part_02 values less than (TO_DATE ('2015-08-01 00 hh34:mi:ss'))

Partition part_03 values less than (maxvalue)

);

There are three types of indexes that can be created on a partitioned table: 1 the same global index as the ordinary table; 2. Global partition index; 3. Local partition index

1. Set up a general index

Create index com_index_range_example_id on range_example (id)

two。 Establish a local partition index

Create index local_index_range_example_id on range_example (id) local

3. Establish a global partition index

Create index gidx_range_exampel_id on range_example (id)

GLOBAL partition by range (id)

(

Part_01 values less than (1000)

Part_02 values less than (MAXVALUE)

);

For the deletion of a partitioned index, local index cannot specify a partition name and delete the partitioned index separately.

The partition corresponding to local index is deleted along with the deletion of the data partition. Global partition index can specify a partition name and delete a partition. It is important to note, however, that if the partition is not empty, it will cause the higher-level index partition to be set to UNUSABLE.

ALTER INDEX gidx_range_exampel_id drop partition part_01; this sentence will cause the part_02 status to be UNUSABLE

Operation of partitioned tables

1. Query

Select * from part_date partition (ORD_ACT_PART01)

two。 Delete a partition

Alter table part_date drop partition ORD_ACT_PART04

Alter table part_date truncate partition ORD_ACT_PART04

3. Add a partition

ALTER TABLE part_date ADD PARTITION ORD_ACT_PART04 VALUES LESS THAN (TO_DATE ('2015-10-01 camera camera YYYYMMI DD')

4. Create an index

Create index idx_part_date on part_date (order_id) local

-online redefinition process-

For the definition in the package, you can use the pl/sql tool to see the usage of the package dbms_redefinition

-- Constants for the options_flag parameter of start_redef_table

Cons_use_pk CONSTANT PLS_INTEGER: = 1betrom-primary key redefinition

Cons_use_rowid CONSTANT PLS_INTEGER: = 2betrowd redefinition

Increase processing speed, add parallelism

Alter session force parallel dml parallel 4

Alter session force parallel query parallel 4

Set up a general table

CREATE TABLE putong_table

(

CUSTOMER_ID NUMBER NOT NULL PRIMARY KEY

FIRST_NAME VARCHAR2 (30) NOT NULL

LAST_NAME VARCHAR2 (30) NOT NULL

PHONE VARCHAR2 (15) NOT NULL

EMAIL VARCHAR2 (80)

STATUS CHAR (1)

);

Create a partition table

CREATE TABLE part_table

(

CUSTOMER_ID NUMBER NOT NULL PRIMARY KEY

FIRST_NAME VARCHAR2 (30) NOT NULL

LAST_NAME VARCHAR2 (30) NOT NULL

PHONE VARCHAR2 (15) NOT NULL

EMAIL VARCHAR2 (80)

STATUS CHAR (1)

)

PARTITION BY RANGE (CUSTOMER_ID)

(

PARTITION exam1 VALUES LESS THAN (100000) TABLESPACE arsystem

PARTITION exam2 VALUES LESS THAN (200000) TABLESPACE arsystem

PARTITION exam3 VALUES LESS THAN (300000) TABLESPACE arsystem

);

Insert test data

Insert into putong_table values (1) 43tcm, f4ym, 3', t54, and so on.

Insert into putong_table values (2, 43, 4, 4, 4, 4, 5, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4,

Insert into putong_table values (3, 43, 4, 4, 4, 4, 5, 5, 4, 5, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4,

Online redefinition process

Test whether the table can be partitioned

Exec dbms_redefinition.can_redef_table ('ARADMIN',' putong_table')

Start

EXEC DBMS_REDEFINITION.START_REDEF_TABLE ('ARADMIN',' putong_table', 'part_table')

-- insert test data (optional)

Insert into putong_table values (4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4')

Synchronous data

EXEC dbms_redefinition.sync_interim_table ('ARADMIN',' putong_table', 'part_table')

Complete

EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE ('ARADMIN',' putong_table', 'part_table')

Rename the partition table

Alter table part_table rename to part_table_new

1. No primary key table

Ordinary watch

CREATE TABLE date_putong

(

ORDER_ID NUMBER (7) NOT NULL

ORDER_DATE DATE

TOTAL_AMOUNT NUMBER

CUSTOTMER_ID NUMBER (7)

PAID CHAR (1)

);

Partition table

CREATE TABLE date_partition

(

ORDER_ID NUMBER (7) NOT NULL

ORDER_DATE DATE

TOTAL_AMOUNT NUMBER

CUSTOTMER_ID NUMBER (7)

PAID CHAR (1)

)

PARTITION BY RANGE (ORDER_DATE)

(

PARTITION ORD_ACT_PART01 VALUES LESS THAN (TO_DATE ('2015-07-01 00 hh34:mi:ss')) TABLESPACE arsystem

PARTITION ORD_ACT_PART02 VALUES LESS THAN (TO_DATE ('2015-08-01 00 hh34:mi:ss')) TABLESPACE arsystem

PARTITION ORD_ACT_PART03 VALUES LESS THAN (TO_DATE ('2015-09-01 00 hh34:mi:ss')) TABLESPACE arsystem

PARTITION ORD_ACT_PART04 VALUES LESS THAN (MAXVALUE) TABLESPACE arsystem

);

Insert data

Insert into date_putong values (1meme toasted date ('2015-08-24jinyyyymm murmurdd`), 1meme 2meme 3)

Insert into date_putong values (1meme toasted date ('2015-08-24jinyyyymm murmurdd`), 1meme 2meme 3)

Insert into date_putong values (1meme toasted date ('2015-08-24jinyyyymm murmurdd`), 1meme 2meme 3)

Redefinition process

Exec dbms_redefinition.can_redef_table ('ARADMIN',' date_putong',2)

EXEC DBMS_REDEFINITION.START_REDEF_TABLE ('ARADMIN',' date_putong', 'date_partition',null,2)

EXEC dbms_redefinition.sync_interim_table ('ARADMIN',' date_putong', 'date_partition')

EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE ('ARADMIN',' date_putong', 'date_partition')

-exception handling-

Terminate the operation under abnormal circumstances

BEGIN

DBMS_REDEFINITION.ABORT_REDEF_TABLE (uname = > 'ARADMIN'

Orig_table = > 'date_putong'

Int_table = > 'date_partition'

);

END

Or

DBMS_REDEFINITION.ABORT_REDEF_TABLE ('ARADMIN',' date_putong', 'date_partition')

ORA-12089: cannot redefine table "ARADMIN". "DATE_PUTONG" online without primary key

ORA-06512: in "SYS.DBMS_REDEFINITION", line 56

ORA-06512: in "SYS.DBMS_REDEFINITION", line 1498

ORA-06512: in line 2

Alter table PUTONG_TABLE add primary key (CUSTOMER_ID)

ORA-12091

Delete materialized view log

Drop materialized view log on BS_T_WF_DEALPROCESS_BAK

Errors when viewing partitions

Select * from DBA_REDEFINITION_ERRORS

Examples of virtual column partitions

It is required to partition on a monthly basis, and the 12 partitions can be recycled, keeping only 2 months of data, so 11g virtual columns are used for partitioning

Create table MACHINE_TEMP_part

(

Id NUMBER

Username VARCHAR2 (30)

Tcp VARCHAR2 (30)

Clientip VARCHAR2 (30)

Logintime VARCHAR2 (30)

Logouttime VARCHAR2 (30)

Serverip VARCHAR2 (30)

Insertdate DATE

Part_numas (to_nubmer (to_char (insertdate,'mm')

)

Tablespace UNIONMON

Artition by range (partition_num)

(partition p1 values less than (2)

Partition p2 values less than (3)

Partition p3 values less than (4)

Partition p4 values less than (5)

Partition p5 values less than (6)

Partition p6 values less than (7)

Partition p7 values less than (8)

Partition p8 values less than (9)

Partition p9 values less than (10)

Partition p10 values less than (11)

Partition p11 values less than (12)

Partition p12 values less than (13)

);

Thank you for your reading, the above is the "oracle partition table can be divided into several categories" content, after the study of this article, I believe you can be divided into several categories of oracle partition table can be divided into several categories of this problem has a deeper understanding, the specific use of the need for you to practice and verify. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

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.