A-A+
不可见索引测试
不可见索引：可以通过该索引，对待删除的索引或者新建索引进行操作前测试。
相关说明见博文：
http://www.yallonking.com/database/basicknowledge/2013/03/770.html
***************************************以下为测试过程***************************************
数据库版本
SQL> select * from v$version; BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production PL/SQL Release 11.2.0.3.0 - Production CORE 11.2.0.3.0 Production TNS for Linux: Version 11.2.0.3.0 - Production NLSRTL Version 11.2.0.3.0 - Production
创建测试表
创建2个权限相同的用户在相同的表空间下。
SQL> select name from v$datafile; NAME -------------------------------------------------------------------------------- /u01/app/oracle/oradata/ora11gr2/system01.dbf /u01/app/oracle/oradata/ora11gr2/sysaux01.dbf /u01/app/oracle/oradata/ora11gr2/undotbs01.dbf /u01/app/oracle/oradata/ora11gr2/users01.dbf SQL> create tablespace yallonking datafile '/u01/app/oracle/oradata/ora11gr2/yallonking01.dbf' size 10m; Tablespace created. SQL> create user yallonking identified by yallonking default tablespace yallonking quota unlimited on yallonking; User created. SQL> create user test identified by test default tablespace yallonking quota unlimited on yallonking; User created. SQL> grant dba to test; Grant succeeded. SQL> grant dba to yallonking; Grant succeeded.
用户yallonking创建1个1w行的测试表。
SQL> conn yallonking/yallonking;
Connected.
SQL> create table yallonking as select rownum as id,'yallonking' as name from dual connect by rownum<10001;
Table created.
SQL> select * from yallonking where id<10;
ID NAME
---------- ----------
1 yallonking
2 yallonking
3 yallonking
4 yallonking
5 yallonking
6 yallonking
7 yallonking
8 yallonking
9 yallonking
9 rows selected.
SQL> select * from yallonking where id>9990;
ID NAME
---------- ----------
9991 yallonking
9992 yallonking
9993 yallonking
9994 yallonking
9995 yallonking
9996 yallonking
9997 yallonking
9998 yallonking
9999 yallonking
10000 yallonking
10 rows selected.
用户test在用户yallonking的表yallonking上创建不可见索引。
SQL> conn test/test Connected. SQL> create index yallonking_invisible_id on yallonking.yallonking(id) invisible; Index created. SQL> conn /as sysdba Connected. SQL> select OWNER,INDEX_NAME,INDEX_TYPE,TABLE_NAME,VISIBILITY FROM dba_indexes where VISIBILITY='INVISIBLE'; OWNER INDEX_NAME INDEX_TYPE TABLE_NAME VISIBILIT ---------- ------------------------------ ---------- -------------------- --------- TEST YALLONKING_INVISIBLE_ID NORMAL YALLONKING INVISIBLE
返回到用户yallonking，对表进行一般查询
SQL> conn yallonking/yallonking
Connected.
SQL> set autot traceonly
SQL> select * from yallonking where id=100;
Execution Plan
----------------------------------------------------------
Plan hash value: 2582482548
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 25 | 10 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| YALLONKING | 1 | 25 | 10 (0)| 00:00:01 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ID"=100)
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
5 recursive calls
0 db block gets
64 consistent gets
0 physical reads
0 redo size
597 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
注意：此处的查询为全表扫描。
yallonking用户使不可见索引可见
SQL> conn yallonking/yallonking Connected. SQL> alter index test.YALLONKING_INVISIBLE_ID visible; Index altered.
再次对表进行一般查询（和上次一样的sql）
SQL> set autot traceonly
SQL> select * from yallonking where id=100;
Execution Plan
----------------------------------------------------------
Plan hash value: 1621529075
-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 25 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| YALLONKING | 1 | 25 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | YALLONKING_INVISIBLE_ID | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"=100)
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
33 recursive calls
0 db block gets
64 consistent gets
1 physical reads
0 redo size
601 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
1 rows processed
注意：此时，该sql已经使用到创建的索引了。
使该索引继续保持不可见
SQL> alter index test.YALLONKING_INVISIBLE_ID invisible; Index altered.
通过设置参数optimizer_use_invisible_indexes来控制会话或者系统对不可见索引采取的措施。
注意：
1、无论不可见索引处于什么状态，oracle引擎室会花成本维护他们的。
2、不可见索引和其他普通索引一样，可以对其收集统计信息
SQL> show parameter invisible NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ optimizer_use_invisible_indexes boolean FALSE
在系统级别设置成true后
SQL> alter system set optimizer_use_invisible_indexes=true;
System altered.
SQL> conn yallonking/yallonking
Connected.
SQL> set autot traceonly
SQL> select * from yallonking where id=100;
Execution Plan
----------------------------------------------------------
Plan hash value: 1621529075
-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 25 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| YALLONKING | 1 | 25 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | YALLONKING_INVISIBLE_ID | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"=100)
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
26 recursive calls
0 db block gets
63 consistent gets
0 physical reads
0 redo size
601 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
1 rows processed
在会话级别设置
SQL> conn /as sysdba
Connected.
SQL> alter system set optimizer_use_invisible_indexes=false;
System altered.
SQL> conn yallonking/yallonking;
Connected.
SQL> alter session set optimizer_use_invisible_indexes=true;
Session altered.
SQL> set autot traceonly
SQL> select * from yallonking where id=100;
Execution Plan
----------------------------------------------------------
Plan hash value: 1621529075
-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 25 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| YALLONKING | 1 | 25 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | YALLONKING_INVISIBLE_ID | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"=100)
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
601 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
连接到其他用户进行测试
SQL> conn test/test
Connected.
SQL> set autot traceonly
SQL> select * from yallonking.yallonking where id=100;
Execution Plan
----------------------------------------------------------
Plan hash value: 2582482548
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 25 | 10 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| YALLONKING | 1 | 25 | 10 (0)| 00:00:01 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ID"=100)
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
5 recursive calls
0 db block gets
64 consistent gets
0 physical reads
0 redo size
597 bytes sent via SQL*Net to client
523 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 test.YALLONKING_INVISIBLE_ID;
SQL> create index YALLONKING_NORMAL_ID on yallonking(id);
Index created.
SQL> set line 400
SQL> set autot traceonly
SQL> select * from yallonking where id=100;
Execution Plan
----------------------------------------------------------
Plan hash value: 3959761638
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 25 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| YALLONKING | 1 | 25 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | YALLONKING_NORMAL_ID | 1 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"=100)
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
601 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> alter index YALLONKING_NORMAL_ID invisible;
Index altered.
SQL> select * from yallonking where id=100;
Execution Plan
----------------------------------------------------------
Plan hash value: 2582482548
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 25 | 10 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| YALLONKING | 1 | 25 | 10 (0)| 00:00:01 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ID"=100)
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
29 recursive calls
0 db block gets
89 consistent gets
0 physical reads
0 redo size
597 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
1 rows processed
