By Brian Fitzgerald
It is well known that moving a table segment causes indexes to become unusable. It is less known that moving an LOB causes indexes to become unusable.
Moving a table segment causes indexes to become unusable because indexes point to the table’s physical location. After a table move, the index still points to the old table location, so the index is invalid. Oracle marks the index as unusable.
alter table t move tablespace ts0002
If a table has an LOB column, you can move the table and the LOB in a single command. Again, the move result in unusable indexes.
alter table t move tablespace ts0002 lob ( l ) store as ( tablespace ts0002 )
If only the LOB is moved, not the table, indexes are also left unusable. This has surprised some users, but it is the expected behavior. The behavior is documented.
alter table t move lob ( l ) store as ( tablespace ts0002 )
Further investigation shows that when an LOB is moved, the table segment is also “moved”. The table data_object_id and the table segment header block change, and every rowid in the table segment changes. Indexes on the table become unusable.
Unusable indexes are unintuitive because the command syntax is to move an LOB column, and the column is not indexed. Why should an index become unusable if the column that is moved is not indexed?
A Google search on “move lob unusable index” turns up questions and answers on this subject, and lead to the documentation, and to Note 1228324.1, which state that moving and LOB leading to unusable indexes is the expected behavior.
Point taken.
But why?
Specifically, why does moving an LOB result in “moving” the table segment?
The reason is clear when you consider that whether the move succeeds of fails, the operation must leave the schema in a consistent state.
Moving an LOB requires updating the LOB locator in every row. It’s similar to an update of every row. Generally, updates can be done conventional or direct path. The choice is an implementation decision. Direct path can be more efficient and less dependent on instance resources such as memory and undo. Alter table is DDL. Direct path is commonly used in DDL.
A DDL operation must succeed or fail atomically. We don’t want to end up half way where some LOBs are migrated and some not. The LOBs are in a single segment, which is the smallest unit of administration. You can’t move part of a segment. The entire move must succeed or fail. This is implemented by making a copy of the LOB segment, and a copy of the table segment. When the copying is complete, the LOB and the table are switched to point to the new copies. After a successful LOB move, the LOB has been copied, the table has been copied, and the new LOB locators point to the copied LOB. If the alter table is interrupted, the copies are discarded and the state is the same as it was to start with. After a failed LOB move, the original LOB locators simply point to the original LOB.
Note that moving an LOB will require not only free space for the new LOB, but also, space for the table.
What follows is a demo of moving an LOB only. There are a few points to notice:
- Table T LOB column L moves from tablespace TS0001 to TS0002
- Table T tablespace remains TS0001.
- Table T data object id changes from 74914 to 74918. The segment in fact “moved”.
- Table T segment header block changes from 130 to 170. The segment “moved”.
- Because the segment moved, index I is left unusable
- tkprof shows direct path read and write, supporting the earlier comment.
Conclusions:
- Whether you move the table, the LOB, or both, indexes will become unusable.
- Unusable indexes are a result of a direct path LOB move implementation.
- Space for the table and the LOB moves have to be planned for.
conn u/u @ columnformat.sql set linesize 32767 set trimspool on column l format a1 drop table t purge; create table t ( c varchar2(1), l clob ) lob ( l ) store as ( tablespace ts0001 disable storage in row ) tablespace ts0001 ; insert into t ( c, l ) select 'A', 'Z' from dual connect by level <= 1000 ; commit; create index i on t ( c ); select df.file_id, df.tablespace_name from dba_data_files df where df.tablespace_name in ( 'TS0001', 'TS0002' ); @ trace.10046.on.sql @ sel.t.info.sql prompt moving the LOB only alter table t move lob ( l ) store as ( tablespace ts0002 ) ; @ sel.t.info.sql @ trace.10046.on.sql @ tkprof.sql SQL> @ movelob.sql Connected. Table dropped. Table created. 1000 rows created. Commit complete. Index created. FILE_ID TABLESPACE_NAME ---------- ------------------------------ 8 TS0001 9 TS0002 Session altered. Session altered. OWNER SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME HEADER_BLOCK EXTENTS ------------------------------ ------------------------------ ------------------ ------------------------------ ------------ ---------- U T TABLE TS0001 130 2 OWNER OBJECT_NAME OBJECT_TYPE DATA_OBJECT_ID ------------------------------ ------------------------------ ------------------------------ -------------- U T TABLE 74914 OWNER TABLE_NAME COLUMN_NAME TABLESPACE_NAME ------------------------------ ------------------------------ ------------------------------ ------------------------------ U T L TS0001 TABLE_NAME INDEX_NAME STATUS ------------------------------ ------------------------------ -------- T I VALID moving the LOB only Table altered. OWNER SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME HEADER_BLOCK EXTENTS ------------------------------ ------------------------------ ------------------ ------------------------------ ------------ ---------- U T TABLE TS0001 178 2 OWNER OBJECT_NAME OBJECT_TYPE DATA_OBJECT_ID ------------------------------ ------------------------------ ------------------------------ -------------- U T TABLE 74918 OWNER TABLE_NAME COLUMN_NAME TABLESPACE_NAME ------------------------------ ------------------------------ ------------------------------ ------------------------------ U T L TS0002 TABLE_NAME INDEX_NAME STATUS ------------------------------ ------------------------------ -------- T I UNUSABLE Session altered. Session altered. TRACEFILENAME ---------------------------------------------------------------------------------------------------- /u01/app/oracle/diag/rdbms/db12201/db12201/trace/db12201_ora_15552.trc BASE ----------------- db12201_ora_15552 TKPROF: Release 12.2.0.1.0 - Development on Sun Nov 19 18:20:59 2017 Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved. created file: db12201_ora_15552.tkp ******************************************************************************** alter table t move lob ( l ) store as ( tablespace ts0002 ) call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 2 1 0 Execute 1 0.58 13.03 1004 87184 61243 1000 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2 0.59 13.03 1004 87186 61244 1000 Misses in library cache during parse: 1 Optimizer mode: ALL_ROWS Parsing user id: 110 Number of plan statistics captured: 1 Rows (1st) Rows (avg) Rows (max) Row Source Operation ---------- ---------- ---------- --------------------------------------------------- 0 0 0 LOAD AS SELECT T (cr=87182 pr=1000 pw=1007 time=12586318 us starts=1) 1000 1000 1000 TABLE ACCESS FULL T (cr=16 pr=0 pw=0 time=0 us starts=1 cost=5 size=237000 card=1000) Elapsed times include waiting on following events: Event waited on Times Max. Wait Total Waited ---------------------------------------- Waited ---------- ------------ PGA memory operation 3 0.00 0.00 direct path read 999 0.88 11.40 local write wait 8 0.00 0.00 acknowledge over PGA limit 1 0.00 0.00 direct path write 1 0.01 0.01 enq: RO - fast object reuse 4 0.06 0.12 db file sequential read 4 0.15 0.15 enq: CR - block range reuse ckpt 4 0.14 0.14 log file sync 1 0.00 0.00 SQL*Net message to client 1 0.00 0.00 SQL*Net message from client 1 0.00 0.00 ********************************************************************************