How to set session variable skip_unusable_indexes to true in a PL/SQL package to speed up a table delete/insert?

Question

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

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

...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?

---

PS - A somewhat related question here.

Answer 1

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?

You can embed the ALTER SESSION in your PL/SQL with dynamic SQL, i.e.

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 documentation states

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.

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.

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.