Database Differentiation Table

Http://blog.163.com/[email protected]/blog/static/12257726201051735823602/

I. Partitioned tables, partitioned index concepts

To satisfy the management of a very large database, you need to create and use partitioned tables and partitioned indexes, and partition tables allow the partitioning of data into smaller, better-managed blocks of partitions or even sub-partitions. Each partition can be managed independently and can be used independently of other partitions, thus providing a more pro-availability and performance-based architecture.

A table or index can share the same logical properties, but can have different physical properties. For example, all partitions/sub-partitions can share the same columns and constraints, but can have different table spaces.

It is a good idea to store partitions of tables or indexes into different table spaces, with the benefit of:
Reduce the likelihood of data collisions in multiple partitions
Each partition can be backed up and restored separately
To control the mapping between partitions and disk drives (balanced I/O load)
Improve manageability, availability, and performance


Second, the partitioning method

1. Range Partitioning

You can use range partitioning when column data can be divided into logical ranges, such as months in the year. The performance is best when the data can be divided evenly across the entire range. If the partition range size is significantly different, you need to consider other partitioning methods.

When you create a range partition, you specify a partition column, which represents the partition boundary, for example:

CREATE TABLE Sales
(Invoice_no number,
Sale_year INT not NULL,
Sale_month INT not NULL,
Sale_day INT not NULL)
PARTITION by RANGE (Sale_year, Sale_month, Sale_day)
(PARTITION sale_q1 VALUES less THAN (1999, 04, 01)
Tabllespace TSA,
PARTITION sale_q2 VALUES Less THAN (1999, 07, 01)
Tabllespace TSB,
PARTITION sale_q3 VALUES Less THAN (1999, 10, 01)
Tabllespace TSC,
PARTITION sale_q4 VALUES Less THAN (2000, 01, 01)
Tabllespace TSD);

Note: For databases with different character sets, the classification sequence of the most characters is sometimes different.

2. Hash partition

The hash partitioning method is used when the data is not easily partitioned, and for performance and management reasons, you want to partition. The hash partition will divide the data equally among the specified number of partitions. To create a hash partition you need to specify the partitioning column, the number of partitions (or a separate partition description) for example:

CREATE TABLE Scubagear
(ID number,
Name VARCHAR2 (60))
PARTITION by HASH (ID)
Partitions 4
STORE in (Gear1, Gear2, Gear3, GEAR4);

3. List partition

You need to use list partitioning when you need to explicitly control how rows are mapped to partitions. You can specify a column of discrete values for the partition in each partition representation.

The difference between a list partition and a range partition, a hash partition, is that
A range partition is a column that assumes a natural range of values and cannot organize partitions that are outside the value range
Hash partitioning cannot control the partitioning of data, which is logically unnecessary

It is important to note that the list partition cannot support multi-column partitioning. Specific examples are as follows:

