Discussion :

[hichaime]

Bonjour,

Je travail sur un projet dont le but est d'optimiser le temps de réponse des requêtes sous oracle 11G avec comme Benchmark TPC-H, pour faire j'utilise la fragmentation (partition) verticale des tables. Donc je dois faire plusieurs tests pour tous les schémas fragmentés, ce qui est trop onéreux.

Ma question : est il possible d’estimer le coût des requêtes sur les schémas fragmentés, mais sans fragmenter réellement le schéma original, un schéma virtuelle.
Explain plan permets de faire des estimations mais sur un le schéma existant, pas sur des schémas hypothétiques.

Merci d’avance pour vos réponses.

[mnitu]

C’est quoi votre façon de "partitionner verticalement" ? Pourriez-vous détailler ?

[hichaime]

Ce que j'entends par partition verticale c'est la fragmentation de la table par rapport aux colonnes et la duplication de la clef primaire exemple : R (Matricule, Nom, Prénom, Adresse, Date Naissance, Lieu Naissance)

soit Q1 : select t.Nom, t.Prénom from R t Where t.Nom Like %toto%;

Il serait intéressant de partitionner la table verticalement sur les colonnes nécessaires pour la charge de travail (requêtes), donc créer 2 nouvelles relations R1 et R2 où
R1 (Matricule, Nom, Prénom)
R2 (Matricule, Adresse, Date Naissance, Lieu Naissance).

le lien suivant est un exemple sur la fragmentation verticale http://www.asktherealtom.ch/?p=73

[mnitu]

C’est OK.
D’abord, avez vous déjà lu cet article du Tom Kyte: "Wide Load Storage" ?
Ensuite, vous gagnez que pour certaines requêtes suite soit au mécanisme d’élimination des tables soit en utilisant l’index à la place de la table.
Donc vous devez d’abord bien être certain que c’est bénéfique par rapport à votre application.
Concernant votre question peut être qu'examiner quelques cases et éventuellement jouer avec les statistiques pour « faire croire à l’optimiseur » suffira pour se faire une idée. De toute façon une validation des modifications par l’application sera nécessaire.

[hichaime]

C'est ce que je pensé, mais les statistiques ne me permettent de simuler un schéma.
j'ai trouver dans la doc 11 G , l'utilitaire Information Lifecycle Management (ILM); il permet de faire une simulation de partition (volet "Partition Simulation") mais seulement pour l'espace de stockage.
Je crois qu'il me reste à faire des estimations théoriques selon le modèle de coût "(cardinalité*longueur de tuple*sélectivité)/taille page" pour calculer le nombre des I/O.
si vous pouvez éclaircir ma lanterne sur :
soit la table R(Clé_A, B, C, D, E, F) partitionné en deux fragments verticaux
R1(Clé_A, B, C) & R2(Clé_A, D, E, F)
quelle serait l'estimation du coût de la jointure de R1 et R2 sur la clé primaire ; -jointure boucle imbriqué sur deux indexes -, autrement dit qu'es que je perds quand je partitionne pour le cas où pour une requête j'aurais besoin de tous les attributs.

[orafrance]

J'ai du mal à comprendre ce que ça peut apporter de plus qu'un index ce partitionnement

[mnitu]

@hichaime
Laissez tomber les calculs théorétiques faite plutôt des tests sur quelques cases.

@orafrance
C’est une autre chose orafrance. T’as lu l’article que j’ai indiqué dans le lien ?

[Waldar]

orafrance, je pense que ça sert surtout si on arrive à bien séparer les données selon leur utilisation.

Imaginons une table avec 50 colonnes. 20 d'entre elles sont très très souvent requêtées, les 30 autres sont des informations plus indicatives et sont plus rarement utilisées.

