【oracle11g,17】存储结构: 段的类型，数据块(行连接、行迁移，块头)，段的管理方式，高水位线...-白红宇

【oracle11g,17】存储结构: 段的类型，数据块(行连接、行迁移，块头)，段的管理方式，高水位线...

阅读量：5819 次

发布时间：2019-06-18

本文共 20000 字，大约阅读时间需要 66 分钟。

一.段的类型：

1.什么是段：段是存储单元。

1.段的类型有：

表

分区表

簇表

索引

索引组织表(IOT表)

分区索引

暂时段

undo段

lob段(blob ,clob)

内嵌表（record类型,table类型）

启动段：打开数据库时要装入的数据字典系统信息，故在系统表空间内。

注意：smon 回收不用的暂时段和undo段。

2.表：

表是存储数据的逻辑单位。

表的功能：存储、管理数据的基本单元。

表的类型：

普通表：堆表，

例：查询表的行的平均长度

AVG_ROW_LEN 平均长度

avg_space 平均空间

SQL> select table_name,tablespace_name,blocks,pct_free,pct_used,avg_space,AVG_ROW_LEN from dba_tables where table_name like 'EMP%';

TABLE_NA TABLESPACE_N BLOCKS PCT_FREE PCT_USED AVG_SPACE AVG_ROW_LEN

-------- ------------ ---------- ---------- ---------- ---------- -----------

EMP1 SYSTEM 44 10 40 0 38

EMP USERS 1 10 40 0 38

EMPLX LXTBS2 10 10 40 1322 38

例：查询表默认的pctfree 和pctused 以及改动

SQL> select table_name,tablespace_name,blocks,pct_free,pct_used from dba_tables where table_name like 'EMP%';

TABLE_NA TABLESPACE_N BLOCKS PCT_FREE PCT_USED

-------- ------------ ---------- ---------- ----------

EMP1 SYSTEM 44 10 40

EMPLX LXTBS2 10 10 40

EMP USERS 1 10 40

SQL> alter table emplx pctfree 12;

Table altered.

SQL> alter table emplx pctused 42;

Table altered.

3.分区表：

比方一个表尺寸大于2g，oracle建议分区。一个分区内的数据量一般不超过50w左右性能最好。最多不要超过100万。

range分区方式，依照指定范围分区，比方按时间分区或值大小分区。

list分区方式，是一种列举方式进行分区

哈数分区方式。依据hash算法分区。有oracle管理。

①.分区表的使用详见：【dba，32】分区表：

②.案例：创建分区索引：

#创建分区表

SQL> CREATE TABLE sales_history

2 (id number(8),name varchar2(30),sales_date date)

3 PARTITION BY RANGE (sales_date)

4 (PARTITION p1 VALUES LESS THAN ( TO_DATE('01-JAN-1999','DD-MON-YYYY')) tablespace users,

5 PARTITION p2 VALUES LESS THAN ( TO_DATE('01-JAN-2000','DD-MON-YYYY')) tablespace users,

6 PARTITION p3 VALUES LESS THAN ( TO_DATE('01-JAN-2001','DD-MON-YYYY')) tablespace users,

7 PARTITION p4 VALUES LESS THAN ( TO_DATE('01-JAN-2002','DD-MON-YYYY')) tablespace users,

8 PARTITION p5 VALUES LESS THAN (MAXVALUE) tablespace users

9 );

Table created.

#插入数据

SQL> insert into sales_history values(1,'PC1',to_date('17-FEB-2000','DD-MON-YYYY'));

SQL> insert into sales_history values(2,'PC2',to_date('17-FEB-2001','DD-MON-YYYY'))

SQL> insert into sales_history values(3,'PC3',to_date('17-FEB-2002','DD-MON-YYYY'));

SQL> insert into sales_history values(4,'PC4',to_date('17-FEB-1999','DD-MON-YYYY'));

SQL> insert into sales_history values(5,'PC5',to_date('15-FEB-2000','DD-MON-YYYY'));

