Explain the table partitions by day, by month, and by year in oracle.

Explain the table partitions by day, by month, and by year in oracle.

Partition Table concept:

When the amount of data in a table increases, the speed of data query slows down, and the performance of applications degrades. In this case, you should consider partitioning the table. After the table is partitioned, the logical table is still a complete table, but the data in the table is physically stored in multiple tablespaces (physical files, it does not scan the entire table every time.

Advantages of Partitioned Tables:

1) Improved query performance: You can query partition objects by searching only the partitions you are concerned about, improving the search speed.

2) Enhanced availability: If a partition of the table fails, the data of the table in other partitions is still available;

3) Easy Maintenance: If a partition of the table fails, you only need to fix the partition to fix the data;

4) Balanced I/O: You can map different partitions to disks to balance I/O and improve the overall system performance.

Partition Table types:

1. Range partitioning

Concept: range partitions map data to each partition based on the range. This range is determined by the partition key you specified when creating the partition. This partitioning method is the most commonly used, and the partition key usually uses the date. For example, you may partition the sales data by month.

-- Partitions by row

SQL> CREATE TABLE part_andy1

2 (

3 andy_ID number not null primary key,

4 FIRST_NAME VARCHAR2 (30) not null,

5 LAST_NAME VARCHAR2 (30) not null,

6 PHONE VARCHAR2 (15) not null,

7 EMAIL VARCHAR2 (80 ),

8 status char (1)

9)

10 partition by range (andy_ID)

11 (

12 PARTITION PART1 values less than (10000 ),

13 PARTITION PART2 values less than (20000)

14 );

Table created.

-- Partition by Time

SQL> CREATE TABLE part_andy2

2 (

3 ORDER_ID NUMBER (7) not null,

4 ORDER_DATE DATE,

5 OTAL_AMOUNT NUMBER,

6 CUSTOTMER_ID NUMBER (7 ),

7 paid char (1)

8)

9 partition by range (ORDER_DATE)

10 (

11 PARTITION p1 values less than (TO_DATE ('1970-10-1 ', 'yyyy-mm-dd ')),

12 PARTITION p2 values less than (TO_DATE ('1970-10-1 ', 'yyyy-mm-dd ')),

13 PARTITION p3 values less than (TO_DATE ('1970-10-1 ', 'yyyy-mm-dd ')),

14 partition p4 values less than (maxvalue)

15 );

Table created.

2. Hash Partition

Concept:

Hash partitions can be used for tables that cannot be effectively partitioned, which will help improve performance. Hash partitions evenly distribute data in the table to the specified partitions. The partition where the column is located is automatically allocated based on the hash value of the partition column, therefore, you cannot control or know which record will be stored in which partition. The hash partition can also support multiple dependent columns.

Note:

The primary mechanism of hash partitions is to calculate the partition to which a specific record should be inserted based on the hash algorithm. The most important part of the hash algorithm is the hash function. If you want to use hash partitions in Oracle, you only need to specify the number of partitions. We recommend that the number of partitions use the N power of 2 to make the data distribution in each shard more even.

-- Partition by hash

SQL> create table part_andy3

2 (

3 transaction_id number primary key,

4 item_id number (8) not null

5)

6 partition by hash (transaction_id)

7 (

8 partition part_01,

9 partition part_02,

10 partition part_03

11 );

Table created.

3. List partitions

Concept:

List partitions also need to specify the column value. The partition value must be explicitly specified. Only one partition column can be specified. Multiple Columns cannot be specified as partition dependency columns in the range or hash partition, however, the corresponding values of a single partition can be multiple.

Note:

When partitioning, you must determine the values that may exist in the partition column. Once the inserted column value is not within the partition range, insertion/update will fail. Therefore, we recommend that you use list partitioning, create a default partition to store records that are not within the specified range, similar to the maxvalue partition in the range partition.

-- Partition by list

SQL> create table part_andy4

2 (

3 id varchar2 (15 byte) not null,

4 city varchar2 (20)

5)

6 partition by list (city)

