Oracle creates partition table by Time ____oracle

First, the distinction between partitioned tables and table partitions is clear: Table partitioning is an idea, and partitioning represents a technology implementation. Table partitioning can be considered when the size of the table is over G, improving query efficiency and balancing IO. An Oracle partitioned table is an implementation form of a table partition provided by an Oracle database. After the table is partitioned, it is still logically a table, the original query SQL is also in effect, and you can use partitioned queries to optimize the efficiency of SQL queries, not every time you scan the entire table

basic operation of partition table 1. Create by Time partition table:

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.

Insert 100W Data

DECLARE
  i    int: = 1;
  YearVARCHAR2 (a);
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

To view the partitions of a partitioned table for more information

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. There are no maxvalue partitions. 2. There are maxvalue partitions. The partition we created is a partition without MaxValue.

1. No maxvalue partitions Add new partitions:

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, have maxvalue partition add new partition:

With MaxValue, you can't add partition directly, but you need Max partition split. For example, we will modify the statement of the partition created by:

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 to:

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: When 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 the merge partition, execution of the statement will cause Glocal index failure to rebuild the global index 2.3 Merging Partitions

Adjacent partitions can be merged into a partition, the lower bounds of the new partition is the original boundary value of the partition, the upper bounds of the original boundary value of a higher partition, the original local index will also merge, the global index will be invalidated, 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 for query 2.1 Query

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

SELECT * FROM T_test

Using partitioned queries: Query only the partition's 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

Use partition deletion

The partition is specified when it is updated, and the data is not deleted when the record is not in the partition based on the query

Delete t_test partition (t_test_2013) where id=1;

Do not use partition deletion

Delete T_test  whereid=1;

2.1 Modification

Using a partition update

The partition is specified when it is updated, and the data is not updated when the record is not in the partition based on the query

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, the common table and the partition table mutual turn

Normal table-> partition table

1, create a new field like 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;

Renaming a new table

RENAME t_new to T;

This is appropriate for static operations and does not guarantee data consistency. If you switch in a production environment, use the online redefine feature