Top 5 Recent Articles

ARTICLES CATEGORIES

Tip: Drop that Spatial Index!

We all make mistakes.

I made a good one this month.

I had to modify (DDL) a very, very large table to change the size of existing columns.

 ALTER TABLE xxxxxx MODIFY (YYYYYY VARCHAR2(100),
                            ZZZZZZ CHAR(1) /* FROM VARCHAR2(1) */
 );

I didn’t think that this would cause me any problems, and so I didn’t drop any indexes.

Was I ever wrong!

Checking why it was taking so long, I discovered that the spatial index was being modified:

 COLUMN usr            FORMAT A5
 COLUMN OSUSER         FORMAT A13
 COLUMN sid            FORMAT 999
 COLUMN serial#        FORMAT 9999999
 COLUMN program        FORMAT A13
 COLUMN process_blocks FORMAT A25
 COLUMN remain_blocks  FORMAT A25
 COLUMN est_finish     FORMAT A20
 COLUMN elapse_secs    FORMAT A10
 COLUMN remain_secs    FORMAT A10
 COLUMN message        FORMAT A75
 SELECT s.username AS usr,
        s.osuser,
        s.program,
        s.sid,
        s.serial#,
        round((sl.sofar / sl.totalwork)*100,1) || '% of ' || sl.units || ' Processed'       AS process_blocks,
        (100-round((sl.sofar / sl.totalwork)*100,1)) || '% of ' || sl.units || ' Remaining' AS remain_blocks,
        to_char(SYSDATE + (sl.time_remaining/(24*60*60)),'DD/MON/YYYY HH24:MI:SS')          AS est_finish,
        ROUND(sl.elapsed_seconds/60) || ':' || MOD(sl.elapsed_seconds,60)                   AS elapse_secs,
        ROUND(sl.time_remaining/60)  || ':' || MOD(sl.time_remaining,60)                    AS remain_secs,
        sl.message
   FROM v$session s,
        v$session_longops sl
  WHERE s.username = 'ELMS'
    AND s.sid     = sl.sid
    AND s.serial# = sl.serial#
    AND sl.totalwork <> 0
    AND ROUND((sl.sofar/sl.totalwork)*100, 2) <> 100;
 -- Result
 USR   OSUSER        PROGRAM        SID  SERIAL# PROCESS_BLOCKS            REMAIN_BLOCKS             EST_FINISH           ELAPSE_SEC REMAIN_SEC MESSAGE
 ----- ------------- ------------- ---- -------- ------------------------- ------------------------- -------------------- ---------- ---------- ------------------------------------------------------------------------
 XXXX  simongreener2 SQL Developer  517    38725 68.9% OF Blocks Processed 31.1% OF Blocks Remaining 11/MAY/2017 21:32:15 769:54     347:25     TABLE Scan:  MDSYS.SDO_TXN_IDX_INSERTS: 123644 OUT OF 179511 Blocks done

I guess the changes caused row movements (new ROWIDs). Because the spatial index uses ROWIDs, it had to be rebuilt.

Because the changes were being made overnight, I didn’t realise the mistake till morning. A collective decision was made to leave it to finish. The concern being that the only way to stop the index build was to kill the original table modification (whose changes were reported as having been done), losing those changes.

Moral of the story
In such a situation, drop the index and rebuild it afterwards (as one should do if DML is modifying a large percentage of a table).