Blog d'un DBA sur le SGBD Oracle et SQL

12 février 2019

Les tables IOT (Index Organized Table) : partie 2, index secondaire décalé avec l'index primaire

 

Introduction
Cet article présentait les concepts de base d'une table IOT : http://dbaoraclesql.canalblog.com/archives/2019/01/29/37058939.html.
Nous allons maintenant voir s'il est possible de prouver, comme le dit la doc Oracle, que lorsque les logicals rowid de l'index secondaire ne sont plus en phase avec les vrais rowids de la table IOT, alors Oracle fait une lecture directe via le rowid qui échoue avant de parcourir la table IOT. 


 
Points d'attention
Aucun.



Base de tests
Une base Oracle 12.



Exemples
============================================================================================
Environnement de tests
============================================================================================

Création de la table de test et d'un index secondaire sur la colonne FIRST_NAME.
     SQL> create table ZZ_IOT (id number primary key, first_name varchar2(50 char), last_name varchar2(50 char), created date) organization index;

     SQL> INSERT INTO zz_iot SELECT rownum, 'First_name' || to_char(rownum), 'Last_name' || to_char(rownum), sysdate FROM dual CONNECT BY LEVEL <=100000;

     SQL> commit;


     SQL> create index idx_zz_iot_first_name on zz_iot(first_name);
     SQL> exec dbms_stats.gather_table_stats('', 'ZZ_IOT', CASCADE=>TRUE);

Pour notre problématique, la colonne importante est PCT_DIRECT_ACCESS de USER_INDEXES. Elle n'a de sens que pour un index secondaire. Oracle dit que c'est : "For a secondary index on an index-organized table, the percentage of rows with VALID guess".
En français : c'est le pourcentage d'accès direct à la table IOT via le logical rowid d'un index secondaire.
En clair? Quand on veut des données qui sont absentes de l'index secondaire en utilisant dans la clause Where une colonne de l'index secondaire, Oracle va alors parcourir l'index secondaire, récupérer le logical rowid ET essayer d'accéder directement au bloc de la table IOT avec ce rowid. Si ce bloc est le bon (celui contenant les données recherchées), on s'arrête là sinon, à cause de réorganisations des blocs de la table IOT, Oracle doit calculer la PK à partir du logical rowid et parcourir l''index primaire (la table IOT) pour retrouver le bon bloc.

Actuellement cette colonne vaut 100 pour l'index secondaire, donc si on utilise celui-ci pour une recherche, Oracle parcourt cet index secondaire et avec le rowid il lit directement le bloc de la table.
     SQL> select INDEX_NAME, INDEX_TYPE, PCT_DIRECT_ACCESS from USER_INDEXES where table_name = 'ZZ_IOT' order by 1;
     INDEX_NAME               INDEX_TYPE     PCT_DIRECT_ACCESS
     ------------------------------ ---------------------------
     IDX_ZZ_IOT_FIRST_NAME    NORMAL              100
     SYS_IOT_TOP_80295        IOT - TOP             0

Maintenant, on force le ré-arrangement des blocs de données en supprimant 99 000 rows et, par le shrink, on invalide les rowid de l'index secondaire car les quelques données restantes changent de bloc.
     SQL> delete from zz_iot where mod(id, 100) <> 0;
     99000 rows deleted.
 
     SQL> commit;

     SQL> ALTER TABLE zz_iot SHRINK SPACE;


     SQL> exec dbms_stats.gather_table_stats('', 'ZZ_IOT', CASCADE=>TRUE);


On a bien maintenant 0 pour le champ PCT_DIRECT_ACCESS.   
     SQL> select INDEX_NAME, INDEX_TYPE, PCT_DIRECT_ACCESS from USER_INDEXES where table_name = 'ZZ_IOT' order by 1;
     INDEX_NAME                       INDEX_TYPE           PCT_DIRECT_ACCESS
     ------------------------------ ---------------------------
     IDX_ZZ_IOT_FIRST_NAME        NORMAL                     0
     SYS_IOT_TOP_80295            IOT - TOP                 0


============================================================================================
Identifier l'accès direct : plan d'exécution
============================================================================================
   
Que ce soit avec un PCT_DIRECT_ACCESS à 0 ou 100, on a le même plan d'exécution. L'accès direct avec logical rowid, qu'il soit OK ou KO, n'apparait pas, il faut donc chercher ailleurs, avec Autotrace ou une trace 10046.
     --------------------------------------------------------------
     | Id  | Operation      | Name          
     --------------------------------------------------------------
     |   0 | SELECT STATEMENT  |    
     |*  1 |  INDEX UNIQUE SCAN| SYS_IOT_TOP_80295      
     |*  2 |   INDEX RANGE SCAN| IDX_ZZ_IOT_FIRST_NAME
     --------------------------------------------------------------

============================================================================================
Identifier l'accès direct : Autotrace
============================================================================================
   
On vide le cache mémoire et on active l'autotrace.
     SQL> alter system flush buffer_cache;

     SQL> set autotrace on

PCT_DIRECT_ACCESS à 0
Première exécution.
     SQL> select * from zz_iot where FIRST_NAME = 'First_name200';   

