Lesson 7 Managing Schema Objects

目标Objectives：

• 定义schema对象和数据类型
• 创建和修改表
• 定义约束
• 浏览列和表的组成
• 创建索引
• 创建视图
• 创建队列
• 解释临时表的使用
• 使用数据字典

 

 

---

1.Schema

 

什么是Schema

 

A shcema is a collection of database objects thar are owned by a paricular user. 

特定用户所拥有的数据库对象的集合

 

schema的名称与拥有该schema的用户名相同。Schema对象是直接对应着数据库数据的逻辑架构，schema对象包括的结构有表、视图和索引。

 

可以通过SQL和EM来创建和操作schema对象。当使用EM时，底层SQL会自动生成。

 

当创建数据库时，有几个schema已经自动创建，有两个重要的schema：

     SYS schema：包含数据字典

     SYSTEM schema：包含存放着管理信息的附加表格和视图。

 

使用Schema对象

 

命名数据库对象

 

- 名称长度要在1~30字节

  数据库名称最多8字节

  数据库链接名称最多128字节

- 未限额名字不能为Oracle的保留字

- 未限额名称必须以字母开始

- 限额名称不推荐使用

 

命名时候加“”的名字 引号内大小写敏感，查询时候要大小写相同。并不建议用这种方法命名。

 

定义表内的数据类型

 

通常数据类型：

     CHAR：固定长字符数据，固定长度size

     VARCHAR2：可变长字符串，最大长度size

     DATA：固定日期从公元前14712.1.1到公元9999.12.31

     NUMBER（p,s）：精确度p（1-38）和测度s（-84~127）

注意没有string类型

 

创建和修改表

 

使用EM 按步骤执行。

 

2.Constraints

Understanding Data Integrity数据的完整性约束

 

常见的五种完整性约束：

【非空约束】NOT NULL：默认状态下，表中的所有列都允许空的值。Null意思是值的空缺。NOT NULL约束要求一个表中的列都不能包含空。比如，可以将雇员表的姓氏列定义为NOT NULL约束。

【唯一键约束】UNIQUE KEY：提供表内提供唯一性约束。确保非主键列不输入重复的值。

【主键约束】PRIMARY KEY： 只能最多有一个主键，可以作为其他表的外键。要求两点：1.同一表格中的两列不允许重复。2.主键的列不允许为空。通常情况下， 数据库通过使用索引来要求主键的约束。

Primary key 、unique key均表示记录在表中的唯一性；

Primary key必定是unique key；反之则不成立；

Primary key也用于表间的关联，可以是其他表的主键的一部分，即外键。

unique key用于表内提供唯一性约束；

【参照完整性约束/外键约束】Referential integrity constraints：关系数据库中的不同表格可以通过共同的列来关联起来，并且管理这种关系的列必须是被保持的。参照完整性约束保证了这些关系可以保存下来。

参照完整性约束要求着表中的每个行，外键的值必须与父键的值所匹配。

例如：雇员表中的宿舍号定义为外键，这保证了宿舍号的列数据的非重复性，并且与宿舍表的主键相匹配。因此，宿舍号会无错误的存在在宿舍表的宿舍id列中。

【检查约束】Check constraints：检查完整性约束规定逻辑来管理DML语句的执行。

 

定义约束：

使用EM添加约束：选中表，点击编辑；点击约束；选择类型添加；确认。

 

约束违例：

比如： - 在主键中插入重复的值  - 删除主表  - 在检查约束的规定之外更新的列数据。

主要发生在DML语言执行的时候，包含以下几种情况：

- 破坏唯一性：尝试将含有唯一键约束的列（主键、唯一index）更改为重复的值。

- 破坏参照完整约束：破坏了每一个子列都有双亲列的约束要求。

- 破坏检查约束： 尝试在列中存储的值不满足检查约束的定义要求。比如AGE列的检查约束要求存入的必须为正值。

 

约束状态：

disable novalidate 既不会约束新增数据也不会验证已有数据，等同于disable

enable novalidate 约束新增数据但不会验证已有数据

disable validate 约束新增数据但不会验证已有数据，启用后禁止DML

