How to make ORACLE indexes invisible

We often create or delete indexes on the database. Because the index has a great impact on SQL Execution performance, it may become very good or very poor, in the offline development environment, we can fully test whether indexes can be created or deleted. However, in the online environment, due to high-concurrency access, if we delete an important large index (larger than GB), we will find that a large number of SQL statements suffer from poor performance after deletion, and soon the host LOAD will soar, the system cannot run. Because the index has been deleted and is large, rebuilding on the spot is basically impossible. Because of the huge index, it may take several minutes or even hours to create it, at this time, the host has basically no response, and I/O is all used up, so I can only stop the application and start opening the application after the index is created, when this happens, you will regret your mistakes. Is there any way to reduce the risk of deleting indexes? See the following:

First, create a table t1 and create a new index idx_t1_table_name in the table_name field of t1.

--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (% CPU) |
--------------------------------------------------------------------------------
| 0 | select statement | 1 | 17 | 2 (50) |
| 1 | sort aggregate | 1 | 17 |
| * 2 | index range scan | IDX_T1_TABLE_NAME | 2 | 34 | 2 (50) |
--------------------------------------------------------------------------------

OK. We can see from the above that the index is normal. Now let's assume that we no longer need this index, so we want to delete it, but we don't know if it will be at the beginning of this article. If the index is invisible before it can be deleted, confirm that there is no problem and then delete the index. To this end, Oracle11g introduces a new function to set whether the index is visible, as shown in the following example:

SQL> alter index IDX_T1_TABLE_NAME invisible;
 
Index altered
 
SQL> explain plan for select count (*) from t1 where table_name = DUAL;
 
Explained
 
SQL> SELECT * FROM TABLE (DBMS_XPLAN.DISPLAY );
 
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (% CPU) |
-------------------------------------------------------------------------
| 0 | select statement | 1 | 17 | 23 (22) |
| 1 | sort aggregate | 1 | 17 |
| * 2 | table access full | T1 | 2 | 34 | 23 (22) |
-------------------------------------------------------------------------

In this way, the index is hidden. If the index is hidden and there is a performance problem, we can immediately open the index:
SQL> alter index IDX_T1_TABLE_NAME visible;
 
Index altered
 
SQL> explain plan for select count (*) from t1 where table_name = T1;
 
Explained
 
SQL> SELECT * FROM TABLE (DBMS_XPLAN.DISPLAY );
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3098159
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Tim
--------------------------------------------------------------------------------
| 0 | select statement | 1 | 17 | 1 (0) | 00:
| 1 | sort aggregate | 1 | 17 |
| * 2 | index range scan | IDX_T1_TABLE_NAME | 1 | 17 | 1 (0) | 00:
--------------------------------------------------------------------------------

 
 

The above is the processing method of Oracle11g, but the index in Oracle9i or Oracle10g does not have the invisible function. What should we do?
Currently, Oracle databases generally use cost-based computing methods to generate execution plans. As long as the indexing cost is lower, ORACLE will choose to use indexes. OK, so long as we tell ORACLE that the cost of using this index is very high, it will not use this index, which will temporarily make the index unavailable. I believe many people know that ORACLE provides the dbms_stats package to manage the statistics of the object. Through the dbms_stats.set_index_stats function, we can set the statistics forcibly. Now we only need to set the index cost to a very large value, as follows:

-- View basic statistics of IDX_T1_TABLE_NAME
SQL> select a. owner, a. index_name, a. blevel, a. leaf_blocks, a. num_rows from all_indexes a where owner = YZS and index_name = IDX_T1_TABLE_NAME;
 
OWNER INDEX_NAME BLEVEL LEAF_BLOCKS NUM_ROWS
-------------------------------------------------------------------------------------------
YZS IDX_T1_TABLE_NAME 1 12 2710

-- Setting very outrageous statistics makes ORACLE think that indexing costs are high.
SQL> exec dbms_stats.set_index_stats (ownname => YZS, indname => IDX_T1_TABLE_NAME, indlevel => 10, numlblks => 1000000000, numrows => 100000000000, no_invalidate => false );
 
PL/SQL procedure successfully completed

Note: The value of indlevel cannot be set too high. In some versions of ORACLE, bugs may cause SQL parsing errors. The numlblks value does not need to be too large, because the maximum number of indexes in ORACLE statistics is only 4294967295. No_invalidate = false indicates that the execution plan in the CACHE is immediately invalidated and the SQL Execution Plan is generated based on the current statistics.

-- Verify if it takes effect
SQL> explain plan for select count (*) from t1 where table_name = DUAL;
 
Explained
 
SQL> SELECT * FROM TABLE (DBMS_XPLAN.DISPLAY );
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3724264953
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time & nbs