Shulou(Shulou.com)05/31 Report--

This article mainly explains "what is the use of the partition table of Oracle". The content of the explanation is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn "what is the use of the partition table of Oracle"?

Here is a simple experiment on the partition table:

First query the table structure of the partitioned table you need:

Sys@FX1TEST > desc fqtbb Name Null? Type- -ID NUMBER YXTIME DATE HASH VARCHAR2 (50) GOODLOOK VARCHAR2 (20)

Query the current data of the table:

Sys@FX1TEST > select * from fqtbb ID YXTIME HASH GOODLOOK- 1 2011-01-02 00:00:00 java111 no 1 2011-04-02 00:00:00 java121 no 1 2011-05-02 00:00:00 java131 no 1 2012-02-02: 00:00 java141 no 1 2013-01-02 00:00:00 java1ds no 1 2012-06-04 00:00:00 java1da no 1 2013-12-21 00:00:00 java771 no 1 2013-02-02 00:00:00 java771 no 1 2014-06-25 00:00:00 java661 no 1 2014-01-02 00:00:00 java156 no 1 2015-05-22 00:00:00 java132 no 1 2015-06-07 0000 java1da no 00 java134 no 1 2016 -04-21 00:00:00 java152 yes 1 2016-11-11 00:00:00 java761 yes 1 2016-01-21 00:00:00 java171 yes 1 2017-04-21 00:00:00 java711 yes 1 2017-05-05 00:00:00 java717 yes 12 017-01-02 00:00:00 java174 yes 1 2018-03-01 00:00:00 java172 yes 1 2018-01-23 00 java176 yes 1 2018-12-22 00 java166 yes21 rows selected.

Here are some commonly used zoning methods

Range partition: use the partition key to define the range, according to the actual value, partition selection, this range is generally stored in digital form, such as time, score, salary and so on, so it is inevitable that this partition method can not do uniform partition.

Here we divide it by time range:

