Shulou(Shulou.com)06/01 Report--

one。 Theoretical knowledge of partition table

Oracle provides partitioning technology to support VLDB (Very Large DataBase). By judging the partition column, the partition table puts the different records of the partition column into different partitions. Partitions are completely transparent to the application.

Oracle's partition table can include multiple partitions, each of which is a separate SEGMENT that can be stored in different table spaces. When querying, you can access the data in each partition by querying the table, or you can query by specifying the partition directly when querying.

When When to Partition a Table needs a partition table, two suggestions on the official website are as follows:

Tables greater than 2GB should always be considered for partitioning.

Tables containing historical data, in which new data is added into the newest partition. A typical example is a historical table where only the current month's data is updatable and the other 11 months are read only.

Maximum support in oracle 10g: 1024k-1 partitions:

Tables can be partitioned into up to 1024K-1 separate partitions

Instructions on partitioned tables and indexes on the online documentation:

Partitioned Tables and Indexes

Http://download.oracle.com/docs/cd/B19306_01/server.102/b14220/partconc.htm#sthref2604

Partitions provide the following benefits:

(1) the possibility of data corruption is reduced by dispersing the data into various partitions.

(2) individual partitions can be backed up and restored

(3) you can map partitions to different physical disks to disperse the IO

(4) improve manageability, availability and performance.

Oracle 10g provides the following partition types:

(1) range partition (range)

(2) Hash partition (hash)

(3) list partition (list)

(4) range-hash composite partition (range-hash)

(5) range-list compound partition (range-list).

Range partition:

Range partition is a widely used way of table partition, which takes the range of column values as the partition condition and stores records in the range partition where the column values are located.

For example, in January 2010, the data is put in partition a, and the data in February is put in partition b. When creating, you need to specify the column based on and the range value of the partition.

When partitioning by time, if some records cannot predict the range temporarily, you can create a maxvalue partition, and all records that are not within the specified range will be stored in the same partition as maxvalue.

Such as:

