Discussion :

[tipeh]

Bonjour,

Je souhaiterais engager des travaux d'optimisation sur une base de données. Et l'un des axes d'amélioration que j'ai repéré c'est de réduire la largeur des tables. Mais avant de m'engager dans ça, je souhaiterai m'assurer que l'impact vaut bien l'effort.

En l'occurrence, il s'agit d'une table de faits, donc j'ai plutôt besoin qu'elle soit performante en lecture.

En consultant les statistiques de ma table de fait, je constate que, actuellement, la taille moyenne d'une ligne est de : AVG_ROW_LEN = 186 Byte. En faisant certains reformatages et en supprimant certaines colonnes inutiles, je peux réduire cette valeur de quasiment 50%. Est ce qu'en faisant cela, je gagnerai aussi 50% en performance de lecture ? Par exemple, si avant optimisation, un full table scan se faisait en 2 min, est ce qu'après optimisation, il se ferait en 1 min ?

Merci.

[mnitu]

Faite un test et vous allez avoir la réponse ! En théorie si la taille des enregistrements est réduite Oracle pourrait stocker plusieurs enregistrement dans un même block ce qui fera moins des blocks nécessaires à stocker la même quantité des données et par conséquence un full table plus rapide. Mais en aucun cas de 50%. Et cela reste de la théorie parce que dans un full table scan Oracle peut être amené à lire bien plus des blocks que le nombre « minimum nécessaire » pour stocker les enregistrements.
Mais je pense que vous vous prenez-mal dans votre démarche d’optimisation.

[pachot]

Bonjour,

Oui la durée du Full Table Scan est proportionnelle à la taille de la table.
C'est de toute façon une bonne chose de supprimer les colonnes inutiles.

Toute l'idée du modèle dimensionnel est bien de réduire la taille de la table de faits.

Cordialement,
Franck.

[ora-2013]

bonjour ,

Avez-vous pensé à compresser la table pour réduire les blocs lus en full scan ?

sinon j'ai une question :

Existe-il une technique permettant de monitorer / détecter les colonnes non utilisées de toutes les tables d'une base de données ?

A+

[doc malkovich]

Bonjour,

C'est quoi les reformatages et comment connaissez-vous les colonnes inutiles ?

Si vous parlez d'une table de faits, c'est ce que c'est une table pour le décisionnel.
Généralement elles sont dénormalisées ; on pense que certaines colonnes sont inutiles mais c'est en fait une astuce pour éviter les jointures. Il faut donc faire attention.

[tipeh]

Faite un test et vous allez avoir la réponse ! En théorie si la taille des enregistrements est réduite Oracle pourrait stocker plusieurs enregistrement dans un même block ce qui fera moins des blocks nécessaires à stocker la même quantité des données et par conséquence un full table plus rapide. Mais en aucun cas de 50%. Et cela reste de la théorie parce que dans un full table scan Oracle peut être amené à lire bien plus des blocks que le nombre « minimum nécessaire » pour stocker les enregistrements.
Mais je pense que vous vous prenez-mal dans votre démarche d’optimisation.

Est-il possible d'aller un peu plus loin et me dire pourquoi je m'y prends mal ?

Bonjour,

Oui la durée du Full Table Scan est proportionnelle à la taille de la table.
C'est de toute façon une bonne chose de supprimer les colonnes inutiles.

Toute l'idée du modèle dimensionnel est bien de réduire la taille de la table de faits.

Cordialement,
Franck.

C'est bien ce qui me semblait. En tout cas, vous me rassurez dans mon approche.

bonjour ,

Avez-vous pensé à compresser la table pour réduire les blocs lus en full scan ?

A+

Non je n'ai pas encore essayer ça. Merci pour le conseil.

Bonjour,

C'est quoi les reformatages et comment connaissez-vous les colonnes inutiles ?

