Oracle practice -- PL/SQL-based Table Partitioning

Basic PL/SQL entry-Table Partitioning

PL/SQL: a programming Language combined with a Structured Language (Structured Query Language). It is an extension of SQL and supports multiple data types, such as large objects and Collection types, you can use control statements such as conditions and loops to create stored procedures, packages, and triggers, and add program logic to SQL statement execution, tightly integrated with Oracle servers and Oracle tools, it is portable, flexible, and secure.

Bytes ------------------------------------------------------------------------------------------------------------

Table Partitioning

/*

Lock: the database controls concurrent access to shared resources to protect the data being modified.

Other users can update the data only after the transaction is committed or rolled back.

Category: Row-level locks and table-level locks

*/

-- Row-Level Lock: A Row-Level Lock is an exclusive lock to prevent other transactions from modifying this row.

-- Oracle automatically uses row-level locks in the following statements

Insert

Update

Delete

Select... forupdate -- allows the user to lock Multiple rows of records at a time for updates

/*

Table-Level Lock: use commands to explicitly lock a table and apply the table-Level Lock syntax.

Category: 1. row sharing; 2. Sharing; 3. Exclusive; 4. Row exclusive: 5. Shared row exclusive

SHARE)

1. Lock the table. Only other users are allowed to query the rows in the table.

2. Disable other users from inserting, updating, and deleting rows.

3. Multiple users can apply the lock to the same table at the same time.

EXCLUSIVE-the most restrictive table lock, which allows other users to query the rows of the table only. Disable table modification and lock

Deadlock:

A deadlock occurs when two transactions wait for each other to release resources.

Oracle automatically detects deadlocks and ends a transaction to solve deadlocks.

*/

Lock table tableName in mode MODE

 

/*

Partition: Oracle allows you to divide a table into multiple partitions, which are created together with the table.

Advantages: 1. Users can perform queries and only access specific partitions;

2. Store different partitions on different disks to improve access performance and security

3. partitions can be backed up and restored independently.

*/

-- Range partition, which is a value range partition of one or more columns in a table.

-- Keyword: partition by range values less

 

create table person1(      pid number primary key,      birthday date)partition by range (birthday)(     partition p1 values less than (to_date('1980-01-01','yyyy-mm-dd')),     partition p2 values less than (to_date('1990-01-01','yyyy-mm-dd')),     --partition p1 values less than (maxvalue))insert into person1 values(1,'ysjian',to_date('1965-02-03','yyyy-mm-dd'));insert into person1 values(2,'ysjian',to_date('1985-02-03','yyyy-mm-dd'));insert into person1 values(3,'ysjian',to_date('1995-02-03','yyyy-mm-dd'));select * from person1 partition(p2);

 

-- Hash partition: allows you to partition data that does not have a logical range. Keyword: hash

-- Execute the hash function on the partition key to determine the partitions to store and evenly distribute data to different partitions.

 

create table person2(      pid number primary key,      pname varchar2(20),      birthday date)partition by hash(birthday)(         partition p1,         partition p2,         partition p3,)

-- Partitions 3; the above four partitions are equivalent to this

-- List partition: allows users to organize irrelevant data. Keywords: list, values

 

Create table person3 (pid number primary key, birthday date, address varchar2 (50) partition by list (address) (partition hubei values ('wuhan ', 'huangshi ', 'huanggang'), partition shanxi values ('hangzhou', 'weinan ', 'baoji '))

-- Composite partition, Keyword: subpartition

 

create table person4(      pid number primary key,      birthday date，      address varchar2(50))partition by range(birthday)subpartition by hash(birthday)subpartitions4(     partition p1 values less than (to_date('1980-01-01','yyyy-mm-dd')),     partition p2 values less than (to_date('1990-01-01','yyyy-mm-dd')),     partition p3 values less than (to_date('2000-01-01','yyyy-mm-dd')))

-- Partition maintenance:
-- 1. Add partition: Add a new partition after the last partition

 

alter table person1 add partition P4 values less than (4000);

-- 2. delete a partition: delete a specified partition, and the data of the partition is also deleted.

 

alter table person1 truncate partition P3;

-- 3. Partition truncation: delete all records in the specified partition. No logs are recorded and cannot be recovered.

 

alter table person1 truncate partition P3;

-- 4. merge partitions-connect two adjacent partitions of a range partition or a composite partition.

 

alter table person1 merge partitions p1, p2 into partition p2;

-- 5. Split partitions-split records in a large partition into two partitions

 

alter table person1split partition p2 AT (1500) into (partition P21,partition P22);

/*

Conclusion: The lock is used to protect the modified data in multi-user environments.

There are two types of locks: Row-level locks and table-level locks.

Table partitions allow a table to be divided into several parts to Improve the Performance of large-scale application systems.

Partition methods include range partitions, hash partitions, composite partitions, and list partitions.

Partition maintenance operations include adding, deleting, truncation, merging, and splitting partitions.

*/