ORACLE 12C Partial global/local Indexes for partitioned Tables

I used to have an idea that I just wanted to create an index on one or a few of these partitions, and that the other partitions didn't want to be created, and neither the local nor global index was able to achieve that requirement, but the partial Global was introduced from Oracle 12C /local Indexes for partitioned tables solves the problem by creating a local index or global index on the specified partition, with the primary syntax set at the table or partition, and at the child partition level [indexing {on | OFF}]
To create a test table

The code is as follows

Copy Code

Cdb_pdb@chf> SELECT BANNER from V$version; BANNER -------------------------------------------------------------------------------- Oracle Database 12c Enterprise Edition release 12.1.0.1.0-64bit Production Pl/sql Release 12.1.0.1.0-production CORE 12.1.0.1.0 Production TNS for 64-bit windows:version 12.1.0.1.0-production Nlsrtl Version 12.1.0.1.0-production Cdb_pdb@chf> CREATE TABLE xifenfei_orders ( 2 order_id Number (12), 3 order_address varchar2 (100), 4 Order_mode VARCHAR2 (20)) 5 Indexing off 6 PARTITION by RANGE (order_id) 7 (PARTITION ord_p1 VALUES less THAN (MB) indexing on, 8 PARTITION ord_p2 VALUES less THAN indexing off, 9 PARTITION ord_p3 VALUES less THAN (+) indexing on, PARTITION ord_p4 VALUES less THAN (400), One PARTITION ord_p5 VALUES less THAN (500));

Table has been created.

The code is as follows

Copy Code

Cdb_pdb@chf> SELECT BANNER from V$version; BANNER -------------------------------------------------------------------------------- Oracle Database 12c Enterprise Edition release 12.1.0.1.0-64bit Production Pl/sql Release 12.1.0.1.0-production CORE 12.1.0.1.0 Production TNS for 64-bit windows:version 12.1.0.1.0-production Nlsrtl Version 12.1.0.1.0-production Cdb_pdb@chf> CREATE TABLE xifenfei_orders ( 2 order_id Number (12), 3 order_address varchar2 (100), 4 Order_mode VARCHAR2 (20)) 5 Indexing off 6 PARTITION by RANGE (order_id) 7 (PARTITION ord_p1 VALUES less THAN (MB) indexing on, 8 PARTITION ord_p2 VALUES less THAN indexing off, 9 PARTITION ord_p3 VALUES less THAN (+) indexing on, PARTITION ord_p4 VALUES less THAN (400), One PARTITION ord_p5 VALUES less THAN (500));

Table has been created.

The code is as follows	Copy Code
Cdb_pdb@chf> CREATE INDEX Ind_lox on Xifenfei_orders (order_address) local indexing PARTIAL;

The index has been created.

The code is as follows

Copy Code

Cdb_pdb@chf> COL index_name for A10 Cdb_pdb@chf> COL Partition_name for A15 Cdb_pdb@chf> Select Index_name, Partition_name,status 2 from User_ind_partitions 3 Where index_name = ' Ind_lox ' Index_name Partition_name STATUS ---------- --------------- -------- Ind_lox ORD_P5 unusable Ind_lox ORD_P4 unusable Ind_lox ORD_P3 USABLE Ind_lox ORD_P2 unusable Ind_lox ORD_P1 USABLE --Set indexing off the index status of unusable, Partition inheritance table Cdb_pdb@chf> Select partition_name,indexing from user_tab_partitions where table_name= ' xifenfei_orders ' Partition_name Inde --------------- ---- ORD_P5 off ORD_P4 off ORD_P3 on ORD_P2 off ORD_P1 on --No partitions and index records because of segment delay and no data Cdb_pdb@chf> SELECT partition_name,segment_name from user_segments WHERE segment_name= ' Ind_lox ' No rows selected Cdb_pdb@chf> SELECT partition_name,segment_name from user_segments WHERE segment_name= ' xifenfei_orders ' No rows selected Cdb_pdb@chf> begin 2 for I in 1. 449 Loop 3 INSERT INTO Xifenfei_orders 4 values 5 (I, ' www.xifenfei.com ' | | I, ' Cherish the division Fly ' | | i); 6 end Loop; 7 commit; 8 End; 9/

The PL/SQL process has completed successfully.

The code is as follows

Copy Code