7 (

8 partition t_list025 values ('beijinging '),

9 partition t_list372 values ('shanghai '),

10 partition t_list510 values ('changsha '),

11 partition p_other values (default)

12 );

Table created.

4. Combined partitioning

Oracle10g provides two partition combinations

-Range-hash

SQL> create table part_andy5

2 (

3 transaction_id number primary key,

4 item_id number (8) not null,

5 item_description varchar2 (300 ),

6 transaction_date date

7)

8 partition by range (transaction_date) subpartition by hash (transaction_id)

9 (

10 partition part_01 values less than (TO_DATE ('2017-10-1 ', 'yyyy-mm-dd ')),

11 partition part_02 values less than (TO_DATE ('2017-10-1 ', 'yyyy-mm-dd ')),

12 partition part_03 values less than (maxvalue)

13 );

Table created.

-Range-list

SQL> CREATE TABLE SALES

2 (

3 PRODUCT_ID VARCHAR2 (5 ),

4 SALES_DATE DATE,

5 SALES_COST NUMBER (10 ),

6 STATUS VARCHAR2 (20)

7)

8 partition by range (SALES_DATE) subpartition by list (STATUS)

9 (

10 PARTITION P1 values less than (TO_DATE ('2017-10-1 ', 'yyyy-mm-dd '))

11 (SUBPARTITION P1SUB1 VALUES ('active'), SUBPARTITION P1SUB2 VALUES ('inactive ')

12), PARTITION P2 values less than (TO_DATE ('2017-10-1 ', 'yyyy-mm-dd '))

13 (

14 SUBPARTITION P2SUB1 VALUES ('active '),

15 SUBPARTITION P2SUB2 VALUES ('inactive ')

16)

17 );

Table created.

Four combinations are added for Oracle11g

-RANGE

-LIST-RANGE

-LIST-HASH

-LIST

The virtual column in Oracle 11g. The partition key of the partition table must exist physically before the 11g. A virtual column is provided at the beginning of 11g and can be used as the partition key.

-- Partitions by week

SQL> CREATE TABLE part_andy6

