Oracle本地前缀索引存在bug,Oracle 局部前缀索引和局部非前缀索引

局部前缀索引和局部非前缀索引是对于分区表而言的，那到底选择哪种索引呢？先做一个实验：

1.制造数据

create table TEST_P

(

OWNER          VARCHAR2(30),

OBJECT_NAME    VARCHAR2(128),

SUBOBJECT_NAME VARCHAR2(30),

OBJECT_ID      NUMBER,

DATA_OBJECT_ID NUMBER,

OBJECT_TYPE    VARCHAR2(19),

CREATED        DATE,

LAST_DDL_TIME  DATE,

TIMESTAMP      VARCHAR2(19),

STATUS         VARCHAR2(7),

TEMPORARY      VARCHAR2(1),

GENERATED      VARCHAR2(1),

SECONDARY      VARCHAR2(1),

NAMESPACE      NUMBER,

EDITION_NAME   VARCHAR2(30)

)partition by list(owner)(

partition  p1 values('SYS'),

partition  p2 values('PUBLIC'),

partition  p3 values('SYSMAN'),

partition  p4 values('APEX_030200'),

partition  p5 values('ORDSYS'),

partition  p6 values('MDSYS'),

partition  p7 values('XDB'),

partition  p8 values('OLAPSYS'),

partition  p9 values('SYSTEM'),

partition  p_default values(DEFAULT)

);

insert into TEST_P select * from dba_objects;

commit;

2.局部前缀索引(local nonprefixed index)

create index ind_local_prfixed on TEST_P(owner,object_id) local nologging;

exec dbms_stats.gather_table_stats(user,'TEST_P',cascade => true);

SQL> set autotrace traceonly

SQL> select * from TEST_P where object_id = 4903;

执行计划

----------------------------------------------------------

Plan hash value: 3793878917

------------------------------------------------------------------------------------------------------------------------

| Id  | Operation                          | Name              | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |

------------------------------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT                   |                   |     1 |    97 |    28   (0)| 00:00:01 |       |       |

|   1 |  PARTITION LIST ALL                |                   |     1 |    97 |    28   (0)| 00:00:01 |     1 |    10 |

|   2 |   TABLE ACCESS BY LOCAL INDEX ROWID| TEST_P            |     1 |    97 |    28   (0)| 00:00:01 |     1 |    10 |

|*  3 |    INDEX SKIP SCAN                 | IND_LOCAL_PRFIXED |     1 |       |    27   (0)| 00:00:01 |     1 |    10 |

------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

3 - access('OBJECT_ID'=4903)

filter('OBJECT_ID'=4903)

统计信息

----------------------------------------------------------

1  recursive calls

0  db block gets

38  consistent gets

0  physical reads

0  redo size

1294  bytes sent via SQL*Net to client

472  bytes received via SQL*Net from client

2  SQL*Net roundtrips to/from client

0  sorts (memory)

0  sorts (disk)

1  rows processed

SQL> select * from TEST_P where object_id = 4903 and owner='SYS';

执行计划

----------------------------------------------------------

Plan hash value: 1414917495

------------------------------------------------------------------------------------------------------------------------

| Id  | Operation                          | Name              | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |

------------------------------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT                   |                   |     1 |    96 |     2   (0)| 00:00:01 |       |       |

|   1 |  PARTITION LIST SINGLE             |                   |     1 |    96 |     2   (0)| 00:00:01 |   KEY |   KEY |

|   2 |   TABLE ACCESS BY LOCAL INDEX ROWID| TEST_P            |     1 |    96 |     2   (0)| 00:00:01 |     1 |     1 |

|*  3 |    INDEX RANGE SCAN                | IND_LOCAL_PRFIXED |     1 |       |     1   (0)| 00:00:01 |     1 |     1 |

------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

3 - access('OWNER'='SYS' AND 'OBJECT_ID'=4903)

统计信息

----------------------------------------------------------

1  recursive calls

0  db block gets

4  consistent gets

0  physical reads

0  redo size

1294  bytes sent via SQL*Net to client

472  bytes received via SQL*Net from client

2  SQL*Net roundtrips to/from client

0  sorts (memory)

0  sorts (disk)

1  rows processed

SQL> drop index ind_local_prfixed;

3.局部非前缀索引(local nonprefixed index)

SQL> create index ind_local_nonprfixed on TEST_P(object_id) local nologging;

SQL> select * from TEST_P where object_id = 4903;

执行计划

----------------------------------------------------------

Plan hash value: 449164761

---------------------------------------------------------------------------------------------------------------------------

| Id  | Operation                          | Name                 | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |

---------------------------------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT                   |                      |     1 |    97 |    12   (0)| 00:00:01 |       |       |

|   1 |  PARTITION LIST ALL                |                      |     1 |    97 |    12   (0)| 00:00:01 |     1 |    10 |

|   2 |   TABLE ACCESS BY LOCAL INDEX ROWID| TEST_P               |     1 |    97 |    12   (0)| 00:00:01 |     1 |    10 |

|*  3 |    INDEX RANGE SCAN                | IND_LOCAL_NONPRFIXED |     1 |       |    11   (0)| 00:00:01 |     1 |    10 |

---------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

3 - access('OBJECT_ID'=4903)

统计信息

----------------------------------------------------------

1  recursive calls

0  db block gets

22  consistent gets

44  physical reads

0  redo size

1294  bytes sent via SQL*Net to client

472  bytes received via SQL*Net from client

2  SQL*Net roundtrips to/from client

0  sorts (memory)

0  sorts (disk)

1  rows processed

SQL> select * from TEST_P where object_id = 4903 and owner='SYS';

执行计划

----------------------------------------------------------

Plan hash value: 747180801

---------------------------------------------------------------------------------------------------------------------------

| Id  | Operation                          | Name                 | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |

---------------------------------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT                   |                      |     1 |    96 |     2   (0)| 00:00:01 |       |       |

|   1 |  PARTITION LIST SINGLE             |                      |     1 |    96 |     2   (0)| 00:00:01 |   KEY |   KEY |

|   2 |   TABLE ACCESS BY LOCAL INDEX ROWID| TEST_P               |     1 |    96 |     2   (0)| 00:00:01 |     1 |     1 |

|*  3 |    INDEX RANGE SCAN                | IND_LOCAL_NONPRFIXED |     1 |       |     1   (0)| 00:00:01 |     1 |     1 |

---------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

3 - access('OBJECT_ID'=4903)

统计信息

----------------------------------------------------------

1  recursive calls

0  db block gets

4  consistent gets

0  physical reads

0  redo size

1294  bytes sent via SQL*Net to client

472  bytes received via SQL*Net from client

2  SQL*Net roundtrips to/from client

0  sorts (memory)

0  sorts (disk)

1  rows processed

结论：从实验来看，不管是使用哪种索引，SQL条件中都要使用分区条件，要不然两种索引都要全分区扫描。加了分区条件后，两种索引都可以消除分区，所以我建议还是用局部非前缀索引。