Oracle-table partitioning

Common oracle table partition usage:

1. table partitioning type:

A) range Partition

B) hash Partition

C) List Partition

D) composite partition includes range-Hash partition and list-Hash partition.

2. table partitioning syntax: syntax

 

A) range partition:

Create Table sales_range

(Salesman_id number (5 ),

Salesman_name varchar2 (30 ),

Sales_mount number (10 ),

Sales_date date)

Compress

Partition by range (sales_date)

(

Partition sales_Jan_2000 values less than (to_date ('2014/1/123', 'Mm/DD/yyyy ')),

Partition sales_Feb_2000 values less than (to_date ('2014/1/123', 'Mm/DD/yyyy ')),

Partition sales_others values less than (MAXVALUE)

);

Note: 1) The maxvalue here is greater than or equal to to_date ('2014/1/2 ', 'Mm/DD/yyyy;

2) You can use user_tab_partitions or dba_tab_partitions to view related table partitions. However, some data needs to be updated by calling dbms_stats.gather_schema_stats, such as num_rows.

B) hash partition

Create table sales_hash

(Salesman_id number (5 ),

Salesman_name varchar2 (30 ),

Sales_mount number (10 ),

Sales_date date)

Partition by hash (sales_date)

Partitions 4;

Note: 1) the partitions value must be the N power of 2.

2) In user_tab_partitions or dba_tab_partition, high_value is displayed as null. The display value of range partition is different from that of range partition.

C) list partition

Create table sales_list

(Salesman_id number (5 ),

Salesman_name varchar2 (30 ),

Sales_mount number (10 ),

Sales_state varchar2 (20 ),

Sales_date date)

Partition by list (sales_state)

(Partition sales_west values ('califonie', 'hawaii '),

Partition sales_east values ('newyork ', 'florida ')

);