ORACLE table partition and partition Index

ORACLE table partition and partition index Partition Table, partition index, and global index: When a table contains more than 20 million data entries or occupies 2 GB of space, we recommend that you create a partition table. Create table ta (c1 int, c2 varchar2 (16), c3 varchar2 (64), c4 int constraint pk_ta primary key (c1) partition by range (c1) (partition p1 values less than (10000000), partition p2 values less than (20000000), partition p3 values less than (30000000), partition p4 values less than (maxvalue )); add partition, delete partition alter table ta add partition p_xx values less than (90000000000); alter table ta drop partition p_xx [upd Ate global indexes]; create table AAA (id number not null, AREAID VARCHAR2 (128), VCHAR6 VARCHAR2 (300) partition by list (vchar6) (partition p_0 values (default ), -- when creating a list partition Table, specify partition p_1 values ('1', '2', '3'), partition p_2 values ('4, 5, 6, ,); partition index and global index: the partition index is created separately on each partition and can be automatically maintained, drop or truncate a partition does not affect the use of other partition indexes of the index, that is, the index will not expire and is easy to maintain, but the query performance is slightly affected. Create index idx_ta_c2 on ta (c2) local (partition p1, partition p2, partition p3, partition p4); or create index idx_ta_c2 on ta (c2) local; in addition, in create unique index idx_ta_c2 on ta (c2) local; the system reports a ORA-14039 error because the partition column of the ta table is c1, you cannot create a primary key in a partition table or a primary key column does not contain a partition column. You cannot create a unique constraint. An oracle global index is used to create an index on the entire table. It can create its own partition. It can be different from a partition in a partition table, that is, it is an independent index. When you drop or truncate a partition, you must create the index alter index idx_xx rebuild. You can also use alter table table_name drop partition partition_name update global indexes, however, if the data volume is large, it takes a long time to re-build the index. You can query the views of user_indexes, user_part_indexes, and user_ind_partitions to check whether the index is valid. Create index idx_ta_c3 on ta (c3); or divide the global index into multiple partitions (note that the partition is different from the partition table): create index idx_ta_c4 on ta (c4) global partition by range (c4) (partition ip1 values less than (10000), partition ip2 values less than (20000), partition ip3 values less than (maxvalue )); note that the bootstrap column on the global index must be consistent with the column after range, otherwise there will be a ORA-14038 error. If the statement cannot be written as follows: create index idx_ta_c4 on ta (c1) global partition by range (c4) (partition ip1 values less than (10000), partition ip2 values less than (20000 ), partition ip3 values less than (maxvalue); oracle will automatically create a global index for the primary key. If you want to make a partition index unavailable, you can use the following script: alter index idx_tab1 modify partition "ind partition name" unusable, otherwise, it cannot be created. To facilitate maintenance, avoid using global indexes when table partitions are frequently deleted and data updates are frequent. Export sub-partitions: exp test1/test1 tables = ta: p1 file = part_1.dmp views used by partitions: Manage user_tab_partitions, user_part_indexes, user_part_tables, user_partial_drop_tabs, user_part_indexes user_part_key_columns ================================== query partition tables: select * from hcl partition (p_200809) Export partition tables: exp username/pwd file =/aa. dmp tables = (hcl: p_200809, hcl: p_200810)