SQL> insert into sales_history values(6,'PC6',to_date('12-FEB-2001','DD-MON-YYYY'));

SQL> insert into sales_history values(7,'PC7',to_date('11-FEB-1998','DD-MON-YYYY'));

SQL> insert into sales_history values(8,'PC8',to_date('13-FEB-2002','DD-MON-YYYY'));

SQL> insert into sales_history values(8,'PC8',to_date('13-FEB-2013','DD-MON-YYYY'));

#查询数据

SQL> select * from sales_history;

ID NAME SALES_DAT

---------- ---------- ---------

7 PC7 11-FEB-98

4 PC4 17-FEB-99

1 PC1 17-FEB-00

5 PC5 15-FEB-00

2 PC2 17-FEB-01

6 PC6 12-FEB-01

3 PC3 17-FEB-02

8 PC8 13-FEB-02

8 PC8 13-FEB-13

9 rows selected.

#查询分区表数据

SQL> select * from sales_history partition(p1);

ID NAME SALES_DAT

---------- ---------- ---------

7 PC7 11-FEB-98

SQL> select * from sales_history partition(p2);

ID NAME SALES_DAT

---------- ---------- ---------

4 PC4 17-FEB-99

SQL> select * from sales_history partition(p3);

ID NAME SALES_DAT

---------- ---------- ---------

1 PC1 17-FEB-00

5 PC5 15-FEB-00

SQL> select * from sales_history partition(p4);

ID NAME SALES_DAT

---------- ---------- ---------

2 PC2 17-FEB-01

6 PC6 12-FEB-01

SQL> select * from sales_history partition(p5);

ID NAME SALES_DAT

---------- ---------- ---------

3 PC3 17-FEB-02

8 PC8 13-FEB-02

8 PC8 13-FEB-13

#创建唯一索引，由于数据中id有反复，所以报错。

SQL> CREATE unique INDEX i_sales_id ON sales_history(id) GLOBAL

2 PARTITION BY HASH (id)

3 (PARTITION pi1 TABLESPACE users,

4 PARTITION pi2 TABLESPACE users,

5 PARTITION pi3 TABLESPACE users,

6 PARTITION pi4 TABLESPACE users) parallel 4;

CREATE unique INDEX i_sales_id ON sales_history(id) GLOBAL

ERROR at line 1:

ORA-12801: error signaled in parallel query server P001

ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found

#依据rowid查询和删除反复数据：

SQL> select a.* from sales_history a

2 where a.rowid in (

3 select

min(b.rowid) from sales_history b

4 where b.id=a.id);

ID NAME SALES_DAT

---------- ---------- ---------

7 PC7 11-FEB-98

4 PC4 17-FEB-99

1 PC1 17-FEB-00

5 PC5 15-FEB-00

2 PC2 17-FEB-01

6 PC6 12-FEB-01

3 PC3 17-FEB-02

8 PC8 13-FEB-02

8 rows selected.

#查询出多余的数据

SQL> select a.* from sales_history a

2 where a.rowid not in (

3 select min(b.rowid) from sales_history b

4 where b.id=a.id);

ID NAME SALES_DAT

---------- ---------- ---------

8 PC8 13-FEB-13

SQL> select a.*,rowid from sales_history a;

ID NAME SALES_DAT ROWID

---------- ---------- --------- ------------------

7 PC7 11-FEB-98 AAACffAAEAAAAA9AAA

4 PC4 17-FEB-99 AAACfgAAEAAAABFAAA

1 PC1 17-FEB-00 AAACfhAAEAAAABNAAA

5 PC5 15-FEB-00 AAACfhAAEAAAABNAAB

2 PC2 17-FEB-01 AAACfiAAEAAAABVAAA

6 PC6 12-FEB-01 AAACfiAAEAAAABVAAB

3 PC3 17-FEB-02 AAACfjAAEAAAABdAAA

8 PC8 13-FEB-02 AAACfjAAEAAAABdAAB