--insert records, the partition table has records cdb_pdb@chf> SELECT partition_name,segment_name,blocks from user_segments WHERE segment_name= ' XIFENFEI_ ORDERS '   partition_name  segment_name        BLOCKS ----- ----------------------------------- ord_p5          xifenfei_ orders       1024 ord_p4          xifenfei_orders       1024 ord_p3           xifenfei_orders       1024 ord_p2           xifenfei_orders       1024 ord_p1           xifenfei_orders       1024

Note: There are only records on user_tab_partitions.indexing, that is to say, as the indexes of these partitions are created, others are not created
Cdb_pdb@chf> SELECT partition_name,segment_name from user_segments WHERE segment_name= ' Ind_lox '

Partition_name segment_name
--------------- ---------------
ORD_P1 Ind_lox
ORD_P3 Ind_lox

Analyze Execution Plan

The code is as follows	Copy Code
Cdb_pdb@chf> EXEC dbms_stats. Gather_table_stats (USER, ' xifenfei_orders ', cascade=>true);

The PL/SQL process has completed successfully.

The code is as follows	Copy Code
cdb_pdb@chf> SET Autot TRACE Cdb_pdb@chf> SELECT Order_mode from xifenfei_orders WHERE order_address= ' www.111cn.net '

No rows selected


Execution plan

The code is as follows

Copy Code

---------------------------------------------------------- Plan Hash value:2800545636 --------------------------------------------------------------------------------------------------------------- --------- -------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time | Pstart| Pstop | --------------------------------------------------------------------------------------------------------------- --------- -------- | 0 |                 SELECT STATEMENT |     |    1 |    30 | 40 (0) |       00:00:01 | | | |  1 | VIEW |     vw_te_2 |    2 |    24 | 40 (0) |       00:00:01 | | | |   2 |                 Union-all |       |       |            |          |       | | | |    3 |                 PARTITION RANGE OR |     |    1 |     34 | 1 (0) | 00:00:01 | KEY (OR) | KEY (OR) | |* 4 | TABLE ACCESS by the local INDEX ROWID batched|     Xifenfei_orders |    1 |     34 | 1 (0) | 00:00:01 | KEY (OR) | KEY (OR) | |* 5 | INDEX RANGE SCAN |     Ind_lox |       1 |     | 1 (0) | 00:00:01 | KEY (OR) | KEY (OR) | |    6 |                 PARTITION RANGE OR |     |    1 |    34 | 39 (0) | 00:00:01 | KEY (OR) | KEY (OR) | |* 7 | TABLE ACCESS Full |     Xifenfei_orders |    1 |    34 | 39 (0) | 00:00:01 | KEY (OR) | KEY (OR) | --------------------------------------------------------------------------------------------------------------- --------- -------- predicate information (identified by Operation ID): --------------------------------------------------- 4-filter ("Xifenfei_orders".) order_id "<100 or=" ">=200 and "Xifenfei_orders". " order_id "<300) 5-access ("order_address" = ' www.xifenfei.com99 ') 7-filter ("order_address" = ' www.xifenfei.com99 ' and ("Xifenfei_orders".) order_id ">=300 and "Xifenfei_orders". " order_id "<500 OR" Xifenfei_orders "." order_id "<200 and=" ">=100)"

--Here you can see, when we do not specify the scope of the partition, the display of the execution plan is the part of the index of the partition directly go index, no partition index is the part of the scan partition


Statistical information

The code is as follows

Copy Code

---------------------------------------------------------- Recursive calls 0 db Block gets Consistent gets 1 physical Reads 0 Redo Size 347 Bytes sent via sql*net to client Bytes received via sql*net from client 1 sql*net roundtrips To/from Client 5 Sorts (memory) 0 Sorts (disk) 0 rows processed Cdb_pdb@chf> SELECT Order_mode from xifenfei_orders t WHERE order_address= ' www.xifenfei.com499 ' and order_id>200 and Ord er_id<300;

No rows selected


Execution plan

The code is as follows

Copy Code