Statistics
----------------------------------------------------------
      0  recursive calls
      0  db block gets
      6  consistent gets
      5  physical reads
      0  redo size
    783  bytes sent via SQL*Net to client
    607  bytes received via SQL*Net from client
      2  SQL*Net roundtrips to/from client
      0  sorts (memory)
      0  sorts (disk)
      1  rows processed
    
Si on ré-exécute l'ordre sans vider le cache, on n'a plus de physical read, seulement 6 consistent gets.
Statistics
----------------------------------------------------------
      0  recursive calls
      0  db block gets
      6  consistent gets
      0  physical reads
      0  redo size
    783  bytes sent via SQL*Net to client
    607  bytes received via SQL*Net from client
      2  SQL*Net roundtrips to/from client
      0  sorts (memory)
      0  sorts (disk)
      1  rows processed
     
PCT_DIRECT_ACCESS à 100
Pour passer d'un PCT_DIRECT_ACCESS de 0 à 100, on remet en phase les rowid de l'index secondaire avec ceux de l'index primaire, c'est à dire de la table. Pour cela on a le choix : REBUILD de l'index ou bien utiliser la commande UPDATE BLOCK REFERENCES.
     SQL> alter index IDX_ZZ_IOT_FIRST_NAME UPDATE BLOCK REFERENCES;
    
Euh, échec...
     SQL> select INDEX_NAME, INDEX_TYPE, PCT_DIRECT_ACCESS from USER_INDEXES where table_name = 'ZZ_IOT' order by 1;
     INDEX_NAME               INDEX_TYPE           PCT_DIRECT_ACCESS
     ------------------------------ --------------------------- ----
     IDX_ZZ_IOT_FIRST_NAME           NORMAL                      0
     SYS_IOT_TOP_80295           IOT - TOP                   0
     
Oups, j'avais oublié de recalculer les stats.
C'est bon, on a à nouveau 100.
     SQL> exec dbms_stats.gather_table_stats('', 'ZZ_IOT', CASCADE=>TRUE);
        
     SQL> select INDEX_NAME, INDEX_TYPE, PCT_DIRECT_ACCESS from USER_INDEXES where table_name = 'ZZ_IOT' order by 1;
     INDEX_NAME               INDEX_TYPE           PCT_DIRECT_ACCESS
     ------------------------------ --------------------------- -----------------
     IDX_ZZ_IOT_FIRST_NAME           NORMAL                     100
     SYS_IOT_TOP_80295           IOT - TOP                   0
     
On vide le cache mémoire.
     SQL> alter system flush buffer_cache;
    
     SQL> set autotrace on

     SQL> select * from zz_iot where FIRST_NAME = 'First_name200';
     ID FIRST_NAME            LAST_NAME            CREATED
     -------------------- ----------------------------------
     200 First_name200        Last_name200         09-FEB-19
    
     Statistics
     ----------------------------------------------------------
      0  recursive calls
      0  db block gets
      6  consistent gets
      5  physical reads
      0  redo size
    783  bytes sent via SQL*Net to client
    607  bytes received via SQL*Net from client
      2  SQL*Net roundtrips to/from client
      0  sorts (memory)
      0  sorts (disk)
      1  rows processed

Si on réexécute plusieurs fois, on a ça.
     Statistics
     ----------------------------------------------------------
      0  recursive calls
      0  db block gets
      6  consistent gets
      0  physical reads
      0  redo size
    783  bytes sent via SQL*Net to client
    607  bytes received via SQL*Net from client
      2  SQL*Net roundtrips to/from client
      0  sorts (memory)
      0  sorts (disk)
      1  rows processed

Aïe, échec à nouveau... Qu'on ait 100 ou 0 dans PCT_DIRECT_ACCESS, on a les mêmes nombre de consistent gets et physical reads.
Bon, avec Autotrace, le résultat n'est pas probant. Voyons voir avec une trace 10046 si on observe quelque chose de bizarre...


============================================================================================
Identifier l'accès direct : trace 10046
============================================================================================
   
Trace 10046 avec PCT_DIRECT_ACCESS à 100
     SQL> ALTER SESSION SET timed_statistics = TRUE;

     SQL> ALTER SESSION SET max_dump_file_size = unlimited;
     SQL> ALTER SESSION SET TRACEFILE_IDENTIFIER = 'zztest_IOT.trc';               
     SQL> ALTER SESSION SET events '10046 trace name context forever, level 4';

     SQL> select * from zz_iot where FIRST_NAME = 'First_name200';
     ID FIRST_NAME                          LAST_NAME                      CREATED
     ---------- -------------------------------------------------- ----------------
     200 First_name200                      Last_name200                     09-FEB-19

On la désactive et on identifie où le fichier a été généré.
     SQL> ALTER SESSION SET events '10046 trace name context off';

     SQL> SELECT value FROM   v$diag_info WHERE  name = 'Default Trace File';
     VALUE
     ---------------------------------------------------------------------------------
     /u01/app/oracle/diag/rdbms/orcl12c/orcl12c/trace/orcl12c_ora_5277_zztest_IOT.trc

