第09部分 oracle11g的表

一、表的类型

1、普通表

2、分区表

3、索引组织表IOT

4、簇表——速度快 

5、临时表--用户断开连接后，数据会消失

6、嵌套表、对象表等（不太重要，用的比较少）

二、高水位线（high-water mark，HWM）

1、高水位线是一个很有趣的概念，但是也是一个非常重要的概念。

     顾名思义，高水位线有点类型于水文监测站里测水深度的标杆一样，当水涨的时候，水位线随之上升，并在标杆留下一个水印痕，这个水印痕就是高水位线。

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

2、高水位线图

    （1）当表刚创建时，HWM位于表的第一个块中。

    （2）过一段时间后，随着在这个表中放入数据，而且使用了越来越多的块，HWM会升高。

    （3）但当我们删除了表中的一些（甚至全部）行，可能就会出现许多块不再包含数据，但仍然处于HWM之下，而且会一直保持在HWM之下。

    （4）记住：HWM永远不会下降，除非使用rebuild、truncated或shrunk这个对象(shrinking是10g的一个新特性，仅ASSM支持）。

    （5）HWM很重要，因为Oracle在全表扫描时会扫描HWM之下的所有块，即使其中不包括任何数据。这会影响full scan的性能，特别是当HWM之下的绝大多数块都为空时。

    （6）下面来看一个例子，创建一个有1000000行的表，然后对其执行select count(*)。接着delete所有行，再执行select count(*)统计出0行，比较两次执行的时间。

三、PCTFREE、PCTUSED

1、看oracle数据块的大小

    SQL>show parameter db_block_size; 

2、PCTFREE=20%，

    当用户insert数据操作，数据块应该保留多少的空间？看PCTFREE值，等于20%，则保留20%——PCFREE 参数含义

    留PCTFREE的空间，让用户进行update操作,'A'，'ABCDEFG' 

3、PCUSED，40%

    delete 操作，如果数据库的使用率低于40%的话，那么，重新让用户可以进行insert操作。pctused参数含义

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

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

  PCTUSED：是指当块里的数据低于多少百分比时，又可以重新被insert，一般默认是40,即40%，即：当数据低于40%时，又可以写入新的数据，这个时候处在下降期。

4、PCTFREE和PCTUSED

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

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

   注意：如果表空间段管理上启用了ASSM（即显示AUTO)，在建立表的时候，只能指定PCTFREE，否则可用指定PCTFREE和PCTUSED。

比如在users表空间中创建表，端管理为AUTO，只能指定PCTFREE大小

而在system 段管理为manual（手动）管理，则可以指定PCTFREE和PCTUSED的

