Detailed examples of Oracle database tables

Advantages of using partitions:

1. Enhance usability: If a partition of a table fails, the data in other partitions is still available;

2, Maintenance Convenience: If the table of a section of the fault, need to repair the data, only repair the partition can;

3, balanced I/O: You can map different partitions to disk to balance I/O, improve the performance of the entire system;

4, Improve query performance: The query on the partition object can only search their own care, improve the speed of retrieval.

　　The Oracle Database Tutorial provides three ways to partition a table or index:

1. Range Zoning

2. Hash partition (hash partition)

3. Composite partition

These three partitioning methods are described in the following example as examples to illustrate the use of partitioned tables. For the convenience of testing , we build three table spaces first.

Create Tablespace DINYA_SPACE01

DataFile '/TEST/DEMO/ORACLE/DEMODATA/DINYA01.DNF ' size 50M

Create Tablespace DINYA_SPACE02

DataFile '/TEST/DEMO/ORACLE/DEMODATA/DINYA02.DNF ' size 50M

Create Tablespace dinya_space03

DataFile '/TEST/DEMO/ORACLE/DEMODATA/DINYA03.DNF ' size 50M

1 Partition Table creation:

1.1 Range Partition

A range partition is a range of values in a datasheet that determines which partition the data is stored on, depending on the scope of the value. If according to the ordinal partition, according to the business record creation date carries on the partition and so on.

Requirements Description: There is a material Transaction table, table name: Material_transactions. The table may have a tens number of data records in the future. The partition table is required to be used when the table is being built. At this point, we can use the ordinal partition three areas, each area is expected to store 30 million of the data, you can also use the date partition, such as every five years of data stored on a partition.

To build a table based on the serial number of the transaction:

Sql> CREATE TABLE Dinya_test

2 (

3 transaction_id number primary key,

4 item_id Number (8) is not NULL,

5 item_description VARCHAR2 (300),

6 Transaction_date date NOT NULL

7)

8 partition by range (transaction_id)

9 (

Partition part_01 values less than (30000000) tablespace dinya_space01,

Partition part_02 values less than (60000000) tablespace dinya_space02,

Partition part_03 values less than (MaxValue) tablespace dinya_space03

13);

Table created.

Sql>

The table succeeds, according to the serial number of the transaction, the record of the transaction ID below 30 million will be stored in the first tablespace dinya_space01, the partition name is: par_01, the record between 30 million and 60 million is stored in the second tablespace: DINYA_SPACE02, the partition name is: Par_ 02, while the transaction ID in more than 60 million of the records stored in the third tablespace dinya_space03, the partition name is par_03.

To create a table based on the transaction date:

Sql> CREATE TABLE Dinya_test

2 (

3 transaction_id number primary key,

4 item_id Number (8) is not NULL,

5 item_description VARCHAR2 (300),

6 Transaction_date date NOT NULL

7)

8 partition by range (transaction_date)

9 (

Partition part_01 values less than (to_date (' 2006-01-01 ', ' yyyy-mm-dd ')) tablespace dinya_space01,

One partition part_02 values less than (to_date (' 2010-01-01 ', ' yyyy-mm-dd ')) tablespace dinya_space02,

Partition part_03 values less than (MaxValue) tablespace dinya_space03

13);

Table created.

Sql>

This allows us to separate the partition tables that are partitioned by transaction number and transaction date. Each time the data is inserted, the record is automatically stored in the defined partition (Tablespace) based on the value of the specified field.

Of course, we can also use the range distribution of two fields to partition according to the requirement, such as partition by range (transaction_id, transaction_date), the values in the partition condition are also changed accordingly, ask the reader to test themselves.

1.2Hash partition (hash partition)

A hash partition is a type of partition that distributes data evenly by specifying the partition number, because these partitions are identical in size by hashing partitions on the I/O device. If the data for the Item Transaction table is hashed in the specified three tablespace according to the transaction ID:

Sql> CREATE TABLE Dinya_test

2 (

3 transaction_id number primary key,

4 item_id Number (8) is not NULL,

5 item_description VARCHAR2 (300),

6 Transaction_date Date

7)

8 partition by hash (transaction_id)

9 (

Partition part_01 tablespace dinya_space01,

One partition part_02 tablespace dinya_space02,

Partition part_03 tablespace dinya_space03

13);

Table created.

Sql>

The table succeeds, the data is inserted at this time, the system will be transaction_id to insert records into three partitions, this is the three different table spaces.

　　

　　

1.3 Composite partitions

