OB的分区键是索引的子集

讨论内容：分区键是索引的子集，创建索引时是否需要包含分区键
1. 查看表的分区键

select * from dba_part_key_columns where name='BX_XX_BUSDEXXXX' order by COLUMN_POSITION;
+--------+-----------------+-------------+-------------+-----------------+
| OWNER  | NAME            | OBJECT_TYPE | COLUMN_NAME | COLUMN_POSITION |
+--------+-----------------+-------------+-------------+-----------------+
| LXUSER | BX_XX_BUSDEXXXX | TABLE       | REGION      |               1 |
| LXUSER | BX_XX_BUSDEXXXX | TABLE       | CYCLE       |               2 |
+--------+-----------------+-------------+-------------+-----------------+

2. 查看表的索引

+-------------+-----------------+--------------------------------+-------------+--------+
| TABLE_OWNER | TABLE_NAME      | INDEX_NAME                     | COLUMN_NAME | STATUS |
+-------------+-----------------+--------------------------------+-------------+--------+
| LXUSER      | BX_XX_BUSDEXXXX | IDX_BX_XX_BUSDEXXXX_ORDERID    | ORDERID     | VALID  |
+-------------+-----------------+--------------------------------+-------------+--------+

3. 查看表的索引信息
–查询表的table_id

select table_id,database_name,table_name from gv$table where table_name='BX_XX_BUSDEXXXX';
+------------------+---------------+-----------------+
| table_id         | database_name | table_name      |
+------------------+---------------+-----------------+
| 1101710651288732 | LXUSER        | BX_XX_BUSDEXXXX |
+------------------+---------------+-----------------+

–查询索引表的table_id

select table_id,table_name,data_table_id from __all_virtual_table where table_name like '%BX_XX_BUSDEXXXX%' and data_table_id=1101710651288732 limit 10 ;
+------------------+-------------------------------------------------------+------------------+
| table_id         | table_name                                            | data_table_id    |
+------------------+-------------------------------------------------------+------------------+
| 1101710651299094 | __idx_1101710651288732_IDX_BX_XX_BUSDEXXXX_ORDERID    | 1101710651288732 |
+------------------+-------------------------------------------------------+------------------+

4. 查看单列索引表IDX_BX_XX_BUSDEXXXX_ORDERID的列明细，索引表包含了表的分区键，分区键是索引的一部分

select column_id,column_name,rowkey_position from __all_virtual_column where table_id=1101710651299094 order by rowkey_position limit 10;
+-----------+----------------+-----------------+
| column_id | column_name    | rowkey_position |
+-----------+----------------+-----------------+
|        19 | ORDERID        |               1 |
|        18 | REGION         |               2 |
|        17 | CYCLE          |               3 |
|         1 | __pk_increment |               4 |
+-----------+----------------+-----------------+

5. 查看执行计划，range部分也可以看到扫描的列包括了分区键（ORDERID+REGION+CYCLE+__pk_increment）

explain extended select ORDERID
from "LXUSER"."BX_XX_BUSDEXXXX" "SD" 
where ("SD"."REGION" = 010) 
and ("SD"."CYCLE" = 202508) 
and ORDERID='17444668836111723620000310000000531000'\G
*************************** 1. row ***************************
Query Plan: ========================================================================
|ID|OPERATOR            |NAME                           |EST. ROWS|COST|
------------------------------------------------------------------------
|0 |EXCHANGE IN REMOTE  |                               |2        |47  |
|1 | EXCHANGE OUT REMOTE|                               |2        |46  |
|2 |  TABLE SCAN        |SD(IDX_BX_XX_BUSDEXXXX_ORDERID)|2        |46  |
========================================================================

Outputs & filters: 
-------------------------------------
  0 - output([SD.ORDERID(0x7f8906f744d0)]), filter(nil)
  1 - output([SD.ORDERID(0x7f8906f744d0)]), filter(nil)
  2 - output([SD.ORDERID(0x7f8906f744d0)]), filter(nil), 
      access([SD.ORDERID(0x7f8906f744d0)]), partitions(p257), 
      is_index_back=false, 
      range_key([SD.ORDERID(0x7f8906f744d0)], [SD.REGION(0x7f8906f715f0)], [SD.CYCLE(0x7f8906f718e0)], [SD.__pk_increment(0x7f8906fc61d0)]), range(17444668836111723620000310000000531000,010,202508,MIN ; 17444668836111723620000310000000531000,010,202508,MAX), 
      range_cond([SD.REGION(0x7f8906f715f0) = 010(0x7f8906f721e0)], [SD.CYCLE(0x7f8906f718e0) = 202508(0x7f8906f72fd0)], [SD.ORDERID(0x7f8906f744d0) = ?(0x7f8906f73db0)])

6. 结论：
当谓词条件中包括分区键和普通字段，需要创建分区键+普通字段的复合索引时，只需要创建该字段的单列索引也是可以利用分区裁剪快速检索数据，创建单列索引相比复合索引可以减少维护索引的成本和磁盘占用量。