Gestion des Tables d'Object

**Laurent Dardenne** · 08/04/2004, 13h21

Bonjour,
j'ai un petit soucis sur la gestion/compréhension des tables d'Objet
dans le contexte suivant :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
 
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
 
Create or Replace Type Repertoire AS OBJECT (
 ID NUMBER,
 Propriétaire Ref Disque, 
 Nom Varchar2(255),
 Pére Ref Repertoire
 );
 
Create Table Repertoires of Repertoire(
Pére With ROWID SCOPE IS Repertoires,
Propriétaire With ROWID SCOPE IS Disques,
CONSTRAINT PK_Repertoires primary key(ID)
);
 
Alter Table Repertoires
 ADD CONSTRAINT FK_Repertoires_PERE FOREIGN KEY (Pére) 
  REFERENCES Repertoires ON DELETE CASCADE;
 
create index I_Pere_Repertoires
on REPERTOIRES(pére);

Alors que la table est une table d'objet ( Row object), j'ai l'erreur suivante :

Code :

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

1
2
3
4
5
 
ORA-02332 cannot create index on attributes of this column
Cause: An attempt was made to create an index on an attributes of an object
type column.
Action: Do not specify the index on the attribute.

Si j'inverse l'ordre de création, ie l'index avant la contrainte je n'ai pas de msg d'erreur.
La premiére démarche me semble théoriquement valide ?
Quelqu'un a-t-il une explication ?

Merci

**Marc Musette** · 08/04/2004, 14h32

Code :

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

1
2
3
4
 
Alter Table Repertoires 
 ADD CONSTRAINT FK_Repertoires_PERE FOREIGN KEY (Pére) 
  REFERENCES Repertoires ON DELETE CASCADE;

tu mets une foreign key qui référence la table sur laquelle tu crées ta foreign key ???

**Laurent Dardenne** · 08/04/2004, 17h57

Oui, c'est une relation pére-fils.
J'essaie de reproduire ce qu'il est possible de faire en relationnel :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
( générée sous AMC )
 
create table REPERTOIRE  (
   OID                  INTEGER                          not null,
   REPNOM               VARCHAR2(255)                    not null,
   NOMUNITE             VARCHAR2(255)                    not null,
   PERE                 INTEGER,
   constraint PK_REPERTOIRE primary key (OID),
);
 
alter table REPERTOIRE
   add constraint FK_REPERTOI_PERE_REPERTOI foreign key (PERE)
      references REPERTOIRE (OID);

**Marc Musette** · 09/04/2004, 11h22

effectivement ...

voici ce que j'ai trouvé dans la doc

Restrictions on Foreign Key Constraints
None of the columns in the foreign key can have datatype LOB, LONG, LONG RAW, VARRAY, NESTED TABLE, OBJECT, BFILE, or REF, or TIMESTAMP WITH TIME ZONE. However, the primary key can contain a column of TIMESTAMP WITH LOCAL TIME ZONE.

or ta colonne est bien de type object non ?

**Laurent Dardenne** · 09/04/2004, 12h36

Bonjour,
la colonne PERE est de type pointeur =REF= RAW.

Si je modifie son type en Objet, le message est

Code :

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

1
2
3
4
5
 
ORA-02267 column type incompatible with referenced column type
Cause: The datatype of the referencing column is incompatible with the
datatype of the referenced column.
Action: Select a compatible datatype for the referencing column.

Ce message me renvoie effectivement vers la restriction que tu cites.
Alors que l'instruction de déclaration de la FK d'origine ne me renvoie aucune erreur.

C'est vrai que cela semble incohérent de placer une FK sur une colonne qui est déjà une référence.
La clause Scope IS permet une contrainte d'intégrité référentielle "partiel".

Ici l'objectif est de gérer les contraintes d'intégrité référentielle (supprimer tous les objet dépendants ).
On peut effectivement les coder avec des triggers.

Le RO est donc une avancée dans le passé

Mais le PB vient de l'index.
j'ai un peu avancé de ce coté là.
Si je déclare l'index puis la contrainte cela fonctionne, enfin je n'ai pas de msg d'erreur ie 'ça tombe en marche'

, mais dans OEM la colonne nommée 'pére' n'est pas présente dans la liste des colonnes de l'index en question !

Si je supprime la contrainte et fait un refresh dans OEM, la colonne 'pére' réapparait dans la liste des colonnes de l'index en question.

