玉面飞龙的BLOG

Oracle11g 给索引增加了一个“隐藏”功能。当索引“隐藏”的时候，Oracle在硬分析决定SQL执行计划的时候不会考虑该索引。可以在session和system级别指定“隐藏”的索引能不能被应用程序SQL使用。

[coolcode lang=”sql” linenum=”no”]
SQL> create index dept_idx2 on dept(dname) online INVISIBLE;
Index created.

SQL> SELECT INDEX_NAME, VISIBILITY FROM USER_INDEXES where table_name=’DEPT’;
INDEX_NAME VISIBILITY
—————————
PK_DEPT VISIBLE
DEPT_IDX2 INVISIBLE

SQL> explain plan for select * from dept where dname=:1;
Explained.

SQL> @?/rdbms/admin/utlxpls
————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
————————————————————————–
| 0 | SELECT STATEMENT | | 1 | 20 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| DEPT | 1 | 20 | 3 (0)| 00:00:01 |
————————————————————————–
Predicate Information (identified by operation id):
—————————————————
PLAN_TABLE_OUTPUT
———————————————————————
1 - filter(”DNAME”=:1)

SQL> alter session set OPTIMIZER_USE_INVISIBLE_INDEXES = true;
Session altered.

SQL> explain plan for select * from dept where dname=:1;
Explained.

SQL> @?/rdbms/admin/utlxpls
Plan hash value: 3506722750
————————————————-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
——————————————————————————–
| 0 | SELECT STATEMENT | | 1 | 20 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 20 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | DEPT_IDX2 | 1 | | 1 (0)| 00:00:01 |
——————————————————————————–
Predicate Information (identified by operation id):
—————————————————
2 - access(”DNAME”=:1)

Test the removal of an index before dropping it.

Use temporary index structures for certain operations or modules of an application without affecting the overall application.
[/coolcode]

文档中提到引入它的原因:

Using invisible indexes, you can do the following:

Test the removal of an index before dropping it.

Use temporary index structures for certain operations or modules of an application without affecting the overall application.

Unlike unusable indexes, an invisible index is maintained during DML statements.

但对于drop index的目的来说，monitoring index和查询v$sql_plan更有效。
[coolcode lang=”sql” linenum=”no”]
select hash_value from v$sql_plan where object_name=:1;
[/coolcode]
一个好处就是在production上，先创建Invisible的索引；然后在session级别，测试(explain plan)相关SQL地执行计划,以确定是否有其他SQL因为新索引而执行计划变的反而糟糕，增加DBA操作的安全性。

不过一般来说，production上建索引之前，一定都要在QA上测试过，很少会”中招”。DBA很难硬性的follow这个process.

Oracle11g还对add column 作了增强。

If a new column is added to a table, the column is initially NULL unless you specify the DEFAULT clause. When you specify a default value, the database immediately updates each row with the default value. Note that this can take some time, and that during the update, there is an exclusive DML lock on the table. For some types of tables (for example, tables without LOB columns), if you specify both a NOT NULL constraint and a default value, the database can optimize the column add operation and greatly reduce the amount of time that the table is locked for DML.

You can add a column with a NOT NULL constraint only if the table does not contain any rows, or you specify a default value.

如文档上所说，Oracle弱化了add column与其他DML的耦合性，增加add column时候并行处理的能力。

Session 1先执行DML
[coolcode lang=”sql” linenum=”no”]
SQL> delete from emp where rownum=1;

1 row deleted.
[/coolcode]
Session 2 增加新的列，并没有发生“resouce busy”的错误。

大部分情况下，增加的列都应该是Null并且没有default value的
[coolcode lang=”sql” linenum=”no”]
SQL> alter table emp add newcol number null;

Table altered.
[/coolcode]
Session 1 再进行查询

[coolcode lang=”sql” linenum=”no”]
SQL> rollback;

Rollback complete.

SQL> set pagesize 100
SQL> select * from emp where rownum=1;

EMPNO ENAME JOB MGR
———- —————————— ————————— ———-
HIREDATE SAL COMM DEPTNO NEWCOL
————— ———- ———- ———- ———-
7369 SMITH CLERK 7902
17-DEC-80 800 20
[/coolcode]
当然DDL导致的cursor invalidation在所难免。DDL之后严重的library cache pin/lock竞争等仍然可能发生。