Common Oracle partition command 2

1.2.2. query Partition Table records:

SQL> select * From dinya_test partition (part_01 );

Transaction_id item_id item_description transaction_date

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

1 12 books 2005-1-14 14: 19:

2 12 books 2005-2-13 14: 19:

SQL>

SQL> select * From dinya_test partition (part_02 );

Transaction_id item_id item_description transaction_date

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

3 12 books 2006-5-30

4 12 books 2007-6-23

SQL>

SQL> select * From dinya_test partition (part_03 );

Transaction_id item_id item_description transaction_date

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

5 12 books 2011-2-26

6 12 books 2011-4-30

SQL>

The query results show that the inserted data has been stored in different partitions according to the transaction time range. The partition query is specified here. Of course, you can directly execute select * From dinya_test to query all records without specifying partitions.

When the retrieved data volume is large, the specified partition will greatly increase the retrieval speed.

1.2.3. Update Partition Table records:

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, we will update the item_description field in the transaction id = 1 record in the first partition to "desk". We can see that a record has been successfully updated. However, when a partition is specified during update, but the queried records are not in the partition, data will not be updated. 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>

Indicates that the record is updated in the first partition, but the condition limits the transaction ID to 6, while the full table is queried, and the transaction ID is 6 in the third partition, this statement will not update the record.

1.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>

In the preceding example, a transaction record with ID 4 in the second partition part_02 is deleted, which is the same as the updated data. If the partition is specified and the data in the condition is not in the partition, no data will be deleted.

1.3. Use of Partitioned table indexes:

You can create an index for a partitioned table like a common table. You can create a local index and a global index for a partitioned table. Global indexes are used when there are many transactions in a partition and the uniqueness of data records in all partitions must be ensured.

1.3.1. Create a local index partition:

SQL> Create index dinya_idx_t on dinya_test (item_id)

2 Local

3 (

4 partition idx_1 tablespace dinya_space01,

5 partition idx_2 tablespace dinya_space02,

6 partition idx_3 tablespace dinya_space03

7 );

Index created.

SQL>

Looking at the query execution plan, we can see from the following execution plan that the system has used the index:

SQL> select * From dinya_test partition (part_01) t where T. item_id = 12;

Execution Plan

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

0 SELECT statement optimizer = choose (cost = 2 card = 1 bytes = 187)

1 0 Table Access (by local index rowid) of 'dinya _ test' (cost =

2 card = 1 bytes = 187)

2 1 index (range scan) of 'dinya _ idx_t '(NON-UNIQUE) (cost = 1

Card = 1)

Statistics

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

0 recursive cballs

0 dB block gets

4 consistent gets

0 physical reads

0 redo size

334 bytes sent via SQL * Net to client

309 bytes encoded ed via SQL * Net From Client

2 SQL * Net roundtrips to/from client

1 sorts (memory)

0 sorts (Disk)

2 rows processed

SQL>

1.3.2. Create a global index Partition

When a global index is created, the global clause allows you to specify the index range value. The range value is the range value of the index field:

SQL> Create index dinya_idx_t on dinya_test (item_id)

2 global partition by range (item_id)

3 (

4 partition idx_1 valueless than (1000) tablespace dinya_space01,

5 partition idx_2 values less than (10000) tablespace dinya_space02,

6 partition idx_3 values less than (maxvalue) tablespace dinya_space03

7 );

Index created.

SQL>

In this example, create an index partition for the item_id field of the table. You can also create an index for the entire table without specifying the index partition name. For example:

SQL> Create index dinya_idx_t on dinya_test (item_id );

Index created.

SQL>

Similarly, according to the execution plan, the global index can be used:

SQL> select * From dinya_test t where T. item_id = 12;

Execution Plan

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

0 SELECT statement optimizer = choose (cost = 2 card = 3 bytes = 561)

1 0 Table Access (by global index rowid) of 'dinya _ test' (Cost

= 2 card = 3 bytes = 561)

2 1 index (range scan) of 'dinya _ idx_t '(NON-UNIQUE) (cost = 1

Card = 3)

Statistics

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

5 recursive cballs

0 dB block gets

10 consistent gets

0 physical reads

0 redo size

420 bytes sent via SQL * Net to client

309 bytes encoded ed via SQL * Net From Client

2 SQL * Net roundtrips to/from client

3 sorts (memory)

0 sorts (Disk)

5 rows processed

SQL>

1.4. Partition Table maintenance:

After learning about Partition Table creation, index creation, and table and index usage, you must maintain and manage partitions frequently in applications. Routine maintenance and management includes adding a partition, merging a partition, and deleting a partition. The following uses a range partition as an example to describe how to add, merge, or delete a partition:

1.4.1. Add a partition:

SQL> ALTER TABLE dinya_test

2 add partition part_04 values less than (to_date ('2017-01-01 ', 'yyyy-mm-dd '))

Tablespace dinya_spa

Ce03;

Table altered.

SQL>

When adding a partition, the added partition condition must be greater than the maximum value of the existing partition, otherwise the system will prompt the ORA-14074 partition bound must collate higher than that of the last partition error.

After a partition is added, the index on the new partition is created on the default tablespace of the partition by default. If you want to store the index on the custom tablespace, run the following command:

Alter index idx_ssn rebuild partition log_3 tablespace log_idx_p3;

 

1.4.2. merge a partition:

SQL> ALTER TABLE dinya_test merge partitions part_01, part_02 into partition part_02;

Table altered.

SQL>

In this example, partition part_01 and partition part_02 of the original table are merged, and the merged partition is part_02. If the merged partition is set to part_01 during the merge, the system will prompt the ORA-14275 cannot reuse lower-bound partition as resulting partition error.

1.4.3. delete partitions:

SQL> ALTER TABLE dinya_test drop partition part_01;

Table altered.

SQL>

After deleting a partition in a partition table, when querying the data in the table, it is displayed that all the data in the partition is lost. Therefore, exercise caution when deleting the partition. Make sure that the data is backed up before execution, or merge partitions.

1.4.3. Split partition:

Split rest partitions
Alter table zhaozhenlong_partition split partition rest at (to_date ('20170101', 'yyymmm ') into (partition p200608, partition rest );
Alter table zhaozhenlong_partition split partition rest at (to_date ('20170101', 'yyymmm ') into (partition p200609, partition rest );

1.5. Conclusion:

It should be noted that, in this example, partitions are specified during the name/Partition Table transaction operation. Because partitions are specified, the system only operates the records of the partition during execution, this improves the data processing speed. Do not specify partitions to directly operate on data. The use of indexes and Multiple indexes on partitioned tables is the same as that on non-partitioned tables. In addition, the partition index may be affected when the partition is maintained. You may need to re-create the index after maintenance. For more information, see the documentation in the index section of the partition table.