Shulou(Shulou.com)05/31 Report--

This article mainly explains "how to use Oracle Partition". Friends who are interested might as well take a look. The method introduced in this paper is simple, fast and practical. Now let the editor take you to learn how to use Oracle Partition.

Oracle Partition Index Type

Local Index

A.Local Prefixed Index

The first index field that can be understood as a partitioned index is the Partition key of the partitioned table

B.Local Non-Prefixed Index

It can be understood that the first index field of a partitioned index is not the Partition key of the partitioned table

Global Prefixed Index

A.range type partition

B.hash type partition

Note: these two Global index partition types have nothing to do with the partition type of the base table. We can create the index on a non-partitioned table

Global Non-Prefixed Index (currently not supported by Oracle)

When the index is created, it prompts ORA-14038: GLOBAL partitioned index must be prefixed

Note: Local Index index partitions and base table partitions correspond one to one

Global Index index partition and base table partition are independent of each other, and there is no one-to-one correspondence between index partition and table partition.

For example, the base table has five partitions and the index has two partitions.

How to determine that the partitioned index is Global/Local,PREFIXED/NON-PREFIXED

SQL > select index_name,table_name,locality,alignment from dba_part_indexes where owner='OHSDBA' INDEX_NAME TABLE_NAME LOCALITY ALIGNMENT-----IDX_GLOBAL_PREFIXED OHS_PART GLOBAL PREFIXEDIDX_LOCAL_NON_PREFIXED OHS_PART LOCAL NON_PREFIXED

How to select the type of partition index

When deciding what kind of partitioned index to use, you should consider the following guidelines in this order:

If the table partitioning column is a subset of the index keys, then use a local index. If this is the case, then you are finished. If this is not the case, then continue to guideline 2.

If the index is unique and does not include the partitioning key columns, then use a global index. If this is the case, then you are finished. Otherwise, continue to guideline 3.

If your priority is manageability, then consider a local index. If this is the case, then you are finished. If this is not the case, continue to guideline 4.

If the application is an OLTP type and users need quick response times, then use a global index. If the application is a DSS type and users are more interested in throughput, then use a local index.

For more information about partitioned indexes and how to decide which type to use, refer to Using Partitioning in a Data Warehouse Environment and Using Partitioning in an Online Transaction Processing Environment.

The following maintenance operations can easily lead to index partition UNUSABLE

1. IMPORT PARTITION or conventional path SQL*Loader.

2. Direct-path SQL*Loader did not complete successfully (local index partitions and global indexes)

3. The maintenance operation is similar to ALTER TABLE MOVE PARTITION.

4. The maintenance operation is similar to ALTER TABLE TRUNCATE PARTITION.

5. The maintenance operation is similar to ALTER TABLE SPLIT PARTITION.

6. The maintenance operation is similar to ALTER INDEX SPLIT PARTITION.

7. Add partitions to tables of Hash partition type (the data in the partition will change)

How to avoid indexing UNUSABLE

To prevent Index from becoming unavailable due to partition maintenance operations, we can use a statement with 'update global indexes'. The following operations support UPDATE GLOBAL INDEXES:

1. ADD PARTITION | SUBPARTITION (hash only)

2. COALESCE PARTITION | SUBPARTITION

3. DROP PARTITION

4. EXCHANGE PARTITION | SUBPARTITIO

5. MERGE PARTITION

6. MOVE PARTITION | SUBPARTITION

7. SPLIT PARTITION

8. TRUNCATE PARTITION | SUBPARTITION

The difference between Update Global Indexes and Update Indexes

Update Global Indexes maintains only global indexes, while Update Indexes maintains both global and local indexes. Update Global Indexes can be understood as a subset of Update Indexes. Suppose you currently have a table on which local and global partitioned index are created, and we spilt/merge one of the non-empty partitions. If you only use Update Global Indexes, then Local Index will be marked as UNUSABLE. If you use Update Indexes, both are valid. You can use 'update global indexes',10g after' update global indexes'/'update indexes' in Oracle 9.2

Test available scripts