8 PC8 13-FEB-13 AAACfjAAEAAAABdAAC

9 rows selected.

#删除反复的数据

SQL> delete from sales_history a

2 where a.rowid not in (

3 select min(b.rowid) from sales_history b

4 where b.id=a.id);

SQL> CREATE unique INDEX i_sales_id ON sales_history(id)

GLOBAL

2 PARTITION BY HASH (id)

3 (PARTITION pi1 TABLESPACE users,

4 PARTITION pi2 TABLESPACE users,

5 PARTITION pi3 TABLESPACE users,

6 PARTITION pi4 TABLESPACE users)

parallel 4;

Index created.

注意：

parallel

在生成库上谨慎使用，可能会把cpu的资源消耗干。

注意：对某个字段已做了分区了，是不同意再建立索引分区的。这一点要很注意。

#创建本地索引

SQL> CREATE INDEX i_sales_date on sales_history(sales_date) local;

Index created.

#创建一个不分区的索引

SQL> CREATE INDEX i_sales_name on sales_history(name);

#查询索引。

partitioned

为YES表示分区索引。

SQL> select index_name,index_type,table_name,tablespace_name,partitioned from dba_indexes where table_name='SALES_HISTORY';

INDEX_NAME INDEX_TYPE TABLE_NAME TABLESPACE PAR

-------------------- ---------- -------------------- ---------- ---

I_SALES_ID NORMAL SALES_HISTORY YES

I_SALES_DATE NORMAL SALES_HISTORY YES

I_SALES_NAME NORMAL SALES_HISTORY SYSTEM NO

#truncate 分区。

SQL> alter table sales_history truncate partition p2 update indexes;

Table truncated.

#重建分区索引

SQL> alter index i_sales_date rebuild partition p3;

Index altered.

#加入分区

SQL> alter table sales_history add partition p6 values less than (to_date('2019-01-01','yyyy-mm-dd')) ;

3.2.

PARTITION BY

REFERENCE:

http://docs.oracle.com/cd/E11882_01/server.112/e25523/part_admin001.htm#i1006455

4.簇表：

主要用于表和表之间的连接查询。

訪问不同的表时要訪问不同的段，磁盘会訪问不同的磁道，假设两个表能存放到一个段。就能提高訪问速度。

一个簇是由共享同样数据块的一组表组成，由于这些表共享公共的列而且常常一起被使用。所以将这些表组合在一起。

簇表长处：降低了磁盘i/o并改善了訪问簇表的连接所化的时间。

簇键是列或列的组。他们是簇表所共用的创建簇时指定簇键的列。

例：创建簇表

#关联查询

SQL> select e.empno,e.ename,e.sal,d.deptno,d.dname from scott.emp e,scott.dept d where e.deptno=d.deptno;

14 rows selected.

Execution Plan

----------------------------------------------------------

Plan hash value: 615168685

---------------------------------------------------------------------------

---------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 14 | 952 | 7 (15)| 00:00:01 |

|* 1 | HASH JOIN | | 14 | 952 | 7 (15)| 00:00:01 |

| 2 | TABLE ACCESS FULL| DEPT | 4 | 88 | 3 (0)| 00:00:01 |

| 3 | TABLE ACCESS FULL| EMP | 14 | 644 | 3 (0)| 00:00:01 |

---------------------------------------------------------------------------

#创建簇键

SQL> CREATE CLUSTER emp_dept (deptno NUMBER(3))

2 SIZE 600

3 TABLESPACE users;

Cluster created.

#创建簇表先创建

dept，然后再创建

emp表。

否则报错。

SQL> create table dept(

2 DEPTNO NUMBER(3) primary key,

3 DNAME VARCHAR2(14),

4 LOC VARCHAR2(13)

5 ) CLUSTER emp_dept (deptno);

Table created.

#创建簇表

