Discussion :

[nuke_y]

Bonjour à tous.

Je me pose une question : est-ce qu'il est possible de voir le plan d'exécution d'une Vue Matéralisée ? En effet je créé actuellement une série de VM dans le but de faire de la ré-écriture de requêtes sur une grosse table d'un datawarehouse.

J'ai un empilement de VM de la plus détaillée à la moins détaillée.Par exemple ma table de base a 1 indicateur et 300 dimensions :
TABLE_DETAIL
KPI | DIM 1... DIM 300
Ma 1ere vue matérialisée n'a plus que 2 dimensions parmis les 300 et l'indicateur est donc aggrégé (somme) :
VM1
KPI | DIM 1 | DIM 2
Ma 2e vue matérialisée n'a plus que 1 dimension parmis les 2 et l'indicateur est donc aggrégé (somme) :
VM2
KPI | DIM 1
Ma 3e vue matérialisée n'a plus aucune dimension et l'indicateur est donc aggrégé (somme) au plus haut niveau (la VM contient 1 seule ligne) :
VM3
KPI

En utilisant la ré-écriture de requêtes, Oracle sait aller chercher les données dans mes VM plutôt que dans la table de détail. Par exemple :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

select DIM1, sum(KPI) FROM TABLE_DETAIL GROUP BY DIM1

va être ré-écrite

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

select DIM1, sum(KPI) FROM VM1 GROUP BY DIM1

ce qui va diminuer drastiquement le temps de la requête et les ressources consommées.

Cependant la question que je me pose c'est : est-ce qu'Oracle pense à se baser sur VM1 lorsqu'il créé VM2 ? Et sur VM2 lorsqu'il créé VM3 ? D'après mes tests non, donc c'est aussi coûteux pour moi de construire chaque VM alors que seule VM1 devrait me coûter du temps.

Exemple :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

create VM1 as select DIM1, DIM2, sum(KPI) FROM TABLE_DETAIL GROUP BY DIM1, DIM2

-> 1mn

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

select DIM1, sum(KPI) FROM TABLE_DETAIL GROUP BY DIM1

-> 0.05s

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

create VM2 as select DIM1, sum(KPI) FROM TABLE_DETAIL GROUP BY DIM1

-> 45s

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

select sum(KPI) FROM TABLE_DETAIL GROUP BY DIM1

-> 0.01s

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

create VM3 as select sum(KPI) FROM TABLE_DETAIL GROUP BY DIM1

-> 25s

Alors que si je force Oracle à exploiter les VM crées au niveau n-1 pour calculer le niveau n:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

create VM1 as select DIM1, DIM2, sum(KPI) FROM TABLE_DETAIL GROUP BY DIM1, DIM2

-> 1mn

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

create VM2 as select DIM1, sum(KPI) FROM VM1 GROUP BY DIM1

-> 0.5s

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

create VM3 as select sum(KPI) FROM VM2 GROUP BY DIM1

-> 0.1s

On voir donc bien qu'Oracle n'utilise pas la ré-écriture de requêtes pour créer les VM. De plus comme je n'arrive pas à afficher le plan d'exécution utilisé pour lé création de VM, je ne peux pas en être sûr. D'ailleurs il semble qu'il soit impossible d'obtenir un plan d'exécution pour tous les ordres CREATE, que ce soit un create VM ou un create table. Est-ce normal ?

En gros 2 questions :
- comment afficher le plan d'exécution utilisé par Oracle dans un Create Table ou un Create VM ?
- comment forcer Oracle à recalculer les VM à partir des autres VM ?

Pour la dernière question, je me dis que c'est peut-être impossible car si VM2 est calculée à partir de VM1 qui elle même a été calculée à partir de table_detail, Oracle sera incapable de comprendre que VM2 est aussi valable que VM1 pour ré-écrire des requêtes portant sur table_detail.

Merci

[orafrance]

l'idéal c'est de tracer la session et appliquer un tkprof. Sinon... j'vois pas pourquoi Oracle réécrirait quoi que ce soit alors qu'une MV n'est ni plus ni moins qu'une table avec des fonctionnalités de rafraichissement. Une MV n'est pas une vue

[nuke_y]

Certes mais pour la trace il faut plus de droits que ce que j'ai. Je vais devoir aller chasser du DBA moi...

Sinon je suis d'accord qu'une VM n'est pas une vue mais la question n'est pas de ré-écrire la requête lors de la sélection des données de la VM mais lors de la sélection des données de la table de détail pour créer la VM.

Quand je fais un select * from ma_table, la requête est ré-écrite en utilisant les VM, mais quand je fais un create MV mv_ma_table as select * from ma_table la requête n'est pas ré-écrite. Et c'est ça que je trouve bizarre.

[orafrance]

Une des contraintes du query rewrite c'est que ça doit être une vue simple, donc sans regroupement notamment il me semble.

Il y a une procédure pour vérifier la complexité des MV mais son nom m'échappe

Sinon, voila un peu de lecture : http://download.oracle.com/docs/cd/B...v.htm#i1006458

[nuke_y]

Hum, c'est possible que ce soit une histoire de complexité mais ça m'étonne car pourquoi ré-écrire une requête lorsqu'elle sert à ramener des données mais ne pas la ré-écrire quand elle sert à créer une vue matérialisée ? Sauf si effectivement Oracle refuse de créer une vue matérialisée à partir d'une autre VM.

Je pense qu'en fait pour tous les ordres CREATE, Oracle s'interdit le Query Rewrite.

Merci pour le lien, je vais voir ce que j'y trouve.

PS : les regroupements sont acceptés pour le query rewrite, j'en fais en ce moment

[orafrance]

au temps pour moi