分区表本地索引需要有分区列才能走iffs

这个问题是群友 牛牛 发现的。

构建环境如下：

CREATE TABLE test1 AS SELECT * FROM dba_objects;
/*按owner进行list分区，提取list分区信息*/
SELECT 'partition ' || owner || ' values (''' || owner || '''),'
FROM
(
SELECT DISTINCT owner FROM test1
)
/*根据得到的信息建表如下*/
DROP TABLE test3 PURGE;
CREATE TABLE test3 PARTITION BY LIST(owner)
(
partition OWBSYS_AUDIT values ('OWBSYS_AUDIT'),
partition PROFILER values ('PROFILER'),
partition MDSYS values ('MDSYS'),
partition PUBLIC1 values ('PUBLIC'),
partition OUTLN values ('OUTLN'),
partition CTXSYS values ('CTXSYS'),
partition OLAPSYS values ('OLAPSYS'),
partition FLOWS_FILES values ('FLOWS_FILES'),
partition OWBSYS values ('OWBSYS'),
partition TEST values ('TEST'),
partition HR values ('HR'),
partition SYSTEM values ('SYSTEM'),
partition ORACLE_OCM values ('ORACLE_OCM'),
partition EXFSYS values ('EXFSYS'),
partition APEX_030200 values ('APEX_030200'),
partition SCOTT values ('SCOTT'),
partition SH values ('SH'),
partition OE values ('OE'),
partition PM values ('PM'),
partition DBSNMP values ('DBSNMP'),
partition ORDSYS values ('ORDSYS'),
partition ORDPLUGINS values ('ORDPLUGINS'),
partition SYSMAN values ('SYSMAN'),
partition IX values ('IX'),
partition APPQOSSYS values ('APPQOSSYS'),
partition XDB values ('XDB'),
partition ORDDATA values ('ORDDATA'),
partition BI values ('BI'),
partition SYS values ('SYS'),
partition WMSYS values ('WMSYS'),
partition SI_INFORMTN_SCHEMA values ('SI_INFORMTN_SCHEMA')
)
 AS
SELECT * FROM test1;

先收集下表信息

BEGIN
  dbms_stats.gather_table_stats(ownname => USER, tabname => 'TEST3');
END;

索引不包含分区列 ，这时不会走索引。

SQL> CREATE INDEX idx_test3_name_type1 ON test3(object_name,object_type) LOCAL;
Index created
SQL> EXPLAIN PLAN FOR SELECT object_name,object_type FROM test3 WHERE owner = 'ORDDATA' AND object_name IS NOT NULL;
Explained
SQL> SELECT * FROM TABLE(dbms_xplan.display());
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2630848743
--------------------------------------------------------------------------------
| Id  | Operation             | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |       |   248 |  8928 |     4   (0)| 00:00:01 |
|   1 |  PARTITION LIST SINGLE|       |   248 |  8928 |     4   (0)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL   | TEST3 |   248 |  8928 |     4   (0)| 00:00:01 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("OBJECT_NAME" IS NOT NULL)
14 rows selected

现在新建一包含分区列的索引

SQL> CREATE INDEX idx_test3_name_type2 ON test3(owner,object_name,object_type) LOCAL;
Index created
SQL> EXPLAIN PLAN FOR SELECT object_name,object_type FROM test3 WHERE owner = 'ORDDATA' AND object_name IS NOT NULL;
Explained
SQL> SELECT * FROM TABLE(dbms_xplan.display());
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 4286585575
--------------------------------------------------------------------------------
| Id  | Operation             | Name                 | Rows  | Bytes | Cost (%CP
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                      |   248 |  8928 |     2   (
|   1 |  PARTITION LIST SINGLE|                      |   248 |  8928 |     2   (
|*  2 |   INDEX FAST FULL SCAN| IDX_TEST3_NAME_TYPE2 |   248 |  8928 |     2   (
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("OBJECT_NAME" IS NOT NULL)
14 rows selected

这时走了index fast full scan。ok实验结束，那以后想要走分区index fast full scan时要记得包括分区列。

些时不加AND object_name IS NOT NULL 也一样

SQL> EXPLAIN PLAN FOR SELECT object_name,object_type FROM test3 WHERE owner = 'ORDDATA';
Explained
SQL> SELECT * FROM TABLE(dbms_xplan.display());
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 4286585575
--------------------------------------------------------------------------------
| Id  | Operation             | Name                 | Rows  | Bytes | Cost (%CP
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                      |   248 |  8928 |     2   (
|   1 |  PARTITION LIST SINGLE|                      |   248 |  8928 |     2   (
|   2 |   INDEX FAST FULL SCAN| IDX_TEST3_NAME_TYPE2 |   248 |  8928 |     2   (
--------------------------------------------------------------------------------
9 rows selected