Techniques d'indexation bitmap Oracle

Bonsoir encore!

Nous lançons le deuxième volet de notre nouveau cours «SGBD relationnel» , que nous avons complété un peu sur les résultats de la première exécution: des classes supplémentaires sur les clusters MySQL et Postgres se sont avérées être demandées par docker et diverses «améliorations de fichiers». Attendez-vous donc à des leçons ouvertes (dans lesquelles certains des anciens sujets ont été retirés) et à du matériel intéressant. Aujourd'hui, nous nous plongons dans les techniques d'Oracle.

Allons-y.

Les index bitmap Oracle sont très différents des index B-tree standard. Dans les structures bitmap, un tableau à deux dimensions est créé avec une colonne pour chaque ligne de la table indexée. Chaque colonne représente une valeur distincte dans l'index bitmap. Ce tableau à deux dimensions affiche chaque valeur d'index multipliée par le nombre de lignes de ce tableau.

Oracle décompresse le bitmap (avec la vitesse de récupération de ligne) dans le tampon de données RAM pour une analyse rapide des valeurs correspondantes. Ces valeurs correspondantes sont transmises à Oracle sous forme de liste d'ID de ligne et les valeurs d'ID de ligne peuvent accéder directement aux informations requises.



Un avantage particulier de l'indexation bitmap se manifeste lorsqu'une seule table comprend plusieurs index bitmap. La puissance de chaque colonne peut être faible. La création de plusieurs index bitmap fournit une approche très puissante pour répondre rapidement aux requêtes SQL complexes.



En utilisant la méthodologie de mise en commun des bitmaps, Oracle offre une réduction du temps de réponse de moins d'une seconde lorsque vous travaillez avec plusieurs colonnes avec un petit nombre d'éléments.

Faites également attention aux notes importantes concernant les valeurs maximales de l'index bitmap Oracle .

Par exemple, imaginez qu'il existe une base de données de voitures avec un grand nombre de colonnes de faible puissance: car_color, car_make, car_model et car_year. Chaque colonne contient moins de 100 valeurs différentes, et l'indice b-tree serait complètement inutile dans une telle base de données de 20 millions de voitures.

Cependant, la fusion de ces index dans une requête peut fournir un temps de réponse élevé beaucoup plus rapide que la méthode traditionnelle de lecture de chacune des 20 millions de lignes de la table de base. Par exemple, supposons que nous voulons trouver une vieille Toyota Corolla bleue fabriquée en 1981:

select license_plat_nbr from vehicle where color = "blue" and make = "toyota" and year = 1981; 

Pour travailler avec cette requête, Oracle utilise une méthode d'optimisation spécialisée appelée combinaison d'index bitmap. Dans cette méthode, chaque liste Row-ID (brièvement RID) est formée séparément à l'aide de bitmaps, et une procédure de fusion spéciale est utilisée pour comparer les listes RID et rechercher les valeurs qui se chevauchent.

À mesure que le nombre de valeurs différentes augmente, la taille du bitmap augmente de façon exponentielle. Ainsi, un index de 100 valeurs peut s'exécuter 1 000 fois plus rapidement que l'index bitmap de 1 000 valeurs de colonne différentes.

Il convient de rappeler que les index bitmap ne conviennent qu'aux tables statiques et aux vues matérialisées qui sont mises à jour la nuit et reconstruites après le chargement par lots des lignes. Si plusieurs DML par seconde se produisent dans votre table, faites attention lors de l'implémentation des index bitmap!

• 1 à 7 valeurs clés différentes - Les requêtes avec des indices bitmap de faible puissance sont très rapides;
• 8 - 100 valeurs clés différentes - Avec une augmentation du nombre de valeurs différentes, la productivité diminue proportionnellement;
• 100 - 10 000 valeurs différentes - Avec plus de 100 valeurs différentes, les index bitmap deviennent énormes et les performances SQL chutent rapidement;
• Plus de 10 000 valeurs clés différentes - à ce stade, les performances sont dix fois inférieures à celles d'un indice à 100 valeurs différentes.

Les index bitmap Oracle sont une fonctionnalité très puissante d'Oracle, mais il y a des pièges!

Vous souhaiterez utiliser l'index bitmap dans les cas suivants:

1. La colonne du tableau est légère - pour le projet de manuel, considérez une image bitmap pour tout index avec moins de 100 valeurs différentes:
   
   

    select region, count(*) from sales group by region; 

2. Tables LOW DML - l'utilisation d'insertion / mise à jour / suppression doit être faible. La mise à jour des index bitmap nécessite beaucoup de ressources, ils sont donc mieux adaptés aux tables en lecture seule et aux tables mises à jour par lots tous les soirs;
3. Plusieurs colonnes - vos requêtes SQL font référence à plusieurs champs avec une faible cardinalité dans l'instruction Where. La présence d'index bitmap facilitera le travail de l'optimiseur Oracle, qui effectue une estimation basée sur le coût (en bref - CBO (Cost-Based Optimizer)).

Dépanner les index Oracle Bitmap

Les problèmes d'implémentation d'index bitmap les plus courants sont les suivants:

• Petite table - CBO peut nécessiter une analyse complète de la table si elle est trop petite!
• Statistiques incorrectes - Assurez-vous d'analyser le bitmap avec dbms_stats juste après la création:

 CREATE BITMAP INDEX emp_bitmap_idx ON index_demo (gender); exec dbms_stats.gather_index_stats(OWNNAME=>'SCOTT', INDNAME=>'EMP_BITMAP_IDX'); 

• Test des info-bulles - Pour utiliser votre nouvel index bitmap, utilisez l'info-bulle Oracle INDEX:

 select /*+ index(emp emp_bitmap_idx) */ count(*) from emp, dept where emp.deptno = dept.deptno; 

Nous attendons vos questions et commentaires ici ou venez à notre nouvelle leçon ouverte .