Ceci est confirmé par le résultat de l'exécution d' explain plan sur une requête utilisant la colonne pére.
Avec la contrainte

Code :

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

1
2
3
 
   TABLE ACCESS FULL	REPERTOIRES
   TABLE ACCESS BY INDEX ROWID	REPERTOIRES

Sans la contrainte

Code :

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

1
2
3
 
   TABLE ACCESS BY INDEX ROWID	REPERTOIRES
    INDEX RANGE SCAN	I_PERE_REPERTOIRES

On peut donc déclarer une contrainte FK sur une colonne de type REF d'une table d'objet mais à partir de là on ne peut plus référencer cette colonne dans un index ?

Voilà, j'essaie de comprendre comment fonctionne le RO sous Oracle, qu'est-ce que l'on peut faire avec, connaitre ses avantages et ses inconvénients...
Merci

**Marc Musette** · 09/04/2004, 15h23

PRIMARY KEY constraints cannot be specified for REF columns

Application Developer's Guide - Object-Relational Features / CHAPITRE 2

??? je t'avoue que j'ai du mal à m'y retrouver ...

**helyos** · 09/04/2004, 15h27

Euh la je suis d'accord j'ai du mal à suivre.

Tu dis que une fois que tu as créer l'index, si tu rajoutes la contrainte, la colonne disparait de l'index???

Peut tu vérifier non pas avec OEM mais directement à la mimine dans les tables USER_TABLES, USER_INDEX, user_ind_columns

**SheikYerbouti** · 09/04/2004, 16h26

Envoyé par Laurent Dardenne

...On peut donc déclarer une contrainte FK sur une colonne de type REF d'une table d'objet mais à partir de là on ne peut plus référencer cette colonne dans un index ?
...

Par contre, je crois qu'il est tout à fait possible de créer un index composite sur les colonnes de l'objet...

**Laurent Dardenne** · 10/04/2004, 21h35

Salut,

Envoyé par Marc Musette

PRIMARY KEY constraints cannot be specified for REF columns

Application Developer's Guide - Object-Relational Features / CHAPITRE 2

Effectivement Marc, si je déclare une PK sur une colonne REF Oracle me parle du pays de l'Objet d'une maniére trés claire ;-).
.. mais je n'ai pas de pb de PK.

Cette Doc je ne l'ai pas encore lu en long et en large, mais je l'ai lu en long dans un premier temps :-)
Et pour simplifier l'apprentissage du RO, uniquement la doc de la 8i. Imagine avec les possibilités de la 9i ...

Pour moi cette doc "Application Developer's Guide - Object-Relational Features " présente le RO sous les meilleurs jours.
Ce que j'essaie de faire n'est pas complexe, ce domaine est pour moi( nous ? ), simplement "Terra incognita".

Envoyé par Marc Musette

??? je t'avoue que j'ai du mal à m'y retrouver ...

Envoyé par helyos

Euh la je suis d'accord j'ai du mal à suivre.

Je vous comprends.Je vous rappelle le nombre de ligne du code d'origine : 16.

Mon schéma de base :
Un disque contient 0-N Répertoire
Un répertoire contient 0-N répertoires-fils
Un répertoire contient 0-N Fichiers
J'ai pris ce cas simple, qui s'avére "pas simple" à mettre en oeuvre en RO. ( ie pas les compétences/connaisances ?)
J'utilise la possibilité du CONNECT BY pour gérer un arbre hiérarchique.
J'essaie d'optimiser l'accés aux données par la création d'index sur des tables d'objets.

Est-ce moi qui fait n'importe quoi ?
OUI : Sur la contrainte FK d'une colonne de type REF, effectivement j'ai un doute.
Si je demande de l'aide sur ce forum c'est justement pour le lever ce doute (gestion des contraintes d'intégrité en RO ).

OUI : Oracle me permet de faire du "spaghetti sauce RO" ? DBA préparez vos mouchoirs ! ( les pro ont du stocke :-) )

NON : comment se fait il que ces problématiques de base ne soient pas clairement explicités dans la documentation ?
Le RO c'est fantastique ! ( create view RO as select Marketing ... )
...

**Laurent Dardenne** · 10/04/2004, 23h00

Envoyé par helyos

