Overblog Suivre ce blog
Administration Créer mon blog
30 juillet 2009 4 30 /07 /juillet /2009 14:11
Pour la définition des index composites, on rencontre souvent la recommandation :
"En premières positions, les colonnes les plus sélectives !"
Bien entendu, ça dépend du contexte... mais certaines personnes prennent cette règle comme vérité ultime.

Cet article s'attache donc à donner quelques arguments pour faire le contraire (encore une fois, ce ne seront pas des règles absolues, mais vraiment en fonction du cas concret...) :
  1. Le skip scan (A partir de Oracle 9i)
  2. La compression d'index (A partir de Oracle 8i)
  3. L'amélioration du clustering factor

(Euh par contre, je retravaillerai la mise en forme une autre fois)

1) L'index skip scan :
Dans les versions antérieures à 9i, un index composite ne pouvait être utilisé que si les prédicats de filtres / jointures s'appuient sur la première colonne de cet index.
Cependant, si la recherche porte sur la deuxième colonne d'un index, et qu'il y a peu de valeurs distinctes sur la première, il peut être très avantageux de tenter toutes les combinaisons de la première colonne avec la valeur recherchée sur la seconde.
C'est le principe de l'index skip scan.
Exemple en "image" :

- Trois catégories, 10000 valeurs par catégorie
SQL> CREATE TABLE test_iss AS
  2  SELECT 'A' AS categ, level AS n FROM DUAL CONNECT BY level <= 10000 UNION ALL
  3  SELECT 'B' AS categ, level AS n FROM DUAL CONNECT BY level <= 10000 UNION ALL
  4  SELECT 'C' AS categ, level AS n FROM DUAL CONNECT BY level <= 10000
  5  /

Table créée.

SQL> CREATE INDEX test_iss_i1 ON test_iss(categ, n) COMPUTE STATISTICS
  2  /

Index créé.

SQL> exec dbms_stats.gather_table_stats(NULL, 'TEST_ISS')

Procédure PL/SQL terminée avec succès.

SQL> EXPLAIN PLAN FOR SELECT * FROM test_iss WHERE n = 1234
  2  /

Explicité.

SQL> SELECT * FROM TABLE(dbms_xplan.display)
  2  /

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2032351434

--------------------------------------------------------------------------------
| Id  | Operation        | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |             |     3 |    15 |     4   (0)| 00:00:01 |
|*  1 |  INDEX SKIP SCAN | TEST_ISS_I1 |     3 |    15 |     4   (0)| 00:00:01 |
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("N"=1234)
       filter("N"=1234)

14 ligne(s) sélectionnée(s).



Bien entendu, pour cette recherche, l'index avec la colonne "n" en première position aurait été plus efficace.
Mais en s'appuyant sur l'index de l'exemple, on couvre un plus grand nombre de requêtes tout en limitant le nombre d'index créés.

2) La compression d'indexes
Un index associe à une valeur de clef (éventuellement composite) l'adresse "physique" de la ligne dans la table (le rowid).
Les valeurs des clefs sont triées. Donc, potentiellement, dans les blocs feuilles, Les "préfixes" de ces valeurs peuvent se répéter un grand nombre de fois...
Dans l'exemple précédent, ça donnerait :
A;1 -> rowid1
A;2 -> rowid2
...
A;10000 -> rowid10000

