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

Editor to share with you how to create partition tables according to time and ID in the database. I hope you will get something after reading this article. Let's discuss it together.

Oracle partitioning advantages:

1. Enhanced availability: if one partition of the table cannot be used due to a system failure, the rest of the good partitions of the table can still be used

2. Reduce the shutdown time: if the system failure affects only part of the partition of the table, then only this part of the partition needs to be repaired, and the mine can take less time to repair than the whole large table.

3. Easy maintenance: if tables need to be built, it is much easier to manage each public area than to manage a single large table.

4. Balanced Icando: different partitions of the table can be allocated to different disks to balance Icano to improve performance.

5, improve performance: query, add, modify and other operations on large tables can be decomposed into different partitions of the table to be executed in parallel, which can make the running speed faster, and the TP query in the data warehouse is particularly useful.

6. The partition is transparent to the user, and the end user does not feel the existence of the partition.

ORACLE table partition:

1. First, you need to determine whether the partition column is ID or date.

2. Determine the partition type: range partition, hash partition, list partition and combined partition.

3. Create a partition table

4. Synchronize the original ordinary table data to the new partition table

5. Rename old and new tables

6. Partition table succeeded

Create a partition table case based on time: (range partition)

Create table T (

"ID" VARCHAR2 (32 CHAR)

"EVT_TIME" TIMESTAMP (6)

)

Partition by RANGE (EVT_TIME)

