Discussion :

[Lancelot du Lac35]

Bonjour à tous,

Voila un problème que je n'arrive pas à résoudre. Pour schématiser :

Code :

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

1
2
3
select TABLE_NAME
from user_tab_columns
where DATA_TYPE = 'SDO_GEOMETRY'

résultat :
TABLE_A
TABLE_B
TABLE_C
TABLE_D

Code :

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

1
2
select *
from user_indexes

résutlat
TABLE_A SPATIAL_INDEX
TABLE_A null
TABLE_B null
TABLE_B SPATIAL_INDEX
TABLE_C SPATIAL_INDEX
TABLE_C null


Résultat souhaité dans ma requête :
TABLE_A
TABLE_B
TABLE_C
TABLE_D

1. Dans oracle j'ai la table user_tab_columns qui contient tout les noms des colonnes de chaque table de mon schéma.

Je fais cette requête pour avoir uniquement les tables dont qui possèdent une colonne de stype SDO_GEOMETRY :

Code :

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

1
2
3
select TABLE_NAME
from user_tab_columns
where DATA_TYPE = 'SDO_GEOMETRY'

2. J'ai également une table user_indexes qui contient tout les index de mon schéma.

Dans cette table je peux avoir plusieurs lignes pour le même nom de table. Par exemple si ma table à plusieurs indexs (un index spatial, un index de clé primaire ...)

3. Résutlat souhaité : j'aimerai par une requête dont je mettrai le résultat dans une vue pouvoir lister les tables qui ne possèdent pas d'index de type SPATIAL_INDEX (valeur stockée dans une colonne de user_indexes).

Le problème que je rencontre c'est que si je fais une simple jointure, il me ramène même les tables qui ont cet indexs puisque dans la table user_tab_columns j'ai autant de ligne que d'indexs.

[skuatamad]

Avec ALL_IND_COLUMNS vous devriez pouvoir affiner votre recherche

[Lancelot du Lac35]

Pas vraiment parce qu'avec cette table je n'ai pas le type de l'index.

Et moi précisement je veux lister les tables qui ont champs de type SDO_GEOMETRY mais qui ne sont pas indexés. Le mieux serait de stocker le résultat dans une vue.

[skuatamad]

Avec ce jeu de test :

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
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
CREATE TABLE cola_markets (
  mkt_id NUMBER PRIMARY KEY,
  name VARCHAR2(32),
  shape MDSYS.SDO_GEOMETRY);
 
INSERT INTO USER_SDO_GEOM_METADATA 
  VALUES (
  'cola_markets',
  'shape',
  MDSYS.SDO_DIM_ARRAY(   -- 20X20 grid
    MDSYS.SDO_DIM_ELEMENT('X', 0, 20, 0.005),
    MDSYS.SDO_DIM_ELEMENT('Y', 0, 20, 0.005)
     ),
  NULL   -- SRID
);
 
CREATE TABLE cola_markets2 (
  mkt_id NUMBER PRIMARY KEY,
  name VARCHAR2(32),
  shape MDSYS.SDO_GEOMETRY);
 
INSERT INTO USER_SDO_GEOM_METADATA 
  VALUES (
  'cola_markets2',
  'shape',
  MDSYS.SDO_DIM_ARRAY(   -- 20X20 grid
    MDSYS.SDO_DIM_ELEMENT('X', 0, 20, 0.005),
    MDSYS.SDO_DIM_ELEMENT('Y', 0, 20, 0.005)
     ),
  NULL   -- SRID
);
 
drop index cola_spatial_idx;  
 
CREATE INDEX cola_spatial_idx
ON cola_markets(shape)
INDEXTYPE IS MDSYS.SPATIAL_INDEX;

La requête suivante semble correspondre au besoin :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
select table_name, column_name
  from user_tab_columns t
 where data_type = 'SDO_GEOMETRY'
   and not exists (select *
                     from user_indexes i
                     join user_ind_columns ic 
                       on ic.index_name  = i.index_name
                    where i.index_type   = 'DOMAIN'
                      and ic.column_name = t.column_name
                      and ic.table_name  = t.table_name
                   )

A noter qu'il 'y a pas de valeur SPATIAL_INDEX dans la colonne INDEX_TYPE de USER_INDEXES

[Lancelot du Lac35]

Et bien écoute skuatamad, c'est un grand merci, car c'est exactement ce qu'il je souhaitais avoir comme résultat.

Sinon pour info dans la colonne ITYP_NAME de la table user_indexes tu as l'information SPATIAL_INDEX.