Invisible indexes of Oracle 11 GB

You are often confused about the invisible indexes of Oracle 11 GB. Is the index really helpful for user queries? It may be helpful for one query, but it may affect 10 other queries. The index will certainly have a negative impact on the INSERT statement, and will also perform potential deletion and update operations, depending on whether the WHERE condition includes this column in the index. A related question is, if the index is deleted, what will affect the query performance? Of course, you can delete the index and view the impact on the query, but it is easy to do. How does an index actually help with queries? You must re-define the index. Therefore, you must re-create the index. After being completely re-created, no one can use it. Re-indexing is also an expensive process; it occupies a lot of useful database resources. Can you create an index that is unavailable for a specific query and does not affect other queries? Before Oracle Database 11 GB, we do not recommend using alter index... UNUSABLE because it will cause all DML failures on the table. However, you can use this option precisely through an invisible index. In short, you can make the index "invisible" to the optimizer, so that no query will use it. If you want to use an index for a query, you must explicitly specify it as a prompt. The following is an example. Suppose there is a table named RES, And you have created the following index: SQL> create index in_res_guest on res (guest_id); after analyzing the table and index, if you execute SQL> select * from res where guest_id = 101; you will find that you are using this index: Execution Plan -------------------------------------------------- Plan hash value: 1519600902 Bytes | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | STATEMENT | 0 | select statement | 1 | 28 | 3 (0) | 00:00:01 | 1 | table access by index rowid | RES | 1 | 28 | 3 (0) | 00:00:01 | * 2 | index range scan | IN_RES_GUEST | 1 | 1 (0) | 00:00:01 | descripredicate Information (identif Ied by operation id): ------------------------------------------------- 2-access ("GUEST_ID" = 101) Now, make the index invisible: SQL> alter Index in_res_guest invisible; index altered. the following content is displayed: SQL> select * from res where guest_id = 101 2/Execution Plan -------------------------------------------------------- Plan hash value: 3824022422 bytes | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | percent | 0 | select statement | 1 | 28 | 140 (2) | 00:00:02 | * 1 | table access full | RES | 1 | 28 | 140 (2) | 00:00:02 | identified Predicate Information (identified by operation id ):-------------------------- ------------------------- 1-filter ("GUEST_ID" = 101) does not use an index. To enable the optimizer to use the index again, you must explicitly name the index in the prompt: (my test does not seem to work, but it only works by changing the session parameter) SQL> select/* + INDEX (res IN_RES_GUEST) */res_id from res where guest_id = 101; region | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | period | 0 | select statement | | 1 | 28 | 3 (0) | 00:00:01 | 1 | table access by index rowid | RES | 1 | 28 | 3 (0) | 00:00:01 | * 2 | index range scan | IN_RES_GUEST | 1 | 1 (0) | 00:00:01 | renew now! The optimizer uses indexes again. Alternatively, you can set session-level parameters to use invisible indexes: SQL> alter session set optimizer_use_invisible_indexes = true; if you cannot modify the code (such as the code in a third-party application ), this feature will be very useful. When creating an index, You can append the clause INVISIBLE at the end to make the index INVISIBLE to the optimizer. You can also use the dictionary view USER_INDEXES to view the current index settings. SQL> select visibility 2 from user_indexes 3 where index_name = 'in _ RES_GUEST '; VISIBILITY--INVISIBLE Note: If you re-build the index, the index is visible. You must explicitly set it to invisible again. So what is the index invisible? Of course, it will not be invisible to users. It is only invisible to the optimizer. Regular database operations (such as insert, update, and delete) will continue to update the index. Note This when creating an invisible index. Because of this index, you will not view the performance again, but you may also pay some price during the DML operation. The examples below are my own examples for reference only: -------------------------------------------------- [SQL] SCOTT @ chan> create table test (id number, name varchar2 (20); the table has been created. SCOTT @ chan> insert into test values (1, 'jack'); 1 row has been created. SCOTT @ chan> insert into test values (2, 'dylan'); 1 row has been created. SCOTT @ chan> commit; the submission is complete. SCOTT @ chan> create index test_idx on test (name); the index has been created. SCOTT @ chan> set autotrace on explain SCOTT @ chan> select * from test where name = 'dylan'; id name ---------- ------------------ 2 DYLAN execution Plan explain Plan hash value: 1389866015 Bytes | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | -------------------- STATEMENT | 0 | select statement | 1 | 25 | 2 (0) | 00:00:01 | 1 | table access by index rowid | TEST | 1 | 25 | 2 (0) | 00:00:01 | * 2 | index range scan | TEST_IDX | 1 | 1 (0) | 00:00:01 | identified Predicate Information (identified by operation id ):------ Release 2-access ("NAME" = 'dylan') Note ------dynamic sampling used for this statement (level = 2) SCOTT @ chan> alter index test_idx invisible; the index has been changed. SCOTT @ chan> select * from test where name = 'dylan'; id name ---------- -------------------- 2 DYLAN execution Plan ---------------------------------------------------------- Plan hash value: 1357081020 Bytes | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | ----------------------------------------------------------------------- --- | 0 | select statement | 1 | 25 | 3 (0) | 00:00:01 | * 1 | table access full | TEST | 1 | 25 | 3 (0) | 00:00:01 | identified Predicate Information (identified by operation id): interval 1-filter ("NAME" = 'dylan') Note ------dynamic sampling used for this statement (level = 2) [SQL] SCOTT @ Chan> alter session set optimizer_use_invisible_indexes = true; the session has been changed. [SQL] SCOTT @ chan> select * from test where name = 'dylan'; ID NAME ---------- -------------------- 2 DYLAN execution Plan ---------------------------------------------------------- Plan hash value: 1389866015 Bytes | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | Bytes | 0 | select statement | 1 | 25 | 2 (0) | 00:00:01 | 1 | table access by index rowid | TEST | 1 | 25 | 2 (0) | 00:00:01 | * 2 | index range scan | TEST_IDX | 1 | 1 (0) | 00:00:01 | identified Predicate Information (identified by operation id ): ----------------------------------------------- 2-access ("NAME" = 'dylan') Note ------dynamic sampling used for this statement (level = 2) SCOTT @ chan> select visibility 2 from user_indexes 3 where index_name = 'test _ idx'; VISIBILIT --------- INVISIBLE -------------------------- Present By Dylan.