首页 » ORACLE, ORACLE [C]系列 » 说说Oracle DB “secondary” index 那些事.(19c drop_secondary_indexes)

说说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> @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.

SQL>

SQL> exec sys.dbms_auto_index.drop_secondary_indexes('SCOTT','EMP');
PL/SQL procedure successfully completed.

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.

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_FK                            1 DEPTNO
                                                    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_FK                         NORMAL     NO   VALID    NO   N     1          1             3         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>

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

Related Posts:

打赏

, ,

目前这篇文章还没有评论(Rss)

我要评论