Oracle-(ii) partitioning (extent)

Basic Relationship: Database---Table space---data segment---partition---data block


First, partition (extent)
Partition extent is a storage structure that is larger than a block of data, and is a combination of several logically contiguous, data blocks. We know that physical storage is usually a random read-write process. Even in the same file, we cannot guarantee that the same information is stored in absolute contiguous physical storage space. The same is true for Oracle data storage.

Partition extent is the smallest unit of disk space allocation. Disks are partitioned, allocating at least one zone at a time. Zones are stored in segments, which consist of contiguous blocks of data. The allocation process of the area, each to allocate 5 districts. If there is not enough free space left for 5 extents, an error occurs: ORA-01653.

When storing data information, Oracle allocates data blocks for storage, but does not guarantee that all allocated data blocks are contiguous structures. Therefore, the concept of partition extent appears, representing a series of contiguous sets of data blocks.

Sql> desc dba_extents;
Name Type Nullable Default Comments
--------------- ------------- -------- ------- ---------------------------------------------------------
Owner VARCHAR2 (x) Y owner of the segment associated with the extent
Segment_name VARCHAR2 (x) Y NAME of the SEGMENT associated with the extent
Partition_name VARCHAR2 (x) Y partition/subpartition NAME, if any, of the segment
Segment_type VARCHAR2 Y TYPE of the SEGMENT
Tablespace_name VARCHAR2 (x) Y NAME of the tablespace containing the extent
extent_id number Y EXTENT number in the segment
FILE_ID number Y Name of the the file containing the extent
BLOCK_ID number Y starting block number of the extent
BYTES number Y Size of the extent in BYTES
BLOCKS number Y Size of the extent in ORACLE BLOCKS
RELATIVE_FNO number Y RELATIVE number of the file containing the segment header

Second, when to use the partition table:
1, the size of the table is more than 2GB.
2, the table contains historical data, new data is added to the new partition.

Third, table partitioning has the following advantages:
1. Improve query performance: Queries on partitioned objects can search only the partitions they care about and improve the retrieval speed.
2. Enhanced usability: If one partition of the table fails, the data of the table in other partitions is still available;
3, Maintenance Convenience: If a partition of the table fails, you need to repair the data, only repair the partition;
4. Balanced I/O: Different partitions can be mapped to disk to balance I/O and improve overall system performance.



Iv. Several types of table partitioning and their operating methods
I. Scope partitioning:
A range partition maps data to each partition based on its scope, which is determined by the partition key you specify when you create the partition. This partitioning method is most commonly used, and the partitioning key often takes the date.
When using range partitioning, consider the following rules:
1. Each partition must have a values less then clause that specifies an upper value that is not included in the partition. Any record of the partition key that is equal to or greater than this upper value is added to the next higher partition.
2. All partitions, except the first one, will have an implicit lower value, which is the upper limit of the previous partition of this partition.
3. In the highest partition, MaxValue is defined. The MaxValue represents an indeterminate value. This value is higher than the value of any partition key in the other partition, and it can be understood to be higher than the value less or less that is specified in any partition, including null values.

