Oracle Practice--pl/sql Base Table Partitioning

PL/SQL Basic Entry table partitioning

PL/sql:Process Language(Procedure Language)and structured language(StructuredQuery Language)The combination of the programming language, is theSQLextensions that support multiple data types, such as large objects and collection types, can use control statements such as conditions and loops to create stored procedures, packages, triggers, and so on, toSQLthe execution of the statement adds program logic, andOracleservers, andOracletools are tightly integrated, with portability, flexibility, and security.

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

Table partitioning

/*

Lock: A mechanism by which a database controls concurrent access to shared resources to protect data that is being modified

No Other user can update the data until the transaction is committed or rolled back

Category: Row-level and table-level locks

*/

-- row-level locks: row-level locks are an exclusive lock that prevents other transactions from modifying this row

--oracle row-level locks are automatically used in the following statements

Insert

Update

Delete

Select  ... for Update -- allows users to lock multiple rows of records at once for updates

/*

table-level locks, using the command to explicitly lock the table, apply table-level lock Syntax

Category:1. row Sharing ; 2. shared ; 3. Exclusive ; 4. Line Exclusive: 5. shared Row exclusive

shared lock (SHARE)

1. Lock the table to allow other users to query the rows in the table only

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

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

Exclusive (EXCLUSIVE) – restricts the strongest table locks, allowing only other users to query rows of the table. Prohibit modifying and locking tables

Deadlock:

When two transactions wait for each other to release resources, a deadlock is formed

Oracle automatically detects deadlocks and resolves deadlocks by ending one of the transactions

*/

Lock Table TableName inch Mode MODE

/*

Partitioning:Oracle allows users to divide a table into multiple partitions , together with the creation of a table

Advantages:1. The user can execute the query and access only the specific partition;

2. storage of different partitions on different disks for improved access performance and security

3. you can back up and restore partitions independently.

*/

-- a range partition that is worth the range of a column or set of columns in a table

-- Key Words: partition by range values less than

CREATE TABLE Person1 (      PID number primary key,      birthday date) partition by range (birthday) (     partition P1 value s 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 the user to partition data that does not have a logical scope, keywords: Hash

--  by executing on the partition key Hash function determines which partitions are stored and distributes the data evenly to different partitions

CREATE TABLE Person2 (      PID number primary key,      pname varchar2 (),      birthday date) partition by hash (birthday) (         partition P1,         partition p2,         partition P3,)

--partitions 3; The above four partitions are equivalent to this

-- List Partitioning: Allow users to organize unrelated data together , Key Words: list,values

CREATE TABLE Person3 (      PID number primary key,      birthday date,      address varchar2 ()) partition by list (address (      partition Hubei values (' Wuhan ', ' Yellowstone ', ' Huanggang '),      partition Shanxi values (' Xian ', ' Weinan ', ' Baoji ')

-- Composite Partitioning , Key Words: subpartition

CREATE TABLE Person4 (      PID number primary key,      birthday date,      address varchar2 ()) partition by range ( Birthday) Subpartition by hash (birthday) subpartitions4 (     partition P1 values less than (' 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 '))

-- Maintenance of partitions :
--1. New Partition : Add a partition after the last partition

ALTER TABLE Person1 add partition P4 values less than (4000);

--2. Delete partition: Deletes a specified partition, and the partition's data is deleted .

ALTER TABLE Person1 truncate partition P3;

--3. truncate partition: Delete all records in the specified partition, log not logged, unrecoverable

ALTER TABLE Person1 truncate partition P3;

--4. Merging Partitions  - connect two contiguous partitions of a range partition or a composite partition

ALTER TABLE Person1 merge partitions p1, p2 into partition p2;

--5. Split Partition  - splitting records from a large partition into two partitions

ALTER TABLE Person1split partition P2 at (partition p21,partition P22);

/ *

Summary: Locks are used to protect data that has been modified in a multi-user environment

locks are divided into two levels, a level lock and a table-level lock

Table partitioning allows a table to be divided into parts to improve the performance of large application Systems

Partitioning methods include range partitions, hash partitions, composite partitions, and list partitions

partition maintenance operations include adding, deleting, truncating, merging, and splitting partitions

*/

Article Source: http://blog.csdn.net/ysjian_pingcx/article/details/25646805