Depuis Oracle 8.1, il est possible d'exploiter ces répétitions en les factorisant : c'est la compression d'index.
Ce que cela apporte ? Un index plus petit, et donc une économie de lecture de blocs pour les opérations de masse ! (soit sur les INDEX RANGE SCAN, mais aussi sur les INDEX FAST FULL SCAN)
(Bien entendu, si on ne cible qu'une seule valeur, ça ne change pas grand chose...)

Un petit exemple sur une table réelle.
La table HISTO_PTF est alimentée mensuellement pour historiser les performances des portefeuilles.
L'index de clef primaire est (ID_PTF , DATE_HISTO) : ID_PTF est la colonne la plus sélective (19000 valeurs distinctes sur 240000 lignes au total), alors que la colonne DT_HISTO comporte l'est très peu (13)
C'est donc un excellent exemple pour montrer l'effet de la compression : créer l'index par colonnes permutées.

SQL> CREATE INDEX test_col_ind ON histo_ptf(DATE_HISTO, ID_PTF) COMPRESS 1 COMPUTE STATISTICS
  2  /
Index created.

SQL> SELECT index_name, compression, leaf_blocks FROM dba_indexes WHERE table_name = 'HISTO_PTF'
  2  /

INDEX_NAME                     COMPRESS LEAF_BLOCKS
------------------------------ -------- -----------
PK_HISTO_PTF                   DISABLED        1941
TEST_COMP                      ENABLED         1239


36 % de gain !

3) Le clustering factor
Le clustering factor (CF) d'un index mesure la corrélation entre l'ordre des valeurs des clefs de l'index et l'ordre des lignes dans la table.
Lorsque le CF est proche du nombre de lignes dans la table, la corrélation est quasi nulle.
A l'inverse, lorsque le CF est proche du nombre de blocs, les lignes sont à peu près rangées dans le même ordre que les valeurs de la clef.

A quoi ça sert ?
Imaginez que vous devez récupérer un grand nombre de lignes dans une table en suivant un index.
A chaque lecture d'une valeur, vous devrez lire la ligne dans la table.
Si la corrélation CF est forte, vous devrez charger beaucoup moins de blocs.
A l'inverse, si vous devez en lire beaucoup, il sera peut être plus rentable de lire directement la table en entier !
Cette donnée statistique (toujours le CF) est utilisée par l'optimiseur pour évaluer le nombre de blocs à lire pour une requête.

Le lien avec la sélectivité des colonnes sur les indexes composites ?
Certaine colonnes peu sélectives représentent des catégories, ou des groupements de lignes.
Par exemple, si vous votre table stocke des factures contenant des références produit, il se peut que la référence produit soit plus sélective (pour peu que vous distribuiez une grande diversité de produits). Cependant, les lignes dans la table seront regroupées par référence facture, puisque celles-ci risquent d'être saisies en un bloc.

Pour visualiser la chose, nous reprendrons l'exemple du 2) : les performances sont historisée chaque mois, et donc l'insertion est faite par date.

SQL> SELECT index_name, clustering_factor FROM dba_indexes WHERE table_name = 'HISTO_PTF'
  2  /

INDEX_NAME                     CLUSTERING_FACTOR
------------------------------ -----------------
PK_HISTO_RISQUE_PTF                       232337
TEST_COMP                                   3090

SQL> SELECT index_name, clustering_factor FROM dba_indexes WHERE table_name = 'HISTO_PTF'
  2  /

INDEX_NAME                     CLUSTERING_FACTOR
------------------------------ -----------------
PK_HISTO_RISQUE_PTF                       232337
TEST_COMP                                   3090


L'index de test présente un CF parfait, l'autre le pire CF qu'on puisse imagine...
Ca en jette, non ?
Repost 0
Published by Pacman - dans SGBD
commenter cet article
24 juillet 2009 5 24 /07 /juillet /2009 16:29
Un exercice assez courant : mettre à jour une table A avec les valeurs d'une table B, en faisant la correspondance sur un certain nombre de colonnes.
MySQL permet l'UPDATE de jointure... mais pas Oracle !
J'ai toujours trouvé ça assez naze, car la requête doit exécuter les mêmes accés deux fois pour :
- Rechercher les valeurs dans le SET
- délimiter le périmètre de mise à jour

Mais avec l'évolution de l'instruction MERGE sous Oracle 10g, le problème est enfin résolu !

Gros Erratum :
Sous 9i, il existe déjà une syntaxe pour le faire ! Merci à Mnitu pour l'info : son commentaire donne la syntaxe.
Cela dit, il faut que la table à modifier soit "key preserved" (et surtout le démontrer à Oracle...).
Vous trouverez un petit complément sur le sujet ici.


Allez, un petit exemple pour illustrer la chose.
Création des jeux de tests :

SQL> CREATE TABLE test_merge AS
  2  SELECT level AS id, 0 as val
  3  FROM DUAL
  4  CONNECT BY level <= 1000000
  5  /

Table créée.

SQL> CREATE UNIQUE INDEX test_merge_ii ON test_merge(id)
  2  /

