Oracle 局部前缀索引和局部非前缀索引

Myth丶恋晨 2022-08-19 04:53 266阅读 0赞

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

**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条件中都要使用分区条件，要不然两种索引都要全分区扫描。加了分区条件后，两种索引都可以消除分区，所以我建议还是用局部非前缀索引。**