oracle 11g supports automatic partitioning, but partitioning must be set when creating tables.

If an existing table needs to be partitioned, it is necessary to rename the current table, create a new table, copy the data to the new table, and then delete the old table.

1. Why Partition?

1. ORACLE is recommended to use partitioned tables when a table is larger than 2G.

2. This table is mainly for query, and can be queried by partition. It only modifies the data of the latest partition, and does not do much to delete and modify the previous partition.

3. The query is slow when the amount of data is large.

4. Maintainability and scalability: New partition table feature in 11g: Partition has always been a proud technology of Oracle database. It is the existence of partition that makes it possible for Oracle to process massive data efficiently. In Oracle 11g, partition technology is again available in terms of usability and scalability. Enhanced.

5. Consistent with sql of common tables, there is no need to modify our code because common tables change partitioned tables.

2. How to divide oracle 11g automatically by day, week, month and year

2.1 Created annually

numtoyminterval(1, 'year') 

--Create partition tables by year
create table test_part
(
   ID NUMBER(20) not null,
   REMARK VARCHAR2(1000),
   create_time DATE
)
PARTITION BY RANGE (CREATE_TIME) INTERVAL (numtoyminterval(1, 'year'))
(partition part_t01 values less than(to_date('2018-11-01', 'yyyy-mm-dd')));

--Create primary keys
alter table test_part add constraint test_part_pk primary key (ID) using INDEX;
-- Create/Recreate indexes 
create index test_part_create_time on TEST_PART (create_time);

2.2 Created monthly

numtoyminterval(1, 'month')

--Create partition tables monthly
create table test_part
(
   ID NUMBER(20) not null,
   REMARK VARCHAR2(1000),
   create_time DATE
)
PARTITION BY RANGE (CREATE_TIME) INTERVAL (numtoyminterval(1, 'month'))
(partition part_t01 values less than(to_date('2018-11-01', 'yyyy-mm-dd')));

--Create primary keys
alter table test_part add constraint test_part_pk primary key (ID) using INDEX;

2.3 Created by Day

NUMTODSINTERVAL(1, 'day')

--Create partition tables by day
create table test_part
(
   ID NUMBER(20) not null,
   REMARK VARCHAR2(1000),
   create_time DATE
)
PARTITION BY RANGE (CREATE_TIME) INTERVAL (NUMTODSINTERVAL(1, 'day'))
(partition part_t01 values less than(to_date('2018-11-12', 'yyyy-mm-dd')));

--Create primary keys
alter table test_part add constraint test_part_pk primary key (ID) using INDEX;

2.4 Created by Week

NUMTODSINTERVAL (7, 'day')

--Create partition tables by week
create table test_part
(
   ID NUMBER(20) not null,
   REMARK VARCHAR2(1000),
   create_time DATE
)
PARTITION BY RANGE (CREATE_TIME) INTERVAL (NUMTODSINTERVAL (7, 'day'))
(partition part_t01 values less than(to_date('2018-11-12', 'yyyy-mm-dd')));

--Create primary keys
alter table test_part add constraint test_part_pk primary key (ID) using INDEX;

2.5 Test

You can add several pieces of data to see the effect, and oracle automatically adds partitions.

--Query how many partitions the current table has
select table_name,partition_name from user_tab_partitions where table_name='TEST_PART';

--Query one of the tables( SYS_P21)Data in
select * from TEST_PART partition(SYS_P21);

3. The difference between numtoyminterval and numtodsinterval

3.1 numtodsinterval (< x >, < c >), x is a number, c is a string.

Convert x to interval day to second data type.

Commonly used units are ('day','hour','minute','second').

Test it:

 select sysdate, sysdate + numtodsinterval(4,'hour') as res from dual;

Result:

3.2 numtoyminterval (<x>,<c>)

Convert x to interval year to month data type.

Commonly used units are ('year','month').

Test it:

select sysdate, sysdate + numtoyminterval(3, 'year') as res from dual;

Result:

5. Partitioning existing tables

You need to back up the table first, then create the table, copy the data, and delete the backup table.

-- 1. rename
alter table test_part rename to test_part_temp;

-- 2. Establish partition table
create table test_part
(
   ID NUMBER(20) not null,
   REMARK VARCHAR2(1000),
   create_time DATE
)
PARTITION BY RANGE (CREATE_TIME) INTERVAL (numtoyminterval(1, 'month'))
(partition part_t1 values less than(to_date('2018-11-01', 'yyyy-mm-dd')));


-- 3. Create primary keys
alter table test_part add constraint test_part_pk_1 primary key (ID) using INDEX;

-- 4. take test_part_temp Data migration from tables to test_part In the table
insert into test_part_temp select * from test_part;

-- 5. Setting indexes for partitioned tables
-- Create/Recreate indexes 
create index test_part_create_time_1 on TEST_PART (create_time); 

-- 6. Delete old test_part_temp surface
drop table test_part_temp purge;

-- 7. The function is to allow partitioning keys of partitioned tables to be updated.
-- When a row is updated, if the partitioned column is updated and the updated column does not belong to the original partition,
-- If this option is turned on, the row will be removed from the partition. delete Drop it and add it to the updated partition, which happens rowid Change.
-- Equivalent to an implicit delete + insert ，But it won't trigger insert/delete Trigger.
alter table test_part enable row movement;

6. Viewing the number of partition data and deleting partitions

select count(*) as record number from table name partition (partition name);

Note the brackets below.

- Delete partitions and automatically delete data

alter table tableName DROP PARTITION partionName;  

- Delete only data

alter table tableName TRUNCATE PARTITION partionName;  

A friend's new public number, help publicize it, will occasionally push solutions to problems encountered in development, and will share some development videos. Information, etc. Please pay attention to it. Thank you.