(

PARTITION T1 VALUES LESS THAN (TO_DATE ('1-01-2018)

PARTITION T2 VALUES LESS THAN (TO_DATE ('1-02-2018)

PARTITION T3 VALUES LESS THAN (TO_DATE ('1-03-2018)

PARTITION T4 VALUES LESS THAN (TO_DATE ('1-04-2018)

PARTITION T5 VALUES LESS THAN (TO_DATE ('1-05-2018)

PARTITION T6 VALUES LESS THAN (TO_DATE ('1-06-2018)

PARTITION T7 VALUES LESS THAN (TO_DATE ('1-07-2018)

PARTITION T8 VALUES LESS THAN (TO_DATE ('1-08-2018)

PARTITION T9 VALUES LESS THAN (TO_DATE ('1-09-2018)

PARTITION T10 VALUES LESS THAN (TO_DATE ('1-10-2018)

PARTITION T11 VALUES LESS THAN (TO_DATE ('1-11-2018)

PARTITION T12 VALUES LESS THAN (TO_DATE ('1-12-2018)

PARTITION T13 VALUES LESS THAN (TO_DATE ('1-01-2019)

PARTITION T14 VALUES LESS THAN (TO_DATE ('1-02-2019)

PARTITION T15 VALUES LESS THAN (TO_DATE ('1-03-2019)

PARTITION T16 VALUES LESS THAN (TO_DATE ('1-04-2019)

PARTITION T17 VALUES LESS THAN (TO_DATE ('1-05-2019)

PARTITION T18 VALUES LESS THAN (TO_DATE ('1-06-2019)

PARTITION T19 VALUES LESS THAN (TO_DATE ('1-07-2019)

PARTITION T20 VALUES LESS THAN (TO_DATE ('1-08-2019)

PARTITION T21 VALUES LESS THAN (TO_DATE ('1-09-2019)

PARTITION T22 VALUES LESS THAN (TO_DATE ('1-10-2019)

PARTITION T23 VALUES LESS THAN (TO_DATE ('1-11-2019)

PARTITION T24 VALUES LESS THAN (TO_DATE ('1-12-2019)

PARTITION TMAX VALUES LESS THAN (MAXVALUE)

);

Insert into T select * from original single table name

Rename original single table name to original single table name _ old

Rename T to original single table name

two。 Create a partition table example based on ID: (range partition)

Create table t_part (id int,name varchar2 (1000)) partition by

Range (id)

(

Partition p1 values less than (10)

Partition p2 values less than (20)

Partition p3 values less than (30)

Partition p4 values less than (40)

Partition pm values less than (maxvalue)

);

Partition table query:

Select * from t_part partition (p1)

Examples of various partition types:

Create tablespace dw1

Datafile'D:\ oracle\ oradata\ ora9\ dw11.ora' size 50m

Create tablespace dw2

Datafile'D:\ oracle\ oradata\ ora9\ dw21.ora' size 50m

First, partition by range: to fix the name means to partition by a certain range. See the following example:

SQL > set linesize 1000

SQL > create table niegc_part

2 (

3 part_id integer primary key

4 part_date date

5 part_dec varchar2

6)

7 partition by range (part_date)

8 (

9 partition part_01 values less than (to_date ('2006-01-01-01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01) tablespace dw1

10 partition part_02 values less than (to_date ('2007-01-01-01-01-01)) tablespace dw2

11 partition part_03 values less than (maxvalue) tablespace dw1

12)

The table has been created.

SQL >

SQL > insert into niegc_part values (1 less tokens date ('2005-12-30 minutes recordings Yyyyyymm MMMMMMMMMMMMMMMMMMMMB),' less 20

06-01-01')

1 line has been created.

SQL > commit

The submission is complete.

SQL > insert into niegc_part values (2 equal toast date ('2006-01-01-01-01-01-01-01-01-01-01-01)

007-01-01')

1 line has been created.

SQL > commit

The submission is complete.

SQL > insert into niegc_part values (3There is sysdatereachsysdate')

1 line has been created.

SQL > commit

The submission is complete.

SQL >

SQL >

SQL > select * from niegc_part partition (part_01)

PART_ID PART_DATE PART_DEC

-

-

1 30-December-05 less 2006-01-01

SQL >

I believe that as long as you are familiar with oracle, you can know the meaning of the range partition above.

Range partitions with more than two fields are more or less the same. Take a look at the following example:

Create table niegc_part

(

Part_id integer primary key

Part_date date

Part_dec varchar2 (100)

)

Partition by range (part_id,part_date)

(

Partition part_01 values less than (1) tablespace dw ('2006-01-01-01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01

Partition part_02 values less than (10 camera date ('2007-01-01-01-01-01)) tablespace dw

Partition part_03 values less than (maxvalue,maxvalue) tablespace dw

);

Second, Hash partition (hash partition). Hash partition A type of partition in which data is evenly distributed by specifying a partition number, because the row partitions are uniform in size by hashing the partitions on the Ihammer O device. For example, the data of part_id is hashed into three specified tablespaces according to its own situation:

Create table niegc_part

(

Part_id integer primary key

Part_date date

Part_dec varchar2 (100)

)

Partition by hash (part_id)

(

Partition part_01 tablespace dw1

Partition part_02 tablespace dw2

);

The system will hash the records into three partitions by part_id, that is, two different tablespaces.

Third, compound partition. After partitioning according to the range, the data in each partition is hashed across several tablespaces, so we use compound partitions. Composite partitioning is a partitioning method in which range partitions are used first, and then hash partitions are used in each partition. For example, records of part_date are partitioned by time, and then the data in each partition is divided into three sub-partitions, and the data is hashed in three specified table spaces:

Create table niegc_part

(

Part_id integer primary key

Part_date date

Part_dec varchar2 (100)

)

Partition by range (part_date) subpartition by hash (part_id)

Subpartitions 2 store in (dw1,dw2)

(

Partition part_01 values less than (to_date ('2006-01-01-01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01) tablespace dw1

Partition part_02 values less than (to_date ('2007-01-01-01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01) tablespace dw2

Partition part_03 values less than (maxvalue) tablespace dw1

);

The range is partitioned according to part_date, and then the records are hashed into two table spaces based on the ID of the transaction.

IV. Index partition:

Note: a field has been partitioned and no more index partitions are allowed. We should pay great attention to this point.

The global clause allows you to specify the range value of the index when the global index is established, which is the range value of the index field:

Create index idx_part_id on niegc_part (part_dec)

Global partition by range (part_dec)

(

Partition idx_1 values less than ('1000') tablespace dw

Partition idx_2 values less than (maxvalue) tablespace dw

)

Establishment of local index partitions: (note: there must be partitions in the table, and the number of partitions must be the same as the number of partitions in the partitioned table, otherwise it cannot be established.)

Create index idx_part_id on niegc_part (part_dec)

Local

(

Partition idx_1 tablespace dw1

Partition idx_2 tablespace dw2

)

5. Partition maintenance: (partition for scope only)

(1) add a partition: the partition range can only be increased upwards, not less than the original partition:

Alter table niegc_part add partition part_03 values less than (maxvalue)

(2) merge partitions: (the merged partition must refer to the next partition of the last big value)

Alter table niegc_part merge partitions part_02,part_03 into partition part_03

(3) Delete a partition:

Alter table niegc_part drop partition part_01

VI. Summary:

It should be noted that when giving examples to illustrate the transaction operation of the partition table, this paper specifies the partition, because the partition is specified, and the system only operates the records of the partition when it is executed, which improves the speed of data processing. It is also possible to manipulate data directly without specifying partitions. Indexing and multiple indexes on partitioned tables are used the same as non-partitioned tables. In addition, because the index of the partition may be affected to a certain extent when maintaining the partition, it may be necessary to rebuild the index after maintenance. Please refer to the documents in the index part of the google partition table for relevant content.

Oracle Partition Command set

-- Create table (create partition table)

Create table BILL_MONTHFEE_ZERO

(

SERV_ID NUMBER (20) not null

BILLING_CYCLE_MONTH NUMBER (6) not null

DATE_TYPE NUMBER (1)

ACC_NBR VARCHAR2 (80)

)

Partition by range (BILLING_CYCLE_MONTH)

(partition packs 200407 values less than (200407)

Tablespace TS_ZIKEN

Storage (initial 100k next 100k minextents 1 maxextents unlimited pctincrease 0)

Partition packs 200408 values less than (200408)

Tablespace TS_ZIKEN

Storage (initial 100k next 100k minextents 1 maxextents unlimited pctincrease 0)

Create index idx_bill_monthfee_zero_idx01 on bill_monthfee_zero (billing_cycle_month)

Tablespace TS_ZIKEN_idx

Storage (initial 100k next 100k minextents 1 maxextents unlimited pctincrease 0) nologging

Grant all on bill_monthfee_zero to dxsq_dev

-- add partition tables

Alter table BILL_MONTHFEE_ZERO add Partition p_200409

Values less than (200409) tablespace ts_ziken

-- Delete a partition

Alter table part_tbl drop Partition part_tbl_08

-- divide a partition into two partitions

Alter table bill_monthfee_zero split Partition packs 200409 at (200409)

Into (Partition packs 200409 / 1 tablespace ts_ziken

Partition packs 200409 / 2 tablespace ts_ziken_idx)

-- merge partitions

ALTERTABLE bill_monthfee_zero

MERGE PARTITIONS p_200408, p_200409 INTOPARTITION p_all

-rename the partition

Altertable bill_monthfee_zero rename Partition p_200408 to p_fee_200408

-- change partition to tablespace

Altertable bill_monthfee_zero move Partition p_200409

Tablespace ts_ziken_01 nologging

-- query specific partitions

Select count (*) from BILL_MONTHFEE_ZERO partition (pendant 200407)

-- add data

Insert into bill_monthfee_zero select * from bill_monthfee_zero partition (pendant 200407)

-- Export of partition tables

Userid=dxsq/teledoone@jndxsq154

Buffer=102400

Tables=bill_monthfee:P_200401

File=E: "exp_para" exp_dxsq_tables.dmp

Log=E: "exp_para" exp_dxsq_tables.log

Tips:

Delete a field from the table:

Alter table bill_monthfee_zero set unused column date_type

Add a field: alter table bill_monthfee_zero add date_type number (1)

Display partition table information

Displays all partition table information accessible to the current user: ALL_PART_TABLES

Displays information about all partitioned tables for the current user: USER_PART_TABLES

Display table partition information displays detailed partition information for all database partition tables: DBA_TAB_PARTITIONS

Displays detailed partition information for all partition tables that the current user can access: ALL_TAB_PARTITIONS

Displays detailed partition information for all partition tables of the current user: USER_TAB_PARTITIONS

Display subpartition information displays the subpartition information of all combined partition tables in the database: DBA_TAB_SUBPARTITIONS

Displays subpartition information for all combined partition tables that the current user can access: ALL_TAB_SUBPARTITIONS

Displays the subpartition information of all combined partition tables for the current user: USER_TAB_SUBPARTITIONS

Show partition column displays partition column information for all partition tables in the database: DBA_PART_KEY_COLUMNS

Displays partition column information for all partition tables that the current user can access: ALL_PART_KEY_COLUMNS

Displays partition column information for all partition tables of the current user: USER_PART_KEY_COLUMNS

Show sub-partition column displays the sub-partition column information of all partition tables in the database: DBA_SUBPART_KEY_COLUMNS

Displays the sub-partition column information of all partition tables that the current user can access: ALL_SUBPART_KEY_COLUMNS

Displays sub-partition column information for all partition tables of the current user: USER_SUBPART_KEY_COLUMNS

After reading this article, I believe you have a certain understanding of "how to create partition tables by time and ID in the database". If you want to know more about it, welcome to follow the industry information channel. Thank you for your reading!

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.