The perfect analytical ORACLE partition table, partition index

Recommended for you: Get network issues from WhatsUp Gold. Not end users.
ORACLE partition table, partition index of ORACLE for the partition table is actually the table fragmentation, the general form is a period of storage, and the partition table can be divided into a plurality of segments, so the search data process is first position according to the location query partition range, namely data within the partition or that a few, and then go to find data in the partition, a partition of a general guarantee about four hundred thousand data is quite normal, but not arbitrary partition table is established, and its maintenance is relatively complex, and the creation of index is also a little attention to detail, the following can be.

1, The type description:
Range partition method, also is the most commonly used method of partitioning, the combination of a field or several fields of value, from small to large, partition according to the specified scope statement, we will be stored in the specified partition in INSERT data.
List partition, is generally two partition more on a range basis, is a list of partition, generally speaking in some areas, or the specified rules code division.
Hash partition method, it has no fixed rules, managed by ORACLE, only need to value INSERT in, ORACLE will automatically go to the partitioned according to a set of HASH algorithm, only need to tell the ORACLE should be divided into several zones can be.

Partitions can be a combination of two two, ORACLE 11G two two before combination must to range as a first level partition, ORACLE currently supports a maximum of 2 level partition, but this level has been enough for us to use.
The most simple way to partition partition is created to illustrate the problem, take the range partition to illustrate the problem. (basic create statement.):
CREATE [url=]TABLE[/url] TABLE_PARTITION(
       COL1   NUMBER,
       COL2   VARCHAR2(10)

partition by range(COL1)(
          partition TAB_PARTOTION_01 values less than (450000),
          partition TAB_PARTOTION_02 values less than (900000),
          partition TAB_PARTOTION_03 values less than (1350000),
          partition TAB_PARTOTION_04 values less than (1800000),
          partition TAB_PARTOTION_OTHER values less THAN (MAXVALUE)
);

The partition table created four fixed length partitions, ideally, store 450000 data, the extended partition is more than the amount of partition, when it is discovered that the extended partition data when, can be extended to do SPLIT operation, the show later, here to say some query function partition table used, we first insert some data into it.
INSERT INTO TABLE_PARTITION(COL1,COL2)
VALUES(1,'Data test');
INSERT INTO TABLE_PARTITION(COL1,COL2)
VALUES(23,'Data test');
INSERT INTO TABLE_PARTITION(COL1,COL2)
VALUES(449000,'Data test');

INSERT INTO TABLE_PARTITION(COL1,COL2)
VALUES(450000,'Data test');



INSERT INTO TABLE_PARTITION(COL1,COL2)
VALUES(1350000,'Data test');

INSERT INTO TABLE_PARTITION(COL1,COL2)) 
VALUES(900000,'Data test');

INSERT INTO TABLE_PARTITION(COL1,COL2)
VALUES(1800000-1,'Data test');
COMMIT;
In order to determine which partition in which data query data according to the partition to (application development basic won't be used, because not to partition written dead)
SQL> SELECT * FROM TABLE_PARTITION partition(TAB_PARTOTION_01);
      COL1     COL2
---------- ---------------
         1        Data test
        23       Data test
449000 test data
The first partition are: 1, 23, 44900 these data, it is inserted, ORACLE is to find their own partition, partition table [url=] in the seed management [/url] can also go through procedures, ORACLE provides a set of, oneself can do. The rest of the data on their own to check, is a truth.

2, Zoned application:
General a table exceeds the size of 2G, ORACLE is recommended to use the partition table, Generally need to create an index partition, When it comes to partition index, It can be divided into: global index, partitioning index, Namely: global index and local index, The former is indexed mode by default to create indexes on partitioned tables., And no index is partitioned index is also (table structure, Indexing large also need to partition, A special index after write) and global index can be modified for the partitioned index, But and local index difference, The former district in full accordance with the way to create a custom, And the table structure is completely independent, It is illustrated below two used online for global indexes on partitioned tables:
2.1, For not partitioned indexes on partitioned tables (there are some tricks, but is partition, but the index does not partition):

Create (to create):
CREATE INDEX <index_name> ON <partition_table_name>(<column_name>);



2.2, For a partitioned indexes on partitioned tables:

Create a grammar:
CREATE [url=]INDEX[/url] INX_TAB_PARTITION_COL1 ON TABLE_PARTITION(COL1)
  GLOBAL PARTITION BY RANGE(COL1)
         PARTITION IDX_P1 values less than (1000000),
         PARTITION IDX_P2 values less than (2000000),
         PARTITION IDX_P3 values less than (MAXVALUE)
  )

2.3, The LOCAL index structure:


Create a grammar:
CREATE INDEX INX_TAB_PARTITION_COL1 ON TABLE_PARTITION(COL1) LOCAL;
Can also be in accordance with the partition table partition structure to give the one one definition, index partitions will be renamed.
Bitmap indexes can only partition on the LOCAL index, cannot be GLOBAL global index.