Suppose there is a customer table with 200000 rows in the table, we partition this table, each partition stores 100000 rows, and we save each partition in a separate tablespace so that the data file can span multiple physical disks. Here is the code to create the table and partition, as follows:
Example one: Partitioning by customer_id
CREATE TABLE Customer
(
customer_idNumber NOT NULL primary key,
First_Name VARCHAR2 (+) NOT NULL,
Last_Name VARCHAR2 (+) NOT NULL,
Phone varchar2 () NOT NULL,
Email VARCHAR2 (80),
Status char (1)
)
Partition by Range (customer_id)
(
Partition Cus_part1 values less than (100000) tablespace cus_ts01,
Partition Cus_part2 values less than (200000) tablespace CUS_TS02
)
Example two: divided by time
CREATE TABLE Order_activities
(
ORDER_ID Number (7) is not NULL,
order_dateDate
Total_amount number,
CUSTOTMER_ID Number (7),
Paid CHAR (1)
)
Partition by Range (order_date)
(
Partition ord_act_part01 values less than (to_date (' 01-may-2003 ', ' dd-mon-yyyy ')) tablespaceord_ts01,
Partition ord_act_part02 values less than (to_date (' 01-jun-2003 ', ' dd-mon-yyyy ') tablespace ord_ts02,
Partition ord_act_part02 values less than (to_date (' 01-jul-2003 ', ' dd-mon-yyyy ') tablespace ord_ts03
)
Example three: MaxValue
CREATE TABLE Rangetable
(
IDD int PRIMARY KEY,
Iname varchar (10),
GradeInt
)
Partition by Range (Grade)
(
Partition Part1 values less then (tablespace part1_tb),
Partition Part2 values less then (maxvalue) tablespace PART2_TB
);

two. List partition:
A list partition explicitly specifies that partitioning is based on a specific value of a field, rather than as a range partition, based on the field's value range. Based on this feature, we can use list partitioning.
Example One
CREATE TABLE Problem_tickets
(
PROBLEM_ID Number (7) Not null primary key,
Description VARCHAR2 (2000),
CUSTOMER_ID Number (7) is not NULL,
Date_entered date NOT NULL,
StatusVARCHAR2 (20)
)
Partition by List (Status)
(
Partition prob_active values (' active ') tablespace prob_ts01,
Partition prob_inactive values (' inactive ') tablespace PROB_TS02
Example Two
CREATE TABLE Listtable
(
ID int PRIMARY KEY,
Name varchar (20),
Areavarchar (10)
)
Partition by List ( Area)
(
Partition part1 values (' Guangdong ', ' Beijing ') tablespace PART1_TB,
Partition part2 values (' Shanghai ', ' Nanjing ') tablespace PART2_TB
);
)

three. Hash partition:
This type of partitioning uses a hashing algorithm on column values to determine which partition the rows are placed in. Hash partitioning is recommended when the value of the column does not have an appropriate condition.
a hash partition is a type of partition that distributes data evenly by specifying the partition number, because the partitions are identical in size by hashing on the I/O device.
Example one:
CREATE TABLE Hash_table
(
ColNumber (8),
INF VARCHAR2 (100)
)
Partition by Hash (Col)
(
Partition part01 tablespace hash_ts01,
Partition part02 tablespace HASH_TS02,
Partition part03 tablespace Hash_ts03
)
Shorthand:
CREATE TABLE EMP
(
empnoNumber (4),
ename VARCHAR2 (30),
Sal number
)
Partition by Hash (empno) Partitions 8
Store in (EMP1,EMP2,EMP3,EMP4,EMP5,EMP6,EMP7,EMP8);
hash partition The main mechanism is based on the hash algorithm to calculate the specific record should be inserted into which partition, hash algorithm is the most important hash function, Oracle if you want to use the hash partition, only specify the number of partitions. It is recommended that the number of partitions be 2 N, which makes the data distribution more evenly between partitions.

four. Combined range hash Partition
This partition is based on a range partition and a list partition, where the table is first partitioned by a column, then a list is partitioned by a column, and the partitions in the partition are called sub-partitions.
CREATE TABLE Sales
(
product_id VARCHAR2 (5),
Sales_date date,
Sales_cost Number (10),
Status Varchar2 (20)
)
Partition by Range (sales_date) Subpartition by list (status)
(
Partition P1 values less than (to_date (' 2003-01-01 ', ' yyyy-mm-dd ') tablespace rptfact2009
(
Subpartition p1sub1 values (' active ') tablespace rptfact2009,
Subpartition p1sub2 values (' inactive ') tablespace rptfact2009
),
Partition P2 values less than (to_date (' 2003-03-01 ', ' yyyy-mm-dd ') tablespace rptfact2009
(
Subpartition p2sub1 values (' active ') tablespace rptfact2009,
Subpartition p2sub2 values (' inactive ') tablespace rptfact2009
)
)

Five. Composite range Hash Partition:
This partition is based on range partitioning and hash partitioning, where the table is first scoped by a column and then hashed by a column.
CREATE TABLE Dinya_test
(
TRANSACTION_ID Number primary Key,
ITEM_ID Number (8) is not NULL,
Item_description VARCHAR2 (300),
Transaction_date Date
)
Partition by Range (transaction_date) subpartition by hash (transaction_id) Subpartitions 3 store in (Dinya_space01,dinya_ SPACE02,DINYA_SPACE03)
(
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)
);

Vi. Some maintenance operations related to table partitioning:
First, add the partition
The following code adds a P3 partition to the sales table
ALTER TABLE Sales ADD partition P3 values less than (to_date (' 2003-06-01 ', ' yyyy-mm-dd '));
Note: The above partition boundaries should be added above the last partition bounds.
The following code adds a P3SUB1 sub-partition to the P3 partition of the sales table
ALTER TABLE Sales Modify partition p3 add subpartition p3sub1 values (' complete ');

Second, delete the partition
The following code removes the P3 table partition:
ALTER TABLE sales drop partition P3;
The P4SUB1 sub-partition was removed in the following code:
ALTER TABLE sales drop subpartition p4sub1;
Note: If the deleted partition is the only partition in the table, then this partition will not be deleted and you must delete the table if you want to delete this partition.

Third, truncate the partition
Truncating a partition means deleting data from a partition, and does not delete the partition, nor does it delete data from other partitions. You can truncate a table even if it has only one partition. Truncate the partition with the following code:
ALTER TABLE sales TRUNCATE partition p2;
Truncate the sub-partition with the following code:
ALTER TABLE sales truncate subpartition p2sub2;

Iv. Merging of partitions
Merging partitions is the merging of adjacent partitions into a single partition, with the result that partitions are bounded by higher partitions, and it is worth noting that partitions cannot be merged into lower-bound partitions. The following code implements the merge of the P1 P2 partition:
ALTER TABLE sales merge partitions p1,p2 into partition p2;

V. Splitting partitions
Split partition splits a partition into two new partitions, after which the original partition no longer exists. Note You cannot split a hash-type partition.
ALTER TABLE sales Sblit partition P2 at (to_date (' 2003-02-01 ', ' yyyy-mm-dd ')] into (partition p21,partition P22);

Vi. junction Zoning (Coalesca)
Combined partitioning is the data in the hash partition into other partitions, when the data in the hash partition is relatively large, you can increase the hash partition and then engage, it is worth noting that the bonded partition can only be used in the hash partition. Use the following code to make a bonded partition:
ALTER TABLE sales COALESCA partition;

Vii. Renaming table Partitions
The following code changes P21 to P2
ALTER TABLE Sales Rename partition p21 to P2;

Eight, related inquiries
Cross-partition queries
Select SUM (*) from
(SELECT COUNT (*) CN from T_TABLE_SS partition (P200709_1)
UNION ALL
Select COUNT (*) CN from T_TABLE_SS partition (p200709_2)
);

How many partitions are on the query table
SELECT * from user_tab_partitions where table_name= ' tablename '

Querying index information
Select Object_name,object_type,tablespace_name,sum (Value)
From V$segment_statistics
where Statistic_name in (' physical reads ', ' physical write ', ' logical reads ') and object_type= ' index '
GROUP BY Object_name,object_type,tablespace_name
ORDER BY 4 DESC

--Displays information about all partitioned tables in the database:
SELECT * FROM Dba_part_tables

--Displays all partition table information that the current user can access:
SELECT * FROM All_part_tables

--Displays information about all the partition tables for the current user:
SELECT * FROM User_part_tables

--Display table partition information displays detailed partition information for all partitioned tables in the database:
SELECT * FROM Dba_tab_partitions

--Displays detailed partitioning information for all partitioned tables that the current user can access:
SELECT * FROM All_tab_partitions

--Displays detailed partitioning information for all partition tables for the current user:
SELECT * FROM User_tab_partitions

--Display sub-partition information displays sub-partition information for all the combined partition tables of the database:
SELECT * FROM Dba_tab_subpartitions

--Displays sub-partition information for all the combined partitioned tables that the current user can access:
SELECT * FROM All_tab_subpartitions

--Displays sub-partition information for all the combined partition tables of the current user:
SELECT * FROM User_tab_subpartitions

--Show partition column displays the partition column information for all partitioned tables in the database:
SELECT * FROM Dba_part_key_columns

--Displays the partition column information for all partitioned tables that the current user can access:
SELECT * FROM All_part_key_columns

--Displays the partition column information for all partition tables for the current user:
SELECT * FROM User_part_key_columns

--Show sub-partition columns displays sub-partition column information for all partitioned tables in the database:
SELECT * FROM Dba_subpart_key_columns

--Displays sub-partition column information for all partitioned tables that the current user can access:
SELECT * FROM All_subpart_key_columns

--Displays sub-partition column information for all partitioned tables of the current user:
SELECT * FROM User_subpart_key_columns

--How to query out all the partitioned tables in the Oracle database
SELECT * from User_tables a where a.partitioned= ' yes '

--deleting data from a table is
TRUNCATE TABLE table_name;

--Delete partition table data for a partition is
ALTER TABLE TABLE_NAME TRUNCATE PARTITION P5;

Oracle-(ii) partitioning (extent)