Overblog
Suivre ce blog
Editer l'article Administration Créer mon blog
30 juillet 2013 2 30 /07 /juillet /2013 15:44

L'autre jour, allez savoir pourquoi, je jouais avec des Index Organized Tables (IOT).

Ces créatures ont cela de spécial qu'il n'y a pas de table à proprement parler, ou plutôt que l'index clef primaire contient toutes les données.

 
Cette structure est particulièrement sympa quand la table n'est pas très large (c'est à dire des lignes pas très grosses), et que l'accès par la clef primaire est très courant. 
En effet, le clustering factor de la PK est par construction parfait pour une IOT.

 
 
Par contre, on a des petits effets rigolos quant on parle d'adressage des lignes : Quand vous insérez une ligne dans une table, on s'en tape un peu de l'endroit physique : soit tout à la fin (aux environs du high water mark), soit quelque part où il y a de la place à réutiliser.

 
 
Dans un index en revanche, les entrées sont ordonnées selon la clef de l'index, puisque l'objectif est quand même à la base d'accélérer la recherche d'une (plage de) valeur donnée.... la suite tout au long de la démo.


1) Le logical rowid
 
Allez, une petite IOT et son index secondaire. 

J'ajoute une colonne m pour pouvoir l'indexer, et une une autre m pour avoir une colonne totalement non indexée.

 

CREATE TABLE testiot (l primary key, m, n) 
 ORGANIZATION INDEX AS
 SELECT 1, 1, 1 
 FROM dual 
 UNION ALL 
 SELECT 10000, 10000, 10000
 FROM dual;

Table created.

CREATE INDEX testiotii ON TESTIOT(m);
Index created.

exec dbms_stats.gather_table_stats('PACMAN', 'TESTIOT', cascade => true)

 PL/SQL procedure successfully completed.

Pour une IOT, Oracle maintient un logical rowid. Ce logical rowid est stocké dans les indexes secondaires, et permet de faire un accès à l'IOT, comme dans un ACCESS BY INDEX ROWID classique.

Vous remarquerez au passage qu'il est bien plus laid de frangliser avec "logical rowid" que de traduire rowid logique. C'ezt mon choix.
SELECT l, m, n, rowid 
FROM testiot;

         L          M          N ROWID
---------- ---------- ---------- -----------------------------------------
         1          1          1 *BAEACwQCwQL+
     10000      10000      10000 *BAEACwQCwwL+


2) Pas fiable à requêter !

Mais si on ajoute une foultitude de clefs entre les deux premières valeurs, que va-t-il arriver à ce rowid ?  

INSERT INTO testiot
SELECT level + 1, level + 1, level + 1
FROM dual
CONNECT BY level <= 9900;

commit;

Commit complete.

set autot on explain

SELECT l, m, n, rowid
FROM testiot
WHERE m in (1, 10000);

        L          M          N ROWID
---------- ---------- ---------- -----------------------------------------
         1          1          1 *BAEACwgCwQL+
     10000      10000      10000 *BAEAWT8CwwL+

-----------------------------------------------------------------------------
| Id  | Operation        | Name                | Rows  | Bytes | Cost (%CPU)|
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT |                     |     2 |    18 |     1   (0)|
|*  1 |  INDEX FULL SCAN | SYS_IOT_TOP_1443683 |     2 |    18 |     1   (0)|
-----------------------------------------------------------------------------

La deuxième ligne a été déplacée, comme attendu.

Maintenant, premier fun fact : 

SELECT
/*+index(testiot testiotii)*/ l, m, rowid
FROM testiot
WHERE m in (1, 10000);

         L          M ROWID
---------- ---------- -----------------------------------------
         1          1 *BAEACwQCwQL+
     10000      10000 *BAEACwQCwwL+

-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |     2 |    18 |     1   (0)| 00:00:01 |
|   1 |  INLIST ITERATOR  |           |       |       |            |          |
|*  2 |   INDEX RANGE SCAN| TESTIOTII |     2 |    18 |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------

La pseudo colonne rowid ne renvoit pas la même chose en fonction du chemin d'accès !
En fait, comme le logical rowid est susceptible de varier souvent, Oracle ne remet pas à jour tous les indexes à chaque changement d'adressage dans l'IOT.

On se retrouve donc avec un rowid pas frais stocké avec la clef de l'index secondaire TESTIOTII.
Et c'est pour cela qu'en plus du logical rowid, la clef primaire de l'IOT est également stockée dans l'index secondaire !

On le voit d'ailleurs très bien dans le plan de cette dernière requête : on SELECT la colonne l, qui n'est pas dans l'index TESTIOTII, mais pourtant il n'y a pas besoin d'accès à l'IOT pour la récupérer.

3) Et un de plus.

Comme je n'a plus de colonne à indexer, je fais un FBI bidon, puis rajoute quelques lignes : 

CREATE INDEX
testiotii2 ON TESTIOT(m * 1);

Index created.

INSERT INTO
testiot
SELECT level + 1.5, level + 1.5, level + 1.5
FROM dual
CONNECT BY level <= 9900;

9900 rows created.

commit;

Commit complete.

Et là, ça devient n'importe quoi, on a 3 trois rowid différents pour la même ligne : 

SELECT
/*+index(testiot testiotii)*/ 'ii' accesstype, rowid, 10000
FROM testiot
WHERE m = 10000
UNION ALL
SELECT /*+index(testiot testiotii2)*/ 'ii2', rowid, 10000
FROM testiot
WHERE m * 1 = 10000
UNION ALL
SELECT /*+index(testiot testiotii2)*/ 'tab', rowid, n
FROM testiot
WHERE m = 10000; 