Tu dis que une fois que tu as créer l'index, si tu rajoutes la contrainte, la colonne disparait de l'index???
Peut tu vérifier non pas avec OEM mais directement à la mimine dans les tables USER_TABLES, USER_INDEX, user_ind_columns

J'ai modifié la déclaration d'index :

Code :

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

1
2
3
 
Create index I_NomPereOID_Repertoires
 on REPERTOIRES(Nom,pére);

Table crée
Index crée
Contrainte non déclarée "Alter Table Repertoires ADD CONSTRAINT FK_Repertoires_PERE FOREIGN KEY (Pére)
REFERENCES Repertoires ON DELETE CASCADE " .

Code :

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

1
2
3
4
5
6
7
8
 
SQL> select COLUMN_NAME From user_ind_columns 
 Where table_name='REPERTOIRES' and  INDEX_NAME='I_NOMPEREOID_REPERTOIRES';
 
COLUMN_NAME
--------------------------------------------------------------------------------
NOM
PÉRE

La colonne PÉRE est présente
( la visu sous OEM est en phase)

Code :

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

1
2
3
4
5
 
SQL> Alter Table Repertoires
  2   ADD CONSTRAINT FK_Repertoires_PERE FOREIGN KEY (Pére) 
  3    REFERENCES Repertoires ON DELETE CASCADE;
Table modifiée.

Code :

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

1
2
3
4
5
6
7
8
 
SQL> select COLUMN_NAME from user_ind_columns where table_name='REPERTOIRES' and  INDEX_NAME='I_NOMP
EREOID_REPERTOIRES' ;
 
COLUMN_NAME
--------------------------------------------------------------------------------
NOM
"PÉRE"