---------------------------------------------------------- Plan Hash value:3337708912 --------------------------------------------------------------------------------------------------------------- --------- ------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time | Pstart| P Stop | --------------------------------------------------------------------------------------------------------------- --------- ------ | 0 |                 SELECT STATEMENT |     |    1 |     34 | 2 (0) |       00:00:01 | | | |  1 |                 PARTITION RANGE Single |     |    1 |     34 | 2 (0) |     00:00:01 | 3 | 3 | |* 2 | TABLE ACCESS by the local INDEX ROWID batched|     Xifenfei_orders |    1 |     34 | 2 (0) |     00:00:01 | 3 | 3 | |* 3 | INDEX RANGE SCAN |     Ind_lox |       1 |     | 1 (0) |     00:00:01 | 3 | 3 | --------------------------------------------------------------------------------------------------------------- --------- ------ predicate information (identified by Operation ID): --------------------------------------------------- 2-filter ("order_id" >200) 3-access ("order_address" = ' www.111cn.net ') --Specify a partitioned query, and you can see explicitly that the SQL directly uses the partitioning index

Statistical information

The code is as follows

Copy Code

---------------------------------------------------------- 1 Recursive calls 0 db Block gets 1 consistent gets 0 physical Reads 0 Redo Size 347 Bytes sent via sql*net to client Bytes received via sql*net from client 1 sql*net roundtrips To/from Client 0 Sorts (memory) 0 Sorts (disk) 0 rows processed Cdb_pdb@chf> SELECT Order_mode from xifenfei_orders t WHERE order_address= ' www.xifenfei.com499 ' and order_id>300 and Ord er_id<400;

No rows selected


Execution plan

The code is as follows

Copy Code

