Oracle 参数 SKIP_UNUSABLE_INDEXES 官方解释，作用，如何配置最优化建议
本站中文解释
SKIP_UNUSABLE_INDEXES 参数是用于控制Oracle引擎在执行SELECT语句时是否跳过无法使用的索引。设置该参数为TRUE时，Oracle引擎将跳过无法使用的索引，并进入下一个有效的索引或全表扫描；设置该参数为FALSE时，Oracle引擎将会使用所有的索引，即使它们无法被使用。
正确的设置方法为：
1、在sqlplus终端中进入系统用户，并执行以下命令：
SQL> ALTER SESSION SET SKIP_UNUSABLE_INDEXES = TRUE;
2、可以在init.ora文件中添加下面这一配置项：
SKIP_UNUSABLE_INDEXES=TRUE
官方英文解释
SKIP_UNUSABLE_INDEXES enables or disables the use and reporting of tables with unusable indexes or index partitions.
| Property | Description |
|---|---|
|
Parameter type |
Boolean |
|
Default value |
|
|
Modifiable |
|
|
Modifiable in a PDB |
Yes |
|
Range of values |
|
|
Basic |
No |
If a SQL statement uses a hint that forces the usage of an unusable index, then this hint takes precedence over initialization parameter settings, including SKIP_UNUSABLE_INDEXES. If the optimizer chooses an unusable index, then an ORA-01502 error will result. (See Oracle Database
Administrator’s Guide for more information about using hints.)
Values
-
trueDisables error reporting of indexes and index partitions marked UNUSABLE. This setting allows all operations (inserts, deletes, updates, and selects) on tables with unusable indexes or index partitions.
Note:
If an index is used to enforce a UNIQUE constraint on a table, then allowing insert and update operations on the table might violate the constraint. Therefore, this setting does not disable error reporting for unusable indexes that are unique.
-
falseEnables error reporting of indexes marked UNUSABLE. This setting does not allow inserts, deletes, and updates on tables with unusable indexes or index partitions.
See Also:
Oracle Database SQL
Language Reference for more information about hints
