说说Oracle DB “secondary” index 那些事.(19c drop_secondary_indexes)
“Secondary” index 这个词比较少见，搜索了一下好像在Cassandra和 Berkeley DB的数据库中可能有该定义吧，就像上一篇笔记中在ORACLE数据库也引发现了这个词，据我10几年的数据库经验，索引创建时没有第一、第二索引的叫法，也可能仅存在于学术中，secondary 是相对于primary的， 对于非primary的应该都可以叫做secondry次要的索引。
在19c 的数据库中dbms_auto_index中有一个存储过程就叫做DROP_SECONDARY_INDEXES， 它是否是删除除了主键索引以外的索引全都删除呢？ 不是的，下面看我的测试。
SQL> desc dbms_auto_index
PROCEDURE CONFIGURE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
PARAMETER_NAME VARCHAR2 IN
PARAMETER_VALUE VARCHAR2 IN
ALLOW BOOLEAN IN DEFAULT
PROCEDURE DROP_SECONDARY_INDEXES
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
OWNNAME VARCHAR2 IN DEFAULT
TABNAME VARCHAR2 IN DEFAULT
FUNCTION REPORT_ACTIVITY RETURNS CLOB
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
ACTIVITY_START TIMESTAMP WITH TIME ZONE IN DEFAULT
ACTIVITY_END TIMESTAMP WITH TIME ZONE IN DEFAULT
TYPE VARCHAR2 IN DEFAULT
SECTION VARCHAR2 IN DEFAULT
LEVEL VARCHAR2 IN DEFAULT
FUNCTION REPORT_LAST_ACTIVITY RETURNS CLOB
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
TYPE VARCHAR2 IN DEFAULT
SECTION VARCHAR2 IN DEFAULT
LEVEL VARCHAR2 IN DEFAULT
在oracle中使用主要索引和次要索引似乎没有什么区别，也不用表在上非先创建主要索引（primary index）才可以创建次要索引（“secondary” index），对于唯一要求的我们还可以创建uniq secondary index. 一些table cluster的数据库，在创建表时需要强制指定一个primary， 但是oracle使用的是heap table，也没有把索引定义为primary 或者secondary区分， 索引也不会依赖于物理存储，这个敏捷性技术始于40年前。
在自动索引的情况下，secondary Index 或者叫辅助索引可能有了不同的意义，如XD MACHINE或ORACLE ADW cloud中的smart scan和storage index。
https://www.oracle.com/database/technologies/datawarehouse-bigdata/adb-faqs.html
Can a customer create secondary indexes, partitioned tables, or materialized views?
Yes, you can create secondary indexes, partitioned tables, or materialized views in ADB. For specific guidance on when this should be done, please see the specific FAQ sections for Autonomous Data Warehousing and Autonomous Transaction Processing.Can a customer create indexes on tables?
The CREATE INDEX statement is supported in ADW. Howver, ADW uses other techniques such as Exadata smart scan and storage indexes to quickly locate data. ADW will automatically create, maintain and delete indexes in certain cases such as with enforced and enabled primary key constraints.
下面使用scott的表为样例， 如果没有安装样例SCHEMA, SCOTT schema的脚本从https://github.com/oracle/dotnet-db-samples/blob/master/schemas/scott.sql 可以得到。测试用的是19c数据库。
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB1 READ WRITE NO
5 ERPDB MOUNTED
SQL> alter session set container=pdb1;
Session altered.
SQL> GRANT CONNECT,RESOURCE,UNLIMITED TABLESPACE TO SCOTT IDENTIFIED BY TIGER;
SQL> ALTER USER SCOTT DEFAULT TABLESPACE USERS;
SQL> ALTER USER SCOTT TEMPORARY TABLESPACE TEMP;
SQL> alter user scott account unlock;
[oracle@anbob19 sqldeveloper]$ sql scott/TIGER@pdb1
SQLcl: Release 18.4 Production on Wed Oct 02 03:49:15 2019
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.2.0.0.0
SQL> create index EMP_HIREDATE on EMP(HIREDATE);
Index created.
SQL> create index EMP_FK on EMP(DEPTNO);
Index created.
SQL> create bitmap index EMP_BITMAP on EMP(JOB);
Index created.
SQL> create index EMP_FKPLUS on EMP(DEPTNO,JOB);
Index created.
SQL> create unique index EMP_UNIQUE on EMP(HIREDATE,ENAME);
Index created.
SQL> set ddl segment_attributes off
DDL Option SEGMENT_ATTRIBUTES off
SQL> set ddl storage off
DDL Option STORAGE off
SQL> select dbms_metadata.get_ddl('INDEX',index_name,user) from user_indexes;
DBMS_METADATA.GET_DDL('INDEX',INDEX_NAME,USER)
--------------------------------------------------------------------------------
CREATE UNIQUE INDEX "SCOTT"."PK_DEPT" ON "SCOTT"."DEPT" ("DEPTNO")
CREATE UNIQUE INDEX "SCOTT"."PK_EMP" ON "SCOTT"."EMP" ("EMPNO")
CREATE INDEX "SCOTT"."EMP_HIREDATE" ON "SCOTT"."EMP" ("HIREDATE")
CREATE INDEX "SCOTT"."EMP_FK" ON "SCOTT"."EMP" ("DEPTNO")
CREATE BITMAP INDEX "SCOTT"."EMP_BITMAP" ON "SCOTT"."EMP" ("JOB")
CREATE INDEX "SCOTT"."EMP_FKPLUS" ON "SCOTT"."EMP" ("DEPTNO", "JOB")
CREATE UNIQUE INDEX "SCOTT"."EMP_UNIQUE" ON "SCOTT"."EMP" ("HIREDATE", "ENAME
7 rows selected.
SQL> @ind scott.dept
Display indexes where table or index name matches %scott.dept%...
TABLE_OWNER TABLE_NAME INDEX_NAME POS# COLUMN_NAME DSC
------------- ------------ ------------- ---- ------------------------------ ----
SCOTT DEPT PK_DEPT 1 DEPTNO
INDEX_OWNER TABLE_NAME INDEX_NAME IDXTYPE UNIQ STATUS PART TEMP H LFBLKS NDK NUM_ROWS CLUF LAST_ANALYZED DEGREE VISIBILIT
------------- ------------ ------------- ---------- ---- -------- ---- ---- -- ---------- ------------- ---------- ---------- ------------------- ------ ---------
SCOTT DEPT PK_DEPT NORMAL YES VALID NO N 1 VISIBLE
SQL> @ind scott.emp
Display indexes where table or index name matches %scott.emp%...
TABLE_OWNER TABLE_NAME INDEX_NAME POS# COLUMN_NAME DSC
-------------------- ----------- --------------- ---- ------------------------------ ----
SCOTT EMP EMP_BITMAP 1 JOB
EMP_FK 1 DEPTNO
EMP_FKPLUS 1 DEPTNO
2 JOB
EMP_HIREDATE 1 HIREDATE
EMP_UNIQUE 1 HIREDATE
2 ENAME
PK_EMP 1 EMPNO
INDEX_OWNER TABLE_NAME INDEX_NAME IDXTYPE UNIQ STATUS PART TEMP H LFBLKS NDK NUM_ROWS CLUF LAST_ANALYZED DEGREE VISIBILIT
-------------------- ----------- --------------- ---------- ---- -------- ---- ---- -- ---------- ------------- ---------- ---------- ------------------- ------ ---------
SCOTT EMP EMP_BITMAP BITMAP NO VALID NO N 1 1 5 5 5 2019-10-02 15:51:45 1 VISIBLE
EMP EMP_FK NORMAL NO VALID NO N 1 1 3 14 1 2019-10-02 15:51:45 1 VISIBLE
EMP EMP_FKPLUS NORMAL NO VALID NO N 1 1 9 14 1 2019-10-02 15:51:45 1 VISIBLE
EMP EMP_HIREDATE NORMAL NO VALID NO N 1 1 13 14 1 2019-10-02 15:51:45 1 VISIBLE
EMP EMP_UNIQUE NORMAL YES VALID NO N 1 1 14 14 1 2019-10-02 15:51:46 1 VISIBLE
EMP PK_EMP NORMAL YES VALID NO N 1 VISIBLE
SQL> @cons scott.%
Show constraints on table scott.%...
OWNER TABLE_NAME CONSTRAINT_NAME C R_CONSTRAINT_NAME COLUMN_NAME POSITION STATUS VALIDATED
------------------------------ ------------------------------ ------------------------------ - ------------------------------ ------------------------------ ---------- -------- -------------
SCOTT DEPT PK_DEPT P DEPTNO 1 ENABLED VALIDATED
SCOTT EMP PK_EMP P EMPNO 1 ENABLED VALIDATED
SCOTT EMP FK_DEPTNO R PK_DEPT DEPTNO 1 ENABLED VALIDATED
3 rows selected.
Note:
这里创建几个索引，EMP表上目前有主键索引，唯一索引，普通非唯一索引，位图索引，外键索引，外键复合索引。
下面执行删除secondary indexes, run as dba
SQL> exec sys.dbms_auto_index.drop_secondary_indexes('SCOTT','EMP');
PL/SQL procedure successfully completed.
查看剩余索引
SQL> @ind scott.%
Display indexes where table or index name matches %scott.%%...
TABLE_OWNER TABLE_NAME INDEX_NAME POS# COLUMN_NAME DSC
-------------------- ------------------------------ ------------------------------ ---- ------------------------------ ----
SCOTT DEPT PK_DEPT 1 DEPTNO
EMP EMP_UNIQUE 1 HIREDATE
2 ENAME
PK_EMP 1 EMPNO
Note:
主键和唯一索引保留；
和外键约束列完全匹配的外键列保留；
其它索引全部被删除；
有一点需要注意，外键列上的复合索引同样也是被删除了，这类索引常用于操作父表键时防止子表锁表的问题而创建，但有时会兼顾业务查询需要创建了复合索引， 此时也会被误删除, 即使没有外键列独立的索引时。
SQL> drop index "SCOTT"."EMP_FK";
Index dropped.
SQL> CREATE INDEX "SCOTT"."EMP_FKPLUS" ON "SCOTT"."EMP" ("DEPTNO", "JOB");
Index created.
SQL> @ind scott.emp
Display indexes where table or index name matches %scott.emp%...
TABLE_OWNER TABLE_NAME INDEX_NAME POS# COLUMN_NAME DSC
-------------------- ------------------------------ ------------------------------ ---- ------------------------------ ----
SCOTT EMP EMP_FKPLUS 1 DEPTNO
2 JOB
EMP_UNIQUE 1 HIREDATE
2 ENAME
PK_EMP 1 EMPNO
SQL> @46on 12
Session altered.
SQL> exec sys.dbms_auto_index.drop_secondary_indexes('SCOTT','EMP');
PL/SQL procedure successfully completed.
SQL> @46off
Session altered.
SQL> oradebug setmypid
Statement processed.
SQL> oradebug tracefile_name
/u01/app/oracle/diag/rdbms/anbob19c/anbob19c/trace/anbob19c_ora_4889.trc
SQL> @ind scott.emp
Display indexes where table or index name matches %scott.emp%...
TABLE_OWNER TABLE_NAME INDEX_NAME POS# COLUMN_NAME DSC
-------------------- ------------------------------ ------------------------------ ---- ------------------------------ ----
SCOTT EMP EMP_UNIQUE 1 HIREDATE
2 ENAME
PK_EMP 1 EMPNO
46 trace file, sql文本为了阅读性格式化过。
******************************************************************************** SQL ID: 00sjs8j1a0svf Plan Hash: 2001100360 WITH C AS ( SELECT /*+ materialize */ INDEX_OWNER, INDEX_NAME FROM ALL_CONSTRAINTS C WHERE C.INDEX_OWNER IS NOT NULL AND C.INDEX_NAME IS NOT NULL ) SELECT /*+dynamic_sampling(11)*/ OWNER, INDEX_NAME FROM ALL_INDEXES I WHERE INDEX_TYPE != 'LOB' AND INDEX_TYPE != 'IOT - TOP' AND UNIQUENESS = 'NONUNIQUE' AND (:B2 IS NULL OR OWNER = :B2) AND (:B1 IS NULL OR TABLE_NAME = :B1) AND TABLE_OWNER NOT IN ( SELECT USERNAME FROM ALL_USERS WHERE ORACLE_MAINTAINED = 'Y' ) AND NOT EXISTS ( SELECT INDEX_OWNER, INDEX_NAME FROM C WHERE C.INDEX_OWNER = I.OWNER AND C.INDEX_NAME = I.INDEX_NAME AND C.INDEX_OWNER IS NOT NULL AND C.INDEX_NAME IS NOT NULL ) AND (OWNER, INDEX_NAME) NOT IN ( SELECT I.INDEX_OWNER, I.INDEX_NAME FROM ( SELECT A.OWNER, A.TABLE_NAME, A.CONSTRAINT_NAME , LISTAGG(A.COLUMN_NAME, ', ') WITHIN GROUP (ORDER BY A.POSITION) AS COLS FROM ALL_CONS_COLUMNS A, ALL_CONSTRAINTS B WHERE A.OWNER = B.OWNER AND A.CONSTRAINT_NAME = B.CONSTRAINT_NAME AND B.CONSTRAINT_TYPE = 'R' GROUP BY A.OWNER, A.TABLE_NAME, A.CONSTRAINT_NAME ) C, ( SELECT INDEX_OWNER, TABLE_OWNER, TABLE_NAME, INDEX_NAME , LISTAGG(COLUMN_NAME, ',') WITHIN GROUP (ORDER BY COLUMN_POSITION) AS COLS FROM ALL_IND_COLUMNS GROUP BY TABLE_OWNER, TABLE_NAME, INDEX_OWNER, INDEX_NAME ) I WHERE C.OWNER = I.TABLE_OWNER AND C.TABLE_NAME = I.TABLE_NAME AND C.COLS = I.COLS ) call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 1.19 5.41 0 0 2 0 Fetch 1 0.01 0.01 0 460 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 3 1.21 5.42 0 460 2 1
Note:
从trace中可以看到使用了LISTAGG 比较了外键列和索引列完全匹配的方式问题（列名加顺序），这可能过于简单，而把外键列上的复合索引也同样删除了。
Summary:
Oracle DB “secondary” index 叫辅助索引或次要的索引，名称是相对primary index命名的，但是在19c的dbms_auto_index.drop_secondary_indexes中可以看出删除的索引也并不是除了primary key以外的全部索引， 主键、外键、唯一键同样也会保留。 该功能可能目前也只能用于测试环境中， 如先把次要的索引全部删除，然后测试让数据库自动创建及删除维护相应的索引。
–源文 www.anbob.com —
Reference Franck Pachot’ Article
微信扫一扫，打赏作者吧～
对不起，这篇文章暂时关闭评论。