Oralce horizontal table sharding

At work, a table inserts a lot of data every day, and the amount of data will become larger than millions or even tens of millions of records over time. In this case, can we split the table? The answer is that Oracle has provided a horizontal split for a long time.

The following three partitions are provided in oracle 10g:

1) Range partition)

2) Hash partition)

3) List partition)

As the level is limited, the following focuses on range partitioning.

Range partitioning, as its name implies, is to divide data based on the value of a field in the table with a fixed range as a partition.

1.1 you can create a table by partitioning the sequence number.

Create table test1

1. (
2. ID NUMBERNot Null,
3. JQBH VARCHAR2 (20 ),
4. Fw.varchar2 (20)
5. )
6. PartitionByRange (ID)
7. (
8. Partition part_01ValuesLess than (30000000) tablespace WLKP_FP_DATA_2011,
9. Partition part_02ValuesLess than (60000000) tablespace WLKP_FP_DATA_2012,
10. Partition part_03ValuesLess than (maxvalue) tablespace WLKP_FP_DATA_2013
11. );

From the above, we can see that the 1-30000000 data is stored in the part_01 partition table, and the 30000000-60000000 data is stored in the part_02 partition table. This achieves the goal of horizontal table sharding.

1.2 table creation by date Partition

Create table test2

1. (
2. ID NUMBERNot Null,
3. JQBH VARCHAR2 (20 ),
4. FJH VARCHAR2 (20 ),
5. KPRQDATE
6. )
7. PartitionByRange (KPRQ)
8. (
9. Partition part_01ValuesLess than (TO_DATE ('2017-01-01 00:00:00','Syyyy-MM-DD HH24: MI: ss','Nls _ CALENDAR = GREGORIAN') Tablespace WLKP_FP_DATA_2011,
10. Partition part_02ValuesLess than (TO_DATE ('2017-01-01 00:00:00','Syyyy-MM-DD HH24: MI: ss','Nls _ CALENDAR = GREGORIAN') Tablespace WLKP_FP_DATA_2012,
11. Partition part_03ValuesLess than (MAXVALUE) tablespace WLKP_FP_DATA_2013
12. );

From the preceding table creation statement, we can see that data tables are stored by year.

In the above method, we can split tables with large data into other partition tables based on the ID time.

In the above method, table sharding is taken into account when the system designs the table structure. In actual work, due to insufficient system data volume, horizontal table sharding is not considered during table design. Is there any way to split and transform existing tables. The answer is yes.

Now the table WLKP_FP_KJ is extended

1. Create a New tablespace

Create tablespace "WLKP_FP_DATA_2010"

Tablespace creation code

1. LOGGING
2. DATAFILE'E: \ oracle \ product \ 10.2.0 \ oradata \ wlkp \ WLKP_FP_DATA_2010.ORA'
3. SIZE 5 M REUSE AUTOEXTEND
4. On next 1024 k maxsize 32767 M EXTENT MANAGEMENT LOCAL
5. Segment space management auto;
6. Commit;

Here we create a new WLKP_FP_DATA_2010 tablespace. The extended WLKP_FP_KJ table over the years can also be created to store future data in tables 2011, 2012, 2013...

2. Create a new table WLKP_FP_KJ_TEST.

Table structure is partitioned by 1.2 date

3. Copy the old table data after the new table is created.

Insert into wlkp_fp_kj_test select * from wlkp_fp_kj

4. Delete the old table wlkp_fp_kj

5. Rename wlkp_fp_kj_test to wlkp_fp_kj.

In this way, we can explain that wlkp_fp_kj is stored in different tablespaces by time.

Query Which partition table the data is stored in.

Select * from WLKP_FP_KJ partition (WLKP_FP_DATA_2011 );