----------------------------------------------------------------------------------------------------------------------------------------------
--模拟MSSM下设置minimize records_per_block表存储情况 SYS@PROD1> create tablespace segman datafile '/u01/app/oracle/oradata/PROD1/segman.dbf' size 30m autoextend on uniform size 1m segment space management manual; Tablespace created. SYS@PROD1> create table t (id number) tablespace segman; --段空间手动管理实验结果更加直观 Table created. SYS@PROD1> alter table t pctfree 99 pctused 1; Table altered. SYS@PROD1> insert into t values (1); 1 row created. SYS@PROD1> alter table t minimize records_per_block; --插入一行后设置即每个块最多可插一行 Table altered. SYS@PROD1> insert into t select rownum from dba_objects where rownum<50; 49 rows created. SYS@PROD1> commit; Commit complete. SYS@PROD1> exec dbms_stats.gather_table_stats('SYS','T',estimate_percent => 100,method_opt => 'for all columns size 1',degree => DBMS_STATS.AUTO_DEGREE,cascade=>TRUE); PL/SQL procedure successfully completed. SYS@PROD1> select owner,table_name,blocks from dba_tables where table_name='T' and owner='SYS'; OWNER TABLE_NAME BLOCKS ------------------------------ ------------------------------ ---------- SYS T 25 SYS@PROD1> select dbms_rowid.rowid_block_number(rowid),count(*) from t group by dbms_rowid.rowid_block_number(rowid); --查看得知每个块都存有两行数据，所以这个参数最小值为2 DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) COUNT(*) ------------------------------------ ---------- 129 2 138 2 151 2 152 2 153 2 134 2 144 2 147 2 132 2 141 2 148 2 149 2 133 2 137 2 143 2 135 2 130 2 131 2 136 2 140 2 142 2 145 2 146 2 139 2 150 2 25 rows selected. --模拟默认ASSM下设置minimize records_per_block表存储情况 SYS@PROD1> drop table t purge; Table dropped. SYS@PROD1> create table t (id number); Table created. SYS@PROD1> alter table t pctfree 99 pctused 1; -- Table altered. SYS@PROD1> insert into t values (1); 1 row created. SYS@PROD1> alter table t minimize records_per_block; Table altered. SYS@PROD1> insert into t select rownum from dba_objects where rownum<50; 49 rows created. SYS@PROD1> commit; Commit complete. SYS@PROD1> exec dbms_stats.gather_table_stats('SYS','T',estimate_percent => 100,method_opt => 'for all columns size 1',degree => DBMS_STATS.AUTO_DEGREE,cascade=>TRUE); PL/SQL procedure successfully completed. SYS@PROD1> select owner,table_name,blocks from dba_tables where table_name='T' and owner='SYS'; --一共用了28个块存储 OWNER TABLE_NAME BLOCKS ------------------------------ ------------------------------ ---------- SYS T 28 SYS@PROD1> select dbms_rowid.rowid_block_number(rowid),count(*) from t group by dbms_rowid.rowid_block_number(rowid); --其中存储数据的占用了25个块，剩余三个为ASSM结构的三层位图块 DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) COUNT(*) ------------------------------------ ---------- 99495 2 99501 2 99513 2 99499 2 99500 2 99503 2 99509 2 99511 2 99491 2 99504 2 99506 2 99507 2 99508 2 99492 2 99502 2 99494 2 99512 2 99489 2 99490 2 99493 2 99496 2 99498 2 99510 2 99497 2 99505 2 25 rows selected. SYS@PROD1> select count(*) from t; COUNT(*) ---------- 50 --模拟普通表存储情况 SYS@PROD1> drop table t purge; Table dropped. SYS@PROD1> create table t (id number); Table created. SYS@PROD1> insert into t select rownum from dba_objects where rownum<51; 50 rows created. SYS@PROD1> commit; Commit complete. SYS@PROD1> exec dbms_stats.gather_table_stats('SYS','T',estimate_percent => 100,method_opt => 'for all columns size 1',degree => DBMS_STATS.AUTO_DEGREE,cascade=>TRUE); PL/SQL procedure successfully completed. SYS@PROD1> select owner,table_name,blocks from dba_tables where table_name='T' and owner='SYS'; --50行数据都存储在一个块中 OWNER TABLE_NAME BLOCKS ------------------------------ ------------------------------ ---------- SYS T 1 SYS@PROD1> select dbms_rowid.rowid_block_number(rowid),count(*) from t group by dbms_rowid.rowid_block_number(rowid); DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) COUNT(*) ------------------------------------ ---------- 99489 50
