Index virtuel et index invisible : à quoi servent-ils? - Virtual index and invisible index: what are they for?
Introduction
La création d'un index, ainsi que sa suppression, peuvent prendre des heures selon la volumétrie des tables. En 2018, une table de 500 millions d'enregistrements n'a rien d'extraordinaire, ni même une table en ayant 5 milliards. Le problème est que lorsqu'on veut optimiser une requête via l'ajout d'un index pour voir si le CBO génère un meilleur plan, tester cette hypothèse va prendre trop de temps. C'est pour répondre à ce problème que Oracle a créé les types d'index suivants : index virtuel et index invisible. Ces types d'index peuvent être créés immédiatement et, pour l'index invisible, modifiés immédiatement et donc testés tout aussi vite.
Voyons dans le détail comment utiliser l'index virtuel, sa manipulation n'est pas si simple.
Points d'attention
NA.
Base de tests
Une base Oracle 12c.
Exemples
============================================================================================
Index virtuel
============================================================================================
Un index virtuel est un index sans segment, avec uniquement des metadatas dans le dictionnaire de données; metadatas utilisables bien sur par le CBO pour générer ses plans d'exécution.
On se met en mode DEFERRED SEGMENT à faux pour nos tests, pour valider qu'un index virtuel ne crée pas de segment.
SQL> alter session set deferred_segment_creation=FALSE;
Session altered.
SQL> show parameter deferr
NAME TYPE VALUE
------------------------------------ -----------
deferred_segment_creation boolean FALSE
SQL> create table zztest(id number, name varchar2(50 CHAR));
Table created.
SQL> INSERT INTO zztest SELECT rownum, 'TEST' || to_char(rownum) FROM dual CONNECT BY LEVEL <=100000;
100000 rows created.
SQL> commit;
Commit complete.
Création de l'index virtuel via la clause NOSEGMENT.
SQL> CREATE UNIQUE INDEX zztest_id ON zztest(id) NOSEGMENT;
Index created.
SQL> exec dbms_stats.gather_table_stats(ownname=>'',tabname=>'ZZTEST', cascade=>TRUE);
PL/SQL procedure successfully completed.
ATTENTION : cet index est virtuel, Oracle appelle cela aussi un Fake Index. Il n'existe pas dans DBA_INDEXES, ni même dans DBA_SEGMENTS puisque sa raison d'être est justement d'être sans segment.
SQL> select count(*) from DBA_INDEXES where index_name = 'ZZTEST_ID';
COUNT(*)
----------
0
SQL> select count(*) from DBA_SEGMENTS where segment_name = 'ZZTEST_ID';
COUNT(*)
----------
0
En revanche la table existe bien sur le disque dur :-)
SQL> select count(*) from DBA_SEGMENTS where segment_name = 'ZZTEST';
COUNT(*)
----------
1
Mais attention, cet index est présent dans DBA_OBJECTS, et avec un statut VALID en plus! C'est très perturbant : un index valide non présent dans DBA_INDEXES... Visiblement, si on crée un index avec la clause NOSEGMENT, cet index existe comme objet mais pas comme un vrai index puisqu'il est absent de DBA_INDEXES; c'est un index virtuel donc virtuellement un index mais pas un vrai index, un fake index quoi :-)
SQL> select OBJECT_TYPE, STATUS from dba_objects where OBJECT_NAME = 'ZZTEST_ID';
OBJECT_TYPE STATUS
-----------------------
INDEX VALID
Bon, maintenant testons son intérêt par le CBO!
Tiens, le CBO ne l'utilise pas, il fait un Full Table Scan; je me suis trompé quelque part?
SQL> explain plan for SELECT * FROM zztest WHERE id = '100';
Explained.
SQL> select * from dbms_xplan.display();
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------
Plan hash value: 3582063246
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 15 | 103 (1)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| ZZTEST | 1 | 15 | 103 (1)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ID"=100)
13 rows selected.
En effet, pour que le CBO puisse utiliser ce type d'index, il faut modifier le paramètre caché _use_nosegment_indexes. Et le commentaire de ce paramètre est clair : les index avec la clause NOSEGMENT ne peuvent être utilisés que dans un EXPLAIN PLAN; puisqu'ils n'existent pas sur le disque dur il est impossible des utiliser lors de l'exécution d'une requête.
ATTENTION : les objets X$ ne sont accessibles que par le user SYS, même si on a le rôle DBA. Cela signifie que le SELECT ci-dessous doit être fait comme SYS mais que l'ALTER SESSION doit être fait par le user applicatif non SYS.
SQL> SELECT A.KSPPINM PARAM ,
B.KSPPSTVL SESSIONVAL ,
C.KSPPSTVL INSTANCEVAL,
A.KSPPDESC DESCR
FROM
SYS.X$KSPPI A ,
SYS.X$KSPPCV B ,
SYS.X$KSPPSV C
WHERE
A.INDX = B.INDX AND
A.INDX = C.INDX AND
A.KSPPINM like '%use_nosegment_indexes'
ORDER BY 1;
PARAM SESSIONVAL INSTANCEVA DESCR
-------------------------- ---------- ---------- ----------------------------------------
_use_nosegment_indexes FALSE FALSE use nosegment indexes in explain plan
SQL> alter session set "_use_nosegment_indexes" = true;
Session altered.
SQL> exec dbms_stats.gather_table_stats(ownname=>'',tabname=>'ZZTEST', cascade=>TRUE);
Et voilà, magique! Le CBO utilise cette fois l'index et le coût passe de 103 pour un FTS à 1 pour l'index.
Cet index est un bon index et nous n'avons pas eu à attendre une heure pour qu'il soit testé.
SQL> explain plan for SELECT * FROM zztest WHERE id = 100;
Explained.
SQL> select * from dbms_xplan.display();
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------
Plan hash value: 3679716074
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 15 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| ZZTEST | 1 | 15 | 2 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | ZZTEST_ID | 1 | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"=100)
14 rows selected.
A noter qu'on ne peut pas altérer un index virtuel.
SQL> alter index ZZTEST_ID invisible;
alter index ZZTEST_ID invisible
*
ERROR at line 1:
ORA-08114: can not alter a fake index
ALTER INDEX n'est pas possible sur un index virtuel mais, lors du CREATE INDEX, je peux mettre ce que je veux, mais bon, l'intérêt est limité de mettre un index nosegment en invisible :-)
SQL> CREATE INDEX zztest_ID_invisible ON zztest(id) nosegment invisible;
Index created.
Nous avons démontré l'intérêt de la clause NOSEGMENT dans le cas de très grosses tables (ce que je n'ai pas pu tester ici, un index sur une table de 100 000 000 de rows nécessitant pas loin d'une heure pour être créé).
Bon, on crée maintenant réellement cet index :-)
Ah non, dernier piège, un "index" de ce nom existe déjà, il faut donc le dropper.
SQL> CREATE UNIQUE INDEX zztest_id ON zztest(id);
CREATE UNIQUE INDEX zztest_id ON zztest(id)
*
ERROR at line 1:
ORA-00955: name is already used by an existing object
SQL> drop index zztest_id;
Index dropped.
Ouf, cette fois ça passe!
SQL> CREATE UNIQUE INDEX zztest_id ON zztest(id);
Index created.
Même si le CREATE INDEX génère des stats, je préfère m'en assurer par moi même : c'est OK, pas la peine d'utiliser DBMS_STATS.
SQL> select to_char(last_analyzed,'DD/MM/YYYY HH24:MI:SS') from dba_ind_statistics where index_name = 'ZZTEST_ID'
TO_CHAR(LAST_ANALYZ
-------------------
20/12/2018 08:35:54
============================================================================================
Index invisible
============================================================================================
Oracle permet de rendre un index invisible. Quel intérêt? Celui opposé à NOSEGMENT : tester très rapidement si la suppression d'un index donne un meilleur plan d'exécution. Si nous pensons que l'index pénalise le SELECT mais que nous ne pouvons pas modifier l'ordre SQL via un hint pour imposer un Full Table Scan, nous avons au moins deux possibilités :
- dropper l'index : cela peut être long et, si le résultat n'est pas probant, il faudra le recréer... ENORME perte de temps!
- rendre l'index invisible car, par défaut, le CBO n'utilise pas les index invisibles
On droppe les index virtuels du test précédent et on en crée un classique.
SQL> DROP INDEX ZZTEST_ID;
Index dropped.
SQL> DROP INDEX zztest_ID_invisible;
Index dropped.
SQL> CREATE UNIQUE INDEX zztest_id ON zztest(id);
Index created.
Cette fois, l'index existe bien dans DBA_INDEXES.
SQL> select INDEX_TYPE, UNIQUENESS, STATUS, NUM_ROWS, VISIBILITY from DBA_INDEXES where index_name = 'ZZTEST_ID';
INDEX_TYPE UNIQUENES STATUS NUM_ROWS VISIBILIT
--------------------------- --------- -------- ---------
NORMAL UNIQUE VALID 100000 VISIBLE
Cet index est bien utilisé et le coût est de 15.
SQL> explain plan for SELECT * FROM zztest WHERE id = 100;
Explained.
SQL> select * from dbms_xplan.display();
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------
Plan hash value: 3679716074
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 15 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| ZZTEST | 1 | 15 | 2 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | ZZTEST_ID | 1 | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"=100)
14 rows selected.
Rendons cet index invisible.
SQL> ALTER INDEX zztest_id INVISIBLE;
Index altered.
SQL> select INDEX_TYPE, UNIQUENESS, STATUS, NUM_ROWS, VISIBILITY from DBA_INDEXES where index_name = 'ZZTEST_ID';
INDEX_TYPE UNIQUENES STATUS NUM_ROWS VISIBILIT
--------------------------- --------- -------- ----------
NORMAL UNIQUE VALID 100000 INVISIBLE
L'index n'est plus utilisé et le coût est de 103... il était donc pertinent!
SQL> explain plan for SELECT * FROM zztest WHERE id = 100;
Explained.
SQL> select * from dbms_xplan.display();
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------
Plan hash value: 3582063246
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 15 | 103 (1)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| ZZTEST | 1 | 15 | 103 (1)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ID"=100)
13 rows selected.
Un paramètre important pour notre test est "optimizer_use_invisible_indexes". Par défaut il vaut FALSE, mettons le à TRUE.
SQL> show parameter invi
NAME TYPE VALUE
------------------------------------ ----------- -------------
optimizer_use_invisible_indexes boolean FALSE
SQL> ALTER SESSION SET optimizer_use_invisible_indexes=TRUE;
Session altered.
SQL> explain plan for SELECT * FROM zztest WHERE id = 100;
Explained.
Bingo, l'index invisible est cette fois bien utilisé :-)
SQL> select * from dbms_xplan.display();
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------
Plan hash value: 3679716074
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 15 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| ZZTEST | 1 | 15 | 2 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | ZZTEST_ID | 1 | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"=100)
14 rows selected.
Bon, vous voilà avec deux outils de plus dans votre panoplie de DBA pour tester rapidement si un index est utile ou non au CBO :-)
/https%3A%2F%2Fassets.over-blog.com%2Ft%2Fcedistic%2Fcamera.png)
/https%3A%2F%2Fprofilepics.canalblog.com%2Fprofilepics%2F1%2F5%2F1555182.jpg)