Ca permet dans la même vue (du côté de l'application / outil de reporting), selon les objets choisis, de n'interroger que la première table si aucun objet de la seconde table n'est sélectionner.

Les deux tables ayant moins de colonnes, j'imagine qu'en plus la compression sera meilleure.

Si je ne me suis pas planté dans mes tests, par contre on ne peut pas éliminer la première table, elle sera toujours là dans le plan d'exécution.

J'ai aussi essayé de combiner les deux partitionnements, mais je n'ai pas obtenu de résultats pleinement satisfaisants.

En tout cas, merci mnitu, c'est bookmarké !

[Waldar]

J'ai essayé avec trois tables :

• si on interroge que la première les deux autres sont bien éliminées du plan
• si on interroge que la deuxième la troisième est éliminée
• si on interroge que la troisième, la deuxième est éliminée.

[orafrance]

@orafrance
C’est une autre chose orafrance. T’as lu l’article que j’ai indiqué dans le lien ?

Maintenant c'est fait, je comprends mieux. Dans le cas de table avec bcp de colonnes en effet, le nombre de colonnes peut être un souci

[hichaime]

Merci bien pour vos réponses, concernant le technique de partitionnement je compte utiliser les A Génétiques ou autres techniques (matrice d'affinité, ou une nouvelle technique que je compte proposer) pour que le partitionnement soit le plus bénéfique pour toutes les requêtes.

Mais concernant les essais ce n'es pas évident car pour 30 colonnes j'ai 10 a la puissance 23 cas (nombre de Bell) à vérifier c'est pour cela que je doit passer par des estimations théoriques basé sur la cardinalité , sélectivité, longueur de tuple, taille page mémoire etc...

Si vous avez une meilleur idée j'en serais tout ouïe, aussi si vous aurez une idée sur l'estimation sur le coût de jointure R1 et R2 avec nested loop:
soit R( A, b, c, d, e, f); R1( A, b, c); R2( A, d, e, f); le coût de jointure de R1 et R2 sur le clef A; ça serais super génial. merci bien à tous;
je vous tiendrais au courant de mes avancements.

[pachot]

Bonjour,

Le coût de la jointure en nested loop est assez facile à calculer:

Si R1 est choisie comme 'inner table', le coût est:
1- le coût de récupération des enregistrements de R1
2- et le coût de récupération d'un enregistrement de R2 à partir de sa cléf primaire ... multiplié par le nombre d'enregistrement de R1.

Pour aller plus loin, celà dépends des prédicats de selection. Par exemple, si le nombre d'enregistrements est importants, l'accès nested loop va devenir très coûteux.

J'ai de très gros doutes sur les performances d'un partitionnement vertical dans ton cas. TPC-H, c'est du décisionnel: des grandes tables de faits et des index bitmaps sur toutes les dimensions. C'est la solution pour optimiser toutes sortes de requêtes ad-hoc.
Et dans ce cas, l'accès à la table se fait par ROWID (après opérations sur les index bitmaps). Dans ce cas on récupère un bloc, qu'il y ait 3 ou 400 colonnes dedans ne changera rien aux performances.

Le partitionnement vertical ne sera utile que pour du full table scan. Mais alors là, c'est la solution de créer des indexes qui ont toutes les colonnes nécessaires.

Il y a des sgbd colonnes, et ils sont optimisés pour ça. Mais ce n'est pas le cas d'Oracle. En séparant en plusieurs tables, tu aura très peu de gain et par contre des performances déplorables dès que tu aura besoin de faire la jointure.

Cordialement,
Franck.

[hichaime]

Merci pour Franck votre réponse et c'est très claire.
ainsi le coût d'une jointure en nested loop est R1 full scan et R2 coût d'accés par clé primaire pour tout les enregistrements.

Effectivement TPC-H est un bench décisionnel, je crois que je vais consulter mon prometteur pour changer de Bench, peut être APB_1.

Sinon vous dite que la frag verticale ne change rien, mais apparemment y a des études qui ont montré le contraire, et l'accès en Row Id c'est une sorte d'index, et du moment que j'aurais moins de colonnes à ramener ça sera bénéfique pour un fulll scan pour les tables volumineuse, vous ne croyais pas.
A propos je n'avais pas penser au index combiné à la place de la frag verticale, très bonne suggestion.

je remercie tout ceux qui m'ont aidé. C'est un très bon forum et c'est un niveau expert. Hichem.

[pachot]

Oui, en full scan (full table scan ou index fast full scan), plus la table sera petite, plus ce sera rapide. Mais sur oracle, je ne m'attends pas à faire des full scan de grosse table sur du décisionnel - contrairement à d'autres sgbd comme teradata par exemple.