十、表

最新推荐文章于 2022-09-22 12:47:36 发布

一个孤独漫步者的遐想

最新推荐文章于 2022-09-22 12:47:36 发布

阅读量335

点赞数

分类专栏： Oracle OCA 文章标签：数据库索引

本文链接：https://blog.csdn.net/weixin_43555997/article/details/118157507

版权

Oracle OCA 专栏收录该内容

15 篇文章 1 订阅

订阅专栏

十、表

理解11g中，与表相关的几个概念：高水位线、 PCTFREE、 PCTUSED
理解11g中，表的几种类型
理解何时需要创建簇表来提高访问速度

表的类型：

普通表
分区表
索引组织表IOT
簇表
临时表
嵌套表、对象表等

高水位线

高水位线（high-water mark，HWM）高水位线是一个很有趣的概念，但是也是一个非常重要的概念。顾名思义，高水位线有点类型于水文监测站里测水深度的标杆一样，当水涨的时候，水位线随之上升，并在标杆留下一个水印痕，这个水印痕就是高水位线。

	在数据库中，上述比喻很恰当。如果把表想象成一个平面结构，或者想象成从左到右依次排开的一系列块，高水位线就是包含了数据的最右边的块。如下图所示

在这里插入图片描述

当表刚创建时，HWM位于表的第一个块中。过一段时间后，随着在这个表中放入数据，而且使用了越来越多的块，HWM会升高。但当我们删除了表中的一些（甚至全部）行，可能就会出现许多块不再包含数据，但仍然处于HWM之下，而且会一直保持在HWM之下。记住：HWM永远不会下降，除非使用rebuild、runcated或shrunk这个对象（shrinking是10g的一个新特性，仅ASSM支持）。

	HWM很重要，因为Oracle在全表 扫描时会扫描HWM之下的所有块，即使其中不包括任何数据。这会影响full scan的性能，特别是当HWM之下的绝大多数块都为空时。下面来看一个例子，创建一个有1000000行的表，然后对其执行select count(*)。接着delete所有行，再执行select count(*)统计出0行，比较两次执行的时间。

PCTFREE

在这里插入图片描述

查看数据块大小

在这里插入图片描述

PCTUSED

在这里插入图片描述

PCTFREE和PCTUSED

建立表时候，注意PCTFREE参数的作用

PCTFREE：为一个块保留的空间百分比，表示数据块在什么情况下可以被insert，默认是10，表示当数据块的可用空间低于10%后，就不可以被insert了，只能被用于update；即：当使用一个block时，在达到pctfree之前，该block是一直可以被插入的，这个时候处在上升期。
PCTUSED：是指当块里的数据低于多少百分比时，又可以重新被insert，一般默认是40,即40%，即：当数据低于40%时，又可以写入新的数据，这个时候处在下降期。

假设你一个块可以存放100个数据，而且PCTFREE 是10， PCTUSED是40，则：
不断的向块中插入数据，如果当存放到90个时，就不能存放新的数据，这是受pctfree来控制，预留的空间是给UPDATE用的。

当你删除一个数据后，再想插入个新数据行不行？不行，必须是删除41个，即低于40个以后才能插入新的数据的，这是受 pctused来控制的。

注意：如果表空间上启用了ASSM，在建立表的时候，只能指定PCTFREE，否则可用指定PCTFREE和PCTUSED。

ASSM自动段管理

在这里插入图片描述

普通表

1，给表分配空间(主动扩展一个表所占用的空间)

alter table scott.t1 allocate extent( datafile ‘D:\app\Administrator\oradata\orcl\TESTTBS01.DBF’ size 1m);

从user_extents查看表所分配的空间大小

在这里插入图片描述

2，移动表move，从一个表空间移动到另一个表空间，可以清楚表里的碎片 alter table t1 move [tablespace users];

优点：清除数据块中的碎片，降低HWM

缺点：move过程中，表上不能有应用。

move之后，表上的索引需要重建。

在这里插入图片描述

3，收缩表shrink，将数据行从一个数据块移动到另一个数据块，分为2个阶段：收缩、降低HWM；在收缩阶段，可以对表进行DML操作，在降低HWM阶段，不能对表进行DML操作。

alter table t2 shrink space [cascade];

前提：表所在的表空间使用了ASSM。

表上启用了 row movement

4，截断表truncate，将表中的记录全部删除，保留表的结构。释放表所占用的全部数据块，并把HWM调整到最低，而且不能回滚。

5，删除表drop

drop table t2 [cascade constraints] [purge];

6，删除列

alter table t2 set unused column tele;

alter table t2 drop unused columns;

索引组织表IOT

1，区别于普通表的无序组织方式，IOT(Index Organized Table)表必须有主键，是有序的表，其中的数据按照主键进行存储和排序。

2，使用堆组织表时，我们必须为表和表主键上的索引分别留出空间。而IOT不存在主键的空间开销，因为IOT的数据存储在与其关联的索引中，索引就是数据，数据就是索引，二者已经合二为一。

3，IOT表中，表的数据存放在索引块中，所以如果通过主键索引访问表时，只需要读取一个块即可。而如果通过主键索引访问普通表，至少需要读取两个块，一个是索引块、一个是数据块。

4，对于经常通过主键访问数据的表来说，适合使用IOT表。

create table iot_student(     
    sno int, 
    sname varchar2(100),
    sage int,     constraint pk_student primary key(sno))
   organization index 
  [ pctthreshold  30 overflow tablespace users ];

因为所有数据都放入索引，所以当表的数据量很大时，会降低索引组织表的查询性能。此时设置溢出段将主键和溢出数据分开来存储以提高效率。

说明： pctthreshold制定一个数据块的百分比，当行数据占用大小超出时，该行的其他列数据放入溢出段，即overflow指定存储空间中去，所以pctthreshold是保留在索引块里的数据量占整个索引块的大小百分比，从0到50%。

默认的 pctthreshold的值是50，即50%。

簇表

两个相互关联的表的数据，同时放到一个簇数据块中，当

以后进行关联读取时，只需要扫描一个数据块就可以了，极

大的提高了效率。

分为索引簇表和哈希簇表两类。

索引簇表的创建步骤：

1，建立簇段cluster segment

2，基于簇，创建两个相关表，每个表都关联到cluster segment上。

3，为簇创建索引。

create cluster  scott.cluster1(code_key number);
create table scott.student (sno1 number, sname  varchar2(10)) cluster scott.cluster1(sno1);
create table scott.address (sno2 number, zz varchar2(10)) cluster scott.cluster1(sno2);
create index index1 on cluster scott.cluster1;   --为簇创建索引