Skip to content

Instantly share code, notes, and snippets.

@lud

lud/update-spatial-indexes.sql

Created June 19, 2014 15:18
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save lud/e40ee6288a1e72cd3baf to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save lud/e40ee6288a1e72cd3baf to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. lud created this gist Jun 19, 2014.
    23 changes: 23 additions & 0 deletions update-spatial-indexes.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,23 @@
    DECLARE
    TYPE curs IS REF CURSOR;
    c curs;
    q varchar2(200);
    idx VARCHAR(200);
    tab VARCHAR(200);
    col VARCHAR(200);
    BEGIN
    q := 'select index_name,table_name,column_name from user_sdo_index_info';
    OPEN c FOR q;
    LOOP
    FETCH c into idx,tab,col;
    EXIT WHEN c%NOTFOUND;
    SYS.dbms_output.put('Supression de l''index ' || idx || ' sur ' || tab);
    execute immediate 'drop index ' || idx;
    SYS.dbms_output.put_line(' ok');
    SYS.dbms_output.put('Création de l''index ' || idx || ' sur ' || tab);
    execute immediate 'create index ' || idx || ' on ' || tab || '(' || col || ') INDEXTYPE IS mdsys.spatial_index';
    SYS.dbms_output.put_line(' ok');
    END LOOP;
    CLOSE c;
    END;