Index créé.

SQL> CREATE TABLE test_merge_source AS
  2  SELECT id, id as val
  3  FROM test_merge
  4  WHERE mod(id, 1000) = 0
  5  /

Table créée.

SQL> CREATE UNIQUE INDEX test_merge_source_ii ON test_merge_source(id)
  2  /

Index créé.

Un petit coup de stats :
SQL> exec dbms_stats.gather_table_stats(NULL, 'TEST_MERGE')

Procédure PL/SQL terminée avec succès.

SQL> exec dbms_stats.gather_table_stats(NULL, 'TEST_MERGE_SOURCE')

Procédure PL/SQL terminée avec succès.



On active l'autotrace, et c'est parti !
SQL> set autotrace on

Première méthode, l'UPDATE simple :
SQL> UPDATE test_merge a
  2  SET val = (SELECT val
  3          FROM test_merge_source b
  4          WHERE a.id = b.id)
  5  /

1000000 ligne(s) mise(s) à jour.


Plan d'exécution
----------------------------------------------------------                                                                         
Plan hash value: 1824631898       
                                  
-----------------------------------------------------------------------------------
| Id  | Operation                    | Name                 | Rows  | Bytes | Cost
-----------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT             |                      |   996K|  5839K|   501
|   1 |  UPDATE                      | TEST_MERGE           |       |              
|   2 |   TABLE ACCESS FULL          | TEST_MERGE           |   996K|  5839K|   501
|   3 |   TABLE ACCESS BY INDEX ROWID| TEST_MERGE_SOURCE    |     1 |     7 |     2
|*  4 |    INDEX UNIQUE SCAN         | TEST_MERGE_SOURCE_II |     1 |       | 
         1
-----------------------------------------------------------------------------------
                                                                                                                                   
Predicate Information (identified by operation id):                                                                                
---------------------------------------------------                                                                                
                                                                                                                                   
   4 - access("B"."ID"=:B1)                                                                                                        


Statistiques
----------------------------------------------------------                                                                         
       2089  recursive calls                                                                                                       
    2073432  db block gets                                                                                                         
    1192599  consistent gets                                                                                                       
          0  physical reads                                                                                                        
  444612940  redo size                                                                                                             
        681  bytes sent via SQL*Net to client                                                                                      
        620  bytes received via SQL*Net from client                                                                                
          4  SQL*Net roundtrips to/from client                                                                                     
          6  sorts (memory)                                                                                                        
          0  sorts (disk)                                                                                                          
    1000000  rows processed                                                                                                        

SQL> rollback

  2  /


Comme on n'a pas délimité le périmètre, toutes les valeurs sans correspondance sont mises à NULL !

C'est pourquoi on est obligé d'ajouter :

SQL> UPDATE test_merge a
  2  SET val = (SELECT val
  3          FROM test_merge_source b
  4          WHERE a.id = b.id)
  5  WHERE EXISTS (SELECT NULL
  6             FROM test_merge_source b
  7             WHERE a.id = b.id)
  8  /

1000 ligne(s) mise(s) à jour.


Plan d'exécution
----------------------------------------------------------                                                                         
Plan hash value:2830137269                                                                                                        
                                                                                                                                   
-----------------------------------------------------------------------------------
| Id  | Operation                    | Name                 | Rows  | Bytes | Cost
-----------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT             |                      |  1000 | 10000 |   508
|   1 |  UPDATE                      | TEST_MERGE           |       |       |      
|   2 |   NESTED LOOPS               |                      |  1000 | 10000 |   508
|   3 |    SORT UNIQUE               |                      |  1000 |  4000 |     2
|   4 |     INDEX FAST FULL SCAN     | TEST_MERGE_SOURCE_II |  1000 |  4000 |     2
|*  5 |    INDEX UNIQUE SCAN         | TEST_MERGE_II        |     1 |     6 |     1
|   6 |   TABLE ACCESS BY INDEX ROWID| TEST_MERGE_SOURCE    |     1 |     7 |     2
|*  7 |    INDEX UNIQUE SCAN         | TEST_MERGE_SOURCE_II |     1 |       |     1
-----------------------------------------------------------------------------------

                                                                                                                                   
Predicate Information (identified by operation id):                                                                                
---------------------------------------------------                                                                                
                                                                                                                                   
   5 - access("A"."ID"="B"."ID")                                                                                                   
   7 - access("B"."ID"=:B1)                                                                                                        


