玉面飞龙的BLOG

Move Lob Segment

March 2nd, 2007 | Categories: Boring | Tags:

Oracle9205中测试移动Lob Segment,是相当的慢。Parallel 选项对move lob segment不起作用，我没有从PQ的v$session_event中看到direct path read(lob)和较多的db file scatter read.只有QC在移动lob segment.

指定 Nologging 倒是可以提高move lob segment的速度。测试如下，

SQL> create table t tablespace md_data nologging as select * from TABLE_CONTAIN_LOBSEG where rownum<=19000;
Table created.
SQL> List LOB SPACE 3560M
OWNER SEGMENT_NAME SEGMENT_TYPE size(M)
——————– —————————— —————————— —–
ORACLE SYS_LOB0000019962C00004$$ LOBSEGMENT 3560

SQL> set timing on
SQL> alter table t move tablespace TESTLMT nologging lob(content) store as (tablespace TESTLMT cache);
Table altered.
Elapsed: 00:07:07.75

SQL> alter table t move tablespace TESTLMT nologging lob(content) store as (tablespace TESTLMT nocache nologging);
Table altered.
Elapsed: 00:04:30.06

Notice: Move lob segment的时候，表也会被移动，相应的索引需要rebuild>>.