move LOB results in unusable indexes

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
********************************************************************************

 

 

 

 

 

 

 

 

 

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s