---------------------------------------------------------- Plan Hash value:2072227240 ---------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time | Pstart| Pstop | ---------------------------------------------------------------------------------------------------------- | 0 |                 SELECT STATEMENT |     |    1 |    34 | 14 (0) |       00:00:01 |    | | |  1 |                 PARTITION RANGE single|     |    1 |    34 | 14 (0) |     00:00:01 |     4 | 4 | |* 2 | TABLE ACCESS Full |     Xifenfei_orders |    1 |    34 | 14 (0) |     00:00:01 |     4 | 4 | ---------------------------------------------------------------------------------------------------------- predicate information (identified by Operation ID): --------------------------------------------------- 2-filter ("order_address" = ' www.xifenfei.com499 ' and "order_id" >300) --When the specified partition has no index, direct judgment take full table scan Statistical information ---------------------------------------------------------- 1 Recursive calls 0 db Block gets Consistent gets 0 physical Reads 0 Redo Size 347 Bytes sent via sql*net to client Bytes received via sql*net from client 1 sql*net roundtrips To/from Client 0 Sorts (memory) 0 Sorts (disk) 0 rows processed Create Global Index cdb_pdb@chf> ALTER INDEX Ind_lox invisible; The index has changed. Cdb_pdb@chf> CREATE INDEX Ind_g_lox on Xifenfei_orders (order_address) Global indexing PARTIAL; The index has been created. Cdb_pdb@chf> Select index_name,indexing from dba_indexes where index_name= ' Ind_g_lox ' Index_name indexin ---------- ------- Ind_g_lox PARTIAL Execution plan Cdb_pdb@chf> SELECT Order_mode from xifenfei_orders t WHERE order_address= ' www.xifenfei.com99 ' No rows selected Execution plan ---------------------------------------------------------- Plan Hash value:1912382893 --------------------------------------------------------------------------------------------------------------- --------- -------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time | Pstart| Pstop | --------------------------------------------------------------------------------------------------------------- --------- -------- | 0 |                 SELECT STATEMENT |     |    1 |    30 | 41 (0) |       00:00:01 | | | |  1 | VIEW |     vw_te_2 |    2 |    24 | 41 (0) |       00:00:01 | | | |   2 |                 Union-all |       |       |            |          |       | | | |* 3 | TABLE ACCESS by GLOBAL INDEX ROWID batched|     Xifenfei_orders |    1 |     34 | 2 (0) | 00:00:01 | ROWID | ROWID | |* 4 | INDEX RANGE SCAN |     Ind_g_lox |       1 |     | 1 (0) |       00:00:01 | | | |    5 |                 PARTITION RANGE OR |     |    1 |    34 | 39 (0) | 00:00:01 | KEY (OR) | KEY (OR) | |* 6 | TABLE ACCESS Full |     Xifenfei_orders |    1 |    34 | 39 (0) | 00:00:01 | KEY (OR) | KEY (OR) | --------------------------------------------------------------------------------------------------------------- --------- -------- predicate information (identified by Operation ID): --------------------------------------------------- 3-filter ("T".) order_id "<100 or=" ">=200 and" T "." order_id "<300) 4-access ("order_address" = ' www.xifenfei.com99 ') 6-filter ("order_address" = ' www.xifenfei.com99 ' and ("T".) order_id ">=300 and" T "." order_id "<500 OR "T". " order_id "<200 and=" ">=100)" --Here you can see that because no partition range is specified, the SQL uses global index in the partition with index, and the full table scan is used directly in the partition without index Statistical information ---------------------------------------------------------- 1 Recursive calls 0 db Block gets Consistent gets 0 physical Reads 0 Redo Size 347 Bytes sent via sql*net to client Bytes received via sql*net from client 1 sql*net roundtrips To/from Client 0 Sorts (memory) 0 Sorts (disk) 0 rows processed Cdb_pdb@chf> SELECT Order_mode from xifenfei_orders t WHERE order_address= ' www.xifenfei.com99 ' and order_id<100; Execution plan ---------------------------------------------------------- Plan Hash value:3717359654 --------------------------------------------------------------------------------------------------------------- --------- ------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time | Pstart| P Stop | --------------------------------------------------------------------------------------------------------------- --------- ------ | 0 |                 SELECT STATEMENT |     |    1 |     31 | 2 (0) |       00:00:01 | | | |* 1 | TABLE ACCESS by GLOBAL INDEX ROWID batched|     Xifenfei_orders |    1 |     31 | 2 (0) |     00:00:01 | 1 | 1 | |* 2 | INDEX RANGE SCAN |     Ind_g_lox |       1 |     | 1 (0) |       00:00:01 | | | --------------------------------------------------------------------------------------------------------------- --------- ------ predicate information (identified by Operation ID): --------------------------------------------------- 1-filter ("order_id" <100) 2-access ("order_address" = ' www.xifenfei.com99 ') --Specifies the scope of the partition, and the partition has index, directly using the global index Statistical information ---------------------------------------------------------- 1 Recursive calls 0 db Block gets 2 consistent gets 0 physical Reads 0 Redo Size 551 Bytes sent via sql*net to client 544 Bytes received via sql*net from client 2 sql*net roundtrips To/from Client 0 Sorts (memory) 0 Sorts (disk) 1 rows processed Cdb_pdb@chf> SELECT Order_mode from xifenfei_orders t WHERE order_address= ' www.xifenfei.com99 ' and order_id>400 No rows selected Execution plan ---------------------------------------------------------- Plan Hash value:2072227240 ---------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time | Pstart| Pstop | ---------------------------------------------------------------------------------------------------------- | 0 |                 SELECT STATEMENT |     |    1 |    34 | 14 (0) |       00:00:01 |    | | |  1 |                 PARTITION RANGE single|     |    1 |    34 | 14 (0) |     00:00:01 |     5 | 5 | |* 2 | TABLE ACCESS Full |     Xifenfei_orders |    1 |    34 | 14 (0) |     00:00:01 |     5 | 5 | ---------------------------------------------------------------------------------------------------------- predicate information (identified by Operation ID): --------------------------------------------------- 2-filter ("order_address" = ' www.xifenfei.com99 ' and "order_id" >400) --Specifies a range of partitions, but the partition has no index and uses a full table scan directly Statistical information ---------------------------------------------------------- 1 Recursive calls 0 db Block gets Consistent gets 0 physical Reads 0 Redo Size 347 Bytes sent via sql*net to client Bytes received via sql*net from client 1 sql*net roundtrips To/from Client 0 Sorts (memory) 0 Sorts (disk) 0 rows processed

Conclusion
By testing, it is proved that partial global/local Indexes for partitioned tables does enable the creation of index for partial partitions.
1 if the query conditions to determine the partition without index, that directly go to the full table scan;
2 If the query conditions can be determined within the scope of the index, will directly use index (whether local or global);
3 If the query conditions do not determine the scope of the partition (including index and no index), then the SQL will be used in the index partition index, in the absence of index area go full table scan

Contact: Mobile Phone (13429648788) QQ (107644445)
Link:http://www.xifenfei.com/4649.html
Author: Xi-FEI