enable validate 约束新增数据并验证已有数据，等同于enable

 

disable / enable 针对新增数据（对于唯一性约束，enable会自动创建唯一性索引，disable会自动drop索引）；

novalidate / validate 针对已有数据。

 

约束检查：

约束在以下时间被检查：

     - 语句执行时，对于非延迟约束

     - COMMIT时，对于延迟约束

例子：DML语句过程：

1.非延迟约束检查。—>2.COMMIT执行。—>3.延迟约束检查—>4.COMMIT完成。（若违反约束，语句将回滚）

 

非延迟约束：DML语句后生效。违反约束条件后会导致语句回滚。

延迟约束：当transaction提交之后进行检查。违反约束条件导致整个事件回滚。

 

举例：使用SQL语言创建约束

 

a.     ALTER TABLE countries

       ADD  ( UNIQUE(country_name)  ENABLE NOVALIDATE); 

语句执行后，所有COUNTRY表中的插入或更改操作都要求country_name的值是唯一的。如果这条语句执行前已经存在重复的值，NOVALIDATE关键字使得约束只对新增数据起到限制。

 

b.     ALTER TABLE employees ADD CONSTRAINT pk PRIMARY KEY

         (employee_id)

这条语句增加了雇员表的主键。PK约束将雇员id这一列设置为表的主键

 

c.     CREATE TABLE t1 (pk NUMBER PRIMARY KEY, fk NUMBER, c1 NUMBER, c2 NUMBER,

         CONSTRAINT ri FOREIGN KEY (fk) REFERENCES t1, CONSTRAINT ck1 CHECK( pk > 0 and c1>0 ) ) ;

这条语句在创建表的同时定义了约束，而不是使用ALTER命令后来更改。RI（参照完整性）约束使FK列的值必须为T1表的主键。CK1约束要求PK和C1列的值大于0

 

每个约束都有名字，如果有约束没在DDL语句中提供，那么会分配一个以SYS_开头的由系统提供的名字。

 

在表中查看一个列

从EM管理页面进入，选中结果中的一个表点击View

 

从表中查看行内容

从Table页面进入，从动作菜单选择浏览数据。

 

表的操作

· 类似创建

· 创建索引

· 生成DDL

· 授予权限

· 显示依附

· 浏览数据

 

drop删除表

删除表删除了以下内容：数据、表结构、触发器、对应索引、有联系的对象权限。【Flash back可找到】

可选的有：Cascade 删除级联的约束、Purge不放入回收站彻底删除。

 

语法：

          DROP TABLE [schema.] table [CASCADE CONSTRAINTS] [PURGE]

 

DROP TABLE命令删除了数据、表结构、关联的对象权限。除此之外：

     · 如果没有PURGE条件，表的定义、关联的索引、触发器会放入回收站中。表的数据仍然存在。但没有了表的定义是无法获取的。如果使用EM删除表，PURGE条件是不会使用的。

     · 使用FLASHBACK TABLE命令来从回收站中恢复schema对象，PURGE RECYCLEBIN命令清空回收站

     · CASCADE CONSTRAINTS选项是来删除所有的依赖的参照完整性约束。

     *如果不使用PURGE条件，删除后表所占据的空间依然有效。

 

truncate截断表

截断表使他的行数据不可用，并且可选择的释放占用的空间。索引也被删除。

作用：保留结构，仅删除数据，数据不能恢复。使用DDL语言，释放空间删除索引。而drop是DML语言，可以使用rollback/flashback来恢复。但空间不释放，只能本段使用。

 

语法：

     TRUNCATE TABLE hr.employees;

命令会产生以下影响。

 · 通过在表的开头设置高水位标志HWM来将表标记为空，使得行数据不可用

 · 没有undo数据生成并且命令静默提交因为 TRUNCATE TABLE是DDL命令。

 · 索引同样被清除

 · 通过外键关联的表格不可以被截断删除

 · 当这条命令被使用时，删除的触发器不会被触发。

 

比DELETE命令速度快数倍，原因有以下：

 · 重设表的HWM而不是对每一行进行删除操作

 · 没有undo data生成

 

3.Indexes

 

索引