Create table fqtbb_part (id number,yxtime date,hash varchar (50), goodlook varchar (20) PARTITION BY RANGE (yxtime) (PARTITION fqtbb_part1 VALUES LESS THAN (TO_DATE ('2011-01-01-01-01)) TABLESPACE test,PARTITION fqtbb_part2 VALUES LESS THAN (TO_DATE (' 2014-01-02)) TABLESPACE test1,PARTITION fqtbb_part3 VALUES LESS THAN (MAXVALUE) TABLESPACE test2), Table created.

Insert the data from the large table into the new table:

Sys@FX1TEST > insert into fqtbb_part select * from fqtbb;21 rows created.sys@FX1TEST > commit;Commit complete.

Next, query the data information for individual partitions.

Sys@FX1TEST > select * from fqtbb_part partition (fqtbb_part3) ID YXTIME HASH GOODLOOK- 1 2014-06-25 00:00:00 java661 no 1 2014-01-02 00:00:00 java156 no 1 2015-05-22 00:00:00 java132 no 1 2015-06-07 00: 00:00 java134 no 1 2016-04-21 00:00:00 java152 yes 1 2016-11-11 00:00:00 java761 yes 1 2016-01-21 00:00:00 java171 yes 1 2017-04-21 00:00:00 java711 yes 1 2017-05-05 00:00:00 java717 yes 1 2017-01-02 00:00:00 java174 yes 1 2018-03-01 00:00:00 java172 yes 1 2018-01-23 00 java176 yes 1 2018 -12-22 00:00:00 java166 yes13 rows selected.sys@FX1TEST > select * from fqtbb_part partition (fqtbb_part2) ID YXTIME HASH GOODLOOK- 1 2011-01-02 00:00:00 java111 no 1 2011-04-02 00:00:00 java121 no 1 2011-05-02 00:00:00 java131 no 1 2012-02-02: 00:00 java141 no 1 2013-01-02 00:00:00 java1ds no 1 2012-06-04 00:00:00 java1da no 1 2013-12-21 00:00:00 java771 no 1 2013-02-02 00:00:00 java771 no 8 rows selected.

Of course, if you query the information of the partition table. There are also corresponding tables for users to query. For example:

Sys@FX1TEST > select TABLE_NAME,PARTITIONING_TYPE,PARTITION_COUNT,STATUS from dba_part_tables where table_name='FQTBB_PART' TABLE_NAME PARTITIONING_TYPE PARTITION_COUNT STATUS---FQTBB_PART RANGE3 VALID

Query the tablespace in which the table partition is located:

Sys@FX1TEST > select PARTITION_NAME,NUM_ROWS,TABLESPACE_NAME,SEGMENT_CREATED from dba_tab_partitions where table_name='FQTBB_PART';PARTITION_NAME NUM_ROWS TABLESPACE SEGMENT_--FQTBB_PART1TEST YESFQTBB_PART2TEST1 YESFQTBB_PART3TEST2 YES

Column partition

Column partitions are partitioned according to the list of values specified by the partitioning key, but this partitioning method is less suitable, such as department list, project group name, and so on.

This is defined according to the goodlook key:

Create table fqtbb_parta (id number,yxtime date,hash varchar (50), goodlook varchar (20) PARTITION BY list (goodlook) (PARTITION fqtbb_part1 VALUES ('yes') TABLESPACE test,PARTITION fqtbb_part2 VALUES (' no') TABLESPACE test1,PARTITION fqtbb_part3 VALUES (default) TABLESPACE test2); Table created.

Insert data

Sys@FX1TEST > insert into fqtbb_parta select * from fqtbb;21 rows created.sys@FX1TEST > commit;Commit complete.

Query verification is performed after the partition table is created:

Sys@FX1TEST > select * from fqtbb_parta partition (fqtbb_part2) ID YXTIME HASH GOODLOOK- 1 2011-01-02 00:00:00 java111 no 1 2011-04-02 00:00:00 java121 no 1 2011-05-02 00:00:00 java131 no 1 2012-02-02: 00:00 java141 no1 2013-01-02 00:00:00 java1ds no1 2012-06-04 00:00:00 java1da no1 2013-12-21 00:00:00 java771 no1 2013-02-02 00:00:00 java771 no1 2014-06-25 00:00:00 java661 no1 2014-01-02 00:00:00 java156 no1 2015-05-22 00:00:00 java132 no1 2015-06-07 00java141 no1 00 java134 no12 rows selected .sys @ FX1TEST > select * from fqtbb_parta partition (fqtbb_part1) ID YXTIME HASH GOODLOOK- 1 2016-04-21 00:00:00 java152 yes 1 2016-11-11 00:00:00 java761 yes 1 2016-01-21 00:00:00 java171 yes 1 2017-04-21 00: 00:00 java711 yes 1 2017-05-05 00:00:00 java717 yes 1 2017-01-02 00:00:00 java174 yes 1 2018-03-01 00:00:00 java172 yes 1 2018-01-23 00:00:00 java176 yes 1 2018-12-22 00:00:00 java166 yes9 rows selected.sys@FX1TEST > select * from fqtbb_parta partition (fqtbb_part3) No rows selected

The following are common administrative actions for oracle partition tables, as follows:

Since we set the default value when we ran the partition before, we cannot increase the partition, so here is a demonstration of deleting a partition first

Sys@FX1TEST > alter table fqtbb_part drop partition fqtbb_part3;Table altered.sys@FX1TEST > select PARTITION_NAME,NUM_ROWS,TABLESPACE_NAME,SEGMENT_CREATED from dba_tab_partitions where table_name='FQTBB_PART' PARTITION_NAME NUM_ROWS TABLESPACE_NAME SEGMENT_ -FQTBB_PART1 0 TEST YESFQTBB_PART2 8 TEST1 YES

Add a partition

Sys@FX1TEST > alter table fqtbb_part add partition p3 values less than (to_date ('2019-09-09-09)); Table altered.

Truncated partition: truncated partition refers to clear data and preservation of partition structure, as follows

Sys@FX1TEST > alter table fqtbb_part truncate partition p3terTable truncated.

Merge partition: refers to merging adjacent partitions into one partition, such as 2019 and 2018, after merging, the partition will adopt the limit of 2019, and the merge will be merged into the partition of 2019;

Sys@FX1TEST > alter table fqtbb_part merge partitions fqtbb_part1,fqtbb_part2 into partition fqtbb_part2;Table altered.sys@FX1TEST > select PARTITION_NAME,NUM_ROWS,TABLESPACE_NAME,SEGMENT_CREATED from dba_tab_partitions where table_name='FQTBB_PART' PARTITION_NAME NUM_ROWS TABLESPACE_NAME SEGMENT_ -FQTBB_PART2SYSTEM YESP3SYSTEM YES

Split partition: if there is a merge, of course there is a split, which means to split a partition into two partitions, and the previous partition will not exist; as follows

Sys@FX1TEST > alter table fqtbb_part split partition fqtbb_part2 at (to_date ('2014-01-01-01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01)) into (partition p1 / mm partition p2); Table altered.sys@FX1TEST > select PARTITION_NAME,NUM_ROWS,TABLESPACE_NAME,SEGMENT_CREATED from dba_tab_partitions where table_name='FQTBB_PART' PARTITION_NAME NUM_ROWS TABLESPACE_NAME SEGMENT_ -P1SYSTEM YESP2SYSTEM YESP3SYSTEM YES

Rename partition: as the name implies, let the partition change its name

Sys@FX1TEST > alter table fqtbb_part rename partition p1 to p11 table altered. Thank you for your reading, the above is the content of "what is the use of Oracle partition table". After the study of this article, I believe you have a deeper understanding of the use of Oracle partition table, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.