Si vous parlez d'une table de faits, c'est ce que c'est une table pour le décisionnel.
Généralement elles sont dénormalisées ; on pense que certaines colonnes sont inutiles mais c'est en fait une astuce pour éviter les jointures. Il faut donc faire attention.

En parlant de reformattage, je me suis probablement mal exprimé. Ce que je voulais dire, c'était de changer le format des clés etrangères stockées dans la table de fait : Actuellement, ils stockent des clés naturelles (c'est à dire les même clés que dans l'appli opérationnelle). Pour les clients par exemple, c'est des clés de type CHAR (15 CHAR) qui occupent énormément de places pour chaque ligne de la table de fait. Je projette de remplacer ça par des clés artificielles qui seraient des séquences.

Pour les colonnes inutiles, justement c'est lié à une trop grande dénormalisation. Par exemple, ils stockent dans cette table de fait le code postal, le département et le pays du client. Et vous avez raison, cela a été fait dans le but d'éviter des jointures. Mais non seulement, cela élargit de trop la table de fait qui sera pénalisée en lecture pour les requêtes qui n'utilisent pas ces infos de géographie, mais en plus ça détruit la vue référentielle. C'est à dire que les même infos de géographie ne pourront pas être réutilisées pour d'autres objets métiers, par exemple les fabricants. Ce que je projette de faire, c'est de supprimer ces colonnes de géographie et de remplacer ça par une clé etrangère vers une table de géographie (et donc normaliser un peu plus la table).

Je suis hyper interessé par avoir des critiques sur l'aspect modélisation car, mis à part des livres que j'ai lu, je n'ai pas une grande expérience professionnelle la dedans. Donc n'hésitez pas à infirmer ou confirmer ce que je dis.

Merci pour vos réponses.

[Waldar]

Oui, c'est une bonne idée de remplacer des clefs métiers par des clefs subrogées.

Je ne suis pas fan de la dénormalisation à outrance, surtout si vous prenez en compte les méconnus bitmap join index.
Il faut, je suppose, une base en Enterprise Edition.

Si votre table de faits a trop de colonnes, regardez parmi les requêtes les plus appelées quelles colonnes sont toujours / beaucoup utilisées et celles qui le sont peu / jamais. Vous pouvez alors mettre en place un partitionnement vertical afin de séparer votre table en deux.
D'expérience, il se combine mal avec le partitionnement classique.

Votre table de faits, si elle est correctement alimentée avec des INSERT et jamais d'UPDATE / DELETE, profiterait également d'un PCTFREE à 0 pour réduire l'espace consommé.

[pachot]

Bonjour,
Oui, la méthode est bonne.

Pour les clients par exemple, c'est des clés de type CHAR (15 CHAR)...Je projette de remplacer ça par des clés artificielles qui seraient des séquences

Oui, très bonne idée. De toute façon on ne veut pas faire un full scan de la table de fait et on y accédera par index. Donc autant passer par une dimension.
Sauf peut-être si la table de fait est partitionnée par hash sur cet id client. Ou si on est sur exadata, et compressé car dans ce cas ce sera peut-être plus rapide de faire un SmartScan sur la table, le prédicat sur l'id client étant filtré par Storage Index puis predicate offloading.

supprimer ces colonnes de géographie et de remplacer ça par une clé etrangère vers une table de géographie (et donc normaliser un peu plus la table)

Oui. En datawarehouse on dénormalise les tables de dimensions car elles sont petites et qu'on veut éviter trop de jointures pour les hierarchies de dimensions, mais on normalise au maximum la table de faits, qui elle est grosse.
Avoir une dimension geographie qui a tous ses détails dans la table de dimension, et juste une foreign key dans la table de fait. Bitmap index sur cette foreign key. Crontrainte foreign key vers la dimension. Et l'optimiseur peut faire du Star Query Transformation.
Donc je confirme, c'est la bonne approche
Cordialement,
Franck.

[tipeh]

Merci beaucoup Waldar et Pachot pour ces conseils hyper intéressants !