SQL> create table emp(

2 EMPNO NUMBER(4) primary key,

3 ENAME VARCHAR2(10),

4 JOB VARCHAR2(9),

5 MGR NUMBER(4),

6 HIREDATE DATE,

7 SAL NUMBER(7,2),

8 COMM NUMBER(7,2),

9 DEPTNO NUMBER(3) references dept(deptno)

10 ) CLUSTER emp_dept (deptno);

Table created.

#创建簇索引

SQL> CREATE INDEX emp_dept_index

2 ON CLUSTER emp_dept

3 TABLESPACE users;

Index created.

#插入数据

SQL> insert into dept select * from scott.dept;

4 rows created.

SQL> insert into emp select * from scott.emp;

14 rows created.

SQL> commit;

Commit complete.

#查询两个表的rowid，rowid的前15位同样说明在同一个数据块中。

SQL> select e.rowid,d.rowid from emp e,dept d where e.deptno=d.deptno;

ROWID ROWID

------------------ ------------------

AAACgFAAEAAAACVAAA

AAACgFAAEAAAACVAAB

AAACgFAAEAAAACVAAB AAACgFAAEAAAACVAAC

AAACgFAAEAAAACVAAC AAACgFAAEAAAACVAAC

AAACgFAAEAAAACVAAD AAACgFAAEAAAACVAAB

AAACgFAAEAAAACVAAE AAACgFAAEAAAACVAAC

AAACgFAAEAAAACVAAF AAACgFAAEAAAACVAAC

AAACgFAAEAAAACVAAG AAACgFAAEAAAACVAAA

AAACgFAAEAAAACVAAH AAACgFAAEAAAACVAAB

AAACgFAAEAAAACVAAI AAACgFAAEAAAACVAAA

AAACgFAAEAAAACVAAJ AAACgFAAEAAAACVAAC

AAACgFAAEAAAACVAAK AAACgFAAEAAAACVAAB

ROWID ROWID

------------------ ------------------

AAACgFAAEAAAACVAAL AAACgFAAEAAAACVAAC

AAACgFAAEAAAACVAAM AAACgFAAEAAAACVAAB

AAACgFAAEAAAACVAAN AAACgFAAEAAAACVAAA

14 rows selected.

#扫描方式发生变化

SQL> select e.empno,e.ename,e.sal,d.deptno,d.dname from emp e,dept d where e.deptno=d.deptno;

14 rows selected.

Execution Plan

----------------------------------------------------------

Plan hash value: 805422011

----------------------------------------------------------------------------------------

----------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 14 | 952 | 6 (0)| 00:00:01 |

| 1 | NESTED LOOPS | | 14 | 952 | 6 (0)| 00:00:01 |

| 2 | TABLE ACCESS FULL | DEPT | 4 | 88 | 3 (0)| 00:00:01 |

| 3 | TABLE ACCESS CLUSTER| EMP | 4 | 184 | 1 (0)| 00:00:01 |

|* 4 | INDEX UNIQUE SCAN | EMP_DEPT_INDEX | 1 | | 0 (0)| 00:00:01 |

----------------------------------------------------------------------------------------

5.索引：

加快查询速度，索引第一次IO先訪问树。到叶节点找到rowid，再通过rowid訪问响应的记录。

6.索引组织表（IOT表）：

能够将数据存放在索引的叶节点，不用再訪问不同的段，iot表不能换查询条件，查询条件必须建成主键约束。假设换查询条件效率会非常低。

iot表存储时依据主键排序进行存储，所以插入数据时，按顺序插入数据。

假设索引组织表中的数据放不下会把一些数据放到溢出表空间。

长处：仅仅要io一次就能找到真实数据。

注意：生产中谨慎使用。

例：将多对多的关系表创建索引组织表

SQL> create table students(stud_id number,fname varchar2(30),lname varchar2(30),constraint pk_student primary key(stud_id));

Table created.

SQL> create table classes(class_id number,cname varchar2(30),constraint pk_class primary key(class_id));

Table created.

#创建索引组织表。

