12c中在同一列创建多个索引

在Oracle Database 11g或更高版本中，可以将索引设置为不可见（或隐式索引），可见和不可见索引可以根据实际需求在数据库中执行切换
同一组列有多个索引时，一次只有一个索引可见。
如果要创建一个可见索引，则该组列的任何现有索引都必须不可见。也可以使同一组列的其他索引不可见，或对该组列创建不可见的索引。
如果 OPTIMIZER_USE_INVISIBLE_INDEXES 设置为 True ，则优化程序可使用不可见索引来创建计划。


--通过sqlplus命令连接到CDB：HFXF中
[oracle@12cdb02 ~]$ export ORACLE_SID=HFXFCDB
[oracle@12cdb02 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Fri Nov 18 19:02:47 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options

SYS@HFXFCDB > show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 XFPDB1                         READ WRITE NO
--切换到PDB：XFPDB1
SYS@HFXFCDB > alter session set container=XFPDB1;

Session altered.
--创建用户hfxrf
SYS@HFXFCDB > create user hfxf identified by hfxf;

User created.
--给用户授权
SYS@HFXFCDB > grant resource,connect to hfxf;
--创建用户表空间hfxf_tbs
SYS@HFXFCDB > create tablespace hfxf_tbs datafile '+DATA/xfpdb1/hfxf_tbs01.bdf' size 50m;

Tablespace created.
--创建索引表空间hfxf_tbs_inx
SYS@HFXFCDB > create tablespace hfxf_tbs_inx datafile '+DATA/xfpdb1/hfxf_tbs_inx01.dbf' size 20m;

Tablespace created.
-通过以下命令验证hfxf用户的默认表空间
SYS@HFXFCDB > select USERNAME,DEFAULT_TABLESPACE from dba_users where username='HFXF';

USERNAME
--------------------------------------------------------------------------------
DEFAULT_TABLESPACE
------------------------------
HFXF
HFXF_TBS

--使用hfxf用户连接到xfpdb1中

SYS@HFXFCDB > conn hfxf/hfxf@xfpdb1
Connected.
--创建cust表
HFXF@xfpdb1 > CREATE TABLE cust
  2  (cust_id    NUMBER
  3  ,last_name  VARCHAR2(30)
  4  ,first_name VARCHAR2(30)
  5  );

Table created.
--向cust表中插入数据
HFXF@xfpdb1 > insert into cust values(7,'ACER','SCOTT');
insert into cust values(7,'ACER','SCOTT')
            *
ERROR at line 1:
ORA-01950: no privileges on tablespace 'HFXF_TBS'


HFXF@xfpdb1 > insert into cust values(5,'STARK','JIM');
insert into cust values(5,'STARK','JIM')
            *
ERROR at line 1:
ORA-01950: no privileges on tablespace 'HFXF_TBS'


HFXF@xfpdb1 > insert into cust values(3,'GREY','BOB');
insert into cust values(3,'GREY','BOB')
            *
ERROR at line 1:
ORA-01950: no privileges on tablespace 'HFXF_TBS'


HFXF@xfpdb1 > insert into cust values(11,'KAHN','BRAD');
insert into cust values(11,'KAHN','BRAD')
            *
ERROR at line 1:
ORA-01950: no privileges on tablespace 'HFXF_TBS'


HFXF@xfpdb1 > insert into cust values(21,'DEAN','ANN');
insert into cust values(21,'DEAN','ANN')
            *
ERROR at line 1:
ORA-01950: no privileges on tablespace 'HFXF_TBS'

--hfxf用户对表空间的操作权限不足
HFXF@xfpdb1 >

SYS@HFXFCDB > grant unlimited tablespace to hfxf;

Grant succeeded.
--在同一列创建多个索引
HFXF@xfpdb1 > create index cust_idx1 on cust(cust_id) tablespace hfxf_tbs_inx;

Index created.

HFXF@xfpdb1 > create index cust_idx2 on cust(cust_id) invisible tablespace hfxf_tbs_inx;
create index cust_idx2 on cust(cust_id) invisible tablespace hfxf_tbs_inx
                               *
ERROR at line 1:
ORA-01408: such column list already indexed

HFXF@xfpdb1 > create bitmap index cust_idx2 on cust(cust_id) invisible tablespace hfxf_tbs_inx;

Index created.

HFXF@xfpdb1 > select index_name,status,visibility from user_indexes;
INDEX_NAME      STATUS   VISIBILIT
--------------- -------- ---------
CUST_IDX2       VALID    INVISIBLE
CUST_IDX1       VALID    VISIBLE

SYS@HFXFCDB > show parameter visible

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_use_invisible_indexes      boolean     FALSE
--通过企业自动跟踪功能查看执行计划，验证不可见索引是否在使用
HFXF@xfpdb1 > set autotrace trace explain
HFXF@xfpdb1 > select cust_id from cust where cust_id=3;

Execution Plan
----------------------------------------------------------
Plan hash value: 3472749082

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

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

   1 - access("CUST_ID"=3)

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)

HFXF@xfpdb1 >

HFXF@xfpdb1 > alter session set optimizer_use_invisible_indexes=true;

Session altered.

HFXF@xfpdb1 > select cust_id from cust where cust_id=3;

Execution Plan
----------------------------------------------------------
Plan hash value: 4102297476

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

| Id  | Operation                    | Name      | Rows  | Bytes | Cost (%CPU)|
Time     |

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

|   0 | SELECT STATEMENT             |           |     1 |    13 |     1   (0)|
00:00:01 |

|   1 |  BITMAP CONVERSION TO ROWIDS |           |     1 |    13 |     1   (0)|
00:00:01 |

|*  2 |   BITMAP INDEX FAST FULL SCAN| CUST_IDX2 |       |       |            |
         |

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


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

   2 - filter("CUST_ID"=3)

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)

HFXF@xfpdb1 >


--使用invisible和visible子句进行可见/不可见索引的切换
HFXF@xfpdb1 > alter index cust_idx1 invisible;

Index altered.

HFXF@xfpdb1 > alter index cust_idx2 visible;

Index altered.
--通过以下SQL查询索引的隐藏模式
HFXF@xfpdb1 > select index_name,status,visibility from user_indexes;

INDEX_NAME      STATUS   VISIBILIT
--------------- -------- ---------
CUST_IDX2       VALID    VISIBLE
CUST_IDX1       VALID    INVISIBLE

--隐藏索引仅是优化器看不到索引

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/28373936/viewspace-2140938/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/28373936/viewspace-2140938/