LOCAL INDEX和GLOBAL INDEX

CREATE INDEX PART_IND_LOCAL ON partitioned_table(A) local;
CREATE INDEX PART_IND_global ON partitioned_table(B) global;
SQL> SELECT INDEX_NAME,TABLE_NAME,STATUS,partitioned FROM DBA_INDEXES WHERE INDEX_NAME like 'PART_IND_%';

INDEX_NAME                     TABLE_NAME                     STATUS   PAR
------------------------------ ------------------------------ -------- ---
PART_IND_GLOBAL                PARTITIONED_TABLE              VALID    NO
PART_IND_LOCAL                 PARTITIONED_TABLE              N/A      YES

---------status为N/A表明是LOCAL INDEX

SQL> select segment_name,partition_name,segment_type from DBA_segments WHERE segment_name like 'PART_IND_%';

SEGMENT_NAME         PARTITION_NAME                 SEGMENT_TYPE
-------------------- ------------------------------ ------------------
PART_IND_GLOBAL                                     INDEX
PART_IND_LOCAL       PART_1                         INDEX PARTITION
PART_IND_LOCAL       PART_2                         INDEX PARTITION

获取创建表，索引的脚本
SQL> set long 900000
SQL> spool dept.sql
SQL> select dbms_metadata.get_ddl('TABLE','PARTITIONED_TABLE','SYS') from dual;
SQL>select dbms_metadata.get_ddl('INDEX','PART_IND_LOCAL','SYS') from dual;

根据系统PROCESS，查询SQL
SQL> select b.event,a.process,c.sql_text from v$session a,v$session_wait b,v$sql c where a.sid=b.sid and a.sql_id=c.sql_id and a.process=2553;

EVENT                          PROCESS      SQL_TEXT
------------------------------ ------------ ------------------------------
SQL*Net message from client    2553         SELECT * FROM DBA_OBJECTS

强制ORACLE执行计划走INDEX：
SQL> select  * from test where status='VALID';

Execution Plan
----------------------------------------------------------
Plan hash value: 1357081020

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      | 59601 |    10M|   157   (3)| 00:00:02 |
|*  1 |  TABLE ACCESS FULL| TEST | 59601 |    10M|   157   (3)| 00:00:02 |
--------------------------------------------------------------------------

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

   1 - filter("STATUS"='VALID')

Note
-----
   - dynamic sampling used for this statement

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       4000  consistent gets
          0  physical reads
          0  redo size
    2592479  bytes sent via SQL*Net to client
      37374  bytes received via SQL*Net from client
       3357  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      50327  rows processed

SQL> select /*+index(test test_ind)*/ * from test where status='VALID';

Execution Plan
----------------------------------------------------------
Plan hash value: 2216933833

----------------------------------------------------------------------------------------
| Id  | Operation                   | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |          | 59601 |    10M|   816   (1)| 00:00:10 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST     | 59601 |    10M|   816   (1)| 00:00:10 |
|*  2 |   INDEX RANGE SCAN          | TEST_IND | 59601 |       |   122   (1)| 00:00:02 |
----------------------------------------------------------------------------------------

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

   2 - access("STATUS"='VALID')

Note
-----
   - dynamic sampling used for this statement

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       7464  consistent gets
          0  physical reads
          0  redo size
    5590468  bytes sent via SQL*Net to client
      37374  bytes received via SQL*Net from client
       3357  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      50327  rows processed