oracle 如何在 PL/SQL 包中将会话变量 skip_unusable_indexes 设置为 true 以加速表删除/插入？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/187988/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to set session variable skip_unusable_indexes to true in a PL/SQL package to speed up a table delete/insert?
提问by dacracot
I'm trying to speed up a data load which is controlled via a PL/SQL stored procedure. I've programmatically altered the indexes for the table I want to refresh to be unusable. I want Oracle to ignore these unusable indexes. I can issue the statement:
我正在尝试加速通过 PL/SQL 存储过程控制的数据加载。我已经以编程方式将要刷新的表的索引更改为不可用。我希望 Oracle 忽略这些不可用的索引。我可以发表声明：
ALTER SESSION SET skip_unusable_indexes = TRUE
but I subsequently get the error:
但我随后收到错误消息：
ORA-01502: index 'MY_INDEX_NAME' or partition of such index is in unusable state
ORA-01502: 索引 'MY_INDEX_NAME' 或此类索引的分区处于不可用状态
so is it seems to have ignored my alter session.
所以它似乎忽略了我的更改会话。
Can I alter my session inside a PL/SQL package? If not, what is my alternative? How else might I disable (set unusable) the indexes to speed up the load?
我可以在 PL/SQL 包中更改我的会话吗？如果没有，我的选择是什么？我还能如何禁用（设置为不可用）索引以加快加载速度？
A somewhat related question here.
这里有一个有点相关的问题。
回答by Justin Cave
Are you issuing the ALTER SESSION statement in the same session that the stored procedure is using? Or is that ALTER SESSION executed in a separate session?
您是否在存储过程使用的同一会话中发出 ALTER SESSION 语句？或者那个 ALTER SESSION 是在单独的会话中执行的？
You can embed the ALTER SESSION in your PL/SQL with dynamic SQL, i.e.
您可以使用动态 SQL 在 PL/SQL 中嵌入 ALTER SESSION，即
BEGIN
EXECUTE IMMEDIATE 'ALTER SESSION SET skip_unusable_indexes = TRUE';
<<more code>>
END;
Are some of the indexes unique (or used to enforce a unique constraint)? As the skip_unusable_indexes documentationstates
某些索引是否唯一（或用于强制执行唯一约束）？正如skip_unusable_indexes 文档所述
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.
注意：如果索引用于对表强制执行 UNIQUE 约束，则允许对表进行插入和更新操作可能会违反约束。因此，此设置不会禁用唯一的不可用索引的错误报告。
If that is the case, can you disable the constraint and/or change the index(es) to be non-unique?
如果是这种情况，您是否可以禁用约束和/或将索引更改为非唯一的？
A quick sample of the difference between unique and non-unique indexes. Note when you have an unusable unique index, skip_unusable_indexes does not suppress the ORA-01502 error as it does when you have an unusable non-unique index.
唯一索引和非唯一索引之间差异的快速示例。请注意，当您有一个不可用的唯一索引时，skip_unusable_indexes 不会像您有一个不可用的非唯一索引时那样抑制 ORA-01502 错误。
SQL> create table a (
2 col1 number
3 );
Table created.
SQL> create unique index idx_a on a( col1 );
Index created.
SQL> insert into a values( 1 );
1 row created.
SQL> commit;
Commit complete.
SQL> alter index idx_a unusable;
Index altered.
SQL> insert into a values( 2 );
insert into a values( 2 )
*
ERROR at line 1:
ORA-01502: index 'SCOTT.IDX_A' or partition of such index is in unusable state
SQL> alter session set skip_unusable_indexes = true;
Session altered.
SQL> insert into a values( 2 );
insert into a values( 2 )
*
ERROR at line 1:
ORA-01502: index 'SCOTT.IDX_A' or partition of such index is in unusable state
SQL> drop index idx_a;
Index dropped.
SQL> create index idx_a_nonunique on a( col1 );
Index created.
SQL> alter index idx_a_nonunique unusable;
Index altered.
SQL> insert into a values( 2 );
1 row created.