2.4, Comparative index:

Generally use the LOCAL index is more convenient, And the maintenance cost is low, And the LOCAL index is created on the basis of the index in the partition, Similar to a sub table to create index, This overhead is to distinguish between the main partition, Management is standard., A wide range of applications in the OLAP system; and the GLOBAL index is global type index, You can adjust the partition according to the actual situation of the class, Not in accordance with the one one partition structure definition, The relative cost of higher, Use more in OLTP environment, Here the so-called OLTP and OLAP is relative, Is not a special project, No concept of absolute, In the application process on the basis of the actual circumstances of the case, To improve the overall running performance.

3, Common view:
1, Query the user what is the partition table:
SELECT * FROM USER_PART_TABLES;

2, What are the current user query partitioning index under:
SELECT * FROM USER_PART_INDEXES;


3, Query the current user partition index partition information:
SELECT * FROM USER_IND_PARTITIONS T
WHERE T.INDEX_NAME=?

4, Query the current user partition table partition information:
SELECT * FROM USER_TAB_PARTITIONS T
WHERE T.TABLE_NAME=?;

5, A partition of data query:
SELECT COUNT(*) FROM TABLE_PARTITION PARTITION(TAB_PARTOTION_01);


6, The query index, table in the list to create a partition:
SELECT * FROM USER_PART_KEY_COLUMNS;


7, Query a user level two partition information (only created two partition is data):
SELECT * FROM USER_TAB_SUBPARTITIONS;

4, Maintenance operation:
4.1, Delete partition
    ALTER TABLE TABLE_PARTITION DROP PARTITION TAB_PARTOTION_03;
     If a global index partition structure, because the global index and table can be inconsistent, if you do not agree, will cause the entire global index failure, when deleted partitions, alter to:
     ALTER TABLE TABLE_PARTITION DROP PARTITION TAB_PARTOTION_03 UPDATE GLOBAL INDEXES;


4.2, Partition merge (delete a partition, from the middle or the two partition is required after the merger reduced the number of partitions)
Merge partitions and remove the middle RANGE is a bit like, but merge partitions is not delete data, for LIST, HASH and RANGE partition partition is not the same, the grammar:
ALTER TABLE TABLE_PARTITION MERGE PARTITIONS    TAB_PARTOTION_01,TAB_PARTOTION_02 INTO PARTITION MERGED_PARTITION;


4.3, Separate partition (general partition from the extended partition from separating)
ALTER TABLE TABLE_PARTITION SPLIT PARTITION TAB_PARTOTION_OTHERE AT(2500000)
INTO (PARTITION TAB_PARTOTION_05,PARTITION TAB_PARTOTION_OTHERE);

4.4, Create a new partition (the partition data if it can not provide scope, then insert error when, need to expand the scope of add partition)
Generally have an extended partition is delimited by the way, if the above create table did not create a TAB_PARTOTION_OTHER partition, inserts the data is large (more than 1800000 according to the established rules, you should create a new partition to store), you can create a new partition, such as:
In order to test, we will extend the first partition deleted and then create a new partition (because the ORACLE requirements, partition of the data are not allowed to overlap, namely according to the same data partitioning field cannot be stored in a different partition):
ALTER TABLE TABLE_PARTITION DROP PARTITION TAB_PARTOTION_OTHER;
ALTER TABLE TABLE_PARTITION ADD PARTITION TAB_PARTOTION_06 VALUES LESS THAN(2500000);


To create a new child in the partition partition as follows (RANGE partition, if LIST or HASH partition, will create modify can correspond to the way):
ALTER TABLE <table_name> MODIFY PARTITION <partition_name> ADD SUBPARTITION <user_define_subpartition_name> VALUES LESS THAN(....);

4.5, Modify the partition name (modify attribute information related):
ALTER TABLE TABLE_PARTITION RENAME PARTITION MERGED_PARTITION TO MERGED_PARTITION02;

4.6, The swap partition (fast data exchange, is actually the exchange segment name pointer)
First create a switching table, identical with the original table structure, if there is data, must conform to the corresponding condition exchange partition:
CREATE TABLE TABLE_PARTITION_2
  AS SELECT * FROM TABLE_PARTITION WHERE 1=2;
Then the first partition of data exchange.
ALTER TABLE TABLE_PARTITION EXCHANGE PARTITION TAB_PARTOTION_01
  WITH TABLE TABLE_PARTITION_2 INCLUDING INDEXES; 
Find the first partition of data and table TABLE_PARTITION_2. The instantaneous exchange at this time, faster than a TRUNCATE, because the procedure is carried out without the data transfer process, only the section name, it doesn't matter and the actual amount of data.

If the sub partitions can also be exchanged with an external table, only need to key is amended as: SUBPARTITION.

