[大数据]Oracle数据库管理（12）——表

一、表的类型

1、普通表

表创建时不做任何设置，就是创建的普通表。

2、分区表

将表分成若干区。分区表可以增大表的存储量。也可以加快查询速度，查询时可以只从某一个分区中查询，而不进行全表扫描。

3、索引组织表（IOT）

索引组织表与普通表在组织结构上有很大区别。

4、簇表

经常将2个表做联合查询时，可以将这2个表设置成簇表，这样可以加快查询速度。

5、临时表

放在临时表空间中的那些表，在用户断开连接时，临时表会全部消失。

6、嵌套表

表中某一行某一列是另一张表。

7、对象表

将对象数据类型插入到表中。

二、高水位线（HWM）

高水位线有点类似于水文监测站里测水位的标杆，当涨水的时候，水位线随之上升，并在标杆上留下一个水印痕，这个水印痕就是高水位线。在数据库中，如果将表想象成一个从左到右依次排开的一系列块（块是Oracle存储数据的最小逻辑单元，例如可以设置一个块为8192字节），高水位线就是包含了数据的最右边的块。当表刚创建时，高水位线位于表的第一个块中，随之往表中插入数据，使用类越来越多的块，高水位线会升高。当我们删除了一些行，甚至是全部行时，就会出现许多块不包含数据，但高水位线永不会降,除非使用rebuild(重建表)、truncated（删除表）或shrunk（收缩）。
Oracle在进行全部扫描时会扫描高水位线下所有块，即使块中不包含任何数据，当高水位线下大多数块为空时，会影响全扫描的性能。

三、PCTFREE和PCRUSED

PCTFREE表示当用户执行“INSERT”操作时，数据块（Oracle的最小逻辑存储单元）应该保留多少的FREE 空间。例如“PCTFREE=20”表示当用户往一个数据块插入80%的数据后，就不会再往这个数据块中插入数据了。设置PCTFREE是为了用户在执行“UPDATE”操作时可以扩展存储空间。例如某张表中的某列是VARCHAR2类型，该列的第一行中存着“ABC”，占用3个字节，用户将该列该行改为“ABCDEF”，占用6个字节，此时留有FREE空间，将使扩展比较容易。PCTFREE的默认值为10。
PCTUSED表示当用户执行“DELETE”，如果数据块的使用率低于PCTUSED设置的值，那么重新让用户可以执行“INSERT”操作。PCTUSED的默认值是40。
如果表空间上启用了ASSM（自动段存储管理），在建立表的时候只能指定PCTFREE；否则可指定PCTFREE和PCTUSED。

//查看数据块的大小。以下查询结果表示一个数据块的大小为8192字节。
SQL> show parameter db_block_size
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_block_size                        integer     8192

四、普通表的存储空间操作

1、给表分配空间（主动扩展一个表所占用的空间）

//创建表空间，允许自动扩展。
SQL> create tablespace testtbs datafile 'E:\app\Administrator\oradata\orcl\testtbs001.dbf' size 20m autoextend on;
//创建表，使用新创建的表空间
SQL> create table t1(id number,name varchar2(10)) tablespace testtbs;
//查看表使用的空间
SQL> select * from dba_extents d where d.owner='SYSTEM' and d.segment_name='T1';
OWNER                          SEGMENT_NAME                                                                     PARTITION_NAME                 SEGMENT_TYPE       TABLESPACE_NAME                 EXTENT_ID    FILE_ID   BLOCK_ID      BYTES     BLOCKS RELATIVE_FNO
------------------------------ -------------------------------------------------------------------------------- ------------------------------ ------------------ ------------------------------ ---------- ---------- ---------- ---------- ---------- ------------
SYSTEM                         T1                                                                                                              TABLE              SYSTEM                                  0          1      86648      65536          8            1
//扩展一个表所占用的空间
SQL> alter table t1 allocate extent(datafile 'E:\app\Administrator\oradata\orcl\testtbs001.dbf' size 1m);
//

2、从一个表空间移动到另一个表空间

优点：可以清除数据块中的碎片。降低高水位现。
缺点：移动过程中，表上不能有应用。移动之后，表上的索引需要重新建。

//语法：
alter table 表名 move【tablespace 表空间名】;
//新建一个表t2，内容和dba_objects一样
SQL> create table t2 tablespace testtbs as select * from dba_objects;
//分析表，分析表之后才能查看表使用的数据块数量
SQL> analyze table t2 compute statistics for table;
Table analyzed
//查看t2表使用的数据块。
SQL> select u.table_name,u.blocks from user_tables u where u.table_name='T2';
TABLE_NAME                         BLOCKS
------------------------------ ----------
T2                                   1058
//删除部分行后，重新查看t2表使用的数据块
delete t2 where rownum < 40000;
SQL> commit;
Commit complete
SQL> select u.table_name,u.blocks from user_tables u where u.table_name='T2';
TABLE_NAME                         BLOCKS
------------------------------ ----------
T2                                   1058
//将t2移动到users表空间
SQL> alter table t2 move tablespace users;
Table altered

3、收缩（shrinking）

将数据从一个数据块移动到另一个数据块，分为2个阶段：收缩和降低高水位线。收缩阶段可以对表进行DML操作，降低高水位线阶段，不能对表进行DML操作。
可以收缩的前提：表所在的表空间上使用了ASSM，表上启用了“ROW MOVEMENT”。

语法：
alter table 表名 shrink sapce 【cascade】;

//创建一个表t3
SQL> create table t3 tablespace testtbs as select * from dba_objects;
//查看t3表有多少行
SQL> select max(rownum) from t3;
MAX(ROWNUM)
-----------
      72480
