Oracle creates partitioned tables by time

The distinction between partitioned tables and table partitioning is clarified first: Table partitioning is an idea, and partitioning represents a technology implementation. When the size of the table over G can consider table partitioning, improve query efficiency, equalization IO. The Oracle partition table is the implementation of a table partition provided by an Oracle database. When the table is partitioned, it is logically still a table, the original query SQL also takes effect, and you can use partitioned queries to optimize the efficiency of SQL queries, not to scan the entire table every time

First, the partition table basic operation 1, by the time partition table creation:

CREATE TABLE T_test (   pk_id number                () not                      null,  add_date_time        date,   constraintpk_t_test Primary KEY (PK_ID)) PARTITION by RANGE (add_date_time) (partitiont_test_2013_less  VALUES less THAN (to_date (' 2013-01-01 00:00:00 ', ' Yyyy-mm-ddhh24:mi:ss ')) tablespace Ts_misps,  partitiont_test_2013 VALUES less THAN (TO_DATE (' 2014-01-01 00:00:00 ', ' yyyy-mm-ddhh24:mi:ss ')) Tablespace Ts_misps,  PARTITION t_test_2014values less THAN (to_date (' 2015-01-01 00:00:00 ', ' yyyy-mm-dd hh24:mi:ss ' )) Tablespace Ts_misps)

Where Add_date_time is a partition field, one partition per year.

Inserting 100W data

DECLARE  i    int: = 1;  yearVARCHAR2; begin  Loop year    : = Casemod (i, 3) while 0 then              ' 2012-01-14 12:00:00 ' when             1 then              ' 2013-01-14 12:00:00 '             ELSE              ' 2014-01-14 12:00:00 '            END;           INSERT into t_test values (I, to_date (year, ' Yyyy-mm-dd Hh24:mi:ss '));    Exit when i= 1000000;    I: = i + 1;  End Loop;end;

To view the partition details for a partitioned table

Select table_name,partition_name,high_value fromdba_tab_partitions where table_name= ' t_test ';

2, Partition Table modification 2.1 Add a partition

In two cases: 1. No maxvalue partition. 2. There are maxvalue partitions. The partition we created is a partition with no MaxValue.

1. No maxvalue partition to add new partition:

ALTER TABLE T_test add partition t_test_2015 valuesless THAN (to_date (' 2015-01-01 00:00:00 ', ' yyyy-mm-dd hh24:mi:ss ') TAB Lespacets_misps;

2. Add a new partition with MaxValue partition:

With MaxValue, you cannot add partition directly, but you need the Max partition split. For example, we will create a partition with the statement modified under:

CREATE TABLE T_test (   pk_id number                () not                      null,  add_date_time        date,   constraintpk_t_test Primary KEY (PK_ID)) PARTITION by RANGE (add_date_time) (partitiont_test_2013_less  VALUES less THAN (to_date (' 2013-01-01 00:00:00 ', ' Yyyy-mm-ddhh24:mi:ss ')) tablespace Ts_misps,  partitiont_test_2013 VALUES less THAN (TO_DATE (' 2014-01-01 00:00:00 ', ' yyyy-mm-ddhh24:mi:ss ')) Tablespace Ts_misps,  partitiont_test_2014 VALUES less THAN (to_date (' 2015-01-01 00:00:00 ', ' yyyy-mm-ddhh24:mi:ss ' )) tablespace Ts_misps,    PARTITION t_test_maxvalues less THAN (MAXVALUE))

Add a 2016-year partition statement as:

ALTER TABLE T_test split partition T_test_max at (to_date (' 2016-01-01 00:00:00 ', ' yyyy-mm-dd hh24:mi:ss ')] into ( Partitiont_test_2015,partition T_test_max);

2.2 Deleting a partition

ALTER TABLE T_test DROP partition t_test_2014

Note: When droppartition, the data stored in this partition will also be deleted, you want to delete the specified partition but keep the data, you should use the merge partition, the execution of this statement will cause the glocal index to fail to rebuild the global index

2.3 Merging partitions

Adjacent partitions can be merged into a single partition, the lower boundary of the new partition is the partition with the original bounding value, the upper boundary is the partition with the higher bounding value, the original local index will also merge, and the global index will be invalidated and need rebuild.

Alter  Table t_test  merge Partitions t_test_2013  , t_test_2014 into partition t_test_2013_to_2014

Second, the partition table to query

2.1 Queries

Do not use partitioned queries: Default query for all partition data

SELECT * FROM T_test

Using partitioned queries: Querying only that partition data

SELECT * from T_testpartition (t_test_2014) where Add_date_time >=to_date (' 2014-01-01 00:00:00 ', ' yyyy-mm-dd hh24:mi: SS ');

2.1 Inserting

INSERT into t_test values (I, to_date (year, ' Yyyy-mm-dd Hh24:mi:ss '));

2.1 Delete

Using partition deletion

The partition is specified when the update is made, and the data is not deleted if the query's records are not in the partition

Delete t_test partition (t_test_2013) where id=1;

Do not use partition deletion

Delete T_test  whereid=1;

2.1 Modifications

Using Partition Updates

A partition is specified when the update is made, and the data is not updated when the query's records are not in the partition

Delete t_test where id=1;update t_test partition (t_test)  set id=1 where id=2;

Do not use partitions

Delete t_test where id=1;update t_test  set id=1 where id=2;

Third, ordinary table and partition Table mutual transfer

Normal table---Partition table

1. Create a new field in the middle of the partition table (t_new)

2. Import T data into t_new

INSERT into T SELECT field1,filed2, ... from t

Rename the cousin

RENAME T to T_old;

Rename a new table

RENAME t_new to T;

This is suitable for static operations and does not guarantee data consistency. If you switch in the production environment, take advantage of the online redefine feature.

Oracle creates partitioned tables by time