Tkprof rend le fichier trace plus lisible.
     SQL> ! tkprof /u01/app/oracle/diag/rdbms/orcl12c/orcl12c/trace/orcl12c_ora_5277_zztest_IOT.trc /u01/app/oracle/diag/rdbms/orcl12c/orcl12c/trace/orcl12c_ora_5277_zztest_IOT.txt
     TKPROF: Release 12.2.0.1.0 - Development on Sat Feb 9 11:35:24 2019
     Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.

Contenu de la trace 10046 avec PCT_DIRECT_ACCESS à 100
     SQL> ! view /u01/app/oracle/diag/rdbms/orcl12c/orcl12c/trace/orcl12c_ora_5277_zztest_IOT.txt
     TKPROF: Release 12.2.0.1.0 - Development on Sat Feb 9 11:35:24 2019

     Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.

     Trace file: /u01/app/oracle/diag/rdbms/orcl12c/orcl12c/trace/orcl12c_ora_5277_zztest_IOT.trc
     Sort options: default

     ********************************************************************************
     count    = number of times OCI procedure was executed
     cpu      = cpu time in seconds executing
     elapsed  = elapsed time in seconds executing
     disk     = number of physical reads of buffers from disk
     query    = number of buffers gotten for consistent read
     current  = number of buffers gotten in current mode (usually for update)
     rows     = number of rows processed by the fetch or execute call
     ********************************************************************************

     SQL ID: b0un6gq6pbk1x Plan Hash: 4235454309

     select *
     from
      zz_iot where FIRST_NAME = 'First_name200'

     call     count       cpu    elapsed       disk      query    current        rows
     ------- ------  -------- ---------- ---------- ---------- ----------  ----------
     Parse        1      0.00       0.00          0          0          0           0
     Execute      1      0.00       0.00          0          0          0           0
     Fetch        2      0.00       0.00          0          5          0           1
     ------- ------  -------- ---------- ---------- ---------- ----------  ----------
     total        4      0.00       0.00          0          5          0           1

     Misses in library cache during parse: 1
     Optimizer mode: ALL_ROWS
     Parsing user id: 128
     Number of plan statistics captured: 1

     Rows (1st) Rows (avg) Rows (max)  Row Source Operation
     ---------- ---------- ----------  ---------------------------------------------------
              1          1          1  INDEX UNIQUE SCAN SYS_IOT_TOP_80295 (cr=5 pr=0 pw=0 time=28 us starts=1 cost=3 size=43 card=1)(object id 80296)
              1          1          1   INDEX RANGE SCAN IDX_ZZ_IOT_FIRST_NAME (cr=4 pr=0 pw=0 time=25 us starts=1 cost=3 size=0 card=1)(object id 80300)

Trace 10046 avec PCT_DIRECT_ACCESS à 0
Maintenant, on génère une trace avec un PCT_DIRECT_ACCESS à 0 et on va comparer les deux.
Le truc est qu'on va faire un MOVE en transférant la table d'un tablespace dans un autre pour invalider les rowid de l'index secondaire. En effet, dans le rowid il y a l'id du fichier et là, en changeant de tbs, on change forcément de fichier.
     SQL> create tablespace tbs_iot datafile '/u01/app/oracle/oradata/orcl12c/orcl/iot.dbf' size 10m EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO;

     SQL> alter user HR quota unlimited on TBS_IOT;


     SQL> alter table zz_iot move tablespace tbs_iot;

     SQL> exec dbms_stats.gather_table_stats('', 'ZZ_IOT', CASCADE=>TRUE);


     SQL> select INDEX_NAME, INDEX_TYPE, PCT_DIRECT_ACCESS from USER_INDEXES where table_name = 'ZZ_IOT' order by 1;
     INDEX_NAME                    INDEX_TYPE             PCT_DIRECT_ACCESS
     -----------------------------------------------------------------
     IDX_ZZ_IOT_FIRST_NAME        NORMAL                      0
     SYS_IOT_TOP_80295            IOT - TOP                 0

Génération de la trace.
     SQL> ALTER SESSION SET timed_statistics = TRUE;
     SQL> ALTER SESSION SET max_dump_file_size = unlimited;
     SQL> ALTER SESSION SET TRACEFILE_IDENTIFIER = 'zztest_IOT_1.trc';               
     SQL> ALTER SESSION SET events '10046 trace name context forever, level 4';

     SQL> select * from zz_iot where FIRST_NAME = 'First_name200';
     ID FIRST_NAME                          LAST_NAME                      CREATED
     ---------- -------------------------------------------------- -----------------
     200 First_name200                      Last_name200                     09-FEB-19

     SQL> ALTER SESSION SET events '10046 trace name context off';

     SQL> ! tkprof /u01/app/oracle/diag/rdbms/orcl12c/orcl12c/trace/orcl12c_ora_5277_zztest_IOT_0.trc /u01/app/oracle/diag/rdbms/orcl12c/orcl12c/trace/orcl12c_ora_5277_zztest_IOT_0.txt