Create table ohs_part (id number, pdate date) partition by range (pdate) (partition ohs_201701 values less than ('2017-02-01)), partition ohs_201702 values less than (to_date (' 2017-03-01)), partition ohs_201703 values less than (to_date ('2017-04-01)), partition ohs_201704 values less than (to_date (' 2017-05-01') 'yyyy-mm-dd'), partition ohs_max values less than (maxvalue)) / insert into ohs_part select 1, sysdate from dual Insert into ohs_part select 2, sysdate from dual;insert into ohs_part select 3, sysdate-15 from dual;insert into ohs_part select 4, sysdate-15 from dual;insert into ohs_part select 5, sysdate + 30 from dual;insert into ohs_part select 6, sysdate + 30 from dual;insert into ohs_part select 7, sysdate + 60 from dual;insert into ohs_part select 8, sysdate + 60 from dual;commit;create index idx_local on ohs_part (pdate) local;create index idx_normal on ohs_part (id) Create index idx_global on ohs_part (pdate,id) global;SQL > create index idx_local on ohs_part (pdate) local; Index created.SQL > create index idx_normal on ohs_part (id); Index created.SQL > SQL > create index idx_global on ohs_part (pdate,id) global;Index created.SQL > SQL > col index_name for a30SQL > select index_name,partitioned,status from user_indexes where table_name='OHS_PART' INDEX_NAME PARTITION STATUS---IDX_GLOBAL NO VALIDIDX_NORMAL NO VALIDIDX_LOCAL YES N/ASQL > select index_name Status from user_ind_partitions where index_name='IDX_LOCAL' INDEX_NAME STATUS-- IDX_LOCAL USABLEIDX_LOCAL USABLEIDX_LOCAL USABLEIDX_LOCAL USABLEIDX_LOCAL USABLESQL > SQL > col table_name for a20SQL > select index_name Table_name,locality,alignment from dba_part_indexes where owner='OHSDBA' INDEX_NAME TABLE_NAME LOCALITY ALIGNMENT--IDX_LOCAL OHS_PART LOCAL PREFIXEDSQL > SQL > create index idx_local_non_prefixed on ohs_part (id Pdate) local Index created.SQL > SQL > select index_name,table_name,locality,alignment from dba_part_indexes where owner='OHSDBA' INDEX_NAME TABLE_NAME LOCALITY ALIGNMENT---IDX _ LOCAL OHS_PART LOCAL PREFIXEDIDX_LOCAL_NON_PREFIXED OHS_PART LOCAL NON_PREFIXEDSQL > drop index idx_local CREATE INDEX idx_global_prefixed ON ohs_part (pdate) GLOBAL PARTITION BY RANGE (pdate) (partition ohs_ind_201701 values less than (to_date ('2017-02-01)), partition ohs_ind_201702 values less than (to_date (' 2017-03-01)), partition ohs_ind_201703 values less than (to_date (2017-04-01)) Partition ohs_ind_201704 values less than (to_date ('2017-05-01)), partition ohs_ind__max values less than (maxvalue) / SQL > drop index idx_local Index dropped.SQL > col index_name for a30SQL > select index_name,table_name,locality,alignment from dba_part_indexes where owner='OHSDBA' INDEX_NAME TABLE_NAME LOCALITY ALIGNMENT---IDX _ LOCAL_NON_PREFIXED OHS_PART LOCAL NON_PREFIXEDSQL > CREATE INDEX idx_global_prefixed ON ohs_part (pdate) 2 GLOBAL PARTITION BY RANGE (pdate) 3 (partition ohs_ind_201701 values less than (to_date ('2017-02-01') ), 4 partition ohs_ind_201702 values less than (to_date ('2017-03-01)), 5 partition ohs_ind_201703 values less than (to_date (' 2017-04-01)), 6 partition ohs_ind_201704 values less than (to_date ('2017-05-01)) 7 partition ohs_ind__max values less than (maxvalue)) 8 / Index created.SQL > select index_name,table_name,locality,alignment from dba_part_indexes where owner='OHSDBA' INDEX_NAME TABLE_NAME LOCALITY ALIGNMENT---IDX _ GLOBAL_PREFIXED OHS_PART GLOBAL PREFIXEDIDX_LOCAL_NON_PREFIXED OHS_PART LOCAL NON_PREFIXEDSQL > so far I believe you have a deeper understanding of "how to use Oracle Partition". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

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.