Schema avec table multi-schema, es possible ?

Version imprimable

27/09/2006, 11h24
ludvax

Schema avec table multi-schema, es possible ?

Bonjour à la communauté,

J'ai un souci de conception sur lequel je sèche !

Voici le contexte :

J'ai des tables identique (avec des données différentes ;) ) sur différents schémas.
Jusqu'ici tout va bien !

On me demande maintenant de pouvoir accéder simultanément aux tables des différents schémas.

Ceci est sûrement un peu flou pour vous et ce n'est pas très facile de l'expliqué. Une illustration vos mieux qu'un long discours.

Illustration :

Actuellement:
SCHEMA_1 (TABLE_A1, TABLEA2...)
SCHEMA_2 (TABLE_A1, TABLEA2...)
SCHEMA_3 (TABLE_A1, TABLEA2...)

Futur:
SCHEMA_1 (TABLE_A1, TABLEA2...)
SCHEMA_2 (TABLE_A1, TABLEA2...)
SCHEMA_3 (TABLE_A1, TABLEA2...)
SCHEMA_4(
TABLE_A1 = SCHEMA_1.TABLE_A1 + SCHEMA_2.TABLE_A1 ,
TABLE_A2 = SCHEMA_1.TABLE_A2 + SCHEMA_2.TABLE_A2
)

Sachant que ces deux tables contiennent plusieurs centaine de milliers de ligne en moyenne sur chaque schémas, le problème est de taille :lol:
27/09/2006, 11h37
orafrance

à mon avis, le mieux c'est d'intégrer toutes les données dans une seule table, ce sera le plus performant.
27/09/2006, 11h41
sygale

Citation:

Envoyé par Fred_D

à mon avis, le mieux c'est d'intégrer toutes les données dans une seule table, ce sera le plus performant.

Cela va doubler le volume de données, une vue ne serait pas mieux ?
Niveaux des perfs, la vue utilisera les index des tables etc ..

Mais je conseille un union all plustot qu'un union qui fera un distinct ;)

Un test serait intéressant
27/09/2006, 11h52
orafrance

bah non, c'est en remplacement bien sûr... tu mets tout dans la même table avec des partitions à la place des schémas et une colonne pour gérer les habilitations éventuellement (le VPD c'est bieng :))
27/09/2006, 12h14
ludvax

Merci pour vos réponses si rapide.

Effectivement le plus simple serai de fusionné les tables des schémas.
Mais ce serai trop simple

Chaque utilisateur est dissocié pour des raisons de conception mais surtout pour les performances.
En effet, chaque table (2 tables dans chaque schéma) contient en moyenne 500 000 lignes !
La fusion de 6 schémas pour une table donnera environ : 3 000 000 lignes !

Est'il possible sous oracle de déclaré une table comme étant la fusion de plusieurs tables (sur différent schémas) ?
27/09/2006, 12h20
Oraman

une table ne peut avoir qu'un seul owner
27/09/2006, 12h32
ludvax

Citation:

Envoyé par Oraman

une table ne peut avoir qu'un seul owner

C'est justement l'un de mes problèmes !

Comment utiliser des tables de plusieurs owner ?

Puis, comment fusionner ces tables ?
27/09/2006, 12h39
Oraman

Citation:

Envoyé par Fred_D

bah non, c'est en remplacement bien sûr... tu mets tout dans la même table avec des partitions à la place des schémas et une colonne pour gérer les habilitations éventuellement (le VPD c'est bieng :))

la solution de FRED en utilisant les tables partitionnée me parait trés bien
27/09/2006, 12h53
ludvax

Je n'ai pas bien compris.

Pourriez vous m'expliqué plus en détails ?
27/09/2006, 14h00
orafrance

Citation:

Envoyé par ludvax

La fusion de 6 schémas pour une table donnera environ : 3 000 000 lignes !

Une vue qui fait un UNION ALL ce sera pareil :?

L'avantage d'une seule table c'est que tu partitionner et donc améliorer significativement les perfs :)
27/09/2006, 14h04
orafrance

Citation:

Envoyé par ludvax

Pourriez vous m'expliqué plus en détails ?

Tu crées une table avec une partition par schéma voir même un partitionnement plus fin.

pour plus d'info : http://download-uk.oracle.com/docs/c...i.htm#CNCPT112

Si on ne fait pas des requêtes tres compliquées, une vue UNION ALL avec une colonne spéciale pour récuperer le nom du schéma, c'est très performant aussi. En tout cas, c'est très vite testé ;)
Code:

