Create a partition table by time in oracle

First, we should clarify the differences between partition tables and table partitions: Table partitions are an idea, and partition represents a technical implementation. When the table size exceeds G, you can consider Table Partitioning to improve query efficiency and balance IO. Oracle Partition Table is an implementation form of table Partitions provided by the oracle database. After the table is partitioned, it is still a table logically, and the original query SQL is also generated

First, we should clarify the differences between partition tables and table partitions: Table partitions are an idea, and partition represents a technical implementation. When the table size exceeds G, you can consider Table Partitioning to improve query efficiency and balance IO. Oracle Partition Table is an implementation form of table Partitions provided by the oracle database. After the table is partitioned, it is still a table logically, and the original query SQL is also generated

First, we should clarify the differences between partition tables and table partitions: Table partitions are an idea, and partition represents a technical implementation. When the table size exceeds G, you can consider Table Partitioning to improve query efficiency and balance IO. Oracle Partition Table is an implementation form of table Partitions provided by the oracle database. After the table is partitioned, the logic is still a table. The original query SQL takes effect as well. You can also use partition query to optimize the SQL query efficiency, so that the entire table is not scanned every time.

I. Partition Table basic operations 1. Create a partition table by Time:

create table t_test (   pk_id                number(30)                      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)

Here, add_date_time is the partition field, which is partitioned every year.

Insert million data

declare  i    int := 1;  yearVARCHAR2(20);begin  loop    year := CASEmod(i, 3)             WHEN 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;

View partition details of a partition table

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

2. Change Partition Table 2.1 to add a partition

There are two cases: 1. There is no maxvalue partition. 2. maxvalue partitions are available. The partition we created is a partition without maxValue.

1. Add a new partition without the maxvalue 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')) TABLESPACETS_MISPS ;

2. Add a new partition with a maxvalue partition:

With maxvalue, you cannot directly add partition. Instead, you need to split the max partition. For example, modify the Statement of the created partition:

create table t_test (   pk_id                number(30)                      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 partition statement for 2016 as follows:

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 Delete a partition

alter table t_test drop partition t_test_2014

Note: During droppartition, the data stored in the partition will also be deleted. your intention is to delete the specified partition but keep the data. You should use merge partition, if you execute this statement, the glocal index becomes invalid and you need to re-create the global index.

2.3 merge partitions

Adjacent partitions can be merge as a partition. The lower boundary of the new partition is a partition with a lower boundary value, and the upper boundary is a partition with a higher boundary value. The original local indexes will also be merged, the global index is invalid and requires a rebuild.

Alter  table t_test  merge partitions t_test_2013  ,t_Test_2014 into partition t_Test_2013_to_2014

2. query Partitioned Tables

2.1 Query

Query without partitions: All partition data is queried by default.

select * from t_test

Query by partition: only query the 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 Insert

insert into t_test values(i, to_date(year,'yyyy-mm-dd hh24:mi:ss'));

2.1 Delete

Delete partitions

Partitions are specified during update, but data is not deleted when the queried records are not in the partition.

delete t_test partition(t_test_2013) where id=1;

Delete without partitions

delete t_test  whereid=1;

2.1 modify

Use partition update

Partitions are specified during update, but data is not updated when the queried 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;

Iii. Conversion Between Common tables and partition tables

Normal table> Partition Table

1. Create a partitioned table (T_NEW) with the same fields)

2. Import T data to T_NEW

INSERT INTO T SELECT field1,filed2, …from T

Rename an old table

RENAME T TO T_OLD;

Rename a new table

RENAME T_NEW TO T;

This method is suitable for static operations and does not guarantee data consistency. If switching is performed in the production environment, the online redefinition function is used.