分类目录归档：Oracle

以前我有个想法，我只想对其中的某个或者某几个分区上创建索引，其他分区不想创建，在12C之前的版本，无论是Local还是Global index，都不能实现该需求，但是从ORACLE 12C开始引进了Partial Global/Local Indexes for Partitioned Tables，解决了该问题，可以在指定的分区上创建本地索引或者全局索引，主要语法是在表或者分区,子分区级别设置[INDEXING { ON | OFF }]
创建测试表

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 (100) INDEXING ON,
  8  PARTITION ord_p2 VALUES LESS THAN (200) INDEXING OFF,
  9  PARTITION ord_p3 VALUES LESS THAN (300) INDEXING ON,
 10  PARTITION ord_p4 VALUES LESS THAN (400),
 11  PARTITION ord_p5 VALUES LESS THAN (500));

表已创建。

创建LOCAL INDEX

CDB_PDB@CHF> CREATE INDEX ind_lox on xifenfei_orders(order_address) LOCAL INDEXING PARTIAL;

索引已创建。

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

--设置INDEXING OFF对应的index 状态为UNUSABLE，分区继承表


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

--因为segment 延迟，无数据，所以无分区和索引记录
CDB_PDB@CHF> SELECT PARTITION_NAME,SEGMENT_NAME FROM USER_SEGMENTS WHERE SEGMENT_NAME='IND_LOX';

未选定行

CDB_PDB@CHF> SELECT PARTITION_NAME,SEGMENT_NAME FROM USER_SEGMENTS WHERE SEGMENT_NAME='XIFENFEI_ORDERS';

未选定行

CDB_PDB@CHF> begin
  2    for i in 1 .. 449 loop
  3      insert into xifenfei_orders
  4      values
  5        (i,'www.xifenfei.com'||i,'惜分飞'||i);
  6    end loop;
  7  commit;
  8  end;
  9  /

PL/SQL 过程已成功完成。

--插入记录后，分区表有相关记录
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

--注意:这里只有user_tab_partitions.indexing为on的有记录,也就是说，至于这些分区的索引被创建，其他的未被创建
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

分析执行计划

CDB_PDB@CHF> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER,'XIFENFEI_ORDERS',CASCADE=>TRUE);

PL/SQL 过程已成功完成。

CDB_PDB@CHF> SET AUTOT TRACE
CDB_PDB@CHF> SELECT ORDER_MODE FROM XIFENFEI_ORDERS WHERE ORDER_ADDRESS='www.xifenfei.com99';

未选定行


执行计划
----------------------------------------------------------
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 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 "XIFENFEI_ORDERS"."ORDER_ID">=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 "XIFENFEI_ORDERS"."ORDER_ID">=100))

--这里可以看到，当我们没有指定分区范围的时候，显示的执行计划是有分区index的部分直接走index，没有分区index的部分是扫描分区


统计信息
----------------------------------------------------------
         34  recursive calls
          0  db block gets
        120  consistent gets
          1  physical reads
          0  redo size
        347  bytes sent via SQL*Net to client
        533  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;

未选定行


执行计划
----------------------------------------------------------
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 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.xifenfei.com499')

--指定分区查询，可以明确的看到，该sql直接使用了分区索引


统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          1  consistent gets
          0  physical reads
          0  redo size
        347  bytes sent via SQL*Net to client
        533  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;

未选定行


执行计划
----------------------------------------------------------
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)

--当指定的分区无index之时，直接判断走全表扫描

统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         22  consistent gets
          0  physical reads
          0  redo size
        347  bytes sent via SQL*Net to client
        533  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

创建Global index

CDB_PDB@CHF> ALTER INDEX ind_lox INVISIBLE;

索引已更改。

CDB_PDB@CHF> CREATE INDEX IND_G_LOX ON XIFENFEI_ORDERS(ORDER_ADDRESS) Global  INDEXING PARTIAL;

索引已创建。

CDB_PDB@CHF> select index_name,indexing from dba_indexes where index_name='IND_G_LOX';

INDEX_NAME INDEXIN
---------- -------
IND_G_LOX  PARTIAL

执行计划

CDB_PDB@CHF> SELECT ORDER_MODE FROM XIFENFEI_ORDERS t WHERE ORDER_ADDRESS='www.xifenfei.com99';

未选定行


执行计划
----------------------------------------------------------
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 "T"."ORDER_ID">=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 "T"."ORDER_ID">=100))

--这里可以看到因为没有指定分区范围，该sql在含index的分区使用全局index，在没有index的分区直接使用全表扫描

统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         67  consistent gets
          0  physical reads
          0  redo size
        347  bytes sent via SQL*Net to client
        533  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;


执行计划
----------------------------------------------------------
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')

--指定了分区范围，而且该分区又有index，直接使用全局index

统计信息
----------------------------------------------------------
          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

未选定行


执行计划
----------------------------------------------------------
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)

--指定了分区范围，但是该分区无index，直接使用全表扫描

统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         22  consistent gets
          0  physical reads
          0  redo size
        347  bytes sent via SQL*Net to client
        533  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

结论
通过测试，证明Partial Global/Local Indexes for Partitioned Tables确实能够实现对部分分区创建index。
1)如果查询条件确定的分区无index，那直接走全表扫描；
2)如果查询条件可以确定的分区范围内有index，会直接使用index(无论是Local还是GLobal)；
3)如果查询条件未确定分区范围(含index和无index分区情况)，那该sql会在有index分区使用index，在没有index区域走全表扫描