Plsql_ Performance Optimization series 09_oracle Partition table big Data partition tables

2014-08-22 Baoxinjian

I. Summary

1. Partition table:

As the table continues to grow, it is more difficult to maintain the new record's increase, find, delete, and so on (DML). For a very large table in a database, you can simplify the management of the database by dividing its data into several small tables. For each simplified small table, we call it a single partition

For access to partitions, we do not need to use special SQL query statements or specific DML statements, and we can manipulate individual partitions instead of the entire table. At the same time, different partitions of data can be placed into different table space, such as the sales data of different years, stored in different table space, that is, the sales data of the year to the tbs_2001,2002 year of sales data stored to tbs_2002, and so on, thus realizing the decentralized storage, This will greatly simplify the management of large capacity tables, improve query performance and I/O concurrency, and so on.

For external applications, although there are different partitions and the data is in a different table space, it is logically still a table

You can use tools such as Sql*loader,impdp,expdp,import,export to mount or unload data from a partitioned table

The ability to partition tables is actually the same concept as a partitioned table in SQL Server, except that the data in SQL Server is stored in filegroups, which is equivalent to the table spaces in Oracle Concepts

Partition Information Control Table: Dba_tab_subpartitions

2. When to partition

When expressed to GB size and continues to grow

Historical data needs to be processed separately from the current data, such as historical data that only needs to be read-only, while current data implements DML

3, the conditions and characteristics of the partition

• Commonality: Different partitions must have the same logical attributes, such as table name, column name, data type, constraints, etc.
• Personality: Each partition can have different physical properties, such as Pctfree, pctused, and tablespaces.
• Partition independence: Other partitions are still available even if some partitions are not available.
• Particularity: A table containing a long, longraw data type cannot be partitioned

4. Advantages of Partitioning

• Improve query performance by searching for specific partitions rather than the entire table, improving query speed
• Save maintenance Time: Data loading for a single partition, index rebuilding, backup, maintenance, etc. will be much smaller than the entire table maintenance time.
• Save maintenance Costs: Each partition can be backed up and restored separately
• Balanced I/O: Map different partitions to different disks to balance I/O and increase concurrency

5. partition Table Type

• Range partition Table
• List partition Table
• Hash partition Table
• Combined partition Table

6. Partition Index Type

• Global index
• Global Partition Index
• Local partition Index

7, partition table to establish four ways of grammar

(1), Range partition table

CREATETABLERange_example (Range_key_column DATE, DATAVARCHAR2 (20), IDINTEGER) PARTITIONByRANGE (Range_key_column) (PARTITION part01VALUESLess THAN (To_date (‘2008-07-1 00:00:00‘,‘YYYY-MM-DD Hh24:mi:ss ' )) Tablespace tbs01, PARTITION part02 values less THAN (to_ DATE ( ' 2008-08-1 00:00:00  ",  yyyy-mm-dd Hh24:mi : Ss ' values less THAN (to_date ( " 2008-09-1 00:00:00 "yyyy-mm-dd hh24:mi:ss          

(2), List partition table

CREATETABLE List_example (dnameVARCHAR2 (), DATAVARCHAR2 (20)) PARTITIONby LIST (dname) (PARTITION part01 VALUES ('ME', 'PE', 'QC', ' RD'), PARTITION part02 VALUES ('SMT', 'SALE'));    

(3), hash partition table

TABLE hash_example (   hash_key_column   DATE,   DATA              VARCHAR2 ( Hash_key_ Cloumn) (PARTITION part01, PARTITION part02);    

(4), Combined partition table

CREATETABLERange_hash_example (Range_column_key DATE, Hash_column_keyInt, the DATAVARCHAR2 (20)) PARTITIONByRANGE (Range_column_key) subpartitionByHASH (Hash_column_key) subpartitions2 PARTITION part_1 values less THAN (to_date ( "2008-08-01 ',  ' yyyy-mm-dd ' )) (Subpartition part_1_sub_1, subpartition part_1_sub_2, Subpartition part_1_sub_3), PARTITION part_2 values less THAN (to_date ( "2008-09-01 ',  ' yyyy-mm-dd '         

8, the partition index structure diagram

Note: When a hash partitioned table is added partition, all data in the existing table will have the hash value recalculated and then reassigned to the partition. So the indexes of the partition to be reassigned needs to be rebuild.

Second, case-create a partitioned table

Case: Create partitioned Table bxj_emp, with gender-sensitive partitioning of data, storing data in two male and female table spaces

1. Create two table empty spaces

create tablespace bxj_emp_ts1 LOGGING datafile  '  SIZE 32M Autoextend on next 32M MAXSIZE 2048M EXTENT MANAGEMENT LOCAL            

2. Create a partitioned table that is differentiated by the sex field

CREATETABLEBXJ_EMP_TB (emp_idnumber Employeee_name varchar (50 ), sex varchar (10), salary number) PARTITION by LIST (Sex) (PARTITION bxj_emp_ts1 values ( ' male "), PARTITION bxj_emp_ts2 values ( female           

3. Establish test data, one for both sexes

InsertInto APPS.BXJ_EMP_TBvalues (1, 'gavin.bao', 'male', 100000); INSERT INTO apps.bxj_emp_tb values (2, 'gavin.bao', 'female',  200000);  

4. When querying with conditional sex = male, the system only traverses tablespace 1 male

5. When querying with conditional sex = female, the system only traverses tablespace 1 female

6. No partition condition query, the system must traverse all tablespace 1 and 2/male and female

Third, case-local partition index

1. Create a local partition index

On apps.bxj_emp_tb (Sex) LOCAL (     PARTITION idx_1 tablespace bxj_emp_ts1,     PARTITION idx_2 tablespace bxj_emp_ Ts2  );

2. Parsing the index traversal method in the plan

Iv. case-Global Partition Index

1. Create a global partition index

INDEX by RANGE (Salary) (   values less THAN (10000values less THAN (MAXVALUE) Tablespac E bxj_emp_ts2);     

2. The condition is salary <= 100, the index only traverses Tablespace1

3. The condition is salary >=100000, the index only traverses Tablespace2

4. When there is no partitioning condition, the index traverses all

V. Case-structure of Oracle ERP transaction table Mtl_material_transactions

SELECT * FROM Dba_tab_subpartitions

WHERE table_name = ' mtl_material_transactions '

Abalone New ********************

Reference: http://www.linuxidc.com/Linux/2011-08/40763.htm

Reference: http://mingyue19850801.blog.163.com/blog/static/19520820201071712231671/

Plsql_ Performance Optimization series 09_oracle Partition table big Data partition tables