invisibleindex - 数据库编程

-------------------------------------------------- 31 recursive calls 0 db block gets 34 consistent gets 0 physical reads 0 redo size 1221 bytes sent via SQL*Net to client 524 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 6 sorts (memory) 0 sorts (disk) 5 rows processed

SQL> alter index idx_deptno_emp1 visible;

Index altered.

SQL>
SQL>
SQL> select * from emp1 where deptno = 20;

Execution Plan
----------------------------------------------------------
Plan hash value: 3783658255

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

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

   2 - access("DEPTNO"=20)

Statistics
----------------------------------------------------------
         43  recursive calls
          0  db block gets
         31  consistent gets
          0  physical reads
          0  redo size
       1241  bytes sent via SQL*Net to client
        524  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          6  sorts (memory)
          0  sorts (disk)
          5  rows processed

SQL>

If you create one index, but you don’t know that do it increase the performance of the sql. In this condition you do create an invisible index , and make it can be used by the oracle CBO in the test session via exec “ALTER SESSION SET optimizer_use_invisible_indexes=TRUE” and if you make sure that will be increase the sql performance , you should make the index be visible.

eg:

create index idx_detno_emp1 on emp1(deptno) invisible;

SQL> select * from emp1 where deptno = 20;

Execution Plan
----------------------------------------------------------
Plan hash value: 2226897347

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     5 |   190 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMP1 |     5 |   190 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

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

   1 - filter("DEPTNO"=20)

Statistics
----------------------------------------------------------
         43  recursive calls
          0  db block gets
         34  consistent gets
          0  physical reads
          0  redo size
       1221  bytes sent via SQL*Net to client
        524  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          6  sorts (memory)
          0  sorts (disk)
          5  rows processed

SQL>

SQL> alter session set optimizer_use_invisible_indexes=true;

Session altered.

SQL> select * from emp1 where deptno=20;


Execution Plan
----------------------------------------------------------
Plan hash value: 3783658255

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

invisibleindex(二)