Oracle Partition Table

Official Document: http://docs.oracle.com/cd/E11882_01/server.112/e40540/schemaob.htm#CNCPT1514

1. Oracle supports 1024K-1 partitions, and tables with a long or long raw data type are not allowed to create partitions. CLOB and Bclob can.

When to partition a table:

① tables larger than 2GB

② historical data is only used for querying, and is constantly inserting new data into the table

1.1 Range Partition

The range partition is the most common type of partition, typically used to partition dates, you must declare the values less than clause, and MaxValue represents the maximum value.

Create Tabletest_range_table (enamevarchar2( -), Deptno Number, hire_date date) partition byRange (hire_date) (Partition before_2014ValuesLess Than (To_date ('2014-01-01','YYYY-MM-DD')), Partition before_2015ValuesLess Than (To_date ('2015-01-01','YYYY-MM-DD')), Partition Before_foreverValuesLess than (MaxValue));

As above SQL creates a new partition table test_range_table, divides the table into three partitions by partitioning the Hire_date partition: before_2014 (hire_date before 2014), before_2015 (hire_ Date between 2014 and 2015), Before_forever (hire_date after 2015 years). Partitioned tables and partitions can be user_tab_partitions,dba_tab_partitions,all_tab _partitions query.

The 1.2 list partition organizes the seemingly unordered data according to a certain rule, and the list distribution does not support partitioning multiple columns, and it supports setting multiple different values for a single partition. If you insert a value that is not defined in the list partition to the list partition table, the default keyword avoids the problem. For partitioned table test_list_table, assuming that the other_region partition is not specified, execute

Insert  into Values (' Zhang San ',ten,' Zhang San ');

Error: "ORA-14400: the inserted partition keyword is not mapped to any partition"

--list partition TableCreate Tabletest_list_table (enamevarchar2( -), Deptno Number, Regionvarchar2( -)) partition byList (region) (Partition China_regionValues(' China'), Partition japan_regionValues('Japan'), Partition europe_regionValues('France','Germany'), partition other_region values (default))

1.3 Hash partition when new, you only need to specify the partition column and the number of partitions, you can also specify the partition table space.

Create Table varchar2 (+),        varchar2 (+),       hire_date date)          by Hash (last_name)        4 In (tb_01,tb_02,tb_03,tb_04);         

1.4 Range-hash Partition, sub-partition information can be queried by user_tab_subpartitions,all_tab_subpartitions,dba_tab_subpartitions.

--Range-hash PartitionCreate Tabletest_range_hash_table (first_namevarchar2( -), last_namevarchar2( -), hire_date date) partition byRange (hire_date)subpartition  byHash (last_name) subpartition Template (
Subpartition SP1, subpartition sp2,subpartition sp3,subpartition SP4) (Partition before_2014ValuesLess Than (To_date ('2014-01-01','YYYY-MM-DD')), Partition before_2015ValuesLess Than (To_date ('2015-01-01','YYYY-MM-DD')), Partition Before_foreverValuesLess than (MaxValue))

1.5 Range-list Partition

Create Tabletest_range_list_table (enamevarchar2( -), regionvarchar2( -), hire_date date) partition byRange (hire_date) subpartition byList (region) subpartition template (subpartition china_regionValues(' China'), Subpartition japan_regionValues('Japan'), Subpartition europe_regionValues('France','Germany'), Subpartition other_regionValues(default)) (Partition before_2014ValuesLess Than (To_date ('2014-01-01','YYYY-MM-DD')), Partition before_2015ValuesLess Than (To_date ('2015-01-01','YYYY-MM-DD')), Partition Before_foreverValuesLess than (MaxValue));

2. Partitioned indexes

The partitioning index classifies two types of global indexes (based on the entire table) and local indexes (table-based partitioning), which generally means that the OLTP system should use the global indexes, and the Data warehouse or DSS should use the local indexes. You can decide which partition index to use in the following order:

Step-1: If the partition column is a subset of the indexed column, end with local index; otherwise Step-2

Step-2: If the index is unique, end with global index; otherwise Step-3

Step-3: If maintenance performance is more important, end with local index, otherwise Step-4

Step-4: If OLTP users value response time with global index, if data Warehouse users value throughput with local index

2.1 LOCAL Partitioned INDEX

The local index and table partitions are one by one corresponding

Local nonprefixed index: A simple index listed as a non-partitioned column, indexed as a composite index of a non-partitioned column, not necessarily allowed for partition pruning

Local prefixed index: A simple index listed as a partitioned column, indexed as a composite index of partitioned columns, allowing partition pruning

Advantages of local Index:

① when data for one partition fails, other partitions are not affected

② When moving a table partition or data being moved out of a partition, only the index of the relevant partition needs to be rebuilt, and for global, all partition indexes are rebuilt

③ when a point-in-time recovery occurs, you can recover only the index of the relevant partition, and you do not need to rebuilit all the partition indexes.

Create Index  on test_range_table (hire_date) local;

2.2 GLOBAL Partitioned INDEX

Global Range Partitioned index: range-based global index

Global Hash partitioned index: Hash-based global index

The global index is independent of the partitioned table, assuming that the table is based on a hire_date partition of 3 (before_2014,before_2015,before_forever), you can create four partitions based on hire_date Global_ Index (BEFORE_2013,BEFORE_2014,BEFORE_2015,BEFORE_FOREVER), global index can also be arbitrarily partitioned for columns that are not column-partitioned.

--Global Range Index--DROP Index TEST_RANGE_TABLE_LOCAL_N1Create IndexTest_range_table_gl_range ontest_range_table (hire_date) Global partition byRange (hire_date) (Partition before_2013ValuesLess Than (To_date ('2013-01-01','YYYY-MM-DD')), Partition before_2014ValuesLess Than (To_date ('2014-01-01','YYYY-MM-DD')), Partition before_2015ValuesLess Than (To_date ('2015-01-01','YYYY-MM-DD')), Partition Before_foreverValuesLess than (MaxValue))--Global Hash Index--DROP index Test_range_table_gl_range;Create IndexTest_range_table_gl_hash ontest_range_table (hire_date) Global partition byHash (hire_date) partitions4;

2.3 GLOBAL nonpartitioned INDEX

2.4 About partitioned Indexes

Partitioned indexes can be queried by user_ind_partitions,user_dba_partitions,user_all_partitions.

You can only create a local type of bitmap index for a partitioned table.

The global index can be a unique index, and local index can be a unique index only if the indexed column is a partitioned column.

Application of partitioned indexes in OLTP systems:

①global index and unique local index performance is superior

② Local index performance is better when there are partitions or sub-partitions maintained on partitioned tables

When the ③ index grows monotonically, hash-partitioned Global index is better because most of the index insertions are to the right of the index.

The application of partition index in DSS or Data warehouse:

①local index is more convenient when loading data or partitioning maintenance

②local index can scan multiple index partitions in parallel

3. Partitioning improves system performance

3.1 PARTITION Pruning (partition Trim)

When a predicate condition contains a partition column, Oracle specifies that the unwanted partition be cut off. When a function is applied to a partitioned column, the partition pruning is invalidated; Similarly, when a function is applied to an indexed column, the index is invalidated unless the index is data-based.

3.2 Partition-wise JOINS

When two partitioned tables are connected, the connection column is exactly the partition column of the two partition table, then the connection operation becomes concurrent operation between multiple partitions and multiple partitions.??

3.3 PARALLEL DML

Oracle Partition Table