AntDB-Oracle Compatibility Developer’s Manual P3–29
Published: 2023-09-06 08:53:28

#### Partitioned Tables

Partitions are useful for managing very large tables and indexes, and use a "divide and conquer" logic. Partitioning introduces the concept of a partition key, which is used to perform aggregation of data based on an interval (or range) of values, a specific list of values, or a hash function value. The benefits of partitioning are listed below in some order.

\*\*Increased data availability:\*\* This feature is applicable to any type of system, regardless of whether the system is essentially an OLTP or warehouse system.

\*\*A corresponding reduction in administrative burden due to the removal of large segments from the database. \*\* When performing administrative operations on a 100GB table (e.g., reorganizing to remove migrations to rows, or reclaiming the "empty" space on the left side of the table after "purging" old information), the former is much more burdensome than performing the same operation on individual 10GB table partitions. The former is much more burdensome than performing the same operation on individual 10GB table partitions.

\*\*Improves the performance of some queries:\*\* This benefit is mainly found in large warehouse environments, where by using partitions, large data intervals can be eliminated so that they don't have to be considered and, accordingly, not accessed at all. However, this does not work in transactional systems, which inherently access only a small amount of data.

\*\*Modifications can be distributed over multiple individual partitions, thus reducing competition on high-capacity OLTP systems:\*\* If a segment encounters intense competition, it can be divided into multiple segments, which gives a side effect: it can proportionally reduce competition.

##### Create partitioned table

**Description:** This section describes how to create a Range partition table, List partition table and Hash partition table.

For example:

1、Create a Range partitioned table sales\_range with 5 partitions.

```
CREATE TABLE sales_range
(
salesman_id  NUMBER(5), 
salesman_name VARCHAR2(30), 
sales_amount  NUMBER(10), 
sales_date    DATE
)
PARTITION BY RANGE(sales_date)
(
PARTITION sales_jan2010 VALUES LESS THAN(TO_DATE('01/02/2010','DD/MM/YYYY')),
PARTITION sales_feb2010 VALUES LESS THAN(TO_DATE('01/03/2010','DD/MM/YYYY')),
PARTITION sales_mar2010 VALUES LESS THAN(TO_DATE('01/04/2010','DD/MM/YYYY')),
PARTITION sales_apr2010 VALUES LESS THAN(TO_DATE('01/05/2010','DD/MM/YYYY')),
PARTITION sales_may2010 VALUES LESS THAN(TO_DATE('01/06/2010','DD/MM/YYYY'))
);
```

2、Create a LIST partitioned table

```
CREATE TABLE CUSTOMER 
( 
    CUSTOMER_ID NUMBER NOT NULL PRIMARY KEY, 
    FIRST_NAME  VARCHAR2(30) NOT NULL, 
    LAST_NAME   VARCHAR2(30) NOT NULL, 
    PHONE        VARCHAR2(15) NOT NULL, 
    EMAIL        VARCHAR2(80), 
    STATUS       CHAR(1) 
) 
PARTITION BY LIST (CUSTOMER_ID) 
( 
    PARTITION CUS_PART1 VALUES(100000,200000) TABLESPACE custs01, 
    PARTITION CUS_PART2 VALUES(300000) TABLESPACE custs01 
);
```

3、Create a HASH partitioned table

```
create table test_part(
  id        number,
  pro_num   varchar2(40), 
  app_type  varchar2(40)
)
partition by hash(pro_num)
(
 partition part_01,
 partition part_02,
 partition part_03,
 partition part_04,
 partition part_05
);
```

4, create a partition table with sub-partitions, that is, composite partitions, a total of seven types are supported, namely List-List, List-Range, List-Hash, Range-List, Range-Range, Range-Hash, Hash-Hash .3, Create HASH partitioned table.Create hash partitions

In the following example, a partition table of List-Range type is created, i.e. the main partition is of List type and the child partition is of Range type.

```
create table subpar_test2(
    id int,
    procuctType varchar(50),
    dt date
    ) 
    partition by list(procuctType)
    subpartition by range(dt)
       (partition p1 values ('Bus','Car','Trunk')
           (subpartition subp11 values less than(to_date('2020-01-01','yyyy-mm-dd')),
            subpartition subp12 values less than(to_date('2021-01-01','yyyy-mm-dd')),
            subpartition subp13 values less than(to_date('2022-01-01','yyyy-mm-dd'))
           ),
        partition p2 values ('Bycle','Trolley')
           (subpartition subp21 values less than(to_date('2020-01-01','yyyy-mm-dd')),
            subpartition subp22 values less than(to_date('2021-01-01','yyyy-mm-dd')),
            subpartition subp23 values less than(to_date('2022-01-01','yyyy-mm-dd'))
           ),
        partition p3 values ('Ship','Yacht','Boat')
           (subpartition subp31 values less than(to_date('2020-01-01','yyyy-mm-dd')),
            subpartition subp32 values less than(to_date('2021-01-01','yyyy-mm-dd')),
            subpartition subp33 values less than(to_date('2022-01-01','yyyy-mm-dd'))
           )
       );
```