//分析t3
SQL> analyze table t3 compute statistics for table;
//查看t3有多少数据块、行
SQL> select u.table_name,u.blocks,u.num_rows from user_tables u where u.table_name = 'T3';
TABLE_NAME                         BLOCKS   NUM_ROWS
------------------------------ ---------- ----------
T3                                   1058      72480
//删除T3的前50000行
SQL> delete t3 where rownum < 50000;
49999 rows deleted
SQL> commit;
Commit complete
//删除行后再进行一次分析，并查看t3还有多少数据块和行
SQL> analyze table t3 compute statistics for table;
Table analyzed
SQL> select u.table_name,u.blocks,u.num_rows from user_tables u where u.table_name = 'T3';
TABLE_NAME                         BLOCKS   NUM_ROWS
------------------------------ ---------- ----------
T3                                   1058      22481
//可以看到t3表的行数减少了，而占用的数据块数并没改变
//t3上启用“ROW MOVEMENT”
SQL> alter table t3 enable row movement;
Table altered
//对t3进行收缩
SQL> alter table t3 shrink space;
Table altered
//再次分析表，并查看t3还有多少数据块和行
SQL> analyze table t3 compute statistics for table;
Table analyzed
SQL> select u.table_name,u.blocks,u.num_rows from user_tables u where u.table_name = 'T3';
TABLE_NAME                         BLOCKS   NUM_ROWS
------------------------------ ---------- ----------
T3                                    321      22481

4、截断表（truncate）

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

5、删除表（drop）

语法：
drop table 表名 【cascade  constraints】【purge】;
//cascade  constraints 表示删除表的所有约束
//purge 表示直接删除表，而不是放入回收站

6、删除列

可以使用

alter talbe 表名 drop column 列名;

删除列，但当表中的行数非常多时，使用这种方法会很满。可以使用下面的方法：

//将某个列设为“未使用”
alter table 表名 set unused column 列名;
//这个执行速度非常快。执行完后，查看表结构时将不显示未使用的列

等数据库空闲的时候再执行

//删除未使用的列
alter table 表名 drop unused columns;

五、索引组织表（IOT）

与普通表无序的组织方式不同，IOT表必须有主键，是有序的表，其中的数据按照主键进行存储和排序。
使用堆组织表（普通表）时，我们必须为主键和主键上索引分别流出空间。而IOT不存在主键的空间开销，因为IOT的数据存储在与其关联的索引中，索引就是数据，数据就是索引，二者已经合二为一。
IOT表中数据存放在索引块中，所以如果通过主键索引访问表时，只需要读取一个块即可。而如果通过主键索引访问普通表，至少需要读取2个块，一个是索引块，一个数据块。
对于经常通过主键访问的表，适合使用IOT表。

语法：
create table 表名(列名1 列属性1【,列名2 列属性2,......】,primary key(列名)) organization index 【pctthreshold 数据块百分比 overflow tablespace 表空间名】

因为所有数据都放入索引中，所以当表的数据量很大时，会降低索引组织表的查询性能，此时设置溢出段将主键和溢出数据分开存储以提高效率。
pcrtthreshold 指定一个数据块的百分比，当一行数据大小占用超出pctthreshold规定的值时，该行的除了主键列以外的其他列放入溢出段，即overflow 指定存储空间中去，所以pctthreshold是保留在索引块里的数据占整个索引块的大小百分比，从0到50%，默认值是50，即50%。

//创建IOT
SQL> create table students(sid number(10),name varchar2(15),primary key(sid)) organization index pctthreshold 30 overflow tablespace testtbs;
Table created
//删除IOT
SQL> drop table students;
Table dropped
//删除IOT后还留有一个溢出段。清空回收站可以自动删除；溢出段。
SQL> purge recyclebin;
Done

六、簇表

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

语法：
create cluster 簇名(关联列名 列属性)
//创建一个簇，簇名叫“cluster1”，关联列叫“student_id”，列类型为number
SQL> create cluster cluster1(student_id number);
Cluster created

2、基于簇，建立2个相关表，每个表都关联到簇段

语法：
create table 表名(列名1 列属性1【,列名2 列属性2,......】) cluster 簇段名(列名);
//创建一个学生信息表，关联列为“sid”
SQL> create table studnets_infor(sid number,sname varchar2(20)) cluster cluster1(sid);
Table created
//创建一个学生住址表，关联列为“snum”
SQL> create table students_addr(snum number,address varchar2(20)) cluster cluster1(snum);
Table created

3、为簇创建索引

create index 索引名 on cluster 簇段名;
//为cluster1创建索引index1
SQL> create index index1 on cluster cluster1;
Index created

4、删除簇

//先删除表
SQL> drop table studnets_infor;
SQL> drop table students_addr;
//再删除簇段
SQL> drop cluster cluster1;
Cluster dropped

七、临时表

存放临时数据时可以使用临时表。临时表被每个session单独使用，即不同的session看到的临时表里的数据可能不一样。
退出session时删除临时表中的数据，可以使用“on commit preserve rows”；如果在用户commit或rollback时删除临时表中的数据，可以使用“on commit delete rows”
从v$sor{t}_{u}sage中查看正在使用临时表空间的session信息和sql语句的ID号，从$sort_segment中查看临时表空间中段的使用情况。
临时表在临时表空间中保存。

语法：
create global temporary table 表名(列名1 列属性1【,列名2 列属性2,......】) on commit preserve rows;
create global temporary table 表名(列名1 列属性1【,列名2 列属性2,......】) on commit delete rows;