Oracle 参数之deferred_segment_creation的测试
deferred_segment_creation参数的主要作用是控制分区表中段的创建，例如很多情况下我们会预先创建未来一段时间某个表的分区表，但是我们还不想让这些预先创建的分区表占用过多的空间，在这种情况下我们设置deferred_segment_creation为false，那么在dba_objects中会有相关分区表的记录，但是dba_segements中不会有相关预先创建的分区表的记录，也就是预先创建的分区表实际不会占用存储空间。
***************************************
当参数deferred_segment_creation为true时
***************************************
SQL> show parameter segment
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
deferred_segment_creation boolean TRUE
rollback_segments string
transactions_per_rollback_segment integer 5
1.创建range分区表
CREATE TABLE tab_seg (ID number(20),datetime date) PARTITION BY RANGE (datetime)
(PARTITION P1 VALUES LESS THAN (TO_DATE('2018-11-1', 'YYYY-MM-DD')),
PARTITION P2 VALUES LESS THAN (TO_DATE('2018-12-1', 'YYYY-MM-DD')),
PARTITION P3 VALUES LESS THAN (TO_DATE('2019-1-1', 'YYYY-MM-DD')),
PARTITION P4 VALUES LESS THAN (TO_DATE('2019-2-1', 'YYYY-MM-DD')),
PARTITION P5 VALUES LESS THAN (TO_DATE('2019-3-1', 'YYYY-MM-DD')),
PARTITION P6 VALUES LESS THAN (TO_DATE('2019-4-1', 'YYYY-MM-DD')),
PARTITION P7 VALUES LESS THAN (TO_DATE('2019-5-1', 'YYYY-MM-DD')),
PARTITION P8 VALUES LESS THAN (TO_DATE('2019-6-1', 'YYYY-MM-DD')),
PARTITION P9 VALUES LESS THAN (TO_DATE('2019-7-1', 'YYYY-MM-DD')),
PARTITION P10 VALUES LESS THAN (TO_DATE('2019-8-1', 'YYYY-MM-DD')),
PARTITION P11 VALUES LESS THAN (TO_DATE('2019-9-1', 'YYYY-MM-DD')),
PARTITION P12 VALUES LESS THAN (TO_DATE('2019-10-1', 'YYYY-MM-DD')),
PARTITION P13 VALUES LESS THAN (TO_DATE('2019-11-1', 'YYYY-MM-DD')),
PARTITION P14 VALUES LESS THAN (TO_DATE('2019-12-1', 'YYYY-MM-DD')),
PARTITION P15 VALUES LESS THAN (TO_DATE('2020-1-1', 'YYYY-MM-DD')),
PARTITION P16 VALUES LESS THAN (TO_DATE('2020-2-1', 'YYYY-MM-DD')),
PARTITION P17 VALUES LESS THAN (MAXVALUE));
2.插入数据
declare
i number;
dd date;
BEGIN
dd := sysdate;
i:= 0;
for x in 1..5000 loop
--if mod(i,100)=0 then
insert into tab_seg(id,datetime) values(i,dd);
dd := dd+1/12;
i := i+1;
end loop;
END;
/
3.查询有数据插入的P15分区
SQL> select * from dba_objects where SUBOBJECT_NAME='P15'
OWNER OBJECT_NAME SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE CREATED LAST_DDL_TIME TIMESTAMP STATUS T G S NAMESPACE EDITION_NAME
------------------------------ -------------------- ------------------------------ ---------- -------------- ------------------- ------------------ ------------------ ------------------- ------- - - - ---------- ------------------------------
DAYU TAB_SEG P15 87258 87258 TABLE PARTITION 22-OCT-18 22-OCT-18 2018-10-22:18:52:37 VALID N N N 1
SQL> select bytes from dba_segments where PARTITION_NAME='P15';
BYTES
----------
8388608
4.查询没有数据插入的P16分区
SQL> select * from dba_objects where SUBOBJECT_NAME='P16';
OWNER OBJECT_NAME SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE CREATED LAST_DDL_TIME TIMESTAMP STATUS T G S NAMESPACE EDITION_NAME
------------------------------ -------------------- ------------------------------ ---------- -------------- ------------------- ------------------ ------------------ ------------------- ------- - - - ---------- ------------------------------
DAYU TAB_SEG P16 87259 87259 TABLE PARTITION 22-OCT-18 22-OCT-18 2018-10-22:18:52:37 VALID N N N 1
SQL> select BYTES from dba_segments where PARTITION_NAME='P16';
no rows selected
5.结论：当deferred_segment_creation为true时，未插入数据的分区在dba_objects会有有相应的记录，但是在dba_segments中没有相应的记录。
***************************************
当参数deferred_segment_creation为false时
***************************************
SQL> show parameter segment
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
deferred_segment_creation boolean FALSE
rollback_segments string
transactions_per_rollback_segment integer 5
SQL>
1.创建range分区表
CREATE TABLE tab_seg (ID number(20),datetime date) PARTITION BY RANGE (datetime)
(PARTITION P1 VALUES LESS THAN (TO_DATE('2018-11-1', 'YYYY-MM-DD')),
PARTITION P2 VALUES LESS THAN (TO_DATE('2018-12-1', 'YYYY-MM-DD')),
PARTITION P3 VALUES LESS THAN (TO_DATE('2019-1-1', 'YYYY-MM-DD')),
PARTITION P4 VALUES LESS THAN (TO_DATE('2019-2-1', 'YYYY-MM-DD')),
PARTITION P5 VALUES LESS THAN (TO_DATE('2019-3-1', 'YYYY-MM-DD')),
PARTITION P6 VALUES LESS THAN (TO_DATE('2019-4-1', 'YYYY-MM-DD')),
PARTITION P7 VALUES LESS THAN (TO_DATE('2019-5-1', 'YYYY-MM-DD')),
PARTITION P8 VALUES LESS THAN (TO_DATE('2019-6-1', 'YYYY-MM-DD')),
PARTITION P9 VALUES LESS THAN (TO_DATE('2019-7-1', 'YYYY-MM-DD')),
PARTITION P10 VALUES LESS THAN (TO_DATE('2019-8-1', 'YYYY-MM-DD')),
PARTITION P11 VALUES LESS THAN (TO_DATE('2019-9-1', 'YYYY-MM-DD')),
PARTITION P12 VALUES LESS THAN (TO_DATE('2019-10-1', 'YYYY-MM-DD')),
PARTITION P13 VALUES LESS THAN (TO_DATE('2019-11-1', 'YYYY-MM-DD')),
PARTITION P14 VALUES LESS THAN (TO_DATE('2019-12-1', 'YYYY-MM-DD')),
PARTITION P15 VALUES LESS THAN (TO_DATE('2020-1-1', 'YYYY-MM-DD')),
PARTITION P16 VALUES LESS THAN (TO_DATE('2020-2-1', 'YYYY-MM-DD')),
PARTITION P17 VALUES LESS THAN (MAXVALUE));
2.插入数据
declare
i number;
dd date;
BEGIN
dd := sysdate;
i:= 0;
for x in 1..5000 loop
--if mod(i,100)=0 then
insert into tab_seg(id,datetime) values(i,dd);
dd := dd+1/12;
i := i+1;
end loop;
END;
/
3.查询有数据插入的P15分区
SQL>select * from dba_objects where SUBOBJECT_NAME='P15'
OWNER OBJECT_NAME SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE CREATED LAST_DDL_TIME TIMESTAMP STATUS T G S NAMESPACE EDITION_NAME
------------------------------ -------------------- ------------------------------ ---------- -------------- ------------------- ------------------ ------------------ ------------------- ------- - - - ---------- ------------------------------
DAYU TAB_SEG P15 87280 87280 TABLE PARTITION 22-OCT-18 22-OCT-18 2018-10-22:19:03:02 VALID N N N 1
SQL> select bytes from dba_segments where PARTITION_NAME='P15';
BYTES
----------
8388608
4.查询没有数据插入的P16分区
SQL> select * from dba_objects where SUBOBJECT_NAME='P16';
OWNER OBJECT_NAME SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE CREATED LAST_DDL_TIME TIMESTAMP STATUS T G S NAMESPACE EDITION_NAME
------------------------------ -------------------- ------------------------------ ---------- -------------- ------------------- ------------------ ------------------ ------------------- ------- - - - ---------- ------------------------------
DAYU TAB_SEG P16 87281 87281 TABLE PARTITION 22-OCT-18 22-OCT-18 2018-10-22:19:03:02 VALID N N N 1
SQL> select bytes from dba_segments where PARTITION_NAME='P16';
BYTES
----------
8388608
5.结论：当deferred_segment_creation为false时，未插入数据的分区在dba_objects会有有相应的记录，在dba_segments也会预先分配空间，有相应的记录。
