Table的学习 ①
又要学习Table了,其实每次学习都会有一些不同的收获。在这本教材里,主要偏重于对对创建Table时的一些边边角角的内容的详细介绍,所以只要能够完全掌握这篇文章,基本上对于Table的理解就可以更进一层。至于那些普通的管理操作,都是浮云。
一、管理表的准则
1、创建表之前的设计
一般表的结构由应用开发者负责,数据库管理员负责设置存储参数以及给表定义簇。具体的需要做到:
① 表被规范化
② 每个列具有合适的数据类型
③ 最后定义允许空值的列,以便节约存储空间
④ 合适的时候对表分簇,以节省存储空间和优化SQL语句性能
2、指定数据块空间参数
在创建每个表的时候,指定PCTFREE和PCTUSED参数。
3、指定每个表的位置
在创建表的时候,都用TABLESPACE子句指定合适的表空间,具体意义有:
① 可以提高数据库系统的性能
② 可以减少数据库管理的时间
③ 如果被默认创建在SYSTEM空间,会影响Oracle性能
④ 相关联的表被随机存储在多个表空间,则管理操作时间会增加
4、考虑并行创建表
如果使用CREATE TABLE ... SELECT ...语句来创建表时,尽量使用并行执行,这样由多个进程一起创建表,会改善创建的效率。
5、考虑创建时使用NOLOGGING
使用NOLOGGING子句可以使创建表时所产生的重做信息量最小,具体好处有:
① 节省了重做日志文件的空间
② 减少了创建表的时间
③ 改善了大表的并行创建的性能
注:NOLOGGING子句还指定了不等级后续的用SQL*Loader和INSERT操作所做的直接加载操作,但是后续的DML语句不受影响,任然会产生重做登记。
6、估计表的大小和设置存储参数
① 通过对表、索引、回滚段、重做日志文件的估计,以决定支持期望的数据库所需磁盘空间,购买合适的硬件设备。
② 估计单个表的大小,以便更好管理该表使用的磁盘空间,以配置合适的存储参数,改善I/O性能
7、规划大表
可以设置MAXEXTENTS UNLIMITED来简单规划大表,减少消耗的空间和碎片。具体可以考虑:
① 将表和索引分开。放入不同的表空间,甚至不同的磁盘。这样在重建索引时也可以更容易找到连续的空间。
② 分配足够的临时空间。用于防止排序时临时空间不够。
注:不能更改数据字典来使MAXEXTENTS大于允许的数据库块的最大值。
8、表的限制
① 包含对象类型的表不能导入Oracle 8之前的数据库中
② 当原始数据还在数据库中时,不能将类型和盘区表移动到不同的模式中
③ 不能将一个导出的表合并到不同模式中相同名字的表中
④ Oracle对表所能具有的列数(或对象类型属性)有限制
二、创建表
具体的创建方式,以及表的类型,可以参见“Oracle表介绍”。这里简单得介绍一下
:
1、表创建的格式
CREATE TABLE emp (
empno NUMBER(5) PRIMARY KEY,
ename VARCHAR2(15) NOT NULL,
job VARCHAR2(10),
mgr NUMBER(5),
hiredate DATE DEFAULT (sysdate),
sal NUMBER(7,2),
comm NUMBER(7,2),
deptno NUMBER(3) NOT NULL,
CONSTRAINT dept_fkey REFERENCES dept)
PCTFREE 10
PCTUSED 40
TABLESPACE users
STORAGE
(INITIAL 50K
NEXT 50K
MAXEXTENTS 10
PCTINCREASE 25)
2、创建临时表
CREATE GLOBAL TEMPORARY TABLE work_area
(startdate DATE,
enddate DATE,
class CHAR(20))
ON COMMIT DELETE ROWS;
说明:
ON COMMIT DELETE ROWS:事务特殊,提交后舍弃表。
ON COMMIT PRESERVE ROWS:会话特殊,结束会话时才舍弃表。
3、并行创建表
可以通过两种方法来并行创建表:
① 在CREATE TABLE .. AS SELECT语句中包含了PARALLEL子句
② 指定了ALTER SESSION FORCE PARALLEL DDL语句
CREATE TABLE emp_dept
PARALLEL
AS SELECT * FROM scott.emp
WHERE deptno = 10;
4、自动收集表上的统计信息
在CREATE(ALTER) TABLE语句中,加入MONITORING子句,则会使用DBMS_STATS包来自动收集统计数据。
可以通过这个包来收集、修改、查看、导出、导入和删除统计数据。所统计的信息可以使用DBA|ALL|USER_TAB_MODIFICATIONS视图来查看。
取消收集用NOMONITORING语句。
三、更改表
可以使用ALTER TABLE语句来更改表。更改表可能处于以下的原因:
● 添加、删除、修改现有的列
● 修改数据块空间的使用参数(PCTFREE|PCTUSED)
● 修改事务入口配置(INITRANS|MAXTRANS)
● 修改存储参数
● 将表移动到一个新段或表空间
● 明确分配一个盘区或收回未使用的空间
● 修改表的日志属性
● 修改CACHE|NOCACHE属性
● 添加、修改、删除表的相关完整性约束
● 启用、停用表的相关完整性约束或触发器
● 修改表的并行度
● 重命名表
● 添加、修改索引结构表的特征
● 添加、修改LOB列
● 添加、修改对象类型、嵌入的表、Varray列
● 启动、停用统计数据采集(MONITORING|NOMONITORING)
下面举例具体操作语句:
1、移动表到一个新段或表空间
ALTER TABLE emp MOVE
STORAGE
(
INITIAL 20K
NEXT 40K
MINEXTENS 2
MAXEXTENS 20
PCTINCREASE 0);
2、手动给表分配存储空间
使用ALTER TABLE语句中的ALLOCATE EXTENT子句来分配新盘区
使用ALTER TABLE语句中的DEALLOCATE UNUSED子句来明确收回未使用的空间
3、删除列
ALTER TABLE emp DROP COLUMN sal;
ALTER TABLE emp DROP (sal,comm);
注:不能删除所有的列,不能删除SYS拥有的表的列。
4、标记未使用且删除
ALTER TABLE emp SET UNUSED (sal,comm);
注1:标记未使用的列在查询和数据字典视图中均不显示,新建列可以使用改名字,且相关的约束、索引、统计数据均被删除。
注2:DBA|ALL|USER_UNUSED_COL_TABS视图中列出所有包含未使用列的表,以及未使用列的个数。
使用以下语句来删除标记为未使用的列:
ALTER TABLE emp DROP UNUSED COLUMNS;