索引是与表相联系的可选择性的结构。他们可以来提高数据修改和检索的性能。Oracle的所以提供了通往一条行数据的直接通道。

 

 

 

索引可以创建在表上的一或多个列上，索引创建之后会自动保持并被Oracle服务使用。更新表的数据，比如添加新的行，更新行数据或者删除行都会自动且对用户透明化地对所有相关索引产生影响。

 

索引种类

 

B-tree索引：binary tree二叉树形式的默认索引类型。

     Key values存放在balanced tree（B-tree）中，允许快速遍历。

Bitmap索引：对每个确定的值都有位图，每个位的位置代表着可能包含索引值的一行。非常适用于值少的情况。

 

B-tree索引

 

 

 

Root为根节点，branch 为分支节点，leaf 到最下面一层称为叶子节点。

每个节点表示一层，当查找某一数据时先读根节点，再读支节点，最后找到叶子节点。

叶子节点会存放index entry （索引入口），每个索引入口对应一条记录。

 

Index entry 的组成部分：

• Indexentry entry header 　　存放一些控制信息。
• Key column length 　　某一key的长度
• Key column value 　　 某一个key 的值
• ROWID 　　 指针，具体指向于某一个数据

 

Index entry特点：

• 如果有多个行有共同的Key值且索引不被压缩，Key值是重复的。
• 如果一个表的所有行都是NULL，那么没有与之匹配的Index entry。因此如果WHERE条件的指定为NULL，返回的结果是全表的scan
• 受限制的ROWID用来指向表中所有行都属于同一个段的rows。

 

DML语句在索引上的影响：

当DML操作在表上执行时，Oracle server维护着所有的索引：

• 插入Insert操作会在适当的块中插入一个Index entry
• 删除一行Delete a row只会在逻辑上删除Index entry。直到块上的所有入口都被删除掉之前，被删除的行的空间不再为新的入口可用。
• 对key columns的更改会导致索引的逻辑删除和添加。除去创建的时候之外，PCTFREE设置对索引没有影响。

 

Bitmap索引

 

 

 

在特定情况下位图索引比二叉树索引有更多好处：

• 当一个表有着数百万的行并且Key列基数少（列中仅有少量确定的值）的情况。比如，位图索引比B-tree更适用于包含于护照记录表中的性别与婚姻的列。
• 当队列经常使用多重与OR操作员有关的WHERE条件的情况。
• 当Key列是只读或者很少更改的情况

 

位图索引架构

 

     同样是平衡二叉树的形式，但是叶子节点存放着每个K值的位图而不是一列ROWID。位图中每个位对应一个可能的ROWID，并且位一旦被设置，意味着拥有对应的ROWID的行包含着Key值。

 

 

从上图，我们可以看出，一个叶子节点（用不同颜色标识）代表一个key , start rowid 和 end rowid规定这种类型的检索范围，一个叶子节点标记一个唯一的bitmap值。因为一个数值类型对应一个节点，当时行查询时，位图索引通过不同位图取值直接的位运算（与或），来获取到结果集合向量（计算出的结果）。

 

叶子节点有以下构成：

• Entry header包括列数字和lock信息
• Key值有长度和匹配的Key列的值组成。图例中，key仅有一个列组成，第一个入口的key值是Blue
• Stard ROWID：图例中标定为块号为10，行号为0，文件号为3
• End ROWID：图例中块号为12，行号8，文件号3
• Bitmap segment：包含一串bit位（当相关行有确定的Key值时bit位被设置，当无确定key值时为unset。Oracle服务使用专门的压缩方法来存放位图段）

 

使用位图索引

 

B-tree用来定位有给定Key值位图段的叶子节点。Start ROWID和位图段用来定位有着Key值的行。

当表中的Key列被修改时候，位图也必须被修改。

 

索引选项

 

• 唯一索引的值唯一
• key值按照升序/降序存储
• 把index key值翻转存储，避免热点操作在同一个块，不适合有条件去查找
• 复合index
• 函数索引
• 省空间

 

创建索引

 

可以从EM schema中通过点击来创建索引

 

视图

 

能起到安全作用，因为授权不能到列，如把某几列数据给某用户时使用。

 

队列