3

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?

A somewhat related question here.

Community
  • 1
  • 1
dacracot
  • 22,002
  • 26
  • 104
  • 152

1 Answers1

4

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.
Justin Cave
  • 227,342
  • 24
  • 367
  • 384
  • Tried that originally, but it still gives me the ORA-01502 error. – dacracot Oct 09 '08 at 16:21
  • I don't think that is relevant, the ORA-01502 error is complaining that the index is unusable (which is intended) not that a unique constraint has been violated. – dacracot Oct 09 '08 at 16:53
  • But that's the point of the statement "this setting does not disable error reporting for unusable indexes that are unique". See the edit I just made with a demonstration. – Justin Cave Oct 09 '08 at 17:00
  • Ok, I see your point. So I tried disabling the unique constraints and running again, but the ORA-01502 still crops up. – dacracot Oct 09 '08 at 19:28
  • I believe so, but I've moved on. I'm satisfied with the speed without any additional alteration to the indexing. Thanks for all your advise. – dacracot Oct 09 '08 at 21:02