SQL> create table attendees(stud_id number,class_id number,

2 constraint pk_stud_class primary key(stud_id,class_id),

3 constraint fk_stud foreign key(stud_id) references students(stud_id),

4 constraint fk_class foreign key(class_id) references classes(class_id)

5 )

organization index;

7.分区索引类型：

①.表是分区表，索引不分区：

②.分区表的全局分区索引：

缺点：在truncate表时，这样的索引会失效。

长处：global所以在全局是有序的，所以效率高。

③.分区表的local分区索引：

语法：create index inx_tab_partition_col table_partition（col1） local;

缺点：

分区上的位图索引仅仅能为local索引。不能为global全局索引。

local分区索引在每一个分区上有序，但在全局是无序的，索引查询效率没有全局分区索引高。

长处:在olap系统中应用广泛，在truncate表时。这样的索引不会失效。

易于管理。

二.数据块：(pctfree和pctused，行连接、行迁移、数据块)

1.数据块分为三部分：block header（块头）。free space（空暇空间）， row data（数据行）。

2.pctfree 和pctused

pctfree就是插入数据到数据块中。达到pctfree时就不在插入数据，pctfree就是为update预留的空间。

pctused就是数据块中存放的数据在pctused之下表示数据块为空块，在段空间手工管理的情况下。就会把这些数据块放到链表freelist队列中。用于再次插入数据。

pctfree 太小在update操作时会导致行迁移，

pctused 太大在insert操作时会导致行连接。

3.行迁移(row migrate)和行连接（row chain）

行迁移：因为update操作导致原本在该块中的记录放不下了，就保留一个该行记录的地址。然后将该行记录迁移到其它块中，可是rowid不变。

行连接：就是一行数据insert到一个块时，根本容不下这行数据，该条记录仅仅能把数据分别放到若干个数据块中，使用row chain连接起来。long或long row数据类型的时候easy产生行连接。

4.数据块头：

块头包含表文件夹，行文件夹。

表文件夹：

这部分信息包括了在这个块中该表或该索引的相关信息。

行文件夹：

包括数据块中的实际行的信息（包括行数据区域中每行的地址)

事务槽（ITL:interested transaction list）：

①. 表初始事务槽是1，索引默觉得2，事务表会依据须要动态扩展，用来记录该数据块全部发生的事务。假设事务已经提交，那么这个itl的位置就能够被重复使用了。

②.

多个事务同一时候改动同一数据块时。就会有多个事务槽，事务槽被占用完了(数据块可用的空间被用完) 当再有事务要操作数据块时，须要等待其它事务提交后。才干进行操作，此时就是事务槽的争用

查看事务槽：

SQL> select table_name ,tablespace_name,blocks,ini_trans,max_trans from dba_tables where table_name like 'EMP%';

TABLE_NAME TABLESPACE_NAME BLOCKS INI_TRANS MAX_TRANS

------------------------------ --------------- ---------- ---------- ----------

EMP1 SYSTEM 44 1 255

EMP USERS 1 1 255

SQL> select index_name,index_type,table_name,tablespace_name,ini_trans,max_trans

from dba_indexes where owner='SCOTT';

INDEX_NAME INDEX_TYPE TABLE_NAME TABLESPACE_NAME INI_TRANS MAX_TRANS

---------- ---------- --------------- --------------- ---------- ----------

PK_EMP NORMAL EMP USERS 2 255

PK_DEPT NORMAL DEPT USERS 2 255

三.段的管理方式(两种)

自己主动段空间管理（ASSM）（数据库默认的管理形式）和手动段空间管理

1.自己主动段空间管理（ASSM）auto segment space manage

不訪问freelist而是訪问自己段头的位图，极大地降低了竞争。

连接列表freelist被位图所代替，它是一个二进制的数组，可以高速有效的管理存储扩展和剩余区块（free block）。因此可以改善分段存储本质。assm 表空间上创建的段还有另外一个称呼叫bitmap managed segments (BMB段)。

