—-对于普通表以及其上的索引/lob segment/lob index等对象的segment,,在表中没有记录的时候,
—-segment 是先不创建的. 只有等到这个表中产生真实的记录的时候, 才会真正生成对应的segment. (见test 1).
但是要注意,,
- 上有1条记录,,哪怕是不涉及到对应的索引/lob项, segment也会创建..(见test 2).
- 分区表只要创建就会生成对应的segment, 也就是对于分区表, Oracle不支持这个特性 (见test 3, 附相关文档说明).
Restrictions on Deferred Segment Creation This clause is subject to the following restrictions:
- You cannot defer segment creation for the following types of tables: partitioned tables, index-organized tables, clustered tables, global temporary tables, session-specific temporary tables, internal tables, typed tables, AQ tables, external tables, and tables owned by
SYS,SYSTEM,PUBLIC,OUTLN, orXDB.- Deferred segment creation is not supported for partitioned indexes, bitmap join indexes, and domain indexes.
- Deferred segment creation is not supported for objects on dictionary-managed tablespaces.
—-test 1
SQL> conn james/james
Connected.
SQL>
SQL> create table james_tt_dsc(
2 id number,
3 name varchar2(20),
4 type varchar2(20),
5 status varchar2(20),
6 detail clob
7 )
8 tablespace test;
Table created.
SQL> set pages 100
SQL> col column_name format a15
SQL> col segment_name format a25
SQL> col index_name format a25
SQL> select table_name,segment_name,index_name,column_name from user_lobs;
TABLE_NAME SEGMENT_NAME
------------------------------ -------------------------
INDEX_NAME COLUMN_NAME
------------------------- ---------------
JAMES_TT_DSC SYS_LOB0000073929C00005$$
SYS_IL0000073929C00005$$ DETAIL
SQL> create index james_tt_dsc_pk on james_tt_dsc(id);
Index created.
SQL> create index james_tt_dsc_st on james_tt_dsc(status) ;
Index created.
SQL> create index james_tt_dsc_tp on james_tt_dsc(type);
Index created.
SQL> select segment_name,segment_type,bytes,blocks from user_segments where segment_name like 'JAMES_TT_DSC%'
2 or segment_name in ('SYS_LOB0000073929C00005$$','SYS_IL0000073929C00005$$');
no rows selected
SQL> insert into james_tt_dsc values(1,'test 1','product','enabled','this is a test');
1 row created.
SQL> select segment_name,segment_type,bytes,blocks
2 from user_segments
3 where segment_name like 'JAMES_TT_DSC%'
4 or segment_name in ('SYS_LOB0000073929C00005$$','SYS_IL0000073929C00005$$');
SEGMENT_NAME SEGMENT_TYPE BYTES BLOCKS
------------------------- ------------------ ---------- ----------
JAMES_TT_DSC TABLE 1048576 128
JAMES_TT_DSC_PK INDEX 1048576 128
JAMES_TT_DSC_ST INDEX 1048576 128
JAMES_TT_DSC_TP INDEX 1048576 128
SYS_IL0000073929C00005$$ LOBINDEX 1048576 128
SYS_LOB0000073929C00005$$ LOBSEGMENT 1048576 128
6 rows selected.
–test 2
SQL> create table james_tt_dsc2(
2 id number,
3 name varchar2(20),
4 type varchar2(20),
5 status varchar2(20),
6 detail clob
7 )
8 tablespace test;
Table created.
SQL> create index james_tt_dsc2_pk on james_tt_dsc2(id);
Index created.
SQL> create index james_tt_dsc2_st on james_tt_dsc2(status) ;
Index created.
SQL> create index james_tt_dsc2_tp on james_tt_dsc2(type);
Index created.
SQL> set lines 120
SQL> select table_name,segment_name,index_name,column_name from user_lobs;
TABLE_NAME SEGMENT_NAME INDEX_NAME COLUMN_NAME
------------------------------ ------------------------- ------------------------- ---------------
JAMES_TT_DSC SYS_LOB0000073929C00005$$ SYS_IL0000073929C00005$$ DETAIL
JAMES_TT_DSC2 SYS_LOB0000073947C00005$$ SYS_IL0000073947C00005$$ DETAIL
SQL> select segment_name,partition_name,segment_type,bytes,blocks
2 from user_segments
3 where segment_name like 'JAMES_TT_DSC2%'
4 or segment_name in ('SYS_LOB0000073947C00005$$','SYS_IL0000073947C00005$$');
no rows selected
SQL> insert into james_tt_dsc2 (id) values(1);
1 row created.
SQL> select segment_name,partition_name,segment_type,bytes,blocks
2 from user_segments
3 where segment_name like 'JAMES_TT_DSC2%'
4 or segment_name in ('SYS_LOB0000073947C00005$$','SYS_IL0000073947C00005$$');
SEGMENT_NAME PARTITION_NA SEGMENT_TYPE BYTES BLOCKS
------------------------- ------------ ------------------ ---------- ----------
JAMES_TT_DSC2 TABLE 1048576 128
JAMES_TT_DSC2_PK INDEX 1048576 128
JAMES_TT_DSC2_ST INDEX 1048576 128
JAMES_TT_DSC2_TP INDEX 1048576 128
SYS_IL0000073947C00005$$ LOBINDEX 1048576 128
SYS_LOB0000073947C00005$$ LOBSEGMENT 1048576 128
6 rows selected.
—-test 3
SQL>
SQL> create table james_tt_dsc_p (
2 id number,
3 name varchar2(20),
4 type varchar2(20),
5 status varchar2(20),
6 detail varchar2(4000)
7 )
8 partition by hash (name) partitions 8;
Table created.
SQL> col partition_name format a12
SQL> select segment_name,partition_name,segment_type,bytes,blocks
2 from user_segments
3 where segment_name like 'JAMES_TT_DSC%'
4 or segment_name in ('SYS_LOB0000073929C00005$$','SYS_IL0000073929C00005$$');
SEGMENT_NAME PARTITION_NA SEGMENT_TYPE BYTES BLOCKS
------------------------- ------------ ------------------ ---------- ----------
JAMES_TT_DSC TABLE 1048576 128
JAMES_TT_DSC_P SYS_P106 TABLE PARTITION 1048576 128
JAMES_TT_DSC_P SYS_P107 TABLE PARTITION 1048576 128
JAMES_TT_DSC_P SYS_P108 TABLE PARTITION 1048576 128
JAMES_TT_DSC_P SYS_P109 TABLE PARTITION 1048576 128
JAMES_TT_DSC_P SYS_P110 TABLE PARTITION 1048576 128
JAMES_TT_DSC_P SYS_P111 TABLE PARTITION 1048576 128
JAMES_TT_DSC_P SYS_P112 TABLE PARTITION 1048576 128
JAMES_TT_DSC_P SYS_P113 TABLE PARTITION 1048576 128
JAMES_TT_DSC_PK INDEX 1048576 128
JAMES_TT_DSC_ST INDEX 1048576 128
JAMES_TT_DSC_TP INDEX 1048576 128
SYS_IL0000073929C00005$$ LOBINDEX 1048576 128
SYS_LOB0000073929C00005$$ LOBSEGMENT 1048576 128
14 rows selected.
Related posts:
最近评论