Oracle partition table and partition index ____oracle

first, the partition table

Several categories of partitioned tables:
1, Range (range) partition
Is the application of a wide range of table partitioning method, which is the range of the value of the column as a partitioning of the conditions, the record is stored in the column value of the
Range partition. For example, according to the time division, 2012 1 quarters of data into a partition, 12 2 quarter of data to the B partition,
So at the time of creation, you need to specify the columns that are based on, and the range values of the partitions, if some records are temporarily unpredictable,
You can create MaxValue partitions, and all records that are not in the specified range are stored in the MaxValue partition.
and supports specifying multiple columns as dependent columns.

Create a range partition case
What we need to specify are:
Column: partition-dependent columns (if multiple, separated by commas);
Partition: partition name;
Values less than: followed by the partition range value (if there are multiple dependent columns, the range corresponding value should also be multiple, separated by commas);
Tablespace_clause: A property of a partition, such as a table space, such as an attribute (nullable), that inherits the properties of the table space on which the base table is located by default.

Example: SQL code    create table rm_cust_month_range   (     year_month                     number (6)  NOT NULL,     ORG_CODE                      VARCHAR2 (8),     REGIE_ORG_CODE          VARCHAR2 (8),     CUST_CODE                     VARCHAR2 (  CUST_NAME    ),                   VARCHAR2 (200),       cust_licence_code       varchar2 (%)       )    Partition by range (year_month) (   partition year_month_1201 values  less than (201201)  TABLESPACE RMS_DATA,   partition year_month_1202  Values less than (201202)  TABLESPACE RMS_DATA,   partition year_month_1203  values less than (201203)  TABLESPACE RMS_DATA,   partition year_month_ 1204 values less than (201204)  TABLESPACE RMS_DATA,   Partition year_ Month_1205 values less than (201205)  TABLESPACE RMS_DATA,   partition  Year_month_1206 values less than (201206)  TABLESPACE RMS_DATA,   PARTITION  year_month_maxvalue values less than (MAXVALUE)  tablespace rms_data);   

2. Hash (hash) partition
For tables that cannot be effectively partitioned, you can use a hash partition.
The hash partition distributes the data in the table evenly to a few partitions that you specify, and the columns are allocated automatically according to the hash value of the partition column.
Therefore, you cannot control or know which records will be placed in which partition, and the hash partition can support multiple dependent columns.

Create a hash partition case
Column: partition-dependent columns (multiple support, comma separated in the middle);
Partition: Specify partitions, there are two ways:
 (1) directly specify the partition name, partition table space and other information.
 (2) Specify only the number of partitions and the table space available for use.
Example: SQL code CREATE TABLE Rm_cust_month_hash (year_month number (6) Not NULL, Org_code VARCHAR2 (8) , Regie_org_code VARCHAR2 (8), Cust_code VARCHAR2 (a), Cust_name VARCHAR2 Ence_code VARCHAR2 ()) PARTITION by HASH (year_month) (PARTITION t_hash_p1 tablespace rms_data, PARTITION t_h ASH_P2 tablespace rms_data, PARTITION t_hash_p3 tablespace);

OR: SQL code CREATE TABLE rm_cust_month_hash_2 (year_month number (6) Not NULL, Org_code VARCHAR2 (8), Regie_org_code VARCHAR2 (8), Cust_code VARCHAR2 (a), Cust_name VARCHAR2 Licence_code VARCHAR2) PARTITION by HASH (year_month) Partitions 3 STORE in (Rms_data,rms_data,rms_data);

3, List (listing) partition
The list partition is similar to the range partition and the hash partition.
This partition is similar to a range partition and requires you to specify the value of the column, but this differs from the range column value of the range partition, whose partition value
Must be explicitly specified. Also different with the hash partition, by explicitly specifying the partition value, you can control which partition the record is stored in.
It can have only one partition column, not as a range or a hash partition that specifies multiple columns as a partitioned dependency column.
Its individual partition corresponding value can be multiple, you must determine the possible value of the partitioning column when you partition, and the Insert/update fails once the column value is inserted, so it is generally recommended that you create a default partition to store those that are not in the specified range when using the list partition.
Records, similar to MaxValue partitions in a range partition.

--Create a list partition case
What we need to specify are:
Column: Partition dependent columns, note: only one;
Partition: partition name;
Literal: partition corresponding value, note: Each partition can correspond to multiple values;
Tablespace_clause: A property of a partition, such as a table space, such as an attribute (nullable), that inherits the properties of the table space on which the base table is located by default.
Example: SQL code    create table rm_cust_month_list   (     year_month         number (6)  NOT NULL,     org_code           VARCHAR2 (8),     REGIE_ORG_CODE     VARCHAR2 (8),     CUST_CODE          VARCHAR2 (  CUST_NAME       ),     VARCHAR2 (),     cust_licence_code varchar2 (  )    partition by list  (Year_month) (   partition t_list_p1 values  ( 201201,201202,201203)  TABLESPACE RMS_DATA,   partition t_list_p2 values  ( 201204,201205,201206)  TABLESPACE RMS_DATA,   partition t_list_p3 values  ( 201207,201208,201209) &NBsp tablespace rms_data,   partition t_list_pd values  (DEFAULT)  TABLESPACE  Rms_data);  

4. Combination partition: Range-hash,range-list
If a table follows a column partition, still larger, or some other requirement, you can also partition the partition in the form of sub partitions within the partition, that is, the way to combine the partitions. To be aware of the order, the root partition is only a range partition, and a child partition can be a hash partition or a list partition.

(1) Create a range-hash combination partition
What we need to specify are:
Column_list: Partition dependent columns (multiple support, comma separated in the middle);
Subpartition: Sub-partitioning method, two places:
Subpartition_by_list: The syntax is exactly the same as the list partition, except that the keyword partition is replaced with subpartition
Subpartition_by_hash: The syntax is identical to the hash partition, except that the keyword partition is replaced with subpartition
Partition: partition name;
Range_partition_values_clause: syntax for range value of range partition;
Tablespace_clause: A property of a partition, such as a table space, such as an attribute (nullable), that inherits the properties of the table space on which the base table is located by default.

Example: SQL code    create table rm_cust_month_range_hash (     YEAR_MONTH         number (6)  NOT NULL,     ORG_CODE           VARCHAR2 (8),     REGIE_ORG_CODE     VARCHAR2 (8),