La colonne PÉRE est présente mais entre guillements.
( la visu sous OEM n'est pas en phase, la colonne Pére est absente)

Code :

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

1
2
3
 
SQL> Alter Table Repertoires DROP CONSTRAINT FK_Repertoires_PERE;
Table modifiée.

Contrainte "FK_... " supprimée.

Code :

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

1
2
3
4
5
6
7
8
 
SQL> select COLUMN_NAME from user_ind_columns where table_name='REPERTOIRES' and  INDEX_NAME='I_NOMP
EREOID_REPERTOIRES';
 
COLUMN_NAME
--------------------------------------------------------------------------------
NOM
PÉRE

La colonne PÉRE est présente
( la visu sous OEM est en phase, la colonne Pére est présente )

**Laurent Dardenne** · 10/04/2004, 23h47

Envoyé par SheikYerbouti

Envoyé par Laurent Dardenne

...On peut donc déclarer une contrainte FK sur une colonne de type REF d'une table d'objet mais à partir de là on ne peut plus référencer cette colonne dans un index ?
...

Par contre, je crois qu'il est tout à fait possible de créer un index composite sur les colonnes de l'objet...

Je confirme SheikYerbouti.

Donc pour en revenir à mon PB
Peut on déclarer une contrainte FK sur une colonne de type REF d'une table d'objet ?
Et peut on référencer cette "colonne contrainte" dans un index ?

QQ peut-il vérifier ce comportement sur un serveur ?
Merci

**Marc Musette** · 13/04/2004, 11h29

en tout cas la doc n'est pas la meilleure qui soit ...

même Tom Kyte annonce clairement qu'il n'utilise pas les types objets pour stocker des données ... il préfère les bonnes vieilles tables et n'utilise que les types etc au niveau programmation

et ce problème de guillements ... ça c'est vraiment fort ...

bon courage Laurent !!!

**Laurent Dardenne** · 13/04/2004, 13h15

Envoyé par Marc Musette

en tout cas la doc n'est pas la meilleure qui soit ...

A mon avis il manque une compilation des informations relative au RO qui sont disséminées sur l'ensemble de la documentation.

Envoyé par Marc Musette

même Tom Kyte annonce clairement qu'il n'utilise pas les types objets pour stocker des données ... il préfère les bonnes vieilles tables et n'utilise que les types etc au niveau programmation

Oui, j'ai lu ça sur le site Ask Tom. Il préconise d'utiliser le relationnel pour la persistence des données, et les objets (notamment les vues Objet) pour leur manipulation.
Et le coté performance n'est pas négligeable ...

Au vu des petits soucis que je rencontre, je vais donc suivre ses conseils

Envoyé par Marc Musette

et ce problème de guillements ... ça c'est vraiment fort ...

Est-ce courant sur Oracle ce type "d'effet de bord" et ce sans message d'avertissement ? à moins que cela soit un bug ?

Merci

Pour info :
Dans un premier temps je souhaitais optimiser ce curseur:

Code :

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

1
2
3
4
5
6
 
   Cursor Parcourt_Fils_Pére(Départ Repertoire) IS
    Select Value(R) From Repertoires R
     Where R.nom<>'ROOT' 
     Connect by prior R.Pére=ref(R)
     Start with Value(R)=Départ;

Mais dans la déclaration de l'index je ne savais pas comment spécifier l'objet lui même ( ie la ligne d'une table d'objet )

Code :

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

1
2
3
 
Create index I_PereOID_Repertoires
on REPERTOIRES(pére, ?);

SELF ne fonctionne que sous PL/SQL.
Et par hasard j'ai trouvé cela dans la doc de SQL*Loader :
il existe une pseudo colonne nommée "SYS_NC_OID$" qui référence l'objet lui même.

On peut donc améliorer la requête "Connect by prior R.Pére=ref(R) " d'une table d'objet par la déclaration de l'index suivant :

Code :

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

1
2
3
 
Create index I_PereOID_Repertoires
on REPERTOIRES(pére, SYS_NC_OID$);

These objects are stored in tables, known as object tables, that have columns
corresponding to the attributes of the object. The object tables have an additional
system-generated column, called SYS_NC_OID$, that stores system-generated
unique identifiers (OIDs) for each of the objects in the table. Columns in other tables
can refer to these objects by using the OIDs.

**orafrance** · 13/04/2004, 13h20

Attention, CONNECT BY PRIOR n'est intéressant que si on cherche une hiérarchie qui va plus loin que père-fils, dans le cas contraire il vaut mieux joindre la table avec elle-même

**Laurent Dardenne** · 13/04/2004, 13h29

Merci,
mais la profondeur peut être importante
"C:\Documents and Settings\Laurent\Application Data\Adobe\Acrobat\6.0\Preferences"
C'est pour ça que je tente d'optimiser le recherche récursive, mais c'est pas gagné !

**helyos** · 13/04/2004, 17h18

Apres observation pour les guillemets.

cela viens du fait que tu utilises un é dans le nom de ta table ce que personnellement je te deconseille fortement ( de plus meme Oracle ne préconise pas l'utilisation de caractère accentué)

pour une table c'est
30 caractères (de a jusqu'a z, et tout les chiffres)
doit commencer par une lettre
et les qq caractères spéciaux.

Donc Oracle à du rajouter implicitement les " pour etre en mesure d'interpreter le é.

De plus OEM à quelque probleme avec les accents et les guillemets (il en rajoute autour des noms d'objets donc "Pére" est reconnue par ""Pére"" ..., c'est pour cela que je t'avais demander de regarder à la mimine.

Si tu veux corriger le pb renomme ta colonne en PERE et non pas Pére

De plus si tu dois créer une table je te conseilles fortement de le faire à la main car OEM n'étant "que" (pardon Oracle) un gros générateur de code, il entoure tout les noms d'objets de " ce qui peut poser problemes suite à l'utilisation de caractères spéciaux, voir des pb de majuscule minuscule.

Pour ce qui est de ton autre probleme, pourrais tu donner l'état d'avancement? As tu changer ta méthode pour utiliser la bonne vielle méthode donnée par Tom, ou as tu conservés ton objet?

De plus qu'est ce qui te pose probleme dans l'optimisation de la requete?

**Laurent Dardenne** · 13/04/2004, 18h37

Envoyé par helyos

Apres observation pour les guillemets.

cela viens du fait que tu utilises un é dans le nom de ta table ce que personnellement je te deconseille fortement ( de plus meme Oracle ne préconise pas l'utilisation de caractère accentué)
Si tu veux corriger le pb renomme ta colonne en PERE et non pas Pére

Effectivement pour SqlLoader j'avais renommé mes tables comportant des accents, mais je n'avais pas modifié les noms de champs.
Malheureusement le probléme persiste.

Code :

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

1
2
3
4
5
6
7
8
9
 
SQL> select COLUMN_NAME From user_ind_columns 
  2   Where table_name='REPERTOIRES' and  INDEX_NAME='I_NOMPEREOID_REPERTOIRES'; 
 
COLUMN_NAME
--------------------------------------------------------------------------------
NOM
"PERE"
SYS_NC_OID$

En regardant dans OEM la colonne nommé 'SYS_NC_OID$' est de type RAW mais la colonne 'PERE' de type REPERTOIRE ( donc objet ).
Je suppose qu'OEM ne fait pas de distinguo entre un objet et une référence d'objet.
Et l'extrait de la doc Oracle que cite Marc Musette, parle de restriction sur une colonne de type OBJET et REF. %-(
Il y a une contradiction entre la doc et la possibilité de déclarer ce type de FK et lors de la création d'un index Oracle me signale une erreur

Envoyé par helyos

De plus si tu dois créer une table je te conseilles fortement de le faire à la main car OEM

Tout est déjà scripté.

Envoyé par helyos

Pour ce qui est de ton autre probleme, pourrais tu donner l'état d'avancement? As tu changer ta méthode pour utiliser la bonne vielle méthode donnée par Tom, ou as tu conservés ton objet?

De plus qu'est ce qui te pose probleme dans l'optimisation de la requete?

Pour le moment, je reste sur les tables objets ensuite j'utiliserais les vues objet.

Le pb c'est l'insertion de masse des données, pour chaque ligne à insérer via SQLoader je dois rechercher le point d'insertion dans l'arbre( ie l'arborescence d'un disque dur). De plus les données en entrée ne sont pas ordonnée.
Je pense ajouter une colonne qui contient le chemin complet pour chaque noeud, c'est pas trés élégant .
J'ai regardé du coté du site SQL de Frédéric Brouard sur les arbres intervallaires mais dans un premier temps je regarde les possibilités de l'instruction 'Connect by'.

**helyos** · 13/04/2004, 19h10

Oki je commence à mieux cerner ton probleme (pour ce qui est de l'index je suis en pleine recherche, je viens de trouver le bug 2451999 qui est en relation avec ce probleme

ORA-2332 creating TEXT index on XDBURITYPE / URITYPE / HTTPURITYPE column

Le problème c'est que c'est censé etre corrigé en 9.2.0.4

)

Bon donc ton probleme c'est l'insertion en masse des chemins .

Pourrais tu donner un exemple du fichier que tu importes?

Car il y a aurait peut etre moyen de faire les importations autrement

**Laurent Dardenne** · 13/04/2004, 20h13

Merci pour l'info du bug

voici qq exemples

nom de domaine; nom de machine;FullPath;FileName;TYPE ( Fichier,Repertoire)

Code :

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

1
2
3
4
5
6
7
8
9
 
Dom_Test;AMD2800;C:\Delphi;;R
Dom_Test;AMD2800;C:\Delphi7;;R
Dom_Test;AMD2800;C:\Dev_ORA;;R
Dom_Test;AMD2800;C:\Delphi\Aprg\0-nt\;jgntcmps.zip;F 
Dom_Test;AMD2800;C:\Delphi\Aprg\0-nt\;mailslot-Messanger.zip;F 
Dom_Test;AMD2800;C:\Delphi\Aprg\0-nt\;Nt-getusergroup.txt;F 
Dom_Test;AMD2800;C:\Delphi\Aprg\0-nt\;ntuser.zip;F 
Dom_Test;AMD2800;C:\Delphi\Aprg\0-nt\;old-citrix.zip;F

J'utilise une table temporaire sur laquelle j'ai posé un trigger qui se charge de créer les objets.
N'ayant pu les charger sous SQL*Loader ...

Code :

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

1
2
3
4
5
6
7
8
 
Create Table DispatchObjet(
 DisqueDomain		Varchar2(15),
 DisqueMachine	Varchar2(15),
 PathNom Varchar2(255),
 FileNom Varchar2(255),
 ObjetType Character
);

Je peux créer un soft sous Delphi pour gérer cela "plus facilement", mais je préfére une solution Oracle et script NT.
On n'a pas tjr un compilo sous la main.

**helyos** · 13/04/2004, 20h45

Pourquoi ne pas tout faire en PL/SQL avec une petite procédure?
Moi je pense que ca serait le plus efficace. Tu charges ton fichier avec SQL Loader (ou utl_file) et tu traites chaques chemin avec du pl.

Gestion des Tables d'Object

SQL Oracle

Discussions similaires

Partager

Partager