I have a table with this definition:


Geoloc It is a spatial column, but I don't see it in the ALL_SDO_INDEX_INFO table


PARALLEL 10 ;   

Has something to do? or maybe my column is missing something?


The ALL_SDO_INDEX_INFO shows spatial indexes on tables in all schemas on which you have SELECT access. If you access it from a schema that cannot see the underlying table, then you will not see the index either.

From what user are you doing the creation ? Are you connected as MYSCHEMA to do that ? And from what user are you reading ALL_SDO_INDEX_INFO ? Can you post a full log of the operations you do including database connects ?

To answer your question: no, the PARALLEL clause has nothing to do with what you observe. Then again, as set it means that the database will fire 10 parallel processes to build the index. Does your server have sufficient capacity for that (meaning: at least that many available cores) ? If not, the parallel processing is very likely going to degrade performance as those processes contend for CPUs with heavy process context switching and possible contention on the disk subsystem.

  • I do have full permissions to the table, the funny thing is, I run [ora2pg]ora2pg.darold.net in this table and, based on the output, he can find the index definition; I guess I have to find another oracle metainfo table – Naty Bizz Feb 22 '18 at 12:54
  • What about ALL_TABLES ? Does this show the table ? And ALL_INDEXES ? I assume you query those when connected as a different user than the actual owner of the tables ? Was the index created by the owner of the table ? Oracle allows you to create an index on a table in a different schema: you then have U1.T1 but U2.I1 on that table. I see no reason for doing that, but I imagine it could have side effects. – Albert Godfrind Feb 23 '18 at 13:33
  • I see the table with ALL_TABLES and ALL_INDEXES; now, searching a little more about this issue, I found this and I performed this query select * from all_indexes where DOMIDX_opstatus is not null and DOMIDX_opstatus != 'VALID'; and my mytable appears... now I have to find the way to make the domain index valid – Naty Bizz Feb 23 '18 at 13:55
  • If the index is marked as not valid, that means the creation failed with some exception. You need to drop it and recreate it. Note that the drop may fail too. If so just use the FORCE option. – Albert Godfrind Feb 24 '18 at 15:09

If you want to list all tables with spatial columns (SDO_GEOMETRY type), you can do it through ALL_TAB_COLUMNS table, regardless if a spatial index has been set or not:

SELECT owner, table_name
FROM all_tab_columns
WHERE data_type = 'SDO_GEOMETRY'
  AND owner <> 'MDSYS';

Note 1: MDSYS is a system SCHEMA you should ignore in this context

Note 2: This will list VIEW's with spatial columns as well. If you want, you can filter this with a JOIN with the ALL_TABLES table.

Note 3: As Albert said, tables like ALL_XXX only list objects that the user with which you're connecting to the database as SELECT access.

  • I can find my 'mytable' table with the query you provided, but I can't find a way to find the spatial index definition – Naty Bizz Feb 22 '18 at 12:49
  • 1
    My proposed solution was for the aim of listing all tables (and views) with spatial columns, if that was what you were aiming, regardless if a spatial index was set. If you want to be sure if the table as a spatial index defined, try this: SELECT * FROM all_indexes WHERE ityp_name = 'SPATIAL_INDEX'; If the spatial index is set, you should be able to get all relevant information about that index through ALL_SDO_INDEX_METADATA table. – Carlos MSF Feb 22 '18 at 13:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.