A-A+

不可见索引测试

2013年05月26日 BasicKnowledge 暂无评论 阅读 1,321 次

不可见索引:可以通过该索引,对待删除的索引或者新建索引进行操作前测试。
相关说明见博文:
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
标签:

给我留言

Copyright © YallonKing 保留所有权利.   Theme  Ality

用户登录

分享到: