Partition Table, partition index, and global index summary

Partition Table, partition index, and global index:

When a table contains more than 20 million data records 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 ));

Partition index and global index:

A partition index is used to create an index on each partition. It can be automatically maintained. When you drop or truncate a partition, it does not affect the use of other partition indexes of the index, that is, the index will not expire, it 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, oracle does not support primary key columns that do not contain partition columns when creating primary key columns in a partition table, nor does it support creating additional constraints (unique.

A 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 need to create the index alter index idx_xx rebuild. You can also use alter table table_name drop partition partition_name update global indexes;, but 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 );

You can also 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 index should be consistent with the column after range, otherwise there will be a ORA-14038 error.

Oracle automatically creates a global index for the primary key.

If you want to create a partition index on the primary key column, unless the primary key includes the partition key, there is also a primary key built on two or more columns.

You can avoid using global indexes for ease of maintenance when table partitions are frequently deleted and data updates are frequent.

 

Display partition table information in Oracle

Display information of all database partition tables: dba_part_tables

Display All the partition tables accessible to the current user: all_part_tables

Display information of all the partition tables of the current user: user_part_tables

Show Oracle view partition table information show detailed partition information of all database partition tables: dba_tab_partitions

Displays the detailed partition information of all partition tables accessible to the current user: all_tab_partitions

Displays detailed partition information for all partition tables of the current user: user_tab_partitions

Show the sub-partition information show the sub-partition information of all the combined partition tables of the database: dba_tab_subpartitions

Display the subpartitions of all the combined partition tables accessible to the current user: all_tab_subpartitions

Displays the subpartition information of all the combined partition tables of the current user: user_tab_subpartitions

Display partition column information of all database partition tables: dba_part_key_columns

Display the partition column information of all partition tables accessible to the current user: all_part_key_columns

Display the partition column information of all partition tables of the current user: user_part_key_columns

Display the subpartition column information of all database partition tables: dba_subpart_key_columns

Display the information of the subpartition columns of all partition tables accessible to the current user: all_subpart_key_columns

Displays the information of subpartition columns in all partition tables of the current user: user_subpart_key_columns

Bytes ---------------------------------------------------------------------------------------------------

How to query all partition tables in the Oracle database

Select * From user_tables A where a. partitioned = 'yes' Delete the data of a table is truncate table table_name;
To delete a partition table, the data in a partition is alter table table_name truncate partition P5;

If I want to clear the data of each partition in the Partition Table, can I use truncate table table_name?
It must be executed from scratch.

1. Alter table table_name truncate partition P1;
2. Alter table table_name truncate partition P2;
3. Alter table table_name truncate partition P3;
4. Alter table table_name truncate partition P4;
5. Alter table table_name truncate partition P5;
6. Alter table table_name truncate partition P6;

A: truncate table table_name