Exemple d'optimisation par indexation [Éléments pour l'optimisation des data warehouses]

Exemple d'optimisation par indexation

Soit la table "tlivres" contenant 19205 lignes :

CTRL+C pour copier, CTRL+V pour coller

CREATE TABLE tlivres (
code number primary key,
isbn varchar2(20),
titre varchar2(255),
annee number(4));

CREATE TABLE tlivres (
code number primary key,
isbn varchar2(20),
titre varchar2(255),
annee number(4));

Requête sur la clé primaire

num. | Ligne | texte | copie |

CTRL+C pour copier, CTRL+V pour coller

SELECT * FROM tlivres WHERE code=1000;

SELECT * FROM tlivres WHERE code=1000;

Enregistrement retourné

Ligne | num. | copie |

CTRL+C pour copier, CTRL+V pour coller

CODE   ISBN          TITRE                ANNEE 
------ ------------- -------------------- ----- 
1000   3-8055-3601-1 Biocompatibility     1994

CODE   ISBN          TITRE                ANNEE 
------ ------------- -------------------- ----- 
1000   3-8055-3601-1 Biocompatibility     1994

Plan d'exécution

Ligne | num. | copie |

CTRL+C pour copier, CTRL+V pour coller

SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=1 Bytes=167 )
0 TABLE ACCESS (BY INDEX ROWID) OF 'TLIVRES' (Cost=2 Card=1 Bytes=167) 
1 INDEX (UNIQUE SCAN) OF 'SYS_C0044628' (UNIQUE) (Cost=1 Card=1)

0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=1 Bytes=167 )
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TLIVRES' (Cost=2 Card=1 Bytes=167) 
2 1 INDEX (UNIQUE SCAN) OF 'SYS_C0044628' (UNIQUE) (Cost=1 Card=1)

Le coût est faible et la cardinalité (nombre de lignes remontées) est de 1, car la clé primaire est toujours associée à un index unique (créé automatiquement par le système si cela n'a pas été fait explicitement par l'utilisateur).

Requête sur le champ ISBN

num. | Ligne | texte | copie |

CTRL+C pour copier, CTRL+V pour coller

SELECT * FROM tlivres WHERE isbn='3-8055-3601-1';

SELECT * FROM tlivres WHERE isbn='3-8055-3601-1';

Plan d'exécution sans index

Ligne | num. | copie |

CTRL+C pour copier, CTRL+V pour coller

0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=25 Card=1 Bytes= 33233)
1 0 TABLE ACCESS (FULL) OF 'TLIVRES' (Cost=25 Card=1 Bytes=33233)

0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=25 Card=1 Bytes= 33233)
1 0 TABLE ACCESS (FULL) OF 'TLIVRES' (Cost=25 Card=1 Bytes=33233)

L'ensemble de la table est parcouru et le coût est de 25. Si les statistiques sont à jour (clause ANALYSE) le système prévoira de ne remontrer qu'un enregistrement (Card=1).

Ajout d'un index B-Tree

num. | Ligne | texte | copie |

CTRL+C pour copier, CTRL+V pour coller

CREATE INDEX idx_isbn ON tlivres(isbn);

CREATE INDEX idx_isbn ON tlivres(isbn);

Plan d'exécution avec index

Ligne | num. | copie |

CTRL+C pour copier, CTRL+V pour coller

SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=1 Bytes=33233) 
0 TABLE ACCESS (BY INDEX ROWID) OF 'TLIVRES' (Cost=2 Card=1 Bytes=33233) 
1 INDEX (RANGE SCAN) OF 'IDX_ISBN' (NON-UNIQUE) (Cost=1 Card=1)

0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=1 Bytes=33233) 
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TLIVRES' (Cost=2 Card=1 Bytes=33233) 
2 1 INDEX (RANGE SCAN) OF 'IDX_ISBN' (NON-UNIQUE) (Cost=1 Card=1)

L'accès se fait à présent via l'index et le coût n'est plus que de 2.

Requête sur le champ ANNEE

Ligne | num. | copie |

CTRL+C pour copier, CTRL+V pour coller

SELECT count(*) FROM tlivres WHERE annee=1994;

SELECT count(*) FROM tlivres WHERE annee=1994;

Enregistrement retourné

Ligne | num. | copie |

CTRL+C pour copier, CTRL+V pour coller

 COUNT(*) 
---------- 
595

 COUNT(*) 
---------- 
595

Plan d'exécution sans index

Ligne | num. | copie |

CTRL+C pour copier, CTRL+V pour coller

SELECT STATEMENT Optimizer=ALL_ROWS (Cost=25 Card=1 Bytes=13 )
0 SORT (AGGREGATE) 
1 TABLE ACCESS (FULL) OF 'TLIVRES' (Cost=25 Card=798 Bytes=2587)

0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=25 Card=1 Bytes=13 )
1 0 SORT (AGGREGATE) 
2 1 TABLE ACCESS (FULL) OF 'TLIVRES' (Cost=25 Card=798 Bytes=2587)

Le coût prévu est de 25 (c'est à dire le même que pour la requête précédente, puisque toute la table devra être parcourue de la même façon). On notera que le nombre estimé de lignes à compter est de 798, pour 595 en réalité.

Ajout d'un index Bitmap

Le champ ANNEE compte 25 valeurs différentes, on choisit de l’indexer par un index bitmap étant donné son relativement faible pouvoir discriminant.

num. | Ligne | texte | copie |

CTRL+C pour copier, CTRL+V pour coller

CREATE BITMAP INDEX idx_annee ON tlivres(annee);

CREATE BITMAP INDEX idx_annee ON tlivres(annee);

Plan d'exécution sans index

Ligne | num. | copie |

CTRL+C pour copier, CTRL+V pour coller

SELECT STATEMENT Optimizer=ALL_ROWS (Cost=1 Card=1 Bytes=13)
0 SORT (AGGREGATE) 
1 BITMAP CONVERSION (COUNT) 
2 BITMAP INDEX (SINGLE VALUE) OF 'IDX_ANNEE'

0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=1 Card=1 Bytes=13)
1 0 SORT (AGGREGATE) 
2 1 BITMAP CONVERSION (COUNT) 
3 2 BITMAP INDEX (SINGLE VALUE) OF 'IDX_ANNEE'

L'accès se fait à présent via l'index bitmap et le coût n'est plus que de 1.