Suggestions for using the skip_unusable_indexes parameter in oracle

The use of SKIP_UNUSABLE_INDEXES is related to index failure. This parameter is introduced at 10 GB, and the default value of 11 GB is TRUE.
If the value is TRUE, if an index in the usable state exists in the database, the new execution plan is automatically generated for the index. (This index is not taken away and no exception is reported ); if this parameter is set to False, an error is returned. in some key systems, the O & M database sets this parameter to False, so that the system can detect index exceptions in a timely manner so as to promptly intervene in repair.
The environment varies, and the setting value can also be set based on the actual situation. If the SQL statement uses hint or the corresponding DML that involves a unique index, this parameter will be invalid.

For some scenarios of this parameter, refer to the following tests:

Create Test tables and indexes

SQL> conn test/test
Connected.
SQL> drop table;
The table has been deleted.
SQL> create table a (id number );
The table has been created.
SQL> create unique index idx_a_id on a (id );
The index has been created.
SQL> declare
2 begin
3 for a in 1 .. 1000 loop
4 insert into a (id) values ();
5 end loop;
6 end;
7/
The PL/SQL process is successfully completed.
SQL> commit;
Submitted.
SQL> show parameter SKIP_UNUSABLE_INDEXES;
NAME TYPE VALUE
------------------------------------------------------------------
Skip_unusable_indexes boolean TRUE
SQL> select * from a where id = 1;

Execution plan
----------------------------------------------------------
Plan hash value: 277080427
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
------------------------------------------------------------------------------
| 0 | select statement | 1 | 13 | 1 (0) | 00:00:01 |
| * 1 | index unique scan | IDX_A_ID | 1 | 13 | 1 (0) | 00:00:01 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id ):
---------------------------------------------------
1-access ("ID" = 1)

Statistics
----------------------------------------------------------
1 recursive cballs
0 db block gets
4 consistent gets
0 physical reads
124 redo size
402 bytes sent via SQL * Net to client
385 bytes encoded ed via SQL * Net from client
2 SQL * Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
Set skip_unusable_indexes to false.

SQL> alter system set skip_unusable_indexes = false scope = memory;
The system has been changed.
Change Index to unavailable
SQL> alter index idx_a_id unusable;
The index has been changed.
An error occurs, indicating that the index is unavailable.
SQL> select * from a where id = 1;
Select * from a where id = 1
*
Row 3 has an error:
ORA-01502: index 'test. IDX_A_ID 'or the partition of this type of index is unavailable
Modify skip_unusable_indexes to true.

SQL> alter system set skip_unusable_indexes = true scope = memory;
The system has been changed.
This SQL statement can run normally during query operations, but a full table scan is performed.

SQL> select * from a where id = 1;

Execution plan
----------------------------------------------------------
Plan hash value: 2248738933
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
--------------------------------------------------------------------------
| 0 | select statement | 4 | 52 | 3 (0) | 00:00:01 |
| * 1 | table access full | A | 4 | 52 | 3 (0) | 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id ):
---------------------------------------------------
1-filter ("ID" = 1)

Statistics
----------------------------------------------------------
1 recursive cballs
0 db block gets
8 consistent gets
0 physical reads
0 redo size
402 bytes sent via SQL * Net to client
385 bytes encoded ed via SQL * Net from client
2 SQL * Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
If you use hint to forcibly use an index, the system prompts that the index is invalid.

SQL> select/* + index (a) */* from a where id = 1;
Select/* + index (a) */* from a where id = 1
*
Row 3 has an error:
ORA-01502: index 'test. IDX_A_ID 'or the partition of this type of index is unavailable
-- Insert operation error
SQL> insert into a values (1002 );
Insert into a values (1002)
*
Row 3 has an error:
ORA-01502: index 'test. IDX_A_ID 'or the partition of this type of index is unavailable
SQL> delete from a where id = 1;
Delete from a where id = 1
*
Row 3 has an error:
ORA-01502: index 'test. IDX_A_ID 'or the partition of this type of index is unavailable

SQL>
Solution: re-create the index

SQL> alter index test. idx_a_id rebuild;
The index has been changed.
SQL> select/* + index (a) */* from a where id = 1;

Execution plan
----------------------------------------------------------
Plan hash value: 277080427
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
------------------------------------------------------------------------------
| 0 | select statement | 1 | 13 | 1 (0) | 00:00:01 |
| * 1 | index unique scan | IDX_A_ID | 1 | 13 | 1 (0) | 00:00:01 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id ):
---------------------------------------------------
1-access ("ID" = 1)

Statistics
----------------------------------------------------------
15 recursive cballs
0 db block gets
5 consistent gets
1 physical reads
0 redo size
402 bytes sent via SQL * Net to client
385 bytes encoded ed via SQL * Net from client
2 SQL * Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

SQL> drop index test. idx_a_id;
The index has been deleted.
SQL> create index test. idx_a_id on a (id );
The index has been created.
SQL> alter index test. idx_a_id unusable;
The index has been changed.
SQL> insert into a values (1002 );
One row has been created.
SQL> commit;
Tests show that SKIP_UNUSABLE_INDEXES does not take effect for statements that use hint to force the use of indexes and insert or delete statements that use unique indexes.

This test was taken from the Internet and made some modifications.