学习不可见索引创建测试

一、需求场景

在运维过程中，存在慢SQL，可以选择创建索引进行优化，但是如果直接创建索引！能否真正的优化问题SQL？？？值得怀疑，如果建了索引，问题更大咋整？

因此可以先建立一个不可见索引，用于测试，测试确认能提高SQL执行效率，在将索引转为有效！这样安全的干活，你值得拥有。

二、测试

1）制造测试数据
SQL> conn scott/tiger
Connected.
SQL> create table test as select * from emp;

Table created.

2）制造烂SQL
SQL> set autotrace on
SQL> select count(*) from test where empno=3899;

  COUNT(*)
----------
         0


Execution Plan
----------------------------------------------------------
Plan hash value: 1950795681

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    13 |     3   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      |     1 |    13 |            |          |
|*  2 |   TABLE ACCESS FULL| TEST |     1 |    13 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

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

   2 - filter("EMPNO"=3899)

Note
-----
   - dynamic sampling used for this statement (level=2)


Statistics
----------------------------------------------------------
          6  recursive calls
          0  db block gets
          9  consistent gets
          1  physical reads

3）创建不可见索引

session1 ！对test表删除一条记录不提交
SQL> delete test where rownum=1;
1 row deleted.

session2 !使用非Online创建不可见索引

create index test_id on test(empno) tablespace users parallel 1 invisible
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

NONONO 加上online

SQL> create index test_id on test(empno) tablespace users parallel 1 invisible online;

hang

session 1 commit;

session 2 Index created.

4)测试SQL创建索引后，能否提升效率

SQL> show parameter invisible

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_use_invisible_indexes boolean FALSE
SQL> alter session set optimizer_use_invisible_indexes = true;

SQL> select count(*) from test where empno=3899;

-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 1 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 13 | | |
|* 2 | INDEX RANGE SCAN| TEST_ID | 1 | 13 | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1 consistent gets
0 physical reads

此时可以在会话1再次测试，还是无法使用索引的！

5）索引置为可见

SQL> alter index test_id visible;

会话1再次执行SQL，执行计划走了索引！