三、普通表

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

     alter table scott.t1 allocate extent( datafile ‘路径/test.DBF' size 1m);

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

    SQL> create tablespace testtbs datafile '/data/u01/app/oracle/oradata/orcl/testtbs01.dbf' size 20m autoextend on;

    SQL>create table scott.t1(id int,name varchar2(10) ) tablespace testtbs;

    查看表t1大小：

    SQL>select * from dba_extents d where d.owner='SCOTT' and d.segment_name='T2';

    扩展表的存储空间

    SQL>alter table scott.t1 allocate exten(datafile '/data/u01/app/oracle/oradata/orcl/testtbs01.dbf' size 1M);

    在查看T1大小：

    SQL>select * from dba_extents d where d.owner='SCOTT' and d.segment_name='T2';

2、移动表move

    移动表move，从一个表空间移动到另一个表空间，可以清除表里的碎片 

    alter table t1 move [tablespace users];

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

    缺点：move过程中，表上不能有应用（不能有增删改的应用）。

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

MOVE的使用

测试在sys用户下：

创建表：SQL> create table t1 tablespace testtbs as select * from dba_objects;

查看有多少行：SQL> select count(*) from t1;或SQL> select max(rownum) from t1;

查看占用blocks数：SQL>select u.table_name,u.blocks,u.num_rows from user_tables u where u.table_name='T1';

分析表：SQL>analyze table t1 compute statistics for table;

查看占用blocks数：SQL>select u.table_name,u.blocks,u.num_rows from user_tables u where u.table_name='T1';

删除一些记录：

重新分析，查询，发现数量变了，但是blocks还是那么多1091未减少

进行高水位线调整，move

（1）移动到其他表空间：

SQL>alter table t1 move tablespace users;

（2）分析表并查看

3、shrink 表收缩表shrink，将数据行从一个数据块移动到另一个数据块，

    原理：分为2个阶段：收缩、降低HWM；在收缩阶段，可以对表进行DML操作，在降低HWM阶段，不能对表进行DML操作。

    语法：alter table t2 shrink  space  [cascade];

    前提：(1)表所在的表空间使用了ASSM。新增tablespace，默认的段管理是auto类型，非manual。

              (2)表上启用了 row movement ;SQL>alter table t2 enable row movement;

    当前表：

、

更改为row movement;

SQL>alter table t2 enable row movement;

shrink 表：

SQL>alter table t2 shrink space;

4，截断表 truncate，

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

    truncate table t2;

5，删除表drop 

    drop table t2 [cascade constraints] [purge];

6，删除列 (有些表的列没用，且数量很大，可以通过删除列进行清除，释放空间），比alter table drop column *要快很多。

    alter table t2 set unused column tele;——先不使用，速度非常快

    alter table t2 drop unused columns;——等后面再执行drop，就可以了

四、分区表----第10部分讲

五、索引组织表IOT

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

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

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

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

索引组织表IOT的创建

create table iot_student(     

    sno int,   sname varchar2(100),    sage int,    constraint pk_student primary key(sno)

    )    

organization index——此句说明是IOT表

   [ pctthreshold  30 overflow tablespace users ];

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

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

六、回收站的表，及清空

清空表回收站：SQL>purge recyclebin

七、簇表

  两个相互关联的表的数据，同时放到一个簇数据块中，当以后进行关联读取时，只需要扫描一个数据块就可以了，极大的提高了效率。 分为索引簇表和哈希簇表两类。

  索引簇表的创建步骤：

1，建立簇段cluster segment

SQL>create cluster cluster1(code_key number);

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

SQL>create table student(sno1 number,sname varchar2(10)) cluster cluster1(sno1);

SQL>create table address(sno2 number,zz varchar2(10)) cluster cluster1(sno2);

3，为簇创建索引。

SQL>create index index1 on cluster cluster1;

结果：后续如果使用student表的sno1和address表的sno2进行关联查询时，执行速度会非常快

4、查看clusters 簇表

SQL>select * from user_clusters;

SQL>select * from user_clu_columns;——查看哪些表的哪些列进行了簇

6、删除簇

SQL> drop cluster cluster1;——如果有关联的表，无法直接删除，先删除关联的表后，再进行簇的删除

八、临时表

    1、存放临时数据，可以使用临时表；临时表被每个session单独使用，即：不同session看到的临时表中的数据可能不一样。

    2、如果在退出session时删除临时表中的数据，可以使用on commit preserve rows；

         如果在用户commit或rollback时删除临时表中的数据，可以使用on commit delete rows；

    3、从v$sort_usage中查看正在使用临时表空间的session信息和SQL语句的ID号，从v$sort_segment中查看临时表空间中的段的使用情况。

    4、临时表在临时表空间中保存。

    5、创建：使用scott/scott 登录，记录session A，参数为on commit preserve rows

    SQL>crete global temporary table tmp_student(sno int,sname varchar2(10),sage int )on commit preserve rows;——用户退出session时删除临时表中的数据

（1）插入数据，并查询可以正常查询

SQL>insert into tmp_student values(1,'A',21);

SQL>insert into tmp_student values(2,'B',22);

SQL>insert into tmp_student values(3,'C',23);

（2）更换新的session B，sqlplus scott/scott登录，查询此表，无法查出数据，因为临时表有此特性

（3）如果在session B中增加数据，进行数据写入，那么session A中无法查询session B中的数据

SQL>insert into tmp_student values(4,'E',24);

SQL>insert into tmp_student values(5,'F',25);

如果删除session B的数据也不会影响session A的数据。

6、使用另一种方式参数on commit delete rows

 SQL>create global temporary table tmp_student(sno int,sname varchar2(10),sage int )on commit delete rows;——用户提交后，会删除临时表中的数据

用户在执行了commit后，就删除了数据；

注：如果不加参数：on commit delete rows，on commit preserve rows;默认用on commit delete rows;

如下图，默认后，类型是sys$transaction，就是事务性

6、删除临时表

    （1）根据条件，退出所有session或rollback，commit后删除——drop table tmp_student

7、查看临时表：select * from user_tables;

SQL>CREATE tablespace MESPRD_TBSP logging DATAFILE'/data/u01/app/oracle/oradata/KSSYORCLPRD/datafile/MESPRD_01.dbf' size 10240m autoextend on next 1000m maxsize 20480m extent management local;  

2、为表空间增加容量

    （1）.增加数据库数据文件大小：ALTER DATABASE DATAFILE '/data/u01/app/oracle/oradata/KSSYORCLPRD/datafile/MESPRD_01.dbf' resize  20480M；——可以扩大，可以缩小

    （2）.向表空间增加新文件： ALTER tablespace MESPRD_TBSP  add DATAFILE  '/data/u01/app/oracle/oradata/KSSYORCLPRD/datafile/MESPRD_02.dbf'   size 10240M  autoextend on next 1000M maxsize 30720M;

3、对表空间的数据文件执行移动操作---为了预防磁盘中的空间过小，数据无法放开，则可以移动到其他磁盘中

    （1）先将表空间设置离线状态---ALTER tabalespace 名字 offline

    （2）移动数据文件亦可以重命名

    （3）ALTER tablespace表空间名 rename DATAFILE '原文件路径及文件名' TO '新文件路径及文件名'

4、修改数据文件的自动扩展性和状态性

    SQL>ALTER DATABASE DATAFILE 数据文件路径和名称 autoextend on/off next几兆 maxsize几兆

    SQL>ALTER DATABASE D ATAFILE 数据文件路径和名称 online|offline|offline DROP（最后offline DROP是删除数据文件，用于数据库运行在非归档模式下的情况

5、重命名表空间、删除表空间

    重命名表空间：ALTER tablespace旧表空间名 rename  TO 新表空间名.注：必须在在线状态online时才能修改，offline时是无法修改的

    删除表空间： DROP tablespace表空间名 including contents and datafiles

注：contents表示删除表空间时，同时删除表空间的对象如：用户，表等；

DATAFILE是数据文件，如果不加此选项，则删除表空间实际上仅从数据字典和控制文件中将该表空间的有关信息删除，而不会删除操作系统中与该表空间对应的数据文件）

3、创建用户

SQL>create user mesprd identified by "Mes1qaz#EDC" default tablespace MESPRD_TBSP temporary tablespace TEMP profile DEFAULT;