Oracle Partition Table Technology (PartitionedTables)
Since Oracle 8 (around 1997), the concept of Partition Table & partition index (Partitioned Tables & Indexes) has been introduced to adjust large Tables and large Indexes to improve performance and improve O & M management capabilities. The partition table and partition index mechanisms are an important mechanism to improve performance in the management of massive Databases (Very Large Databases, VLDB.
Oracle Partitioning technology's history Oracle 8 introduced the concept of partitioning, and each subsequent version has optimized and improved the partitioning mechanism. Benefits of Using Partitioned Tables/Indexes
Performance improvement:The Select statement only retrieves records in the current partition, reducing the total number of records and effectively improving query performance. In addition, partitions can be mapped to different physical disks through tablespaces, which disperses device IO and improves performance;
Partition O & M:You can manage data loading, index creation (and Reconstruction), and data backup and recovery for different partitions. Because partition management can effectively reduce the interference and impact of intervals. For more information about the benefits of Partitioning, see Oracle Partitioning.
When to use a partitioned table and when to use a partitioned table, there is no precise boundary. Oracle only has some general suggestions. You need to evaluate the specific usage: <br> tables = "" greater = "" than = "" 2 gb = "" shold = "" always = "" be = "" considered = "" for = "" partitioning. tables = "" containing = "" historical = "" data, = "" in = "" which = "" new = "" data = "" is = "" added = "" into = "" the = "" newest = "" partition. = "" a = "" typical = "" example = "" table = "" where = "" only = "" current = "" month's = "" updatable =" "and =" "other =" "11 =" "months =" "are =" "read =" "only. tip: = "" SQL statement used to calculate the table size
How to partition (partition method)
Partition KeyThe Partition table/index determines the Partition in which the record is stored based on one or more fields. The selected Partition field is called the Partition Key, oracle will automatically find the corresponding Partition based on the Partition Key for insert, update, and delete operations.
Partitioning Method Oracle provides six partitioning methods:
Partitioning Method
Brief Description
Range Partitioning
Used when there are logical ranges of data. Possible usage: dates, part numbers, and serial numbers
Hash Partitioning
Used to spread data evenly over partitions. Possible usage: data has no logical groupings
List Partitioning
Used to list together unrelated data into partitions. Possible usage: a number of states list partitioned into a region
Composite Range-Hash Partitioning
Used to range partition first, then spreads data into hash partitions. Possible usage: range partition by date of birth then hash partition by name; store the results into the hash partitions
Composite Range-List Partitioning
Used to range partition first, then spreads data into list partitions. Possible usage: range partition by date of birth then list partition by state, then store the results into the list partitions
Examples
Range Partitioning Example-- Create tablespace invoices_2010 datafile 'e: \ app \ TianPan \ oradata \ tbs01.dbf' size 50 m;
List Partitioning Example -- create a tablespace
Useful Query
[Query] How do I know whether a table or index is partitioned?SELECT * FROM dba_part_tables;
[Query] how to list partition table or index partition objects SELECT * FROM dba_tab_partitions WHERE table_name = '<table_name> ';
Partition Table data export and import
Full table ExportC: \ Users \ TianPan>
Exp system/welcome @ ptian file = c: \ test. dmp tables = Invoices
Partition Table export C: \ Users \ TianPan>
Exp system/welcome @ ptian file = c: \ test_part.dmp tables = Invoices: part03
Full table Import
Imp system/welcome @ ptian file = test. dmp full = y
Partition Table ImportImp system/welcome @ ptianfile = test_part.dmp full = y
Data Pump expdp/impdp ModeExport the entire table