4.7, Empty partition data
   ALTER TABLE <table_name> TRUNCATE PARTITION <partition_name>;
   ALTER TABLE <table_name> TRUNCATE subpartition <subpartition_name>;

9, Debris disk compression
On a partition of the table to disk compression, when to partition the internal data for a number of UPDATE, DELETE after the operation, need time for disk compression, or in the case of a query, if the FULL SCAN scan data, will put the empty block will scan the table, disk compression required for migration, so first of all need to operate:
ALTER TABLE <table_name> ENABLE ROW MOVEMENT ;


On a partition of the table compression grammar:
ALTER TABLE <table_name>
modify partition <partition_name> shrink space;
Compression of the common table:
ALTER TABLE <table_name> shrink space;
For an index will also need to be compressed, index and table:
ALTER INDEX <index_name> shrink space;

10, The partition table is the re analysis and index analysis
Compression of tables, need the tables and indexes of re analysis, on the re analysis, there are two general ways:
In the ORACLE 10G previously, the use of:
BEGIN
     dbms_stats.gather_table_stats(USER,UPPER('<table_name>'));
  END;


ORACLE 10G, you can use:
  ANALYZE TABLE <table_name> COMPUTE STATISTICS;

Index to analysis, the two kinds of methods are modified, such as the first can use: gather_index_stats, while the second is amended as: ANALYZE INDEX can, but generally more common is recompiled:
For the partition table and index partitions, each partition of the index need to recompile, here to LOCAL index (for example each index partition and partition structure of the same name, the default partition and partition the same name):
ALTER INDEX <index_name> REBUILD PARTITION <partition_name>;
The global index, can be modified according to the partition name global index lock, without partition, and the ordinary single table index to re compile the same way:
ALTER INDEX <index_name> REBUILD;


11, Associated objects recompilation,
The above table, index recompiled, especially on the compression will produce for migration, this process may result in failure, some view the process object, time to re compile time.


12, Spread: HASH partition, if you create a new partition, it can be re HASH distribution:
ALTER TABLE <table_name> COALESCA PARTITION% 

5, Summary: when building regression partition, partition type, index, how to corresponding[url=]SQL[/url]

1, Create the opportunity

     The above has been described, 2G above the table, ORACLE recommends creating partitions.
     Partition means according to actual circumstances, can improve the overall performance.
     Partition of the field must be often used to field to extract data, otherwise it will lead to traverse multiple partitions in the extraction process, so no more slowly than partition.
     District field to select the appropriate data, more evenly distributed to each partition, not too much or too little, and according to the district field can quickly locate partition range.
     Under normal circumstances, as far as possible and business operation is completed within a partition.

2, Partition type
The partition is RANGE, LIST, HASH,
     RANGE through range partitioning, is also the most commonly used partition, the partition that variable length numeric string in one, many people will lead to think is a numeric type, and in accordance with the digital District, this will happen very uneven distribution phenomenon.
     LIST is a list of partition, as the two level partition exists (of course can also be its own partition, ORACLE 11G on the partition can also exist as a primary partition), on the basis of RANGE, if the data needs to be partitioned, and in the RANGE based on the amount of data is relatively fixed, only bigger, can according to certain the rule further partition. }
     HASH only specifies the number of partitions, partition details are completed by ORACLE, increase HASH partitions can be re distributed data.
     Note: District field cannot use function after conversion in the partition, such as, a digital string field, the first TO_NUMER (COL_NAME) after partition.
 
3, Index category
Divided: GLOBAL index and LOCAL index, money and can be divided into: GLOBAL does not partition index, and GLOBAL index partition.

     GLOBAL is not partitioned index generally not recommended, because it is the mapping of a table, with a big tree in the process, this speed is not necessarily faster than division.
     GLOBAL partition index, find the data if the through the index, is the first index of internal partition location, and then to find ROWID in the partitioned index, and then back to the table data extraction.
     The LOCAL index is corresponding to a number of partitions and one by one, can be said to locate the partition table partitioning can also said that the first location index partitions, as they are one one corresponding, find the corresponding partition, partition internal index data set.

4, The corresponding application 0
     The partition table, partition index, index, to make use of the advantages, the most basic is to extract the data, through the narrow range of data to a even do a comprehensive scan will not be too slow when.
      A WHERE condition so SQL must have the field on the partition, the partition data quickly locate inside, and try to locate a partition inside (this is related and create partition rules).
      Create partition itself is not the performance, to make good use of it can improve performance, in the RAC cluster in the necessary, if there is more appropriate to extract data partition, parallel extraction could improve the extraction speed.
      For the index, there is only mentioned the partitioned index creation and indexing the maintenance mode, the index theory understanding will be easier to recognize data extraction techniques.
 
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download

Posted by April at December 17, 2013 - 11:18 PM