assm长处：减轻缓冲区忙等待(buffer busy waits)的负担，有assm之后，oracle提高了DML并发操作的性能。由于位图的不同部分能够被同一时候使用。这样就消除了寻找剩余空间的串行化。依据oracle的測试结果，使用位图会消除所用分段头部的争夺，还能获得超快的并发插入操作。消除并发插入的热快问题和buffer busy wait。

assm的局限性：无法控制tablespace内部的独立表和索引的存储行为

大型对象不能使用assm。并且必须为包括lob数据类型的表创建分离的tablespace。

不能使用assm创建暂时表空间。这是因为排序时暂时分段的短暂特性所决定的。

仅仅用本地管理的tablespace才干使用位图分段管理。

使用超高容量的dml的时候可能会出现性能上的问题。

注意：不要与内存分配自己主动管理（asmm）混淆。

2.手动段空间管理(MSSM)

系统会在段头建立一个fresslist链表，我们会把有空暇空间的块，放到列表中。当块中的数据量少于pctused会插入freelist。然而链表freelist是串行化操作。

在没有多个freelist的时候，当数据缓冲内的数据块因为被还有一个dml事务处理锁定而无法使用的时候，缓冲区忙等待就会发生，当你须要将多个任务插入到同一表格里的时候。这些任务就被强制等待，而同一时候oracle会在同一时候分派剩余的区块，一次一个（buffer busy wait）。

非常多用户都訪问freelist 形成热块。

3.两种段空间的管理比較：

同样点：主要是管理已经分配给段的数据块。

不同点：手工段空间管理方式：pctfree和pctused 都有效。

自己主动段空间管理方式：仅仅使用pctfree来决定是不是空块，pctused 被位图的四个状态位所代替（如：>75%,50%~75%,50%~25%，<25%）。

assm

无法控制tablespace内部的独立表和索引的存储行为。

ssm能够

控制tablespace内部的独立表和索引的存储行为。

4.查看段的管理方式

SQL> select tablespace_name ,contents,status,extent_management,segment_space_management from dba_tablespaces;

TABLESPACE_NAME CONTENTS STATUS EXTENT_MAN SEGMEN

------------------------------ --------- --------- ---------- ------

SYSTEM PERMANENT ONLINE LOCAL MANUAL

SYSAUX PERMANENT ONLINE LOCAL AUTO

UNDOTBS UNDO ONLINE LOCAL MANUAL

USERS PERMANENT ONLINE LOCAL MANUAL

TEMPTS2 TEMPORARY ONLINE LOCAL MANUAL

LXTBS1 PERMANENT ONLINE LOCAL AUTO

LXTBS2 PERMANENT ONLINE LOCAL AUTO

LXTBS3 PERMANENT ONLINE LOCAL AUTO

TEMPTS TEMPORARY ONLINE LOCAL MANUAL

四.区的管理方式和段的管理方式的差别：

区的管理方式：区来自表空间，这些数据块还没有被分配出去。

段的管理方式：

主要是管理已经分配给段的数据块,

五.创建assm和mssm管理的表空间：

1.创建ASSM段管理的表空间 (

数据库段空间管理默认方式为ASSM)

SQL> create tablespace lxtbs1 datafile '/u01/app/oracle/oradata/prod/disk3/lxtbs01.dbf' size 50m segment space management auto;

或者

create tablespace lxtbs1 datafile '/u01/app/oracle/oradata/prod/disk3/lxtbs01.dbf' size 50m;

2.创建MSSM手工管理的表空间

SQL> create tablespace lxtbs2 datafile '/u01/app/oracle/oradata/prod/disk3/lxtbs01.dbf' size 50m

segment space management manual

;

六.在mssm段管理方式的表空间创建表并设计表空间分配：

注意：仅仅能在mssm段管理的方式的表空间上才干对表的空间分配进行设计。

#创建表

SQL> create table emplx tablespace lxtbs2 storage(initial 32k next 64k pctincrease 50) as select * from scott.emp;

#插入数据

SQL>insert into emplx select * from emplx;