Statistiques
----------------------------------------------------------                                                                         
         10  recursive calls                                                                                                       
       1032  db block gets                                                                                                         
       4013  consistent gets                                                                                                       
          0  physical reads                                                                                                        
     247328  redo size                                                                                                             
        686  bytes sent via SQL*Net to client                                                                                      
        700  bytes received via SQL*Net from client                                                                                
          4  SQL*Net roundtrips to/from client                                                                                     
          2  sorts (memory)                                                                                                        
          0  sorts (disk)                                                                                                          
       1000  rows processed                                                                                                        

SQL> rollback
  2  /

Annulation (rollback) effectuée.


En regardant le plan d'exécution, on voit bien que la jointure est réalisée 2 fois, ce qui est un véritable drame humain...
Du coup, sous 9i ou moins, il était plus efficace de faire CURSOR UPDATE en parcourant la jointure, un gros aveu d'échec.

Mais depuis 10g, il est possible d'utiliser MERGE (merci SQL 2003 !)pour faire faire un UPSERT... sans le SERT !
(Il n'est plus obligatoire d'implémenter les deux clauses MATCHED et NOT MATCHED.

La petite démo :
SQL> MERGE INTO test_merge a
  2  USING test_merge_source b
  3  ON (a.id = b.id)
  4  WHEN MATCHED THEN UPDATE SET a.val = b.val
  5  /
1000 lignes fusionnées.

Plan d'exécution
----------------------------------------------------------                                                                         
Plan hash value: 2484597143                                                                                                        
                                                                                                                                   
------------------------------------------------------------------------
| Id  | Operation            | Name              | Rows  | Bytes | Cost
------------------------------------------------------------------------                                         
|   0 | MERGE STATEMENT      |                   |  1000 | 15000 |   526
|   1 |  MERGE               | TEST_MERGE        |       |       |      
|   2 |   VIEW               |                   |       |       |      
|*  3 |    HASH JOIN         |                   |  1000 | 13000 |   526
|   4 |     TABLE ACCESS FULL| TEST_MERGE_SOURCE |  1000 |  7000 |     3
|   5 |     TABLE ACCESS FULL| TEST_MERGE        |   996K|  5839K|   501
------------------------------------------------------------------------

                                                                                                                                   
Predicate Information (identified by operation id):                                                                                
---------------------------------------------------                                                                                
                                                                                                                                   
   3 - access("A"."ID"="B"."ID")                                                                                                   


Statistiques
----------------------------------------------------------                                                                         
          1  recursive calls                                                                                                       
       1021  db block gets                                                                                                         
       1667  consistent gets                                                                                                       
          0  physical reads                                                                                                        
     248796  redo size                                                                                                             
        688  bytes sent via SQL*Net to client                                                                                      
        640  bytes received via SQL*Net from client                                                                                
          4  SQL*Net roundtrips to/from client                                                                                     
          1  sorts (memory)                                                                                                        
          0  sorts (disk)                                                                                                          
       1000  rows processed                                                                                                        

SQL> COMMIT
  2  /

Validation effectuée.


Ils ne sont pas mignons, le plan d'exécution / les stats ?



Repost 0
Published by Pacman - dans SQL
commenter cet article
18 juillet 2009 6 18 /07 /juillet /2009 16:01
Voilà voilà, j'ai toujours rêvé d'avoir un journal intime, et de le laisser traîner un peu partout pour que tout le monde puisse le lire.
C'est bien à ça que ça sert, un blog ?
J'ai enfin fait le pas. Je suis tellement heureux que je vais m'ouvrir une bière (et changer de caleçon éventuellement).

Bref plus sérieusement, j'ai vu qu'un collègue de forum avait créé un blog pour y poster des petits tours de magie en SQL...

Donc ici, à part mes élucubrations et les expressions de mes perversions les plus étranges, on va parler d'Oracle et de SQL.
Repost 0
Published by Pacman - dans Journal intime
commenter cet article