1 2 3 4 5 6 7 CREATE OR REPLACE VIEW V_TABLES_A1 AS SELECT 'SCHEMA_1' as SCHEMA, TA1S1.* FROM SCHEMA_1.TABLE_A1 UNION ALL SELECT 'SCHEMA_2' as SCHEMA, TA1S2.* FROM SCHEMA_1.TABLE_A1 UNION ALL SELECT 'SCHEMA_3' as SCHEMA, TA1S3.* FROM SCHEMA_1.TABLE_A1 ...
L'optimiseur sait traiter ce genre de choses. D'experience, ça se complique quand on veux faire des requêtes tres complexes (impliquant une dizaine d'entités avec des jointures exotiques).

Et puis si le but est de n'acceder qu'a une partition de la "grosse table", il suffit de ne s'attaquer qu'à la table dans son schéma dédié ce qui sera encore plus performant, ou bien de faire un select sur la vue avec un "where SCHEMA = 'XXX'" et oracle fera l'équivalence.

Précision: Là ou ça va gagner en perf sur une table partitionnée c'est si tu met un index glogal (non partionné) sur une colonne sur laquelle tu dois faire une recherche globale. Sur le modèle de la vue (ou d'un index partitionné) il va faire autant de fois la recherche qu'il y a d'entité. Apres, c'est à toi de voir si c'est trés pénalisant, à mon avis si tu n'a que 6 schémas, ça doit etre acceptable.

27/09/2006, 14h58
orafrance

oui mais tu fais autant d'accés que d'UNION même si un seul est vraiment utile... et selon la version l'optimiseur réagit plus ou moins bien :?

Edit : attention aux indexes globaux, lors de la création d'une nouvelle partition ça peut concidérablement plomber les perfs :?

Je suis d'accord avec toi Fred_D,
le partitionnement est la meilleur solution.

Je sais aussi que pour les perfs on peut acceder directement à une parition dans une table partitionnée
Code:

1 2 3 4 5 6 7 8 9 10 11 12 CREATE TABLE my_command (artno number(10), cmddate date) PARTITION BY RANGE (cmddate) (PARTITION an_1999 VALUES LESS THAN (TO_DATE('01/01/2000','DD/MM/YYYY')) TABLESPACE data1, PARTITION an_2000 VALUES LESS THAN (TO_DATE('01/01/2001','DD/MM/YYYY')) TABLESPACE data1); select * from my_command partition (an_1999); select * from my_command partition (an_2000);
Bon courage

PS : Peux tu être plus précis sur les index globaux , c'est quoi exactement ?
Des indexs sur non-partitionné sur une table partitionnée ?

27/09/2006, 16h44
orafrance

un index local est un index partitionné selon la clé de partitionnement. Un index global n'est pas partitionné alors que la table l'est. Ca permet d'éviter de traverser toutes les partitions lorsque l'index n'a rien à voir avec le critère de partitionnement. Mais c'est plus couteux en écriture.
27/09/2006, 17h21
remi4444

Les index locaux peuvent considérablement écrouler les perfs lorsque il y a des jointures entre tables trés grosses et trés partitionnées (j'ai vu un cas extreme ou le facteur était de 100 environ).

Je suis d'accord sur le principe général de la table partitionnée, mais dans le cas précis, il ne faut peut etre pas être si dogmatique et peser le pour et le contre. La démarche est un peu différente de la démarche classique qui amène à choisir une table partitionnée. Classiquement, on a une table globale trés grosse puis on cherche un critère de partitionnement pour éviter les problèmes liés aux gros volumes. Là, il y a déja une partition "naturelle" avec je suppose, tout un jeu de requête existant qui accède localement à ces partitions (qui sont les tables en fait). Le choix ne peut donc à mon avis pas être fait sur une simple décision de principe mais est conditionner par les contrainte et les besoins de cette situation.

Pour résumer je dirais qu'il y a du bon et du moins bon dans les 2 solutions:

La vue avec "UNION ALL"
Avantages: très simple à mettre en oeuvre, pas de migration physique à effectuer, simplicité pour la gestion des droits (chacun son schéma), pas de modification des requêtes existantes donc pas de risque de régression.
Inconveniant:
Performances risquée si utilisation fréquente et complexes de requêtes globales, maintenance de la base et de l'application déclinée en autant de schémas

La table partitionnée
Avantages: Performances accrues pour les recherches globales en particulier par la possibilité de faire des index globaux (si besoin), maintenance de la base et de l'application mutualisée.
Inconveniant: Migration physique des données à effectuée, complexité de la gestion des droits, (il va faloir magouiller pour faire des vues modifiables restrintes sur un schéma intermédiaire et distribuer des droits pour ces vues sur les différents users), possible modification de l'existant quant à l'écriture de certaines requêtes.

Voilà les données du problème donc il reste plus que le choix à faire ;) Mon sentiment (peut etre me trompe-je...) est qu'il ne coute pas grand chose de tester la 1iere solution et de basculer sur la 2ieme si de gros pb de perfs apparaissent...
27/09/2006, 17h22
remi4444

question subsidiaire

A ce propos je me pose une question, il ne faut pas acheter une licence spécifique pour utiliser le partitioning ?
27/09/2006, 17h40
orafrance

si c'est payant en effet :)

Et il faut faire attention à la version parce qu'il y a pas mal de bugs corrigés selon la version.
27/09/2006, 17h41
orafrance