Si votre table de faits a trop de colonnes, regardez parmi les requêtes les plus appelées quelles colonnes sont toujours / beaucoup utilisées et celles qui le sont peu / jamais. Vous pouvez alors mettre en place un partitionnement vertical afin de séparer votre table en deux.
D'expérience, il se combine mal avec le partitionnement classique.

Votre table de faits, si elle est correctement alimentée avec des INSERT et jamais d'UPDATE / DELETE, profiterait également d'un PCTFREE à 0 pour réduire l'espace consommé.

Super idée. J'avais un peu lu sur le partitionnement vertical mais je le voyais inapplicable dans mon cas. Mais c'est vrai qu'en monitorant les requêtes sur ma table de fait j'arriverai peut être à trouver des colonnes candidates à ce partitionnement.
Pour ma table de fait, en effet, il n'y a jamais d'UPDATE ni de DELETE, c'est uniquement des INSERT. Donc je pense que c'est effectivement très applicable pour mon cas. J'en discuterai avec mon DBA. Merci pour le conseil.

Avoir une dimension geographie qui a tous ses détails dans la table de dimension, et juste une foreign key dans la table de fait. Bitmap index sur cette foreign key. Crontrainte foreign key vers la dimension. Et l'optimiseur peut faire du Star Query Transformation.
Franck.

ça m'a l'air super pertinent ce que vous dites, et je n'étais pas du tout au courant de cette possibilité. Par contre, cela soulève plein de questions pour moi :
- Est ce que cela veut dire que je dois créer un index bitmap pour chacune de mes foreign key à faible cardinalité de ma table de fait ?
- Pour pas que cela pénalise l'alimentation de ma table de fait, est-il une bonne pratique de supprimer mes index (avec un drop) avant chaque alimentation et de les reconstruire juste après ?

Merci encore.

[Mohamed.Houri]

Oui, c'est une bonne idée de remplacer des clefs métiers par des clefs subrogées.

Je ne serai pas aussi catégorique

https://community.oracle.com/thread/...rt=15&tstart=0

Comme toujours avec Oracle : Ça dépends. Il y va de la flexibilité et de la performance et comme les deux ne s'entendent pas très bien, je serai prudent avec cette affirmation.

[Waldar]

Dans un système OLTP je suis conscient que cette affirmation est discutable, mais sur un système OLAP je ne pense pas qu'il y ait débat.

Un datawarehouse étant une base qui contient les données issues de plusieurs applications, les clefs naturelles n'ont pas forcément de sens si un même client provient de trois systèmes différents.

tipah, prenez quelques heures et lisez le guide DWH d'Oracle qui est bien fait :
http://docs.oracle.com/cd/E16655_01/...e17749/toc.htm

[Ahmed AANGOUR]

Bonjour,

Réduire la largeur des colonnes et donc le nombre de blocks pour une table est forcément quelque chose de positif.
Toutefois, si la table concernée est essentiellement accédée via des index il est peu probable que vos pb de performances s'en trouve positivement affectés.
Avant de se pencher sur les éventuelles solutions je pense qu'il faut d'abord bien définir la nature de vos pb de performance. Je pense que c'est ce que voulait dire MNITU dans son commentaire.

[tipeh]

tipah, prenez quelques heures et lisez le guide DWH d'Oracle qui est bien fait :
http://docs.oracle.com/cd/E16655_01/...e17749/toc.htm

J'ai commencé à le lire. C'est super intéressant, merci beaucoup.

Bonjour,

Réduire la largeur des colonnes et donc le nombre de blocks pour une table est forcément quelque chose de positif.
Toutefois, si la table concernée est essentiellement accédée via des index il est peu probable que vos pb de performances s'en trouve positivement affectés.
Avant de se pencher sur les éventuelles solutions je pense qu'il faut d'abord bien définir la nature de vos pb de performance. Je pense que c'est ce que voulait dire MNITU dans son commentaire.

En effet, c'est un travail que j'ai déjà fait en amont. Merci pour ces précisions.