ACC ROWID                                          10000
--- ----------------------------------------- ----------
ii  *BAEACwQCwwL+                                  10000
ii2 *BAEAWT8CwwL+                                  10000
tab *BAEAGj0CwwL+                                  10000

-------------------------------------------------------------------------------
| Id  | Operation          | Name                | Rows  | Bytes | Cost (%CPU)|
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                     |     3 |    27 |    31  (97)|
|   1 |  UNION-ALL         |                     |       |       |            |
|*  2 |   INDEX RANGE SCAN | TESTIOTII           |     1 |     9 |     1   (0)|
|*  3 |   INDEX RANGE SCAN | TESTIOTII2          |     1 |     9 |     1   (0)|
|*  4 |   INDEX UNIQUE SCAN| SYS_IOT_TOP_1443683 |     1 |     9 |    29   (0)|
|   5 |    INDEX FULL SCAN | TESTIOTII2          |  9902 |       |    29   (0)|
-------------------------------------------------------------------------------

Le problème, quand on a des estimations fausses du rowid, c'est qu'on commence par lire un bloc pour rien, puis on déplie l'index PK pour récupérer la ligne finale... plein de boulot dans le vent, en somme.
Les accès directs réussis par rowid sont d'ailleurs recencés dans les stats de l'index : 

exec dbms_stats.gather_table_stats('PACMAN', 'TESTIOT', cascade => true)

PL/SQL procedure successfully completed.

set autot off

SELECT
index_name, blevel, num_rows, leaf_blocks, pct_direct_access
FROM dba_indexes
WHERE table_name = 'TESTIOT';

INDEX_NAME                         BLEVEL   NUM_ROWS LEAF_BLOCKS PCT_DIRECT_ACCESS
------------------------------ ---------- ---------- ----------- -----------------
SYS_IOT_TOP_1443683                     1      19802         209                 0
TESTIOTII                               1      19802         234                50
TESTIOTII2                              1      19802         110                53

4) Re-link-er correctement

Du coup, quand ça devient trop le foutoir, il faut y remettre de l'ordre : 

ALTER INDEX
testiotii UPDATE BLOCK REFERENCES;

Index altered.

exec dbms_stats.gather_table_stats('PACMAN', 'TESTIOT', cascade => true)

PL/SQL procedure successfully completed.

SELECT
index_name, blevel, num_rows, leaf_blocks, pct_direct_access
FROM dba_indexes
WHERE table_name = 'TESTIOT';

INDEX_NAME                         BLEVEL   NUM_ROWS LEAF_BLOCKS PCT_DIRECT_ACCESS
------------------------------ ---------- ---------- ----------- -----------------
SYS_IOT_TOP_1443683                     1      19802         209                 0
TESTIOTII                               1      19802         234               100
TESTIOTII2                              1      19802         110                50

5) Ou rebuild !

Un dernier petit test, où on va claquer la moitié de l'IOT : 

DELETE FROM
testiot
WHERE l <= 5000;

9998 rows deleted.

commit
;

Commit complete.

On réorg l'IOT pour ramener tout le monde vers le début : 

ALTER TABLE
testiot MOVE;

Table altered.

... et on rebuild l'index : 

ALTER INDEX
testiotii2 REBUILD ONLINE;

Index altered.

SELECT
/*+index(testiot testiotii)*/ 'ii' accesstype, rowid, 10000
FROM testiot
WHERE m = 10000
UNION ALL
SELECT /*+index(testiot testiotii2)*/ 'ii2', rowid, 10000
FROM testiot
WHERE m * 1 = 10000
UNION ALL
SELECT /*+index(testiot testiotii2)*/ 'tab', rowid, n
FROM testiot
WHERE m = 10000; 

ACC ROWID                                          10000
--- ----------------------------------------- ----------
ii  *BAEAGj0CwwL+                                  10000
ii2 *BAEAGqsCwwL+                                  10000
tab *BAEAGqsCwwL+                                  10000

Voilà, le rebuild a réaligné son estimation de rowid de l'index sur le rowid logique de l'IOT !

A noter que les stats de l'index sont recalculés au rebuild (c'est vrai que tant qu'à ce repalucher toutes les données, pourquoi ne pas en profiter...)

SELECT
index_name, blevel, num_rows, leaf_blocks, pct_direct_access
FROM dba_indexes
WHERE table_name = 'TESTIOT';

INDEX_NAME                         BLEVEL   NUM_ROWS LEAF_BLOCKS PCT_DIRECT_ACCESS
------------------------------ ---------- ---------- ----------- -----------------
SYS_IOT_TOP_1443683                     1      19802         209                 0
TESTIOTII                               1      19802         234                 0
TESTIOTII2                              1       9804          29               100

COMPUTE STATISTICS Clause

This clause has been deprecated. Oracle Database now automatically collects statistics during index creation and rebuild. 
This clause is supported for backward compatibility and will not cause errors.

Bref, notons surtout que COALESCE, qui permet également de recompacter l'index, ne remet pas à jour les rowid (normal, on re-façonne juste l'arbre)

ALTER INDEX
TESTIOTII COALESCE;

exec dbms_stats.gather_table_stats('PACMAN', 'TESTIOT', cascade => true)

SELECT
index_name, blevel, num_rows, leaf_blocks, pct_direct_access
FROM dba_indexes
WHERE table_name = 'TESTIOT';

INDEX_NAME                         BLEVEL   NUM_ROWS LEAF_BLOCKS PCT_DIRECT_ACCESS
------------------------------ ---------- ---------- ----------- -----------------
SYS_IOT_TOP_1443683                     1       9804          29                 0
TESTIOTII                               1       9804         116                 0
TESTIOTII2                              1       9804          29               100

Partager cet article

Published by Pacman - dans SGBD
commenter cet article

commentaires