Contenu de la trace.
     select *
     from
      zz_iot where FIRST_NAME = 'First_name200'

     call     count       cpu    elapsed       disk      query    current        rows
     ------- ------  -------- ---------- ---------- ---------- ----------  ----------
     Parse        1      0.00       0.00          0          2          0           0
     Execute      1      0.00       0.00          0          0          0           0
     Fetch        2      0.00       0.00          4          6          0           1
     ------- ------  -------- ---------- ---------- ---------- ----------  ----------
     total        4      0.00       0.00          4          8          0           1

     Misses in library cache during parse: 1
     Optimizer mode: ALL_ROWS
     Parsing user id: 128
     Number of plan statistics captured: 1

     Rows (1st) Rows (avg) Rows (max)  Row Source Operation
     ---------- ---------- ----------  ---------------------------------------------------
         1          1          1  INDEX UNIQUE SCAN SYS_IOT_TOP_80295 (cr=6 pr=4 pw=0 time=63 us starts=1 cost=4 size=43 card=1)(object id 80296)
         1          1          1   INDEX RANGE SCAN IDX_ZZ_IOT_FIRST_NAME (cr=4 pr=0 pw=0 time=15 us starts=1 cost=3 size=0 card=1)(object id 80300)

Si on ré-exécute l'ordre : le nombre de cr (consistent gets) ne change pas et le nombre de pr (physical reads) tombe à 0.
     SQL> ! tkprof /u01/app/oracle/diag/rdbms/orcl12c/orcl12c/trace/orcl12c_ora_5277_zztest_IOT_1.trc /u01/app/oracle/diag/rdbms/orcl12c/orcl12c/trace/orcl12c_ora_5277_zztest_IOT_1.txt
     SQL> ! view /u01/app/oracle/diag/rdbms/orcl12c/orcl12c/trace/orcl12c_ora_5277_zztest_IOT_1.txt
     SQL ID: b0un6gq6pbk1x Plan Hash: 4235454309
     select *
     from
      zz_iot where FIRST_NAME = 'First_name200'

     call     count       cpu    elapsed       disk      query    current        rows
     ------- ------  -------- ---------- ---------- ---------- ----------  ----------
     Parse        1      0.00       0.00          0          0          0           0
     Execute      1      0.00       0.00          0          0          0           0
     Fetch        2      0.00       0.00          0          6          0           1
     ------- ------  -------- ---------- ---------- ---------- ----------  ----------
     total        4      0.00       0.00          0          6          0           1

     Misses in library cache during parse: 1
     Optimizer mode: ALL_ROWS
     Parsing user id: 128
     Number of plan statistics captured: 1

     Rows (1st) Rows (avg) Rows (max)  Row Source Operation
     ---------- ---------- ----------  ---------------------------------------------------
         1          1          1  INDEX UNIQUE SCAN SYS_IOT_TOP_80295 (cr=6 pr=0 pw=0 time=37 us starts=1 cost=4 size=43 card=1)(object id 80296)
         1          1          1   INDEX RANGE SCAN IDX_ZZ_IOT_FIRST_NAME (cr=4 pr=0 pw=0 time=23 us starts=1 cost=3 size=0 card=1)(object id 80300)


Maintenant, si on compare les traces, que voit-on?
Quand le champ PCT_DIRECT_ACCESS vaut 100, on a pour l'opération INDEX UNIQUE SCAN SYS_IOT_TOP_80295 le chiffre de cr=5 (Consistent Reads), quand il vaut 0, on a cr=6. A noter que pour Autotrace, le libellé est "Consistent Gets" : les deux stats sont équivalentes? Autre chose, avec la trace 10046 on a le détail des blocs lus opération par opération alors que pour l'autotrace, c'est pour l'ordre SQL en entier.

Est-ce suffisant pour dire qu'on a la preuve qu'Oracle fait une lecture directe via le logical rowid et, comme il échoue, il parcourt l'index primaire?
C'est compliqué à dire car la doc sur les traces 10046 est peu abondante et interpréter les résultats est difficile.

Ce que je comprends est que pour un PCT_DIRECT_ACCESS à 100 : 
- Opération exécutée en second : INDEX UNIQUE SCAN SYS_IOT_TOP_80295 cr=5 : accès direct au bloc de la table car PCT_DIRECT_ACCESS à 100; je m'attendais à 1 cr, pas à 5. Pourquoi cr = 5? Ca correspond à 5 lecture de blocs mémoire pour cette table? Mais pourquoi pas 1 seulement? Ou bien c'est 5 en tout : les 4 cr de l'opération sur l'index secondaire + la lecture du bloc de la table justement via un accès direct réussi?

- Opération exécutée en premier : INDEX RANGE SCAN IDX_ZZ_IOT_FIRST_NAME cr=4 : 4 consistent gets pour parcourir l'index secondaire et récupérer le logical rowid.


Pour un PCT_DIRECT_ACCESS à 0 :
- Opération exécutée en second : INDEX UNIQUE SCAN SYS_IOT_TOP_80295 cr=6 : accès direct KO car PCT_DIRECT_ACCESS à 0 et donc Oracle doit parcourir la table IOT comme on parcourt un index; On a bien 1 cr de plus que lorsque PCT_DIRECT_ACCESS vaut 100, ce qui serait bien la preuve que l'accès direct a échoué et qu'Oracle a ensuite dû parcourir la table IOT.

- Opération exécutée en premier : INDEX RANGE SCAN IDX_ZZ_IOT_FIRST_NAME cr=4 : 4 consistent gets pour parcourir l'index secondaire et récupérer le logical rowid


Pour info, voici le niveau des index utilisés; dans USER_INDEXES, la colonne Blevel signifie : "depth of the index from its root block to its leaf blocks".
     SQL> select index_name, BLEVEL, NUM_ROWS from user_indexes where table_name = 'ZZ_IOT' order by 1;
     INDEX_NAME                       BLEVEL    NUM_ROWS
     ------------------------- ---------- ----------
     IDX_ZZ_IOT_FIRST_NAME            2        1000
     SYS_IOT_TOP_80295                1        1000


============================================================================================
Conclusion
============================================================================================

Bon, ce fut compliqué et passionnant, mais on a bien montré que, lorsque les logical rowids d'un index secondaire sur une table IOT ne sont plus en phase avec l'index primaire, Oracle échoue dans son accès direct à la table IOT, ce qui se voit dans la trace 10046 avec la stat cr (Consistent Read).


Posté par David DBA à 13:14 - - Permalien [#]
Tags : ,


29 janvier 2019

Les tables IOT (Index Organized Table) : partie 1, généralités

 

Introduction
Une table IOT (Index Organized Table) est une table d'un genre spécial. A la différence d'une table classique, dite aussi HOT (Heap Organized Table), cette table possède de multiples particularités qu'il est bon de connaître pour ne pas être vite débordé par leur complexité.

En premier, une table classique, avec un index pour la PK, est en réalité un ensemble de deux objets : la table ET l'index, comme un livre avec son index à la fin. En revanche une table IOT ne comprend en réalité qu'un index et pas de table; c'est, dans la vraie vie, un annuaire téléphonique ou un dictionnaire : toutes les infos sont présentes dans l'index.

En deuxième, une table IOT a des logical rowids et pas, comme les tables HOT, des physical rowids (nous verrons cela plus en détail plus bas). A quoi cela est-il dû? Le H d'une table dite HOT signifie Heap, c'est à dire Tas en français. Les enregistrements dans une table classique sont mis n'importe où, là où il y a de la place, sans ordre; si une table HOT subit de nombreux Deletes, les prochains Inserts pourront se mettre là où il y a de la place; dans le premier bloc par exemple. En revanche, une table IOT est un index, donc les données sont triées! Cela veut dire que ces données changent régulièrement de bloc (sauf si on a construit la table IOT sur une séquence). La conséquence de ceci est qu'il n'est pas possible d'identifier un enregistrement d'une table IOT par un physical rowid puisqu'il est valide lors de l'Insert mais peut-être plus valide après plusieurs jours et splits de blocs. Oracle s'en sort en gérant l'emplacement de ces enregistrements via un logical rowid.

Dernier point, plus vicieux : une table IOT est constituée d'un seul segment, de type index, alors qu'une table classique, avec PK, est constituée de deux segments, l'un pour la table, l'un pour l'index. Donc une recherche dans DBA_SEGMENTS sur le nom de la table ne renverra rien...


Quel est l'intérêt de ce type de table? Si vous ne faites des recherches QUE sur la PK, alors créez une table IOT plutôt qu'une table HOT; c'est plus performant car il y a moins de blocs à parcourir pour récupérer les données.

Pour les DBA avec une certaine expérience, vous aurez compris ce qu'est la table IOT : un index composite poussé à l'extrème car composé de TOUTES les colonnes de la table. Et, par rapport à une table qui aurait un tel index composite, vous n'avez plus de données dupliquées :-)


 
Points d'attention
Aucun.



Base de tests
Une base Oracle 12.



Exemples
============================================================================================
Création de la table IOT
============================================================================================

Pour créer une table IOT, il faut impérativement déclarer une Primary Key; normal puisque cette table est en réalité un index; à noter que ce n'est pas le cas pour une table HOT. Le mot clé est "organization index" pour créer une table IOT.
     SQL> create table ZZIOT_KO (id number, name varchar2(50 char)) organization index;
     create table ZZIOT_KO (id number, name varchar2(50 char)) organization index
                                                                            *
     ERROR at line 1:
     ORA-25175: no PRIMARY KEY constraint found
     
     SQL> create table ZZ_IOT (id number primary key, first_name varchar2(50 char), last_name varchar2(50 char), created date) organization index;
     Table created.
     
     SQL> select iot_type from dba_tables where table_name = 'ZZ_IOT';
     IOT_TYPE
     ------------
     IOT

On remplit celle-ci avec 1000 enregistrements.    
     SQL> INSERT INTO zz_iot SELECT rownum, 'First_name' || to_char(rownum), 'Last_name' || to_char(rownum), sysdate FROM dual CONNECT BY LEVEL <=1000;
     1000 rows created.
     
     SQL> commit;
     Commit complete.

     SQL> select * from zz_iot where id = 200;
     ID FIRST_NAME                          LAST_NAME                  CREATED
     ---------- -------------------------------------------------- ---------------
     200 First_name200                      Last_name200               27-JAN-19

     SQL> exec dbms_stats.gather_table_stats('', 'ZZ_IOT');
     PL/SQL procedure successfully completed.
    
Le nom de l'index est celui de la pk; il est dans cet exemple généré par Oracle.
     SQL> select index_name from dba_indexes where table_name='ZZ_IOT';
     INDEX_NAME
     ---------------------
     SYS_IOT_TOP_80252
     
Attention, comme je le disais en introduction, contrairement à une table classique, créer une table IOT consiste à créer deux objets, une table et un index, MAIS à créer un seul segment, de type index. Attention aussi, ce segment de type index est plus proche d'un index composé que d'un index classique car si cet index porte sur la colonne PK, il renferme également les données de toutes les autres colonnes de la table.

Deux objets, un index, une table MAIS un seul segment.
     SQL> select object_name, object_type from user_objects where object_name in ('ZZ_IOT', 'SYS_IOT_TOP_80252') order by object_name;
     OBJECT_NAME                    OBJECT_TYPE
     -----------------------------------------
     SYS_IOT_TOP_80252            INDEX
     ZZ_IOT                        TABLE
     
     SQL> select table_name from dba_tables where table_name in ('ZZ_IOT', 'SYS_IOT_TOP_80252');
     TABLE_NAME
     ----------
     ZZ_IOT
    
     SQL> select index_name, index_type from user_indexes where index_name = 'SYS_IOT_TOP_80252';
     INDEX_NAME           INDEX_TYPE
     -------------------- ---------------------------
     SYS_IOT_TOP_80252    IOT - TOP

     SQL> select segment_name from dba_segments where segment_name in ('ZZ_IOT', 'SYS_IOT_TOP_80252');
     SEGMENT_NAME
     ------------------
     SYS_IOT_TOP_80252
     
Petite surprise supplémentaire, une table IOT a ce qu'on appelle des logical rowids et pas des physycal rowid comme les tables HOT. Il suffit des afficher pour voir de suite la différence. A noter qu'avec un Logical rowid, Oracle peut recalculer la PK à partir de ce rowid, cela sera utile pour les index secondaires.

Logical rowids
     SQL> select rowid from zz_iot where rownum < 11 order by rowid;
     ROWID
     --------------------
     *BAMAJEQDwgJS/g
     *BAMAJEQDwgJT/g
     *BAMAJEQDwgJU/g
     *BAMAJEQDwgJV/g
     *BAMAJEQDwgJW/g
     *BAMAJEQDwgJX/g
     *BAMAJEQDwgJY/g
     *BAMAJEQDwgJZ/g
     *BAMAJEQDwgJa/g
     *BAMAJEQDwgJb/g
     
     10 rows selected.
    
Physical rowids
     SQL> select rowid from employees where rownum < 11 order by rowid;
     ROWID
     ------------------
     AAATTBAAMAAACD9AAD
     AAATTBAAMAAACD9AAP
     AAATTBAAMAAACD9AAV
     AAATTBAAMAAACD9AAv
     AAATTBAAMAAACD9AA6
     AAATTBAAMAAACD9ABD
     AAATTBAAMAAACD9ABL
     AAATTBAAMAAACD9ABV
     AAATTBAAMAAACD9ABX
     AAATTBAAMAAACD9ABg
     
     10 rows selected.
     

============================================================================================
La table IOT est un index et seulement un index
============================================================================================
    
La table IOT est bien considérée par Oracle comme un index, sans table associée, c'est ce que nous avons vu dans DBA_SEGMENTS.

Regardons maintenant le plan d'exécution : on accède à toutes les données de la table (SELECT *) via un INDEX UNIQUE SCAN, sans avoir ensuite un TABLE ACCESS BY INDEX ROWID. Pourtant, la PK ne porte que sur la colonne ID. Pour une table HOT on aurait eu le même plan d'exécution si le Select avait porté uniquement sur la colonne ID.
     SQL> select * from zz_iot where id = 200;
     ID FIRST_NAME                          LAST_NAME                  CREATED
     ---------- -------------------------------------------------- -----------------
     200 First_name200                      Last_name200               27-JAN-19
    
     SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL ,NULL, 'TYPICAL'));
     PLAN_TABLE_OUTPUT
     --------------------------------------------------------------------------
     SQL_ID    4u2tuk6g76g5n, child number 0
     -------------------------------------
     select * from zz_iot where id = 200
     
     Plan hash value: 631232691
     
     ---------------------------------------------------------------------------------------
     | Id  | Operation      | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
     ---------------------------------------------------------------------------------------
     |   0 | SELECT STATEMENT  |              |       |       |     1 (100)|          |
     |*  1 |  INDEX UNIQUE SCAN| SYS_IOT_TOP_80252 |     1 |    39 |     1    (0)| 00:00:01 |
     ---------------------------------------------------------------------------------------
     
     Predicate Information (identified by operation id):
     ---------------------------------------------------
        1 - access("ID"=200)
     
     18 rows selected.
     

============================================================================================
La table IOT et les index secondaires : Oracle peut indexer un index
============================================================================================
    
     
Si, au bout d'un certain temps, on se rends compte que la recherche sur la PK n'est plus suffisante, il est possible de créer un index dit index secondaire sur une ou des colonnes non participantes à la PK : c'est, comme le dit Oracle dans sa doc, un index sur index puisque la table IOT est en réalité un index. Oracle permet donc d'indexer un index! Cet index sera de type NORMAL et non pas IOT - TOP comme l'index primaire.
     SQL> create index idx_zz_iot_first_name on zz_iot(first_name);
     Index created.
     
     SQL> select index_name, INDEX_TYPE, TABLE_TYPE, UNIQUENESS from dba_indexes where table_name = 'ZZ_IOT' order by 1;
     INDEX_NAME                 INDEX_TYPE          TABLE_TYPE  UNIQUENES
     ------------------------- --------------------------- ----------- ---------
     IDX_ZZ_IOT_FIRST_NAME      NORMAL              TABLE       NONUNIQUE
     SYS_IOT_TOP_80252          IOT - TOP           TABLE       UNIQUE
    
Les stats sur les index sont OK.
     SQL> exec dbms_stats.gather_table_stats('', 'ZZ_IOT', cascade=>TRUE);
     PL/SQL procedure successfully completed.

     SQL> select num_rows, index_name from user_indexes where table_name = 'ZZ_IOT';

       NUM_ROWS INDEX_NAME
     ---------- -------------------------
           1000 SYS_IOT_TOP_80252
           1000 IDX_ZZ_IOT_FIRST_NAME
     
Si on fait une recherche sur la colonne de l'index secondaire, Oracle parcourt cet index secondaire puis parcourt aussi le premier pour récupérer les autres données. Mais n'est-ce pas bizarre? Pourquoi pas d'accès direct à la table IOT via le logical rowid? On a bien un INDEX UNIQUE SCAN mais pas un INDEX ACCESS BY ROWID (ça n'existe pas) : donc Oracle, même s'il récupère un seul enregistrement, parcourt l'index primaire; ça me surprends.
     SQL> select * from zz_iot where first_name = 'First_name200';
     ID FIRST_NAME                          LAST_NAME                      CREATED
     ---------- -------------------------------------------------- -------------------
     200 First_name200                      Last_name200                     27-JAN-19
     
     SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL ,NULL, 'TYPICAL'));
     PLAN_TABLE_OUTPUT
     ------------------------------------------------------------------------------------------
     SQL_ID    4b5xhv7xbfzm0, child number 0
     -------------------------------------
     select * from zz_iot where first_name = 'First_name200'
     
     Plan hash value: 2333378696
     
     -------------------------------------------------------------------------------------------
     | Id  | Operation      | Name          | Rows  | Bytes | Cost (%CPU)| Time      |
     -------------------------------------------------------------------------------------------
     |   0 | SELECT STATEMENT  |              |      |      |    1 (100)|      |
     |*  1 |  INDEX UNIQUE SCAN| SYS_IOT_TOP_80252      |    1 |    39 |    1   (0)| 00:00:01 |
     |*  2 |   INDEX RANGE SCAN| IDX_ZZ_IOT_FIRST_NAME |    1 |      |    1   (0)| 00:00:01 |
     -------------------------------------------------------------------------------------------
     
     Predicate Information (identified by operation id):
     ---------------------------------------------------
        1 - access("FIRST_NAME"='First_name200')
        2 - access("FIRST_NAME"='First_name200')
     
     20 rows selected.


La colonne PCT_DIRECT_ACCESS 
Dans les vues xxx_INDEXES, la colonne PCT_DIRECT_ACCESS signifie "Quel est le pourcentage d'accès direct à l'index primaire via le logical rowid récupéré dans l'index secondaire?". Attention, cette colonne est une statistique, pas une probabilité :-)

La colonne PCT_DIRECT_ACCESS vaut 0 pour SYS_IOT_TOP_80252 parce qu'on est sur l'index primaire, et que cette colonne n'a de sens que pour un index secondaire... Mais on a 100 pour IDX_ZZ_IOT_FIRST_NAME donc pourquoi accéder à l'index primaire via un INDEX UNIQUE SCAN et pas un accès direct via rowid? Tout simplement parce que Oracle n'a pas de mode d'accès direct aux index via un rowid?
     SQL> select INDEX_NAME, INDEX_TYPE, PCT_DIRECT_ACCESS from USER_INDEXES where table_name = 'ZZ_IOT' order by 1;
     INDEX_NAME                       INDEX_TYPE           PCT_DIRECT_ACCESS
     ------------------------------ ---------------------------------
     IDX_ZZ_IOT_FIRST_NAME            NORMAL                100
     SYS_IOT_TOP_80252                IOT - TOP              0
     
Quel est le plan d'exécution si j'utilise un logical rowid dans le WHERE?
Oh, intéressant, même si on a bien un prédicat d'accès via le ROWID, Oracle met dans le plan d'exécution une opération de type INDEX UNIQUE SCAN. Cela veut-il dire qu'Oracle doive toujours parcourir l'index lié à la PK et que son logical rowid ne permette pas un accès direct au bloc de l'index de la pk?
     SQL> select rowid from zz_iot where id = 200;

     ROWID
     -----------------------------------------
     *BAMAJEQCwgP+
     
     SQL> select * from zz_iot where rowid = '*BAMAJEQCwgP+';
     ID FIRST_NAME                          LAST_NAME                CREATED
     ---------- -------------------------------------------------- -----------------
     200 First_name200                      Last_name200             27-JAN-19
    
     PLAN_TABLE_OUTPUT
     --------------------------------------------------------------------------------------
     SQL_ID    4matk2kgx2yrk, child number 0
     -------------------------------------
     select * from zz_iot where rowid = '*BAMAJEQCwgP+'
     
     Plan hash value: 631232691
     
     ---------------------------------------------------------------------------------------
     | Id  | Operation      | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
     ---------------------------------------------------------------------------------------
     |   0 | SELECT STATEMENT  |              |       |       |     1 (100)|          |
     |*  1 |  INDEX UNIQUE SCAN| SYS_IOT_TOP_80252 |     1 |    39 |     3    (0)| 00:00:01 |
     ---------------------------------------------------------------------------------------
     
     Predicate Information (identified by operation id):
     ---------------------------------------------------
        1 - access(ROWID=:B1)
     
     18 rows selected.
   
Essayons de mettre le champ PCT_DIRECT_ACCESS à 0 pour voir si cela change le plan d'exécution. Pour se faire on va supprimer 90% de la table puis faire un shrink pour changer les emplacements des enregistrements restants et forcer le recalcul des rowids dans la table; de la sorte on crée un décalage avec ceux de l'index et on invalide les logical rowids de l'index secondaire.
     SQL> delete from zz_iot where mod(id, 10) <> 0;
     900 rows deleted.
     
     SQL> commit;
     Commit complete.
     
Tiens, ENABLE ROW MOVEMENT n'est pas obligatoire pour faire un SHRINK sur les tables IOT non partitionnées.
     SQL> alter table zz_iot ENABLE ROW MOVEMENT;
     alter table zz_iot ENABLE ROW MOVEMENT
     *
     ERROR at line 1:
     ORA-14066: illegal option for a non-partitioned index-organized table
     
     SQL> ALTER TABLE zz_iot SHRINK SPACE;
     Table altered.
     
     SQL> exec dbms_stats.gather_table_stats('', 'ZZ_IOT');
     PL/SQL procedure successfully completed.
     
     SQL> exec dbms_stats.gather_index_stats('', 'IDX_ZZ_IOT_FIRST_NAME');
     PL/SQL procedure successfully completed.
     
Héhé, on est passé de 100 à 17! Mais le plan d'exécution lui ne change pas... bizarre, bizarre...
     SQL> select INDEX_NAME, INDEX_TYPE, PCT_DIRECT_ACCESS from USER_INDEXES where table_name = 'ZZ_IOT' order by 1;
     INDEX_NAME                 INDEX_TYPE     PCT_DIRECT_ACCESS
     -------------------- --------------------------------------
     IDX_ZZ_IOT_FIRST_NAME      NORMAL             17
     SYS_IOT_TOP_80252          IOT - TOP          0
     
     SQL> set autotrace on
     SQL> select * from zz_iot where first_name = 'First_name10';
         ID FIRST_NAME                          LAST_NAME                      CREATED
     ---------- -------------------------------------------------- -----------------------
         10 First_name10                       Last_name10                     27-JAN-19
    
     Execution Plan
     ----------------------------------------------------------
     Plan hash value: 2333378696
     
     -------------------------------------------------------------------------------------------
     | Id  | Operation      | Name          | Rows  | Bytes | Cost (%CPU)| Time      |
     -------------------------------------------------------------------------------------------
     |   0 | SELECT STATEMENT  |              |    1 |    39 |    2   (0)| 00:00:01 |
     |*  1 |  INDEX UNIQUE SCAN| SYS_IOT_TOP_80252      |    1 |    39 |    2   (0)| 00:00:01 |
     |*  2 |   INDEX RANGE SCAN| IDX_ZZ_IOT_FIRST_NAME |    1 |      |    1   (0)| 00:00:01 |
     -------------------------------------------------------------------------------------------
     
     Predicate Information (identified by operation id):
     ---------------------------------------------------
        1 - access("FIRST_NAME"='First_name10')
        2 - access("FIRST_NAME"='First_name10')
     
     
     Statistics
     ----------------------------------------------------------
           1  recursive calls
           0  db block gets
           5  consistent gets
           0  physical reads
           0  redo size
         781  bytes sent via SQL*Net to client
         607  bytes received via SQL*Net from client
           2  SQL*Net roundtrips to/from client
           0  sorts (memory)
           0  sorts (disk)
           1  rows processed



============================================================================================
Conclusion
============================================================================================
     
On en reste là pour le moment, je vous laisse cogiter sur le plan d'exécution quand on utilise un index secondaire d'une table IOT.

La suite dans un prochain article.



Posté par David DBA à 13:00 - - Permalien [#]
Tags : ,