#查询段的空间分配情况

SQL> select segment_name,segment_type,tablespace_name,bytes/1024 k,extents,blocks,initial_extent/1024 init,next_extent/1024 next,pct_increase from dba_segments where segment_name like 'EMP%';

SEGMENT_NA SEGMENT_TYPE TABLESPACE_NAME K EXTENTS BLOCKS INIT NEXT PCT_INCREASE

---------- ------------ --------------- ---------- ---------- ---------- ---------- ---------- ------------

EMP TABLE USERS 64 1 8 64 1024

EMPLX TABLE LXTBS2 192 3 24 32 64

EMP1 TABLE SYSTEM 384 6 48 64 1024

七.高水位线：

1.高水位线介绍：

(段使用区的最后一个块的位置叫高水位线)，高水位线是oracle对表进行全表扫描的最后的位置。当数据删除。高水位线并不会下降。

2.分析表: 实验比較

truncate与delete的差别

使用：

analyze table emplx compute statistics;命令分析表(计算表。一般用于小表)。

SQL>

analyze table scott.emp estimate statistics;(对表进行估算，一般用于大表)。

SQL> exec DBMS_STATS.GATHER_TABLE_STATS('SCOTT','EMP'); (plsql分析表)。

#查看运行计划

SQL> select * from emplx;

Execution Plan

----------------------------------------------------------

Plan hash value: 155704778

---------------------------------------------------------------------------

---------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 3584 | 112K| 8 (0)| 00:00:01 |

| 1 | TABLE ACCESS FULL| EMPLX | 3584 | 112K| 8 (0)| 00:00:01 |

---------------------------------------------------------------------------

SQL> select table_name,tablespace_name,blocks, empty_blocks from dba_tables where table_name='EMPLX';

TABLE_NAME TABLESPACE_NAME BLOCKS EMPTY_BLOCKS

--------------- --------------- ---------- ------------

EMPLX LXTBS2

#分析表

SQL> analyze table emplx compute statistics;

Table analyzed.

SQL> select table_name,tablespace_name,blocks, empty_blocks from dba_tables where table_name='EMPLX';

TABLE_NAME TABLESPACE_NAME BLOCKS EMPTY_BLOCKS

--------------- --------------- ---------- ------------

EMPLX LXTBS2 23 0

#删除操作，

SQL> delete from emplx;

3584 rows deleted.

SQL> commit;

Commit complete.

#delete删除全部数据后没有减少高水位线。并不会减少全表扫描的代价

SQL> select table_name,tablespace_name,blocks, empty_blocks from dba_tables where table_name='EMPLX';

TABLE_NAME TABLESPACE_NAME BLOCKS EMPTY_BLOCKS

--------------- --------------- ---------- ------------

EMPLX LXTBS2

23 0

SQL> select * from emplx;

no rows selected

Execution Plan

----------------------------------------------------------

Plan hash value: 155704778

---------------------------------------------------------------------------

---------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 3584 | 112K| 8 (0)| 00:00:01 |

| 1 | TABLE ACCESS FULL| EMPLX | 3584 | 112K| 8 (0)| 00:00:01 |

---------------------------------------------------------------------------

#运行truncate操作：

SQL> truncate table emplx;

Table truncated.

#再对进行分析

SQL> analyze table emplx compute statistics;

Table analyzed.

#truncate减少高水位线，仅仅剩余一个区。

而且会减少全表扫描的代价

SQL> select table_name,tablespace_name,blocks, empty_blocks from dba_tables where table_name='EMPLX';

TABLE_NAME TABLESPACE_NAME BLOCKS EMPTY_BLOCKS

--------------- --------------- ---------- ------------

EMPLX LXTBS2 0 7

3.delete操作后减少表空间。（重点）

使用alter table emplx move 和alter table emplx shrink space都能够用来进行段收缩，减少高水位线。也都能够用来消除行连接和行迁移。

①.使用alter table move会把表最多收缩到创建表时的storage子句中指定的初始大小，使用later shrink space 不会受此限制。

