分区使用本地索引时，SQL注意条件写法

1.创建表及数据量
create table pt01 (
object_id number,
object_type varchar2(30),
object_name varchar2(35))
partition by list(object_type)
(
partition p01 values('TABLE'),
partition p02 values('INDEX')
);

SQL> select count(1) from pt01;

  COUNT(1)
----------
     20352

 2.创建本地索引
create index IDX_PT01_OBJECT_ID on pt01(object_id) local;

SQL> l
  1* select * from pt01 where object_id=12345678 and object_type='TABLE'
SQL> /

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 583878700

-------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name               | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |                    |     1 |    30 |     2   (0)| 00:00:01 |       |       |
|   1 |  PARTITION LIST SINGLE             |                    |     1 |    30 |     2   (0)| 00:00:01 |   KEY |   KEY |
|   2 |   TABLE ACCESS BY LOCAL INDEX ROWID| PT01               |     1 |    30 |     2   (0)| 00:00:01 |     1 |     1 |
|*  3 |    INDEX RANGE SCAN                | IDX_PT01_OBJECT_ID |     1 |       |     1   (0)| 00:00:01 |     1 |     1 |
-------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("OBJECT_ID"=12345678)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          2  consistent gets
          0  physical reads
          0  redo size
        483  bytes sent via SQL*Net to client
        509  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

3.不加object_type列SQL，本地索引的特性决定它会挨个遍历分区
SQL> select * from pt01 where object_id=12345678 ;

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 626817554

-------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name               | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |                    |     1 |    32 |     4   (0)| 00:00:01 |       |       |
|   1 |  PARTITION LIST ALL                |                    |     1 |    32 |     4   (0)| 00:00:01 |     1 |     2 |
|   2 |   TABLE ACCESS BY LOCAL INDEX ROWID| PT01               |     1 |    32 |     4   (0)| 00:00:01 |     1 |     2 |
|*  3 |    INDEX RANGE SCAN                | IDX_PT01_OBJECT_ID |     1 |       |     3   (0)| 00:00:01 |     1 |     2 |
-------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("OBJECT_ID"=12345678)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          4  consistent gets
          0  physical reads
          0  redo size
        483  bytes sent via SQL*Net to client
        509  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed
          
          
          
4.建立一普通表做比对
SQL> create table lt01 as select * from pt01;

Table created.
5.建一普通索引，可以发现在没有分区字段情况下，局部分区索引不如全局索引使用；

SQL> create index idx_lt01_index_object_id on lt01(object_id);

Index created.

SQL> EXEC dbms_stats.gather_table_stats('AIKI','LT01',CASCADE=>TRUE);

PL/SQL procedure successfully completed.

select * from lt01 where object_id=12345678 ;        

SQL> select * from lt01 where object_id=12345678 ;       

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 3640300324

--------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name                     | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                          |     1 |    32 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| LT01                     |     1 |    32 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IDX_LT01_INDEX_OBJECT_ID |     1 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("OBJECT_ID"=12345678)


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          2  consistent gets
          0  physical reads
          0  redo size
        483  bytes sent via SQL*Net to client
        509  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed