Effects of skip_unusable_indexes parameters on invisible indexes

Effects of skip_unusable_indexes parameters on invisible indexes

How the database handles the unavailable indexes is mainly determined by the parameter skip_unusable_indexes;

If the parameter is set to True, the database encounters an unavailable index. Only ignores and does not prompt for any error messages, and even if the table has an unavailable index or index partition, you can perform a DML operation on the table, and the DML statements for unavailable indexes will execute normally, but the database stops Maintain related indexes.

Show Parameters skip_unusable_indexes;

-----database ignores indexes to perform full table scans

CREATE TABLE T2

(

Sid int NOT NULL,

Sname VARCHAR2 (10)

)

Tablespace test;

--Loop Import data

Declare

MAXRECORDS constant int:=100000;

I int: = 1;

Begin

For I in 1..maxrecords loop

INSERT into T2 values (i, ' Ocpyang ');

End Loop;

Dbms_output.put_line (' Successful data entry! ');

Commit

End

/

Create index index_t2 on T2 (SID) Tablespace Pindex;

Set AUTOTRACE on exp;

SELECT * FROM T2 where sid<10;

Execution plan

---------------------------------------------------- ------

Plan Hash value:48609158

--------------------------------------- -----------------------------------------

--------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Ti

Me |

------- -------------------------------------------------------------------------

---- ----

| 0 |          SELECT STATEMENT |     |   9 |     180 | 3 (0) | 00

: 00:01 |

|  1 | TABLE ACCESS by INDEX rowid|     T2 |   9 |     180 | 3 (0) | 00

: 00:01 |

|* 2 | INDEX RANGE SCAN |     Index_t2 |       9 |     | 2 (0) | 00

: 00:01 |

-------------------------------- ------------------------------------------------

predicate information (identified by Operation ID):

------------------------------------------------ ---

2-access ("SID" <10)

Note

-----

-Dynamic sampling used for this statement (level=2)

Sql>

Set Autotrace off;

Alter index INDEX_T2 unusable;

Set AUTOTRACE on exp;

SELECT * FROM T2 where sid<10;

Execution plan

------------------ ----------------------------------------

Plan Hash value:1513984157

--- -----------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |

------------------------------------------------- -------------------------

| 0 |      SELECT STATEMENT |     |    4 |   80 | 103 (1) | 00:00:02 |

|* 1 | TABLE ACCESS full|     T2 |    4 |   80 | 103 (1) | 00:00:02 |

--------------------------------- -----------------------------------------

predicate information (identified by Operation ID):

---------------------------------------------------

1-filter ("SID" <10)

Note

-----

-Dynamic sampling used for this statement (level=2)

-SQL Plan Baseline "SQL_PLAN_51MJ7YNVY9STNB860BCF2" used for this statement

Set Autotrace off;

-----Verify the effect of skip_unusable_indexes to False

Show Parameters skip_unusable_indexes;

alter system set SKIP_UNUSABLE_INDEXES=FALSE;

Alter index INDEX_T2 unusable;

Set AUTOTRACE on exp;

SELECT * FROM T2 where sid<10;

*