Skip to main content

Search

Welcome to Tibero GTS!

How to Use the INVISIBLE Index Feature

Document Type | Technical Information

Category | Tuning

Document Number | TTUTI015

 

Overview

In a running Tibero DB environment, when creating an index, you can use the INDEX INVISIBLE feature to check the impact of creating the index.

The impacts that can be checked through the INVISIBLE Index are as follows.
  • When you want to review in advance whether the execution plan using the created index is efficient after creating the index
  • When you want to check whether the efficiency of an existing efficient query plan has decreased after creating the index

This guide explains how to use the INDEX INVISIBLE feature to verify the above.
 
 

Method

To use INDEX INVISIBLE, you need to change the parameter settings and use the INVISIBLE option when creating the index.
 

Check Parameter (Default NO)

SHOW PARAM OPTIMIZER_USE_INVISIBLE_INDEXES


Change Parameter

ALTER SESSION SET OPTIMIZER_USE_INVISIBLE_INDEXES=Y;


Create INVISIBLE Index

CREATE INDEX IDX_TEST ON TEST (N) INVISIBLE;


Change to VISIBLE Index

ALTER INDEX IDX_TEST VISIBLE;

 

Test Scenario

The following scenario shows how the execution plan changes depending on whether the INVISIBLE index is created and the parameter is set.
 
Scenario 1
1. Created as INVISIBLE
CREATE TABLE TEST (N NUMBER);

INSERT INTO TEST SELECT LEVEL FROM DUAL CONNECT BY LEVEL <= 10000;

COMMIT;

CREATE INDEX IDX_TEST ON TEST (N) INVISIBLE;

SELECT * FROM DBA_INDEXES WHERE INDEX_NAME='IDX_TEST';

SET AUTOT ON EXP PLANS STAT

SELECT * FROM TEST WHERE N=500;

2. Created as INVISIBLE and parameter changed
ALTER SESSION SET OPTIMIZER_USE_INVISIBLE_INDEXES=Y;

SELECT * FROM TEST WHERE N=700;

3. Execution Results
1) Created INVISIBLE Index
Since the optimizer does not recognize the index, even though the index exists, the table is processed by a full table scan.
SQL> SHOW PARAM OPTIMIZER_USE_INVISIBLE_INDEXES

NAME                         TYPE     VALUE
---------------------------- -------- ----------------------------------------
OPTIMIZER_USE_INVISIBLE_INDE Y_N      NO
XES


SQL> CREATE TABLE TEST (N NUMBER);

Table 'TEST' created.

SQL> INSERT INTO TEST SELECT LEVEL FROM DUAL CONNECT BY LEVEL <= 10000;

10000 rows inserted.

SQL> COMMIT;

Commit completed.

SQL> CREATE INDEX IDX_TEST ON TEST (N) INVISIBLE;

Index 'IDX_TEST' created.

SQL> SELECT * FROM DBA_INDEXES WHERE INDEX_NAME='IDX_TEST';

-- (omitted)

1 row selected.

SQL> SET AUTOT ON EXP PLANS STAT
SQL> SELECT * FROM TEST WHERE N=500;

         N
----------
       500

1 row selected.

SQL ID: gc26azr12fduq
Child number: 307
Plan hash value: 1463502807

Execution Plan
--------------------------------------------------------------------------------
   1  TABLE ACCESS (FULL): TEST (Cost:24, %%CPU:0, Rows:1)


Predicate Information
--------------------------------------------------------------------------------
   1 - filter: ("TEST"."N" = 500) (0.000)


Note
--------------------------------------------------------------------------------
   1 - dynamic sampling used for this table (32 blocks)


NAME                                VALUE
------------------------------ ----------
db block gets                         137
consistent gets                       138
physical reads                          0
redo size                             188
sorts (disk)                            0
sorts (memory)                          1
rows processed                          1


Execution Stat
--------------------------------------------------------------------------------
   1  TABLE ACCESS (FULL): TEST (Time:0. ms, Rows:0, Starts:0)
2) Created INVISIBLE Index and changed parameter
After changing the parameter, even if the index is INVISIBLE, the optimizer recognizes it and uses the index.
SQL> ALTER SESSION SET OPTIMIZER_USE_INVISIBLE_INDEXES=Y;

Session altered.

SQL> SELECT * FROM TEST WHERE N=700;

         N
----------
       700

1 row selected.

SQL ID: 622bxdyk716p2
Child number: 324
Plan hash value: 1576365538

Execution Plan
--------------------------------------------------------------------------------
   1  COLUMN PROJECTION (Cost:2, %%CPU:0, Rows:1)
   2    INDEX (RANGE SCAN): IDX_TEST (Cost:2, %%CPU:0, Rows:1)


Predicate Information
--------------------------------------------------------------------------------
   2 - access: ("TEST"."N" = 700) (0.000)


Note
--------------------------------------------------------------------------------
   2 - dynamic sampling used for this table (32 blocks)


NAME                                VALUE
------------------------------ ----------
db block gets                         137
consistent gets                        39
physical reads                          1
redo size                              60
sorts (disk)                            0
sorts (memory)                          1
rows processed                          1


Execution Stat
--------------------------------------------------------------------------------
   1  COLUMN PROJECTION (Time:0. ms, Rows:0, Starts:0)
   2    INDEX (RANGE SCAN): IDX_TEST (Time:0. ms, Rows:0, Starts:0)

 

 

 

Related to