Create table pdba (id number, time date) partition by range (time) (partition p1 values less than (to_date ('2010-10-1,' yyyy-mm-dd')), partition p2 values less than (to_date ('2010-11-1,' yyyy-mm-dd')), partition p3 values less than (to_date ('2010-12-1,' yyyy-mm-dd')), partition p4 values less than (maxvalue) Hash partition:

For tables that cannot be effectively scoped, you can use hash partitioning, which is still helpful to improve performance. Hash partition will evenly distribute the data in the table to several partitions you specify, and the partition where the column is located is automatically allocated according to the hash value of the partition column, so you cannot control or know which record will be placed in which partition. Hash partition can also support multiple dependent columns.

Such as:

Create table test (transaction_id number primary key,item_id number (8) not null) partition by hash (transaction_id) (partition part_01 tablespace tablespace01,partition part_02 tablespace tablespace02,partition part_03 tablespace tablespace03)

Here, we specify the tablespace for each partition.

List partition:

The List partition also needs to specify the value of the column, and its partition value must be specified explicitly. There can be only one partition column, and multiple columns can not be specified as partition dependent columns at the same time as range or hash partitions, but its corresponding values for a single partition can be multiple.

When partitioning, you must determine the possible values of the partition column, and once the inserted column value is not within the partition range, the insert / update will fail, so it is generally recommended that when using a list partition, create a default partition to store records that are not within the specified range, similar to the maxvalue partition in the range partition.

When partitioning according to a field, such as a city code, you can specify default to put all data from non-partitioning rules into this default partition.

Such as:

Create table custaddr (id varchar2 (15 byte) not null,areacode varchar2 (4 byte)) partition by list (areacode) (partition t_list025 values ('025'), partition t_list372 values (' 372'), partition t_list510 values ('510'), partition p_other values (default)) combined partition:

If a table is still large after partitioning according to a column, or for some other requirements, the partition can also be re-partitioned by creating subpartitions within the partition, that is, by combining partitions.

There are two kinds of combined partitions in 10g: range-hash,range-list. Note the order that the root partition can only be a range partition, and the subpartition can be a hash partition or a list partition.

Such as:

Create table test (transaction_id number primary key,transaction_date date) partition by range (transaction_date) subpartition by hash (transaction_id) subpartitions 3 store in (tablespace01,tablespace02,tablespace03) (partition part_01 values less than (to_date ('2009-01-01-01-01), partition part_02 values less than (to_date (' 2010-01-01-01), partition part_03 values less than (maxvalue)) Create table emp_sub_template (deptno number, empname varchar (32), grade number) partition by range (deptno) subpartition by hash (empname) subpartition template (subpartition a tablespace ts1,subpartition b tablespace ts2,subpartition c tablespace ts3,subpartition d tablespace ts4) (partition p1 values less than (1000), partition p2 values less than (2000), partition p3 values less than (maxvalue)) Create table quarterly_regional_sales (deptno number, item_no varchar2 (20), txn_date date, txn_amount number, state varchar2 (2)) tablespace ts4partition by range (txn_date) subpartition by list (state) (partition Q1 / 1999 values less than (to_date)) (subpartition q1_1999_northwest values ('or',' wa'), subpartition q1_1999_southwest values ('az',' ut', 'nm') Subpartition q1_1999_northeast values ('ny',' vm', 'nj'), subpartition q1_1999_southeast values (' fl', 'ga'), subpartition q1_1999_northcentral values (' sd', 'wi'), subpartition q1_1999_southcentral values (' ok', 'tx'), partition Q2, 1999 values less than (to_date) (subpartition q2_1999_northwest values (' or', 'wa') Subpartition q2_1999_southwest values ('az',' ut', 'nm'), subpartition q2_1999_northeast values (' ny', 'vm',' nj'), subpartition q2_1999_southeast values ('fl',' ga'), subpartition q2_1999_northcentral values ('sd',' wi'), subpartition q2_1999_southcentral values ('ok',' tx'), partition Q3 October 1999 values less than (to_date ) (subpartition q3_1999_northwest values ('or',' wa'), subpartition q3_1999_southwest values ('az',' ut', 'nm'), subpartition q3_1999_northeast values (' ny', 'vm',' nj'), subpartition q3_1999_southeast values ('fl',' ga'), subpartition q3_1999_northcentral values ('sd',' wi'), subpartition q3_1999_southcentral values ('ok',' tx')) Partition q4x1999 values less than (to_date ('1murjanly2000)) (subpartition q4_1999_northwest values (' or', 'wa'), subpartition q4_1999_southwest values (' az', 'ut',' nm'), subpartition q4_1999_northeast values ('ny',' vm', 'nj'), subpartition q4_1999_southeast values (' fl', 'ga'), subpartition q4_1999_northcentral values (' sd', 'wi') Subpartition q4_1999_southcentral values ('ok',' tx')

In Oracle 11g, the combined partition function has been enhanced and range-range,list-range has been added.

List-list,list-hash, and supports Interval partitions and virtual column partitions in 11g.

For this piece, please refer to Blog:

Brief introduction of New Features of Oracle 11g

Http://blog.csdn.net/tianlesoftware/archive/2010/01/06/5134819.aspx

Interval partitions and virtual columns of partition tables by week partition table

Http://blog.csdn.net/tianlesoftware/archive/2010/06/10/5662337.aspx

two。 There are four ways to convert a normal table to a partitioned table:

Export/import method

Insert with a subquery method

Partition exchange method

DBMS_REDEFINITION

Specific reference:

How to Partition a Non-partitioned Table [ID 1070693.6]

Http://blog.csdn.net/tianlesoftware/archive/2011/03/02/6218704.aspx

Logical export import is not explained here, let's take a look at the other three methods.

2.1insert: Insert with a subquery method

This method is implemented using insert. Of course, you can insert data together when you create a partition table, or you can enter it in insert after you create it. This method uses DDL statement, does not generate UNDO, only produces a small amount of REDO, after the completion of the table, the data has been distributed to each partition.

The SQL > select count (*) from dba;COUNT (*)-2713235SQL > alter session set nls_date_format='yyyy-mm-dd hh34:mi:ss'; session has changed. Interval of SQL > select time_fee from dba where rownum2.1.1 Oracle 11g

Interval creation in 11g, this method is automatically created for partitions that are not fully written. For example, I only write the date of January here, and if the inserted data has other months, the corresponding partition will be generated automatically.

/ * Formatted on 15:41:09 on 2011-03-02 (QP5 v5.115.810.9015) * / CREATE TABLE intervaldavePARTITION BY RANGE (time_fee) INTERVAL (NUMTOYMINTERVAL (1, 'MONTH')) (PARTITION part1VALUES LESS THAN (TO_DATE (' 01 MM/DD/YYYY') ASSELECT ID, TIME_FEE FROM DAVE;SQL > select table_name,partition_name from user_tab_partitions where table_name='INTERVALDAVE' TABLE_NAME PARTITION_NAME---INTERVALDAVE PART1INTERVALDAVE SYS_P24INTERVALDAVE SYS_P25INTERVALDAVE SYS_P26INTERVALDAVE SYS_P33INTERVALDAVE SYS_P27INTERVALDAVE SYS_P282.1.2 Oracle 10g version

In 10g, I need to write all the partitions.

Sql > create table pdba (id, time) partition by range (time) 2 (partition p1 values less than (to_date ('2010-10-1,' yyyy-mm-dd')), 3 partition p2 values less than (to_date ('2010-11-1,' yyyy-mm-dd')), 4 partition p3 values less than (to_date ('2010-12-1,' yyyy-mm-dd')), 5 partition p4 values less than (maxvalue)) 6 as select id, time_fee from dba; table has been created. SQL > select table_name,partition_name from user_tab_partitions where table_name='PDBA';TABLE_NAME PARTITION_NAME-- PDBA P1PDBA P2PDBA P3PDBA P4sql > select count (*) from pdba partition (p1) Count (*)-1718285sql > select count (*) from pdba partition (p2); count (*)-183667sql > select count (*) from pdba partition (p3); count (*)-188701sql > select count (*) from pdba partition (p4) Count (*)-622582sql > now that the partitioned table is built, but the name of the table is different, you need to rename the table with rename: SQL > rename dba to dba_old; table has been renamed. The SQL > rename pdba to dba; table has been renamed. SQL > select table_name,partition_name from user_tab_partitions where table_name='DBA';TABLE_NAME PARTITION_NAME-- DBA P1DBA P2DBA P3DBA P42.2. Swap partition: Partition exchange method

This method only modifies the definition of partitions and tables in the data dictionary, without data modification or replication, and is the most efficient. It is suitable for the operation that a table containing a large amount of data is transferred to a partition in a partitioned table. Try to operate in your spare time.

The steps for swapping partitions are as follows:

Create a partition table, assuming that there are 2 partitions, P 1 and P 2.

Create Table A to store the data of the P1 rule.

Create Table B to store the data of the P2 rule.

Swap with tables An and P1. Put the data from Table An into the P1 partition

Swap with table B and p2. Store the data from Table B in the P2 partition.

Create a partition table: sql > create table p_dba2 (id number,time date) 3 partition by range (time) 4 (5 partition p1 values less than (to_date ('2010-09-1,' yyyy-mm-dd')), 6 partition p2 values less than (to_date ('2010-11-1,' yyyy-mm-dd')) 7); the table has been created.

Note: I have only created 2 partitions here, not partitions for other data.

Create 2 base tables corresponding to partitions:

SQL > CREATE TABLE dba_p1 as SELECT id,time_fee FROM dba_old WHERE time_fee CREATE TABLE dba_p2 as SELECT id,time_fee FROM dba_old WHERE time_feeTO_DATE ('2010-09-1,' YYYY-MM-DD'); the table has been created. SQL > select count (*) from dba_p1;COUNT (*)-1536020SQL > select count (*) from dba_p2;COUNT (*)-365932SQL >

Exchange 2 base tables with 2 partitions:

The SQL > alter table p_dba exchange partition p1 with table dba_p1; table has changed. The SQL > alter table p_dba exchange partition p2 with table dba_p2; table has changed.

Query 2 partitions:

SQL > select count (*) from p_dba partition (p1); COUNT (*)-1536020SQL > select count (*) from p_dba partition (p2); COUNT (*)-365932 Note: the data is the same as the previous base table. Query the original two base tables: SQL > select count (*) from dba_p2;COUNT (*)-0SQL > select count (*) from dba_p1;COUNT (*)-0

Note: the data of the 2 base tables becomes 0.

Let's look at a problem here. In general, when we create a partition table, there will be another partition to store data that does not match the partition rules. In this example, I only created two partitions, not maxvalue partitions. Now I'm going to insert a piece of data that doesn't meet the rules and look at the results:

SQL > insert into p_dba values (2012-12-29); insert into p_dba values (2012-12-29)

*

An error occurred on line 1:

ORA-14400: the inserted partition keyword is not mapped to any partition

SQL > insert into p_dba values (999999 memoir toast date ('2009-12-29mm maidd'))

1 line has been created.

SQL > select * from p_dba where id=999999

ID TIME

-- 999999 29-12-09SQL > alter session set nls_date_format='yyyy-mm-dd hh34:mi:ss'

The session has changed.

SQL > select * from p_dba where id=999999

ID TIME

-- 999999 2009-12-29 00:00:00SQL >

Through this test, it is clear that if the inserted data does not meet the partition rules, an ORA-14400 error will be reported.

2.3. Use online redefinition: DBMS_REDEFINITION

Online redefinition ensures data consistency, and tables can operate DML normally most of the time. The table is locked only at the moment of switching, which has high maneuverability. This method has strong flexibility and can meet a variety of different needs. Moreover, the corresponding authorization and various constraints can be established before the handover, so that no additional management operations are needed after the handover is completed.

For an introduction to DBMS_REDEFINITION, refer to the official link:

Http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_redefi.htm#CBBFDJBC

For creating a partition table with online redefinition, refer to:

How To Partition Existing Table Using DBMS_Redefinition [ID 472449.1]

Http://blog.csdn.net/tianlesoftware/archive/2011/03/02/6218693.aspx

This feature is available only in versions after 9.2.0.4, and the online redefinition table has the following features:

(1) modify the storage parameters of the table

(2) transfer the table to another tablespace

(3) add parallel query options.

(4) add or delete partitions

(5) rebuild the table to reduce fragmentation

(6) change the heap table into an index to organize the table or vice versa.

(7) add or delete a column.

Use some of the constraints that are redefined online:

(1) There must be enough space to hold two copies of the table.

(2) Primary key columns cannot be modified.

(3) Tables must have primary keys.

(4) Redefinition must be done within the same schema.

(5) New columns added cannot be made NOT NULL until after the redefinition operation.

(6) Tables cannot contain LONGs, BFILEs or User Defined Types.

(7) Clustered tables cannot be redefined.

(8) Tables in the SYS or SYSTEM schema cannot be redefined.

(9) Tables with materialized view logs or materialized views defined on them cannot be redefined.

(10) Horizontal sub setting of data cannot be performed during the redefinition.

Under Oracle 10.2.0.4 and 11.1.0.7 versions, online redefinition may encounter the following bug:

Bug 7007594-ORA-600 [12261]

Http://blog.csdn.net/tianlesoftware/archive/2011/03/02/6218681.aspx

The general operation flow of online redefinition is as follows:

(1) create the underlying table A, if it exists, no action is required.

(2) create a temporary partition table B.

(3) start redefining and import the data from base table An into temporary partition table B.

(4) end the redefinition. At this time, the two tables have been exchanged in the Name Directory of DB. That is, at this point, base table A becomes a partition table, and the temporary partition table B we created becomes a regular table. At this point we can delete the temporary table B we created. It's already a regular watch.

Let's look at an example:

Create basic tables and indexes

Sql > conn icd/icd; is connected. Sql > create table unpar_table (2 id number (10) primary key,3 create_date date4); table created. Sql > insert into unpar_table select rownum, created from dba_objects; has created 72288 lines. Sql > create index create_date_ind on unpar_table (create_date); index created. Sql > commit; submission completed.

Collect statistics for the table

Sql > exec dbms_stats.gather_table_stats ('icd',' unpar_table', cascade = > true)

The pl/sql process completed successfully.

Create a temporary partition table

Sql > create table par_table (id number primary key, time date) partition by range (time) 2 (partition p1 values less than (to_date ('2004-7-1,' yyyy-mm-dd')), 3 partition p2 values less than (to_date ('2005-1-1,' yyyy-mm-dd')), 4 partition p3 values less than (to_date ('2005-7-1,' yyyy-mm-dd')), 5 partition p4 values less than (maxvalue); the table has been created.

Perform a redefinition operation

4.1 check the reasonableness of redefinition

Sql > exec dbms_redefinition.can_redef_table ('icd',' unpar_table')

The pl/sql process completed successfully.

4.2 if there is no problem with 4.1, start redefining, which may take a while.

Note here: if the partitioning table has the same column name as the original table, it can be done as follows:

SQL > BEGIN

DBMS_REDEFINITION.start_redef_table (

Uname = > 'ICD'

Orig_table = > 'unpar_table'

Int_table = > 'par_table')

END

/

If the column names of the partitioned table are not the same as the original table, you need to respecify the mapping relationship when you start the redefinition:

SQL > EXEC DBMS_REDEFINITION.START_REDEF_TABLE (

'ICD'

'unpar_table'

'par_table'

'ID ID, create_date TIME',-- specify the new mapping relationship here

DBMS_REDEFINITION.CONS_USE_PK)

After this step, the data has been synchronized to the temporary partition table.

4.3 synchronize new tables, which is an optional operation

SQL > BEGIN

2 dbms_redefinition.sync_interim_table (

3 uname = > 'ICD'

4 orig_table = > 'unpar_table'

5 int_table = > 'par_table')

6 END

7 /

The PL/SQL process completed successfully.

4.4 create an index, online redefinition redefines only the data, and the index needs to be created separately.

Sql > create index create_date_ind2 on par_table (time)

The index has been created.

4.5 collect statistics for the new table

Sql > exec dbms_stats.gather_table_stats ('icd',' par_table', cascade = > true)

The pl/sql process completed successfully.

4.6 end redefinition

SQL > BEGIN

2 dbms_redefinition.finish_redef_table (

3 uname = > 'ICD'

4 orig_table = > 'unpar_table'

5 int_table = > 'par_table')

6 END

7 /

The PL/SQL process completed successfully.

End the meaning of the redefinition:

The base table unpar_table and the temporary partition table par_table are exchanged. At this point, the temporary partition table par_table becomes a regular table, and our base table, unpar_table, becomes a partition table.

When we redefine, the base table unpar_table can perform DML operations. There is only a brief lock on the table when the two tables are switched.

Delete temporary table

SQL > DROP TABLE par_table

Table has been deleted.

Index renaming

SQL > ALTER INDEX create_date_ind2 RENAME TO create_date_ind

The index has changed.

Verification

Sql > select partitioned from user_tables where table_name = 'UNPAR_TABLE'

Par

-

Yes

Sql > select partition_name from user_tab_partitions where table_name = 'UNPAR_TABLE'

Partition_name

-

P1

P2

P3

P4

Sql > select count () from unpar_table

Count ()

-

72288

Sql > select count () from unpar_table partition (p4)

Count ()

-

72288

Sql >

three。 Other operations of partitioned tables

3.1 add a new partition

There are 2 situations when adding a new partition:

(1) the boundary in the original partition is maxvalue or default. In this case, we need to remove the boundary partition drop, add a new partition, and then add a new partition. Or use split to split the boundary partition.

(2) there is no boundary zone. In this case, just add the partition.

Example of adding a new zone with a boundary zone:

(1) the information of partitioned tables and indexes is as follows:

SQL > create table custaddr

2 (

3 id varchar2 (15 byte) not null

4 areacode varchar2 (4 byte)

5)

6 partition by list (areacode)

7 (

8 partition t_list556 values ('556') tablespace icd_service

9 partition p_other values (default) tablespace icd_service

10)

The table has been created.

SQL > create index ix_custaddr_id on custaddr (id)

2 local (

3 partition t_list556 tablespace icd_service

4 partition p_other tablespace icd_service

5)

The index has been created.

(2) insert several test data:

SQL > insert into custaddr values ('1Fengliao 556')

1 line has been created.

SQL > insert into custaddr values (551)

1 line has been created.

SQL > insert into custaddr values ('3cm, 555')

1 line has been created.

SQL > commit

The submission is complete.

SQL > select * from custaddr

ID AREA

-

1 556

2 551

3 555

SQL > select * from custaddr partition (t_list556)

ID AREA

-

1 556

SQL >

(3) delete default partition

Sql > alter table custaddr drop partition p_other

The table has changed.

Sql > select table_name,partition_name from user_tab_partitions where table_name='CUSTADDR'

Table_name partition_name

Custaddr t_list556

(4) add a new partition

SQL > alter table custaddr add partition t_list551 values ('551') tablespace icd_service

The table has changed.

SQL > select table_name,partition_name from user_tab_partitions where table_name='CUSTADDR'

TABLE_NAME PARTITION_NAME

CUSTADDR T_LIST556

CUSTADDR T_LIST551

(5) add default partition

SQL > alter table custaddr add partition p_other values (default) tablespace icd_service

The table has changed.

SQL > select table_name,partition_name from user_tab_partitions where table_name='CUSTADDR'

TABLE_NAME PARTITION_NAME

CUSTADDR T_LIST556

CUSTADDR T_LIST551

CUSTADDR P_OTHER

(6) for local indexes, oracle will automatically add a local partitioned index. Verify:

Sql > select owner,index_name,table_name,partitioning_type from dba_part_indexes where index_name='ix_custaddr_id'

Owner index_name table_name

-

Icd ix_custaddr_id custaddr

Sql > select index_owner,index_name,partition_name from dba_ind_partitions where index_name='ix_custaddr_id'

Index_owner index_name partition_name

-

Icd ix_custaddr_id p_other

Icd ix_custaddr_id t_list551

Icd ix_custaddr_id t_list556

The partition index is created automatically.

3.2 split Partition split

In 3. 1, we showed that you can use split to add partitions. Here we use the split method to continue the above experiment.

Sql > alter table custaddr split partition p_other values ('552') into (partition t_list552 tablespace icd_service, partition p_other tablespace icd_service)

The table has changed.

Notice the red here. If it is of type Range, use at,List and use Values.

SQL > select table_name,partition_name from user_tab_partitions where table_name='CUSTADDR'

TABLE_NAME PARTITION_NAME

CUSTADDR T_LIST556

CUSTADDR T_LIST551

CUSTADDR T_LIST552

CUSTADDR P_OTHER

SQL > select index_owner,index_name,partition_name from dba_ind_partitions where index_name='IX_CUSTADDR_ID'

Index_owner index_name partition_name

-

Icd ix_custaddr_id p_other

Icd ix_custaddr_id t_list551

Icd ix_custaddr_id t_list552

Icd ix_custaddr_id t_list556

Note: partitioned tables automatically maintain local partitioned indexes. The global index is invalidated and rebuild is required.

3.3 merge partition Merge

The adjacent partition can be merge as a partition, the lower boundary of the new partition is the partition with lower boundary value, and the upper boundary is the partition with higher boundary value. The original local index will also be merged accordingly, and the global index will be invalid, which requires rebuild.

SQL > alter table custaddr merge partitions tasking list552 pumped other into partition p_other

The table has changed.

SQL > select index_owner,index_name,partition_name from dba_ind_partitions where index_name='IX_CUSTADDR_ID'

Index_owner index_name partition_name

Icd ix_custaddr_id p_other

Icd ix_custaddr_id t_list551

Icd ix_custaddr_id t_list556

SQL > select table_name,partition_name from user_tab_partitions where table_name='CUSTADDR'

Table_name partition_name

Custaddr t_list556

Custaddr t_list551

Custaddr p_other

3.4. Move Partition

SQL > alter table custaddr move partition P_OTHER tablespace system

The table has changed.

SQL > alter table custaddr move partition P_OTHER tablespace icd_service

The table has changed.

Note: partition movement will automatically maintain the local partition index, oracle will not automatically maintain the global index, so we need to re-rebuild the partition index. Which indexes we need to rebuild can be judged by dba_part_indexes,dba_ind_partitions.

SQL > Select index_name,status From user_indexes Where table_name='CUSTADDR'

INDEX_NAME STATUS

IX_CUSTADDR_ID N/A

3.5. Truncate partition

SQL > select * from custaddr partition (T_LIST556)

ID AREA

-

1 556

SQL > alter table custaddr truncate partition (T_LIST556)

The watch is truncated.

SQL > select * from custaddr partition (T_LIST556)

No rows selected

Description:

Truncate operates faster than delete, and bulk data loading of a large amount of data in the data warehouse may be useful; truncated partitions also automatically maintain the local partition index, while making the global index unusable, which needs to be rebuilt

3.6. Drop partition

SQL > alter table custaddr drop partition T_LIST551

The table has changed.

SQL > select table_name,partition_name from user_tab_partitions where table_name='CUSTADDR'

TABLE_NAME PARTITION_NAME

CUSTADDR T_LIST556

CUSTADDR P_OTHER

It also automatically maintains the local partitioned index and makes the global index unusable, which needs to be rebuilt

four。 Index of partitioned table

Partitioned indexes are divided into local (local index) indexes and global indexes (global index). Local indexes are easier to manage than global indexes, while global indexes are faster.

Tables related to the index:

Summary statistics of dba_part_indexes partitioned indexes, you can know which partitioned indexes are on each table and the type of partitioned index (local/global)

Partition-level statistics for each partition index of dba_ind_partitions

Dba_indexes/dba_part_indexes can find out which non-partitioned indexes are on each table.

The Local index must be a partitioned index. The Global index can choose whether or not to partition. If partitioned, it can only be a partitioned index with a prefix.

Partitioned indexes are divided into two categories: partitioned indexes with prefix (prefix) and partitioned indexes without prefix (nonprefix):

(1) A partitioned index with a prefix is an index that contains a partitioning key and uses it as a boot column.

Such as:

Create index i_id_global on PDBA (id) global-Boot column

2 partition by range (id)-Partition key

3 (partition p1 values less than)

4 partition p2 values less than (maxvalue)

5)

The ID here is the partitioning key, and the partitioning key id is also the boot column of the index.

(2) the column of an unprefixed partition index does not begin with a partition key or does not contain a partition key column.

Such as:

Create index ix_custaddr_local_id_p on custaddr (id)

Local (

Partition t_list556 tablespace icd_service

Partition p_other tablespace icd_service

)

This partition is based on areacode. But the leading column of the index is ID. So it is a non-prefix partitioned index.

Global partition indexes do not support non-prefixed partition indexes. If created, the error is as follows:

SQL > create index i_time_global on PDBA (id) global-- Index boot column

2 partition by range (time)-zoning

3 (partition p1 values less than (TO_DATE ('2010-12-1,' YYYY-MM-DD'))

4 partition p2 values less than (maxvalue)

5)

Partition by range (time)

An error occurred on line 2:

ORA-14038: GLOBAL partitioned indexes must be prefixed

4.1 Local Local Index

For local indexes, when the partition of the table changes, the index is maintained automatically by Oracle.

Note:

(1) the local index must be a partitioned index, and the partitioning key is equivalent to the partitioning key of the table.

(2) both prefix and non-prefix indexes can support index partition elimination, as long as the condition of the query contains the index partition key.

(3) the local index only supports the uniqueness within the partition, but can not support the uniqueness on the table, so if you want to use the local index to constrain the uniqueness of the table, the partition key columns must be included in the constraint.

(4) the local partition index is on a single partition, and each partition index only points to one table partition; otherwise, a partition index can point to n table partitions, at the same time, a table partition may also point to n index partitions, and doing truncate or move,shrink on a partition in the partition table may affect n global index partitions, because of this, the local partition index has higher availability.

(5) the bitmap index must be a local partition index.

(6) Local index is mostly used in data warehouse environment.

(7) both B-tree index and bitmap index can be partitioned, but HASH index cannot be partitioned.

Example:

Sql > create index ix_custaddr_local_id on custaddr (id) local

The index has been created.

The effect is the same as the following SQL, because the local index is a partitioned index:

Create index ix_custaddr_local_id_p on custaddr (id)

Local (

Partition t_list556 tablespace icd_service

Partition p_other tablespace icd_service

)

SQL > create index ix_custaddr_local_areacode on custaddr (areacode) local

The index has been created.

Verify the type of 2 indexes:

SQL > select index_name,table_name,partitioning_type,locality,ALIGNMENT from user_part_indexes where table_name='CUSTADDR'

Index_name table_name partition locali alignment

Ix_custaddr_local_areacode custaddr list local prefixed

Ix_custaddr_local_id custaddr list local non_prefixed

Because our custaddr table is partitioned by areacode, the index ix_custaddr_local_areacode is a prefixed index (prefixed). Ix_custaddr_local_id is a non-prefix index.

4.2 Global Index

For global indexes, you can choose whether or not to partition, and the partition of the index may not correspond to the table partition. Global partitioned indexes can only be B-tree indexes. So far (10gR2), oracle only supports prefixed global indexes.

In addition, oracle will not automatically maintain the global partition index, when we modify the partition of the table, if we do not add update global indexes to the partition maintenance operation, it will usually lead to the INVALDED of the global index, which must be REBUILD after the operation.

Note:

(1) the global index can be partitioned or unpartitioned, and the global index must be a prefix index, that is, the index column of the global index must take the index partitioning key as its first few columns.

(2) the global index can be attached to a partitioned table or a non-partitioned table.

(3) the index entry of a global partitioned index may point to several partitions, so for a global partitioned index, even truncating the data in one partition requires rebulid several partitions or even the whole index.

(4) Global index is mostly used in oltp system.

(5) the global partition index is only partitioned by range or hash, and the hash partition is only supported after 10g.

(6) when oracle9i move or truncate the partition table in the future, you can use the update global indexes statement to update the global partition index synchronously and consume certain resources in exchange for a high degree of availability.

(7) the table uses column an as the partition, and quotes b as the local partition index. if b is used in the where condition, then oracle will scan the partitions of all tables and indexes, and the cost will be higher than the partition. At this time, you can consider using b as the global partition index.

Note: Oracle only supports global partitioned indexes of type 2:

Range partitioned and Hash Partitioned.

The official website says as follows:

Global Partitioned Indexes

Oracle offers two types of global partitioned index: range partitioned and hash partitioned.

(1) Global Range Partitioned Indexes

Global range partitioned indexes are flexible in that the degree of partitioning and the partitioning key are independent from the table's partitioning method. They are commonly used for OLTP environments and offer efficient access to any individual record.

The highest partition of a global index must have a partition bound, all of whose values are MAXVALUE. This ensures that all rows in the underlying table can be represented in the index. Global prefixed indexes can be unique or nonunique.

You cannot add a partition to a global index because the highest partition always has a partition bound of MAXVALUE. If you wish to add a new highest partition, use the ALTER INDEX SPLIT PARTITION statement. If a global index partition is empty, you can explicitly drop it by issuing the ALTER INDEX DROP PARTITION statement. If a global index partition contains data, dropping the partition causes the next highest partition to be marked unusable. You cannot drop the highest partition in a global index.

(2) Global Hash Partitioned Indexes

Global hash partitioned indexes improve performance by spreading out contention when the index is monotonically growing. In other words, most of the index insertions occur only on the right edge of an index.

(3) Maintenance of Global Partitioned Indexes

By default, the following operations on partitions on a heap-organized table mark all global indexes as unusable:

ADD (HASH)

COALESCE (HASH)

DROP

EXCHANGE

MERGE

MOVE

SPLIT

TRUNCATE

Example 1 Global index, which is supported for all partition types:

Sql > create index ix_custaddr_ global_id on custaddr (id) global

The index has been created.

Example 2: global partition index, only Range partition and Hash partition are supported:

(1) create 2 test partition tables:

Sql > create table pdba (id number, time date) partition by range (time)

2 (

3 partition p1 values less than (to_date ('2010-10-1,' yyyy-mm-dd'))

4 partition p2 values less than (to_date ('2010-11-1,' yyyy-mm-dd'))

5 partition p3 values less than (to_date ('2010-12-1,' yyyy-mm-dd'))

6 partition p4 values less than (maxvalue)

7)

The table has been created.

SQL > create table Thash

2 (

3 id number primary key

4 item_id number (8) not null

5)

6 partition by hash (id)

7 (

8 partition part_01

9 partition part_02

10 partition part_03

11)

The table has been created.

(2) create a partitioned index

Example 2: global partition index

SQL > create index i_id_global on PDBA (id) global

2 partition by range (id)

3 (partition p1 values less than)

4 partition p2 values less than (maxvalue)

5)

The index has been created.

This is a prefixed partition index.

SQL > create index i_time_global on PDBA (id) global

2 partition by range (time)

3 (partition p1 values less than (TO_DATE ('2010-12-1,' YYYY-MM-DD'))

4 partition p2 values less than (maxvalue)

5)

Partition by range (time)

An error occurred on line 2:

ORA-14038: GLOBAL partitioned indexes must be prefixed

SQL > create index i_time_global on PDBA (time) global

2 partition by range (time)

3 (partition p1 values less than (TO_DATE ('2010-12-1,' YYYY-MM-DD'))

4 partition p2 values less than (maxvalue)

5)

The index has been created.

-Partition index with prefix

SQL > select index_name,table_name,partitioning_type,locality,ALIGNMENT from user_part_indexes where table_name='PDBA'

Index_name table_name partition locali alignment

I_id_global pdba range global prefixed

I_time_global pdba range global prefixed

SQL > CREATE INDEX ix_hash ON PDBA (id,time) GLOBAL

2 PARTITION BY HASH (id)

3 (PARTITION p1

4 PARTITION p2

5 PARTITION p3

6 PARTITION p4)

The index has been created.

As long as the boot column of the index contains a partition key, it is a partitioned index with a prefix.

4.3 Index Reconstruction issu

(1) Partition index

For partitioned indexes, you cannot rebuild as a whole, you can only rebuild a single partition. The syntax is as follows:

Alter index idx_name rebuild partition index_partition_name [online nologging]

Description:

Online: indicates that the table will not be locked when rebuilding.

Nologging: indicates that no log is generated when the index is built, which speeds up the speed.

If you want to rebuild the partitioned index, you can only drop the original index of the table before recreating:

SQL > create index loc_xxxx_col on xxxx (col) local tablespace SYSTEM

This operation requires a large temporary tablespace and sort area.

Example:

SQL > select index_name,partition_name from user_ind_partitions where index_name='I_TIME_GLOBAL'

INDEX_NAME PARTITION_NAME

I_TIME_GLOBAL P1

I_TIME_GLOBAL P2

SQL > alter index I_TIME_GLOBAL rebuild partition p1 online nologging

The index has changed.

SQL > alter index I_TIME_GLOBAL rebuild partition p2 online nologging

The index has changed.

(2) Global index

Oracle automatically maintains partitioned indexes. For global indexes, if update index is not specified when operating on partitioned tables, it will invalidate the global index and need to be rebuilt.

SQL > select owner,index_name,table_name,status from dba_indexes where INDEX_NAME='IX_PDBA_GLOBAL'

Owner index_name table_name status

-

Sys ix_pdba_global pdba valid

Delete a partition:

SQL > alter table pdba drop partition p2

The table has changed.

SQL > select owner,index_name,table_name,status from dba_indexes where INDEX_NAME='IX_PDBA_GLOBAL'

Owner index_name table_name status

-

Sys ix_pdba_global pdba valid

Split partition:

SQL > alter table pdba split partition P4 at (TO_DATE (partition P4, partition P5)) into (partition P4, partition P5)

The table has changed.

SQL > select owner,index_name,table_name,status from dba_indexes where INDEX_NAME='IX_PDBA_GLOBAL'

Owner index_name table_name status

-

Sys ix_pdba_global pdba valid

Use update indexes when partitioning drop

SQL > alter table pdba drop partition P4 UPDATE INDEXES

The table has changed.

SQL > select owner,index_name,table_name,status from dba_indexes where INDEX_NAME='IX_PDBA_GLOBAL'

Owner index_name table_name status

-

Sys ix_pdba_global pdba valid

After several drop partitioning operations, the global index did not fail, which is a bit strange. But if you are in a production environment, be careful.

The command to rebuild the global index is as follows:

Alter index idx_name rebuild [online nologging]

Example:

SQL > Alter index ix_pdba_global rebuild online nologging

The index has changed.

In addition, the problem of storage space in partitioned tables:

SQL > select table_name,partition_name,tablespace_name from user_tab_partitions where table_name='DBA'

TABLE_NAME PARTITION_NAME TABLESPACE_NAME

-

DBA P1 SYSTEM

DBA P2 SYSTEM

DBA P3 SYSTEM

DBA P4 SYSTEM

Through the user_tab_partitions table, you can view the corresponding tablesapce_name for each partition. However, if you go through the all_tables table, you cannot find the information about the corresponding table space of the partitioned table.

Partition table:

SQL > select owner,table_name,tablespace_name,cluster_name from all_tables where table_name='DBA'

OWNER TABLE_NAME TABLESPACE_NAME CLUSTER_NAME

SYS DBA

Ordinary form:

SQL > select owner,table_name,tablespace_name,cluster_name from all_tables where table_name='DAVE'

OWNER TABLE_NAME TABLESPACE_NAME CLUSTER_NAME

SYS DAVE SYSTEM

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.