②.使用alter table move 之后，索引会无效，须要重建，使用alter table shrink space 则不会。

③.仅仅能在表所在的表空间是自己主动段管理(ASSM)的时候，才干使用alter table shrink space; 而move在

自己主动段管理（ASSM）和手动段管理(MSSM)方式下都能使用。

④.能够使用alter table shrink space compact来对表进行碎片整理。而不调整高水位线，之后再次调用alter table shrink space 来释放空间。

⑤.能够使用alter table shrink space cascade;指定了cascade。则收缩行为将级联到全部支持收缩操作的从属段。可是物化视图、LOB索引、和IOT表例外;

⑥.move 更快 shrink space 较慢。

例：

alter table emplx move 和alter table emplx shrink space使用：在MSSM管理的表空间上创建EMPLX表

SQL> select table_name,tablespace_name, blocks,empty_blocks from dba_tables where table_name='EMPLX';

TABLE_NAME TABLESPACE_NAME BLOCKS EMPTY_BLOCKS

------------ ------------------------------ ---------- ------------

EMPLX LXTBS2 0 7

#插入数据

SQL> insert into emplx select * from emplx;

1792 rows created.

#分析表

SQL> analyze table emplx compute statistics;

Table analyzed.

SQL> select table_name,tablespace_name, blocks,empty_blocks from dba_tables where table_name='EMPLX';

TABLE_NAME TABLESPACE_NAME BLOCKS EMPTY_BLOCKS

------------ ------------------------------ ---------- ------------

EMPLX LXTBS2 23 0

#删除数据

SQL> delete from emplx where rownum <=2000;

2000 rows deleted.

SQL> commit;

Commit complete.

SQL> select table_name,tablespace_name, blocks,empty_blocks from dba_tables where table_name='EMPLX';

TABLE_NAME TABLESPACE_NAME BLOCKS EMPTY_BLOCKS

------------ ------------------------------ ---------- ------------

EMPLX LXTBS2 23 0

#使用shrink space 的前奏

SQL> alter table emplx enable row movement ;

Table altered.

#使用shrink space减少高水位线，报错，由于在shrink space仅仅能使用在assm管理段空间方式的表空间上。

SQL> alter table emplx shrink space;

alter table emplx shrink space

ERROR at line 1:

ORA-10635: Invalid segment or tablespace type

#使用move减少高水位线

SQL> alter table emplx move;

Table altered.

#分析表

SQL> analyze table emplx compute statistics;

Table analyzed.

#查看块的使用情况

SQL> select table_name,tablespace_name, blocks,empty_blocks from dba_tables where table_name='EMPLX';

TABLE_NAME TABLESPACE_NAME BLOCKS EMPTY_BLOCKS

------------ ------------------------------ ---------- ------------

EMPLX LXTBS2 10 5

转载地址：http://ylwdx.baihongyu.com/

你可能感兴趣的文章

java只能的round,ceil,floor方法的使用

查看>>

由于无法创建应用程序域，因此未能执行请求。错误: 0x80070002 系统找不到指定的文件...

Online Patching--EBS R12.2最大的改进

查看>>

Binary Search Tree Iterator leetcode

查看>>

Oracle性能优化--DBMS_PROFILER

zoj 2412 dfs 求连通分量的个数

查看>>

Java设计模式

查看>>

一文读懂 AOP | 你想要的最全面 AOP 方法探讨

查看>>

一.段的类型：

1.段的类型有：

2.表：

3.分区表：

4.簇表：

5.索引：

6.索引组织表（IOT表）：

7.分区索引类型：

二.数据块：(pctfree和pctused，行连接、行迁移、数据块)

三.段的管理方式(两种)

四.区的管理方式和段的管理方式的差别：

五.创建assm和mssm管理的表空间：

六.在mssm段管理方式的表空间创建表并设计表空间分配 ：

七.高水位线：

六.在mssm段管理方式的表空间创建表并设计表空间分配：