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).