2 (

3 getdate date not null,

4 wd number generated always as (TO_NUMBER (TO_CHAR (getdate, 'D') VIRTUAL

5)

6 partition by list (wd)

7 (

8 PARTITION Mon VALUES (1 ),

9 PARTITION Tue VALUES (2 ),

10 PARTITION Wed VALUES (3 ),

11 PARTITION Thu VALUES (4 ),

12 PARTITION Fri VALUES (5 ),

13 PARTITION Sat VALUES (6 ),

14 PARTITION Sun VALUES (7)

15 );

Table created.

SQL>

SQL> insert into part_andy6 (getdate) values (sysdate );

1 row created.

SQL> insert into part_andy6 (getdate) values (sysdate-1 );

1 row created.

SQL> insert into part_andy6 (getdate) values (sysdate-2 );

1 row created.

SQL> insert into part_andy6 (getdate) values (sysdate-3 );

1 row created.

SQL> insert into part_andy6 (getdate) values (sysdate-4 );

1 row created.

SQL> insert into part_andy6 (getdate) values (sysdate-5 );

1 row created.

SQL> insert into part_andy6 (getdate) values (sysdate-6 );

1 row created.

SQL> insert into part_andy6 (getdate) values (sysdate-7 );

1 row created.

-- Check that the test is successful

SQL> select * from part_andy6;

GETDATE WD

-----------------------------

16:35:07 1

16:35:07 2

16:35:07 3

16:35:07 4

16:35:07 5

16:35:07 6

16:35:07 7

16:35:08 7

8 rows selected.

Oracle Database 11g, Interval partition table, you can automatically create partitions at specified intervals based on the data loaded.

Create a partitioned table by month:

A. Create a partition table

SQL> CREATE TABLE interval_andy7 (a1 NUMBER, a2 DATE)

2 partition by range (a2)

3 INTERVAL (NUMTOYMINTERVAL (1, 'month '))

4 (PARTITION part1

5 values less than (TO_DATE ('1970-11-1 ', 'yyyy-mm-dd ')),

6 PARTITION part2

7 values less than (TO_DATE ('2017-12-1 ', 'yyyy-mm-dd '))

8 );

Table created.

Note: If the Interval partition table is not fully written, the partition is automatically generated after the related data is inserted.

B. view the partitions in the current table:

SQL> select table_name, partition_name from user_tab_partitions where table_name = 'interval _ andy7 ';

TABLE_NAME PARTITION_NAME

------------------------------------------------------------

INTERVALPART PART1

INTERVALPART PART2

C. Insert test data:

SQL> begin

2 for I in 0 .. 11 loop

3 insert into interval_andy7 values (I, add_months (to_date ('2017-11-1 ', 'yyyy-mm-dd'), I ));

4 end loop;

5 commit;

6 end;

7/

PL/SQL procedure successfully completed.

The PL/SQL process is successfully completed.

Supplement: The add_months () function obtains the month of the previous month or the next month. The negative number in the parameter indicates the forward value, and the positive number indicates the next value.

-- Last month

Select to_char (add_months (trunc (sysdate),-1), 'yyyymm') from dual;

-- Next month

Select to_char (add_months (trunc (sysdate), 1), 'yyyymm') from dual;

D. Observe the automatically created partitions:

SQL> select table_name, partition_name from user_tab_partitions where table_name = 'interval _ andy7 ';

TABLE_NAME PARTITION_NAME

------------------------------------------------------------

INTERVAL_ANDY7 PART1

INTERVAL_ANDY7 PART2

INTERVAL_ANDY7 SYS_P24

INTERVAL_ANDY7 SYS_P25

INTERVAL_ANDY7 SYS_P26

INTERVAL_ANDY7 sys_p2

INTERVAL_ANDY7 SYS_P28

INTERVAL_ANDY7 SYS_P29

INTERVAL_ANDY7 SYS_P30

INTERVAL_ANDY7 SYS_P31

INTERVAL_ANDY7 SYS_P32

TABLE_NAME PARTITION_NAME

------------------------------------------------------------

INTERVAL_ANDY7 SYS_P33

INTERVAL_ANDY7 SYS_P34

13 rows selected.

Create a partition table separated by days:

1. Create a partition table:

SQL> create table interval_andy8

2 (

3 id number,

4 dt date

5)

6 partition by range (dt)

7 INTERVAL (NUMTODSINTERVAL (1, 'day '))

8 (

9 partition p20141101 values less than (to_date ('2017-11-1 ', 'yyyy-mm-dd '))

10 );

Table created.

2. View table partitions:

SQL> select table_name, partition_name from user_tab_partitions where table_name = 'interval _ ANDY8 ';

TABLE_NAME PARTITION_NAME

------------------------------------------------------------

INTERVAL_ANDY8 P20141101

3. Insert test data:

Begin

For I in 1 .. 12 loop

Insert into INTERVAL_ANDY8 values (I, trunc (to_date ('2017-11-1 ', 'yyyy-mm-dd') + I ));

End loop;

Commit;

End;

/

The PL/SQL process is successfully completed.

4. Observe the automatically created partitions:

SQL> select table_name, partition_name from user_tab_partitions where table_name = 'interval _ ANDY8 ';

TABLE_NAME PARTITION_NAME

------------------------------------------------------------

INTERVAL_ANDY8 P20141101

INTERVAL_ANDY8 SYS_P35

INTERVAL_ANDY8 SYS_P36

INTERVAL_ANDY8 SYS_P37

INTERVAL_ANDY8 sys_spo2

INTERVAL_ANDY8 SYS_P39

INTERVAL_ANDY8 SYS_P40

INTERVAL_ANDY8 SYS_P41

INTERVAL_ANDY8 SYS_P42

INTERVAL_ANDY8 SYS_P43

INTERVAL_ANDY8 SYS_P44

TABLE_NAME PARTITION_NAME

------------------------------------------------------------

INTERVAL_ANDY8 SYS_P45

INTERVAL_ANDY8 SYS_P46

13 rows selected.