ORA-13226: Interface not supported without a spatial index.
I hate this error. Someone would give me a new Oracle dump file. I’d try to view the spatial data and boom – ORA-13226. If I was lucky, there was only a couple of tables in it. Chances are, there were hundreds of tables in it. Sometimes I’d need to add the reference to the USER_SDO_GEOM_METADATA view, sometimes I wouldn’t. Sometimes the index would already exist and just need to be rebuilt.
Regardless of the scenario, it would often involve a lot of SQL – or messing around to try and remember the SQL. Every time this would pop up I would think to myself, “I really should write an app…”. Well I finally did.
So, here is my announcement. The RADE Spatial Indexer is just about ready for beta. Initially this app does three things.
- It will list tables and views with geometry columns that are not part of the USER_SDO_GEOM_META data view and help you add them.
- It will list spatial tables without indexes and help you create them.
- It will list spatial tables with indexes and help you re-index them.
No more exporting queries to text files and using a macro to edit them to build your SQL for rebuilding those indexes. I’ve tried to add some nice friendly helpers, such as a button that will suggest the spatial metadata settings for a table based on the existing meta data records. The ability to mass create or rebuild indexes is also been a fantastic helper.
I’m recruiting brave individuals for a small closed beta program that will begin in about two weeks. If you are interested in being a tester please e-mail indexer (at) landorIS.com and let me know. Remember, this is a beta – so I really do not suggest you run this on a production, or important server. That said, I have been using it in various states on my own ‘production’ development Oracle server.
As an incentive for helping me out. If you submit feedback (be it a bug report or feature request) – I will set you up with a free license once we release 1.0. I know you’re hooked and want to sign up. I know it. Just in case you need a little more, here is a few screen caps..
Look at how easy it could be to define metadata. Click the suggest button and it will iterate through the metadata settings for other spatial tables in the schema. Once you have one row defined, use the sync button to set all rows to the same values. Click process and its done.
Within seconds, have hundreds of spatial indexes being re-created…Creating new indexes is almost as easy.