CREATE TABLE sales_by_region
(Deptno number,
Deptname varchar2 (20),
Quarterly_sales number (10,2),
State VARCHAR2 (2))
PARTITION by LIST (state)
(PARTITION q1_northwest VALUES (' OR ', ' WA '),
PARTITION q1_southwest VALUES (' AZ ', ' UT ', ' NM '),
PARTITION q1_northeast VALUES (' NY ', ' VM ', ' NJ '),
PARTITION q1_southeast VALUES (' FL ', ' GA '),
PARTITION q1_northcentral VALUES (' SD ', ' WI '),
PARTITION q1_southcentral VALUES (' OK ', ' TX ');

4. Combined partitioning

A combined partition uses a range partition in a partition, and a hash partition in a child partition. Combined partitioning is well suited for both historical data and bar data, improving the management of range partitioning and data placement. For example, consider the following examples:

CREATE TABLE Scubaqear (equipno number, Equipname VARCHAR (+), price number)
PARTITION by RANGE (equipno) subpartition by HASH (Equipname)
Subpartitions 8 STORE in (Ts1, TS2, Ts3, TS4)
(PARTITION p1 VALUES less THAN (1000),
PARTITION P2 VALUES Less THAN (2000),
PARTITION P3 VALUES less THAN (MAXVALUE));


Iii. Creation of partitioned tables

1. Create a range partition table

Use the partition by RANGE clause to indicate the range partition, use the partition clause to identify each partition range, and the partition clause subordinate clause to specify the physical properties that are specifically used for the partition segment, and automatically inherit the properties of the underlying table if there are no overloads.

Re-Modify the example above:

CREATE TABLE Sales
(Invoice_no number,
Sale_year INT not NULL,
Sale_month INT not NULL,
Sale_day INT not NULL)
STORAGE (INITIAL 100K NEXT 50K) LOGGING
PARTITION by RANGE (Sale_year, Sale_month, Sale_day)
(PARTITION sale_q1 VALUES less THAN (1999, 04, 01)
Tabllespace TSA STORAGE (INITIAL 20K, NEXT 10K),
PARTITION sale_q2 VALUES Less THAN (1999, 07, 01)
Tabllespace TSB,
PARTITION sale_q3 VALUES Less THAN (1999, 10, 01)
Tabllespace TSC,
PARTITION sale_q4 VALUES Less THAN (2000, 01, 01)
Tabllespace TSD)
ENABLE ROW movment;

Description: A storage parameter logging attribute was specified at the surface level, and storage parameters in partition sale_q1 were reset because there was less business in the first quarter. Alternatively, use the Enable row movment clause, which means that if the key value changes, the row is allowed to be migrated to the new partition.

Another global index that creates a range partition is as follows:

CREATE INDEX Month_ix on sales (sales_month)
Grobal PARTITION by RANGE (Sales_month)
(PARTITION Pm1_ix VALUES less THAN (2)
PARTITION Pm2_ix VALUES Less THAN (3)
PARTITION Pm3_ix VALUES Less THAN (4)
PARTITION Pm4_ix VALUES Less THAN (5)
PARTITION Pm5_ix VALUES less THAN (6)
PARTITION Pm6_ix VALUES less THAN (7)
PARTITION Pm7_ix VALUES less THAN (8)
PARTITION Pm8_ix VALUES less THAN (9)
PARTITION Pm9_ix VALUES less THAN (10)
PARTITION Pm10_ix VALUES less THAN (11)
PARTITION Pm11_ix VALUES less THAN (12)
PARTITION Pm12_ix VALUES less THAN (MAXVALUE));

2. Create a hash partition table

Use the partition by hash clause to indicate the hash partition, use the partitions clause to specify the number of partitions to create, and use the partition clause to name each partition and its table space, but only the tablespace attribute can be specified. Other attributes can only be inherited from the table hierarchy. Examples are as follows:

CREATE TABLE Dept (deptno number, dept name VARCHAR2 (32))
STORAGE (INITIAL 10K)
PARTITION by HASH (DEPTNO)
(PARTITION p1 tablespace ts1, PARTITION p2 tablespace ts2,
PARTITION P3 tablespace Ts3, PARTITION P4 tablespace TS4);

When you create a local index for the table above, Oracle automatically creates an index with the same partition as the underlying table.

CREATE INDEX Locd_dept_ix on dept (DEPTNO) LOCAL

3. Create a list partition table

Use the partition by list clause to indicate the list partition, and use the partition clause to specify a string of literal values, which is a discrete value for the partitioning column. In addition, the partition clause subordinate clause can specify a physical property that is specifically used for the partition segment, and automatically inherits the properties of the underlying table if there is no overload.

This type is basically the same as the example above, no longer re-example.

4. Create a combined partition table

Use the partition by Range clause first, and then specify a subpartition by hash clause with the partition by hash statement to follow the syntax and rules to indicate the combined partition, Each partition clause is followed by a subpartition or subpartitions clause.

You can also specify different properties for each (range) partition, and you can use the store in clause to specify different table spaces.

CREATE TABLE EMP (deptno number, EmpName VARCHAR (+), grade number)
PARTITION by RANGE (Deptno) subpartition by HASH (EmpName)
Subpartitions 8 STORE in (Ts1, Ts3, TS5, TS7)
(PARTITION p1 VALUES less THAN (+) PCTFREE 40,
PARTITION P2 VALUES Less THAN (2000)
STORE in (TS2, TS4, TS6, TS8),
PARTITION P3 VALUES less THAN (MAXVALUE)
(Subpartition p3_s1 tablespace ts4,
Subpartition p3_s2 tablespace ts5));

Another local index is created, and the segments are distributed in Tablespace Ts7, TS8, TS9

CREATE INDEX Emp_ix on EMP (DEPTNO)
LOCAL STORE in (TS7, TS8, TS9);

5. Create a sub-index structure table

You can use a range partition or a hash partition on an indexed structure table, only a range-partitioned index structure table can contain columns for LOB data types.

Creating a range partition or hash partition index structure table is similar to creating a normal table, but there are differences, except that:
You need to specify the organization INDEX clause when you create the table, and also specify the including and overflow clauses when needed
The partition or partitions clause can have a overflow subordinate clause that allows the properties of the overflow segment to be specified at the partition level

Note: The partitioned column collection of the index structure table must be a subset of the primary key columns because the rows of the index structure table are stored by the primary key index of the table, and by selecting the partition key as a subset of the primary key, the insert operation only needs to verify the uniqueness of the primary key in a single partition, so the maintenance of the partition is not dependent

A. Creating a range-partitioned index structure table

CREATE TABLE Sales (acct_no number (5),
Acct_name CHAR (30),
Amount_of_sale Number (6),
Week_no INTEGER,
Sale_detailes varchar2 (1000),
PRIMARY KEY (Acct_no, Acct_name, Week_no))
ORGANIZATION INDEX
including Week_no
OVERFLOW tablespace Overflow_here
PARTITION by RANGE (week_no)
(PARTITION VALUES less THAN (5)
Tablespace Ts1,
PARTITION VALUES less THAN (9)
Tablespace ts2 OVERFLOW tablespace overflow_ts2,
...
PARTITION VALUES less THAN (MAXVALUE)
Tablespace ts13);

Description
1. The including clause specifies that all columns after the week_no column are stored in the overflow segment.
2. Each partition has an overflow segment, which is stored in the same tablespace (Overflow_here).
3. Specify the overflow table space for each partition level through the overflow tablespace clause.


B. Creating a hash-partitioned index structure table

CREATE TABLE Sales (acct_no number (5),
Acct_name CHAR (30),
Amount_of_sale Number (6),
Week_no INTEGER,
Sale_details VARCHAR2 (1000),
PRIMARY KEY (Acct_no, Acct_name, Week_no))
ORGANIZATION INDEX
including Week_no
OVERFLOW
PARTITION by HASH (week_no)
Partitions 16
STORE in (Ts1, TS2, Ts3, TS4)
OVERFLOW STORE in (Ts3, TS6, TS9);

It is recommended that you explicitly specify the row movement enable clause when creating a hash partition index structure table with a variable partition key, because a good hash function makes the rows a good balance, so changing the primary key column is likely to move to another partition.

6. Partitioning limits for multiple data block sizes

Special attention is needed when creating partitioned objects in a table space with multiple block sizes, because partitioning objects are subject to certain restrictions when they are stored in these tablespaces. For example, the following partitions must be stored in a tablespace with the same chunk size:

General table
Index
Index structure table's primary key index segment
Overflow segment of index structure table
LOB columns that are stored outside

Database Differentiation Table