Citation:

Envoyé par remi4444

complexité de la gestion des droits, (il va faloir magouiller pour faire des vues modifiables restrintes sur un schéma intermédiaire et distribuer des droits pour ces vues sur les différents users), possible modification de l'existant quant à l'écriture de certaines requêtes.

Il suffit de faire une vue ;)

Et le VPD c'est bien aussi :)

Citation:

Envoyé par remi4444

Voilà les données du problème donc il reste plus que le choix à faire ;) Mon sentiment (peut etre me trompe-je...) est qu'il ne coute pas grand chose de tester la 1iere solution et de basculer sur la 2ieme si de gros pb de perfs apparaissent...

Moi je dirais le contraire :aie:

c'est tellement couteux (selon taille) qu'il vaut mieux prévoir le partitionnement tout de suite... le retour arrière est très simple ;)
27/09/2006, 19h35
remi4444

Citation:

Envoyé par Fred_D

c'est tellement couteux (selon taille) qu'il vaut mieux prévoir le partitionnement tout de suite... le retour arrière est très simple ;)

Je ne parlais pas du cout en perf, je parlais du coup en développement ;) Si les tables sont déja chargées, ça prend 10 sec de faire la vue et de tester...

Tu ne m'enlevera pas de l'idée que si le but est de gerer des droits d'accés séparés sauf dans quelques requêtes générales, il est un peu loufoque de concatener toutes les tables en une pour ensuite travailler à re-séparer des vue pour simuler des tables qu'on avait avant :aie:

Lorsqu'on édite un plan d'exécution d'une requête sur une telle vue, et surtout lorsqu'on l'exécute, on constate que l'optimiseur sait gerer la colonne de répartition et ne passe pas dans les parties inutiles de la vue, c'était d'ailleurs me semble-t-il une méthode documentée par oracle avant qu'il ne fournissent le partitioning packagé.

Je quite un peu la casquette DBA pour celle de chef de projet (bon ok je drevrais peut etre pas en arriver à de telles extrémités :aie: ), pour savoir dans quel contexte on se trouve:
- est-ce que l'existant est déja en production avec les tables chargées au max, des développement tout fait, des nombreux utilisateurs ?
- ou est-ce que c'est un nouveau développement qui part de 0 ?

Parceque selon qu'on soit dans le premier cas ou le second, la notion de risque n'est pas la même, et une solution "idéale" de manière générale peut s'avérer trop risquée et compliquée dans un cas particulier.
28/09/2006, 08h21
orafrance

et si ta table évolue ou que tu en ajoutes une, tu dois livrer la modif sur toutes les tables de manière synchroniser et recréer ta vue... une vue sur plusieurs tables c'est la solution court terme idéal coté projet qui voit pas plus loin que le bout de son nez :mouarf:. Moi, je suis DBA et donc garant de la qualité technique des solutions qui sont mises en oeuvre dans ma base, en tant que tel, je ne peux pas proposer autre chose que le partitionning et VPD (ou autre gestion des habilitations) qui facilitent largement la maintenance sur le long terme.

Ceci étant, je modére mon propos ;), le partitioning n'est pas une option gratuite et le CBO ne réagit pas toujours bien... conclusion : rien ne vaut un bon prototype et des tests rigoureux :mrgreen:
28/09/2006, 10h07
remi4444

Construire ou reconstruire une vue est trés leger puisque ce n'est qu'un stockage de requête.

Moi j'ai parfois été du coté DBA, parfois du coté dévellopement, parfois du coté gestion de projet, et je trouve qu'il faut éviter les positions trop dogmatiques si on veux rester crédible. Tout le monde à sa vérité dans ce genre de chose, le danger est que si en tant que DBA, tu t'en tiens à un avis de principe sans t'interresser au contexte (ou sans en donner l'impression), alors le chef de projet va penser de toi que tu reste dans ta bulle, et donc la fois suivante ne te demandera même pas ton avis. Tu sera étiqueté comme le ronchon d'en bas qui dit toujours non et étalera sa science :aie:. Entre l'innacceptable et l'idéal, il y a souvent quelques solution qui sont correctes meme si elle ne sont pas idéales sur tout les points, et pour moi la vue ne fait pas partie du domaine de l'innacceptable.
28/09/2006, 10h11
orafrance

j'ai pas dit que j'impose mon avis mais que je l'affirme ;)
11/10/2006, 10h26
ludvax

Merci a vous tous

Bonjours a tous (DBA, Développeur, chef de project...) ;) ,

je tiens à vous remercié pour vos avis et je constate que ma question est intéressante par l'intérêt que vous y avez porté.

Aussi je me devais vous informé de la décision prise (car elle ne me semble par être la meilleur, mais la plus facile a mettre place) :

Du fait de l'utilisation occasionelle (10*/an) et que cette fonction est dissocié de l'application principal, un serveur dédié sera mis en place.

votre avis m'intéresse !