Sometimes when we need to partition the range, the data in each partition is scattered in several tablespaces, so we use a composite partition. A composite partition is a partitioning method that uses a range partition and then uses a hash partition within each partition, such as dividing records for item transactions by time, and then dividing data in each partition into three sub partitions, where the data is hashed in three specified table spaces:

Sql> CREATE TABLE Dinya_test

2 (

3 transaction_id number primary key,

4 item_id Number (8) is not NULL,

5 item_description VARCHAR2 (300),

6 Transaction_date Date

7)

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

9 Subpartitions 3 Store in (DINYA_SPACE01,DINYA_SPACE02,DINYA_SPACE03)

10 (

One partition part_01 values less than (to_date (' 2006-01-01 ', ' yyyy-mm-dd ')),

Partition part_02 values less than (to_date (' 2010-01-01 ', ' yyyy-mm-dd '))

Partition part_03 values less than (MaxValue)

14);

Table created.

Sql>

In this example, the range partition is first based on the transaction date, and then the records are hashed in three tablespaces based on the transaction ID.

2 Partitioned Table Operations

For a table of three partitioned tables, the following uses the actual data and tests the data records of the partitioned tables for the range partition by date.

2.1 Insert Record:

sql> INSERT into dinya_test values (1,12, ' books ', sysdate);

1 row created.

sql> INSERT into dinya_test values (2,12, ' books ', sysdate+30);

1 row created.

sql> INSERT into dinya_test values (3,12, ' books ', To_date (' 2006-05-30 ', ' yyyy-mm-dd '));

1 row created.

sql> INSERT into dinya_test values (4,12, ' books ', To_date (' 2007-06-23 ', ' yyyy-mm-dd '));

1 row created. sql> INSERT into dinya_test values (5,12, ' books ', To_date (' 2011-02-26 ', ' yyyy-mm-dd '));

1 row created.

sql> INSERT into dinya_test values (6,12, ' books ', To_date (' 2011-04-30 ', ' yyyy-mm-dd '));

1 row created.

Sql> commit;

Commit complete.

Sql>

According to the table above, data from 2006 years ago will be stored on the first partition part_01, while the transaction data from 2006 to 2010 will be stored on the second partition part_02, and after 2010, the records are stored on the third partition part_03.

2.2 Query partition Table records:

Sql> SELECT * from Dinya_test partition (PART_01);

transaction_iditem_id item_description Transaction_date

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

112 Books 2005-1-14 14:19:

212 Books 2005-2-13 14:19:

Sql>

Sql> SELECT * from Dinya_test partition (PART_02);

transaction_iditem_id item_description Transaction_date

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

3 Books, 2006-5-30

4 Books, 2007-6-23

Sql>

Sql> SELECT * from Dinya_test partition (PART_03);

Transaction_iditem_iditem_description transaction_date

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

5 12BOOKS 2011-2-26

6 12BOOKS 2011-4-30

Sql>

From the results of the query, you can see that the inserted data has been stored in different partitions according to the trading time range. This is the query that specifies the partition, and of course you can execute the SELECT * from Dinya_test query all records directly without specifying the partition. When the amount of data that is also retrieved is large, the specified partition can greatly improve the retrieval speed.

2.3 Update the records of the partition table:

sql> update dinya_test partition (PART_01) t set t.item_description= ' DESK ' where t.transaction_id=1;

1 row updated.

Sql> commit;

Commit complete.

Sql>

Here, the Item_description field in the record of the transaction id=1 in the first partition is updated to "DESK" to see that a record has been successfully updated. However, when the partition is specified when the update is made, and the data is not updated according to the query's record, see the following example:

sql> update dinya_test partition (PART_01) t set t.item_description= ' DESK ' where t.transaction_id=6;

0 rows updated.

Sql> commit;

Commit complete.

Sql>

Specifies that records are updated in the first partition, but in conditions where the transaction ID is 6, and the query whole table, transaction ID 6 is in the third partition so that the statement will not update the record.

2.4 Delete partition Table records:

sql> Delete from dinya_test partition (part_02) t where t.transaction_id=4;

1 row deleted.

Sql> commit;

Commit complete.

Sql>

The example above deletes a record with a transaction ID of 4 in the second partition part_02, the same as the update data, and no data is deleted if the partition is specified and the data in the condition is not in the partition.

3 Use of partitioned table indexes:

Partitioned tables and general tables can be indexed, and partitioned tables can create local indexes and global indexes. A global index is used when there are many transactions in the partition and the uniqueness of the data records in all partitions is ensured.

3.1 The establishment of local index partitions:

Sql> CREATE index dinya_idx_t on dinya_test (item_id)

2 Local

3 (