表分析、约束及表间关系——《12年资深DBA教你Oracle开发与优化——性能优化部分》

目录：

Oracle数据完整性和锁机制 

索引及优化之表分析 

表分析、约束及表间关系 

Oracle体系结构1

Oracle体系结构2 

海量数据库及分区1 

海量数据库及分区2 

海量数据库及分区3 

海量数据库及分区4 

高级SQL优化(一) 

PPT和源码下载：   http://sishuok.com/forum/posts/list/6365.html

配套视频课程

   Oracle性能优化 http://sishuok.com/product/601 

   海量数据库和高级SQL优化 http://sishuok.com/product/602

 

表分析

Oracle优化器简述

  

   Oracle提供两种优化器，CBO(Cost based Optimizer)和RBO(Ruler Based Optimizer)。CBO要求的是 最有效的方式是成本最小的访问方法，其中成本也称为开销，主要指I/O时间和CPU时间，大多数情况下，主要开销在对磁盘的I/O的开销。CBO的依据包括：

  

 

表分析原理

CBO要基于统计数据

l默认情况下，Oracle 11g数据库会自动收集CBO需要的统计数据，默认的是工作日晚上10点至早上6点以及休息日全天；

l一般是自上次统计至今数据变化超过10%的表会被自动重新统计；

l如果没有分析统计数据，则使用参数 OPTMIZER_DYNAMIC_SAMPLING的默认值，此举往往是低效的；

 

何时需要手动统计

l易变化的表发生了删除、清空等操作；

l表的体积发生了10%以上的增加时，但我们建议减少时也可以统计

l统计的方法是对表进行分析，oracle会同步分析表的列、索引

 

如何查看表是否被分析过

lSelect a.LAST_ANALYZED,a.TABLE_NAME,

  a.NUM_ROWS,a.SAMPLE_SIZE

  from dba_tables a

    where owner = 'XJGL‘  order by a.LAST_ANALYZED desc

 

如何查看自动收集统计信息的任务

lselect client_name,status from dba_autotask_client，其中client_name为auto optimizer stats collection的即代表自动收集任务；

l而10g中通过查询dba_scheduler_jobs视图来查看，11g和10g的此不同点在部分11g书籍中描述是错误的，应以0racle官方文档为准。

 

 

启用自动收集统计信息的任务

BEGIN

    DBMS_AUTO_TASK_ADMIN.ENABLE(

      client_name => 'auto optimizer stats collection',

      operation => NULL,

     window_name => NULL);

 END;

禁用自动收集统计信息的任务

 

BEGIN

    DBMS_AUTO_TASK_ADMIN.DISABLE(

      client_name => 'auto optimizer stats collection',

      operation => NULL,

     window_name => NULL);

 END;

自动收集统计信息的任务依赖于谁

l依赖于Oracle的修改监控(modification monitoring )功能，如果该功能被禁用，则自动收集统计信息的任务无法探测到失真的统计信息(stale statistics)；

l只有当参数STATISTICS_LEVEL设置为ALL或者TYPICAL时，修改监控功能才有效，该参数的默认值是TYPICAL。

l在OEM中，选择服务器->数据库配置->初始化参数可查看该参数：

 

 

防止失真的统计信息

 

l修改监控的功能是跟踪自上次收集统计信息以来，估算其所监控的表的更新操作（I、U、D）和清空操作所引起的数据变化量（10%的问题）；

l我们可以使用视图USER_TAB_ MODIFICATIONS来查询表中数据的变化情况，但是一般情况下，该表的数据的更新会有延迟，此时可以使用DBMS_ STATS.FLUSH_DATABASE_MONITORING_INFO 从内存中立即获取未推送的监控信息；

l收集统计数据(当数据变化超过10%时，选项设置为GATHER _AUTO或GATHER _STALE ，示例见附件) 
 gather_database_stats   为数据库的所有对象收集统计数据

    gather_schema_stats    为用户(模式)的所有对象收集统计数据，最常用

 gather_table_stats         为某个表及其索引收集统计数据

  gather_index_stats         为某个索引收集统计数据

 

表分析的目的和作用

 

l收集或删除索引或索引分区、分区表或表、索引组织表、聚簇、或标量对象属性的统计信息；

l验证结构索引或索引分区、表或表分区、索引组织表、聚簇、或对象的引用（ REF ）；

l识别表、聚簇中行迁移。

针对analyze的搜集和删除统计信息功能而言，oracle推荐使用DBMS_STATS包来搜集优化信息，DBMS_STATS可以并行的搜集信息，可以搜集分区表的全局信息，进一步来说，按成本的优化器只会使用DBMS_STATS包所统计出来的信息。使用ANALYZE在两方面不依赖于CBO：

(1).使用VALIDATE或LIST CHAINED ROWS子句 (2)收集freelist的块信息

 

表分析需要的权限

l模式的表分析：模式首先必须是本地的，且该模式要么是自己的、要么需有ANALYZE ANY系统权限

l聚簇或表的行迁移列出分析：列表对象是属于当前模式自己的、或在列表对象上具有INSERT权限、或具有INSERT ANY TABLE系统权限。

l分区表验证：将你分析的rowid写入的表上具有INSERT权限，或者或具有INSERT ANY TABLE系统权限。

 

表分析语法

 

ANALYZE 
  { TABLE [ schema.]table 
      [ PARTITION ( partition ) | SUBPARTITION ( subpartition ) ] 
   | INDEX [ schema. ]index 
      [ PARTITION ( partition ) | SUBPARTITION ( subpartition ) ] 
  | CLUSTER [ schema. ]cluster 
  } 
  { COMPUTE [ SYSTEM ] STATISTICS [for_clause] 
  | ESTIMATE [ SYSTEM ] STATISTICS [for_clause][SAMPLE integer { ROWS | PERCENT }] 
  | validation_clauses 
  | LIST CHAINED ROWS [ into_clause ]
  | DELETE [ SYSTEM ] STATISTICS 
  } ;

 

  表

 

l对指定的表进行分析，同步也会分析该表上基于函数的索引，分析的结果会放在USER_TABLES, ALL_TABLES,DBA_TABLES表

lnum_rows(行数)、*blocks(表用到的数据块数)、*empty_blocks(分配给该表未使用的数据块数)、ave_space(每个数据块平均可用字节数)、chain_cnt(行迁移数)、avg_row_len（行平均字节数），带*的表示会精确统计。

l不能分析数据字典表、外部表(可用dbms_stat)、临时表、可变数组、嵌套表、引用以及其它对象类型；建议仅仅对自己模式下的数据表进行分析。

 

   索引

 

l对指定的索引进行分析，分析的结果会放在USER_INDEXES, ALL_INDEXES, DBA_INDEXES中

l*blevel(根节点至叶子节点的深度)、leaf_blocks(叶子节点块数)、distinct_keys(唯一索引的数量)、avg_leaf_blocks_per_key(每个索引的平均叶子节点数)、 avg_data_blocks_per_key(每个索引的平均数据块数) 、avg_row_len（行平均字节数），带*的表示会精确统计。

   聚簇

 

l对指定的聚簇进行分析，分析的结果会放在USER_CLUSTERS, ALL_CLUSTERS, DBA_CLUSTERS中

l如果一定要用聚簇，请参阅相关文档。

 

   统计

 

lCOUMPUTE全分析统计，为CBO服务

lESTIMATE按抽样的比例进行分析，也是为CBO服务

 

   其它

 

lLIST CHAINED ROWS列出行迁移数据，可以使用数据库自带的脚本（ UTLCHAIN.SQL ）建立结果写入的表CHAINED_ROWS

lDELETE表示删除统计数据，如果不再需要统计数据时，可以使用该项，指定SYSTEM表示删除Oracle数据库系统的统计数据（不含用户统计的）

 

 

方法

1.写ANALYZE语句     2.使用TOAD工具

  

  

 

 

表分析举例

 

  

  

 

 

约束

常用的数据完整性约束规则包括：

1.NOT NULL

2.唯一关键字

3.主关键字

4.外键

5.检查项Check

 

由于本部分内容再前面的章节中已经穿插讲解，本处不再赘述

 

约束条件

l使用约束的目的是为了保持数据的完整性；

l约束条件是定义一个或多个条件的一种方法，用户的输入在被Oracle接收进数据表之前，必须满足这些约束条件；

l约束条件作为表的定义的一部分被存储，以备将来自动执行

l不符合约束条件的数据将被Oracle中断;

lDML中只有Insert、Update语句能触发约束条件；

l约束数据存放在USER_CONSTRAINS、DBA_CONSTRAINS、ALL_CONSTRAINS中。

 

NULL & NOT NULL

l建立数据库表时，字段的默认值是NULL，如果要约定非空需要指定；

l可以修改字段属性，语法：

  ALTER TABLE table_name MODIFY(column_name NULL|NOT NULL)

但如果数据库中已经存在数据，且该列有 null值，则修改不会成功。

唯一约束

l唯一约束禁止一个列或者联合列的数据重复，但允许某些列为null；

l数据库在创建唯一约束时，强制创建或者重用列上的索引。如果列上无索引，那么强制创建一个唯一索引，否则就重用之前的索引

l增加唯一约束语法：

  ALTER TABLE table_name ADD CONSTRAINT  constrain_name UNIQUE(column_name)

 

主键

l主键实际上是非空约束和唯一约束的组合体；

l虽然Oracle也可以通过Alter语句修改、增加主键，但我们还是建议先删除，再增加；建议使用CASE工具，以避免死记语法。（语法在表间关系部分讲）

 

外键

l外键是保证数据完整性的，表示数据间关系代数的关系，但不建议大量使用，而采用替代的方法；

l同主键，增加和修改建议使用工具。 （语法在表间关系部分讲）

CHECK

l检查约束要求取值符合特定的范围；

l一般用在取值基数少的情况下使用，如状态、性别等；

l检查约束上建立位图索引会是一个很不错的主意；

l增加检查约束的语法：

ALTER TABLE table_name ADD CONSTRAINT  constraint_name

 CHECK(column_name and conditaion);

l原则上，检查约束应该在数据库设计阶段就已经定义好，因此在创建表时就应建立：

XB    CHAR(1) default '男' 

         constraint CKC_XB_STUDENT check (XB is null or ( XB in ('男','女') ))

 

启用、禁用、删除和重命名约束

l启用语法（详细参考Oracle官方文档） ：

ALTER TABLE table_name ENABLE CONSTRAINT  constraint_name;

l禁用语法（详细参考Oracle官方文档） ：

    ALTER  TABLE table_name DISABLE CONSTRAINT  constraint_name;

l删除语法（详细参考Oracle官方文档）：

    ALTER  TABLE table_name DROP CONSTRAINT  constraint_name;

l修改语法（详细参考Oracle官方文档） ：

    ALTER  TABLE table_name MODIFY CONSTRAINT  constraint_name;

l重命名

    ALTER  TABLE table_name RENAME CONSTRAINT  old_name TO new_name;

 

重要提醒：强烈建议此类管理功能一律使用工具（如TOAD）来实现。

 

约束使用经验谈

l能定义为NOT NULL的一律定义为NOT NULL，如果确实可能取值为NULL，建议使用默认值后再定义为NOT NULL（如日期型和数字型），此举好处很多；

l网络连接速度慢，客户端要求响应时间快的，约束最好放在前台程序实现，反之最好放在后台实现；

l由于唯一约束实际上是唯一索引，扫描最快，所以能建议唯一约束的一定要使用唯一约束；

l能命名的约束要尽量命名，以便于维护和分析（索引）；

lCHCEK约束尽量使用NOT NULL，如检索较多，建立位图索引。

 

表间关系

主从表

   如果表A跟表B之间的关系是1:n，则A表和B表之间是主从关系，即A表是主表，B表是从表。主从表经常在主表上使用删除触发器来保证A表的记录被删除时，B表中对应的记录通过触发器自动被删除，从而维持数据的一致性。

  

 

 

主键

语法：

   ALTER TABLE table_name ADD CONSTRAINT constraint_name PRIMARY KEY(column1,column2,…);

   也可以创建表同步创建主键

 create table Student  (

   XH                   CHAR(4)                          not null,

   XM                   VARCHAR2(10),

   NL                   INTEGER,

   XB                   CHAR(2)                        default '男' 

         constraint CKC_XB_STUDENT check (XB is null or ( XB in ('男','女') )),

   JG                   VARCHAR2(10),

   constraint PK_STUDENT primary key (XH)

)；

外键

语法：

   ALTER TABLE table_name1 ADD CONSTRAINT constraint_name FOREIGN KEY(column) REFRENCE table_name2(column);

create table gjdm  (

   GJDM                 CHAR(3)                          not null,

   GJMC                 VARCHAR2(60)                     not null,

   GJJC                 VARCHAR2(20),

   YWMC                 VARCHAR2(32),

   DQDM                 CHAR(8),

   WHR                  VARCHAR2(20),

   WHRQ                 DATE,

   constraint PK_gjdm primary key (GJDM)

);

alter table PTJC_GJDM

   add constraint FK_gjdm_FK_dqdm foreign key (DQDM)

      references dqdm (DQDM);

 

表连接

1.相等连接

  相连接的两个表或视图中两个字段的取值完全相同的连接。

2.自连接

  一个表自己与自己进行的连接。

SELECT e1.last_name||' works for '||e2.last_name

"Employees and Their Managers"

FROM employees e1, employees e2

WHERE e1.manager_id = e2.employee_id AND e1.last_name LIKE 'R%'

ORDER BY e1.last_name;

3.内连接

  内连接又称为简单连接，是仅仅返回满足条件的行数据的一种连接， 内连接 是相较于外连接的一个概念。

 

4.外连接

  外连接不仅返回满足条件的数据，也返回在一个表中存在而在另外一个表中不存在的数据，是对内连接的一种扩展， 又分为左外连接和右外连接。 第十二 课中左外连接内容如下：

 

左外连接是使用Left join、或left outer join或者在右侧表使用“（+）”，表示以左表为驱动表，左表的记录全部显示出来，右表匹配的数据才才显示出来，即左表不加限制，右表加限制。

示例:

 (1). select  * from classes a                   --(15 lines)

 (2). select  * from classes_2 a              --(3 lines)

 －－

第十二课中右外连接内容如下：

使用right join、或right outer join或者在左侧表使用“（+）”，表示以右表为驱动表，右表的记录全部显示出来，左表匹配的数据才才显示出来，即右表不加限制，左表加限制。示例:

 (1). select *                                           --(3 lines，右表3行，左表匹配3行)

            from classes a,  classes_2 b 

             where a.bjbh(+)  = b.bjbh;

 (2). select * from classes a right  join classes_2 b               --(同(1))

            on a.bjbh  = b.bjbh

 (3). select * from classes a right outer  join classes_2 b      --(同(1))

      on a.bjbh  = b.bjbh

---

 

序列

  序列(SEQUCENCE)其实就是顺序号的记数器，按定义的取值方向不断变化，一般用来给表当主键，此时该主键往往是代理主键。

 

 

语法1:

 CREATE SEQUENCE sequence_name;

 

语法2:

 CREATE SEQUENCE sequence_name

 [INCREMENT BY increment_value]

 [START WITH start_value]

 [MAXVALUE max_value]

 [MINVALUE min_value]

 [CYCLE];

 

 

使用方法：

SELECT sequence_name.nextval

  FROM DUAL;

 

在INSERT语句的VALUE部分可以直接使用sequence_name.nextval取值

 

 

同义词

   同义词就是别名，作用是将应用的对象加以简化。访问其它用户已经授权的表，需要用格式：user_name.table_name的格式，因此可以使用同义词将简化对表的访问。

  语法：

CREATE [PUBLIC] SYNONYM synonym_name FOR object_name；

其中[PUBLIC]选项表示是公共同义词。

  修改同义词

 原则：先删除后建立

 语法：

   DROP [PUBLIC] SYNONYM synonym_name ；

 

DBLINK

   数据库链接是用来在一个数据库中访问另外一个数据库中已经授权的对象， 一般用作分布式数据库接口。

  语法：

CREATE [SHARED][PUBLIC] DATABASE LINK dblink_name

[CONNECT TO [ usrer] [ current_user] IDENTIFIED BY  password] [AUTHENTICATED BY user IDENTIFIED BY password] USINGconnect_string

 

   使用方法：

 object_name@dblink_name

 

使用

1.序列往往用作代理主键，即一个表的主键应该是联合主键时，采用序列来建立一个代理作为主键，而联合主键往往定义为唯一索引；

2.同义词是用来简化对其他数据库中对象、或者同一数据库中其它模式的对象访问的，往往与DBLink一起使用；

3.如果前端程序采用Java Hibernate框架，原则上应尽量多的使用同义词，并在其模型xml文件中指定表的代理主键id对应的序列，此举会大大简化编程；

4.基于数据库的不同物理地址的数据库接口开发，一个非常简便的方法就是使用dblink+同义词+存储过程+oracle Job，并定义和记录自己的日志。

 

习题

1.Oracle的优化器分哪两种类型？其对SQL的执行一般包括哪些方法，结合第21课的内容采用执行计划举例说明。

2.绘制Oracle优化器模型结构图，并简单加以说明。

3.为什么要对表和索引等数据库对象进行分析？如何查看自动收集统计信息的任务，并写出启用和禁用该任务的PL/SQL块。

4.如何查看表或者索引是否被分析过？

5.对表和索引进行分析后，其数据分别存放在哪些数据字典中 ？各有哪些重要信息，试举例说明之。

6.不同类型的约束分别建议在什么情况下时候？

7.如何启用和禁用约束，什么情况下对可空约束改成非空约束数据库会报错？

8.举例说明序列和同义词，并解释哪些情况下应该尽量使用序列。

 

转载请注明【http://sishuok.com/forum/blogPost/list/0/6373.html】 

表分析

Oracle优化器简述

  

   Oracle提供两种优化器，CBO(Cost based Optimizer)和RBO(Ruler Based Optimizer)。CBO要求的是 最有效的方式是成本最小的访问方法，其中成本也称为开销，主要指I/O时间和CPU时间，大多数情况下，主要开销在对磁盘的I/O的开销。CBO的依据包括：

  

 

表分析原理

CBO要基于统计数据

l默认情况下，Oracle 11g数据库会自动收集CBO需要的统计数据，默认的是工作日晚上10点至早上6点以及休息日全天；

l一般是自上次统计至今数据变化超过10%的表会被自动重新统计；

l如果没有分析统计数据，则使用参数 OPTMIZER_DYNAMIC_SAMPLING的默认值，此举往往是低效的；

 

何时需要手动统计

l易变化的表发生了删除、清空等操作；

l表的体积发生了10%以上的增加时，但我们建议减少时也可以统计

l统计的方法是对表进行分析，oracle会同步分析表的列、索引

 

如何查看表是否被分析过

lSelect a.LAST_ANALYZED,a.TABLE_NAME,

  a.NUM_ROWS,a.SAMPLE_SIZE

  from dba_tables a

    where owner = 'XJGL‘  order by a.LAST_ANALYZED desc

 

如何查看自动收集统计信息的任务

lselect client_name,status from dba_autotask_client，其中client_name为auto optimizer stats collection的即代表自动收集任务；

l而10g中通过查询dba_scheduler_jobs视图来查看，11g和10g的此不同点在部分11g书籍中描述是错误的，应以0racle官方文档为准。

 

 

启用自动收集统计信息的任务

BEGIN

    DBMS_AUTO_TASK_ADMIN.ENABLE(

      client_name => 'auto optimizer stats collection',

      operation => NULL,

     window_name => NULL);

 END;

禁用自动收集统计信息的任务

 

BEGIN

    DBMS_AUTO_TASK_ADMIN.DISABLE(

      client_name => 'auto optimizer stats collection',

      operation => NULL,

     window_name => NULL);

 END;

自动收集统计信息的任务依赖于谁

l依赖于Oracle的修改监控(modification monitoring )功能，如果该功能被禁用，则自动收集统计信息的任务无法探测到失真的统计信息(stale statistics)；

l只有当参数STATISTICS_LEVEL设置为ALL或者TYPICAL时，修改监控功能才有效，该参数的默认值是TYPICAL。

l在OEM中，选择服务器->数据库配置->初始化参数可查看该参数：

 

 

防止失真的统计信息

 

l修改监控的功能是跟踪自上次收集统计信息以来，估算其所监控的表的更新操作（I、U、D）和清空操作所引起的数据变化量（10%的问题）；

l我们可以使用视图USER_TAB_ MODIFICATIONS来查询表中数据的变化情况，但是一般情况下，该表的数据的更新会有延迟，此时可以使用DBMS_ STATS.FLUSH_DATABASE_MONITORING_INFO 从内存中立即获取未推送的监控信息；

l收集统计数据(当数据变化超过10%时，选项设置为GATHER _AUTO或GATHER _STALE ，示例见附件) 
 gather_database_stats   为数据库的所有对象收集统计数据

    gather_schema_stats    为用户(模式)的所有对象收集统计数据，最常用

 gather_table_stats         为某个表及其索引收集统计数据

  gather_index_stats         为某个索引收集统计数据

 

表分析的目的和作用

 

l收集或删除索引或索引分区、分区表或表、索引组织表、聚簇、或标量对象属性的统计信息；

l验证结构索引或索引分区、表或表分区、索引组织表、聚簇、或对象的引用（ REF ）；

l识别表、聚簇中行迁移。

针对analyze的搜集和删除统计信息功能而言，oracle推荐使用DBMS_STATS包来搜集优化信息，DBMS_STATS可以并行的搜集信息，可以搜集分区表的全局信息，进一步来说，按成本的优化器只会使用DBMS_STATS包所统计出来的信息。使用ANALYZE在两方面不依赖于CBO：

(1).使用VALIDATE或LIST CHAINED ROWS子句 (2)收集freelist的块信息

 

表分析需要的权限

l模式的表分析：模式首先必须是本地的，且该模式要么是自己的、要么需有ANALYZE ANY系统权限

l聚簇或表的行迁移列出分析：列表对象是属于当前模式自己的、或在列表对象上具有INSERT权限、或具有INSERT ANY TABLE系统权限。

l分区表验证：将你分析的rowid写入的表上具有INSERT权限，或者或具有INSERT ANY TABLE系统权限。

 

表分析语法

 

ANALYZE 
  { TABLE [ schema.]table 
      [ PARTITION ( partition ) | SUBPARTITION ( subpartition ) ] 
   | INDEX [ schema. ]index 
      [ PARTITION ( partition ) | SUBPARTITION ( subpartition ) ] 
  | CLUSTER [ schema. ]cluster 
  } 
  { COMPUTE [ SYSTEM ] STATISTICS [for_clause] 
  | ESTIMATE [ SYSTEM ] STATISTICS [for_clause][SAMPLE integer { ROWS | PERCENT }] 
  | validation_clauses 
  | LIST CHAINED ROWS [ into_clause ]
  | DELETE [ SYSTEM ] STATISTICS 
  } ;

 

  表

 

l对指定的表进行分析，同步也会分析该表上基于函数的索引，分析的结果会放在USER_TABLES, ALL_TABLES,DBA_TABLES表

lnum_rows(行数)、*blocks(表用到的数据块数)、*empty_blocks(分配给该表未使用的数据块数)、ave_space(每个数据块平均可用字节数)、chain_cnt(行迁移数)、avg_row_len（行平均字节数），带*的表示会精确统计。

l不能分析数据字典表、外部表(可用dbms_stat)、临时表、可变数组、嵌套表、引用以及其它对象类型；建议仅仅对自己模式下的数据表进行分析。

 

   索引

 

l对指定的索引进行分析，分析的结果会放在USER_INDEXES, ALL_INDEXES, DBA_INDEXES中

l*blevel(根节点至叶子节点的深度)、leaf_blocks(叶子节点块数)、distinct_keys(唯一索引的数量)、avg_leaf_blocks_per_key(每个索引的平均叶子节点数)、 avg_data_blocks_per_key(每个索引的平均数据块数) 、avg_row_len（行平均字节数），带*的表示会精确统计。

   聚簇

 

l对指定的聚簇进行分析，分析的结果会放在USER_CLUSTERS, ALL_CLUSTERS, DBA_CLUSTERS中

l如果一定要用聚簇，请参阅相关文档。

 

   统计

 

lCOUMPUTE全分析统计，为CBO服务

lESTIMATE按抽样的比例进行分析，也是为CBO服务

 

   其它

 

lLIST CHAINED ROWS列出行迁移数据，可以使用数据库自带的脚本（ UTLCHAIN.SQL ）建立结果写入的表CHAINED_ROWS

lDELETE表示删除统计数据，如果不再需要统计数据时，可以使用该项，指定SYSTEM表示删除Oracle数据库系统的统计数据（不含用户统计的）

 

 

方法

1.写ANALYZE语句     2.使用TOAD工具

  

  

 

 

表分析举例

 

  

  

 

 

约束

常用的数据完整性约束规则包括：

1.NOT NULL

2.唯一关键字

3.主关键字

4.外键

5.检查项Check

 

由于本部分内容再前面的章节中已经穿插讲解，本处不再赘述

 

约束条件

l使用约束的目的是为了保持数据的完整性；

l约束条件是定义一个或多个条件的一种方法，用户的输入在被Oracle接收进数据表之前，必须满足这些约束条件；

l约束条件作为表的定义的一部分被存储，以备将来自动执行

l不符合约束条件的数据将被Oracle中断;

lDML中只有Insert、Update语句能触发约束条件；

l约束数据存放在USER_CONSTRAINS、DBA_CONSTRAINS、ALL_CONSTRAINS中。

 

NULL & NOT NULL

l建立数据库表时，字段的默认值是NULL，如果要约定非空需要指定；

l可以修改字段属性，语法：

  ALTER TABLE table_name MODIFY(column_name NULL|NOT NULL)

但如果数据库中已经存在数据，且该列有 null值，则修改不会成功。

唯一约束

l唯一约束禁止一个列或者联合列的数据重复，但允许某些列为null；

l数据库在创建唯一约束时，强制创建或者重用列上的索引。如果列上无索引，那么强制创建一个唯一索引，否则就重用之前的索引

l增加唯一约束语法：

  ALTER TABLE table_name ADD CONSTRAINT  constrain_name UNIQUE(column_name)

 

主键

l主键实际上是非空约束和唯一约束的组合体；

l虽然Oracle也可以通过Alter语句修改、增加主键，但我们还是建议先删除，再增加；建议使用CASE工具，以避免死记语法。（语法在表间关系部分讲）

 

外键

l外键是保证数据完整性的，表示数据间关系代数的关系，但不建议大量使用，而采用替代的方法；

l同主键，增加和修改建议使用工具。 （语法在表间关系部分讲）

CHECK

l检查约束要求取值符合特定的范围；

l一般用在取值基数少的情况下使用，如状态、性别等；

l检查约束上建立位图索引会是一个很不错的主意；

l增加检查约束的语法：

ALTER TABLE table_name ADD CONSTRAINT  constraint_name

 CHECK(column_name and conditaion);

l原则上，检查约束应该在数据库设计阶段就已经定义好，因此在创建表时就应建立：

XB    CHAR(1) default '男' 

         constraint CKC_XB_STUDENT check (XB is null or ( XB in ('男','女') ))

 

启用、禁用、删除和重命名约束

l启用语法（详细参考Oracle官方文档） ：

ALTER TABLE table_name ENABLE CONSTRAINT  constraint_name;

l禁用语法（详细参考Oracle官方文档） ：

    ALTER  TABLE table_name DISABLE CONSTRAINT  constraint_name;

l删除语法（详细参考Oracle官方文档）：

    ALTER  TABLE table_name DROP CONSTRAINT  constraint_name;

l修改语法（详细参考Oracle官方文档） ：

    ALTER  TABLE table_name MODIFY CONSTRAINT  constraint_name;

l重命名

    ALTER  TABLE table_name RENAME CONSTRAINT  old_name TO new_name;

 

重要提醒：强烈建议此类管理功能一律使用工具（如TOAD）来实现。

 

约束使用经验谈

l能定义为NOT NULL的一律定义为NOT NULL，如果确实可能取值为NULL，建议使用默认值后再定义为NOT NULL（如日期型和数字型），此举好处很多；

l网络连接速度慢，客户端要求响应时间快的，约束最好放在前台程序实现，反之最好放在后台实现；

l由于唯一约束实际上是唯一索引，扫描最快，所以能建议唯一约束的一定要使用唯一约束；

l能命名的约束要尽量命名，以便于维护和分析（索引）；

lCHCEK约束尽量使用NOT NULL，如检索较多，建立位图索引。

 

表间关系

主从表

   如果表A跟表B之间的关系是1:n，则A表和B表之间是主从关系，即A表是主表，B表是从表。主从表经常在主表上使用删除触发器来保证A表的记录被删除时，B表中对应的记录通过触发器自动被删除，从而维持数据的一致性。

  

 

 

主键

语法：

   ALTER TABLE table_name ADD CONSTRAINT constraint_name PRIMARY KEY(column1,column2,…);

   也可以创建表同步创建主键

 create table Student  (

   XH                   CHAR(4)                          not null,

   XM                   VARCHAR2(10),

   NL                   INTEGER,

   XB                   CHAR(2)                        default '男' 

         constraint CKC_XB_STUDENT check (XB is null or ( XB in ('男','女') )),

   JG                   VARCHAR2(10),

   constraint PK_STUDENT primary key (XH)

)；

外键

语法：

   ALTER TABLE table_name1 ADD CONSTRAINT constraint_name FOREIGN KEY(column) REFRENCE table_name2(column);

create table gjdm  (

   GJDM                 CHAR(3)                          not null,

   GJMC                 VARCHAR2(60)                     not null,

   GJJC                 VARCHAR2(20),

   YWMC                 VARCHAR2(32),

   DQDM                 CHAR(8),

   WHR                  VARCHAR2(20),

   WHRQ                 DATE,

   constraint PK_gjdm primary key (GJDM)

);

alter table PTJC_GJDM

   add constraint FK_gjdm_FK_dqdm foreign key (DQDM)

      references dqdm (DQDM);

 

表连接

1.相等连接

  相连接的两个表或视图中两个字段的取值完全相同的连接。

2.自连接

  一个表自己与自己进行的连接。

SELECT e1.last_name||' works for '||e2.last_name

"Employees and Their Managers"

FROM employees e1, employees e2

WHERE e1.manager_id = e2.employee_id AND e1.last_name LIKE 'R%'

ORDER BY e1.last_name;

3.内连接

  内连接又称为简单连接，是仅仅返回满足条件的行数据的一种连接， 内连接 是相较于外连接的一个概念。

 

4.外连接

  外连接不仅返回满足条件的数据，也返回在一个表中存在而在另外一个表中不存在的数据，是对内连接的一种扩展， 又分为左外连接和右外连接。 第十二 课中左外连接内容如下：

 

左外连接是使用Left join、或left outer join或者在右侧表使用“（+）”，表示以左表为驱动表，左表的记录全部显示出来，右表匹配的数据才才显示出来，即左表不加限制，右表加限制。

示例:

 (1). select  * from classes a                   --(15 lines)

 (2). select  * from classes_2 a              --(3 lines)

 －－

第十二课中右外连接内容如下：

使用right join、或right outer join或者在左侧表使用“（+）”，表示以右表为驱动表，右表的记录全部显示出来，左表匹配的数据才才显示出来，即右表不加限制，左表加限制。示例:

 (1). select *                                           --(3 lines，右表3行，左表匹配3行)

            from classes a,  classes_2 b 

             where a.bjbh(+)  = b.bjbh;

 (2). select * from classes a right  join classes_2 b               --(同(1))

            on a.bjbh  = b.bjbh

 (3). select * from classes a right outer  join classes_2 b      --(同(1))

      on a.bjbh  = b.bjbh

---

 

序列

  序列(SEQUCENCE)其实就是顺序号的记数器，按定义的取值方向不断变化，一般用来给表当主键，此时该主键往往是代理主键。

 

 

语法1:

 CREATE SEQUENCE sequence_name;

 

语法2:

 CREATE SEQUENCE sequence_name

 [INCREMENT BY increment_value]

 [START WITH start_value]

 [MAXVALUE max_value]

 [MINVALUE min_value]

 [CYCLE];

 

 

使用方法：

SELECT sequence_name.nextval

  FROM DUAL;

 

在INSERT语句的VALUE部分可以直接使用sequence_name.nextval取值

 

 

同义词

   同义词就是别名，作用是将应用的对象加以简化。访问其它用户已经授权的表，需要用格式：user_name.table_name的格式，因此可以使用同义词将简化对表的访问。

  语法：

CREATE [PUBLIC] SYNONYM synonym_name FOR object_name；

其中[PUBLIC]选项表示是公共同义词。

  修改同义词

 原则：先删除后建立

 语法：

   DROP [PUBLIC] SYNONYM synonym_name ；

 

DBLINK

   数据库链接是用来在一个数据库中访问另外一个数据库中已经授权的对象， 一般用作分布式数据库接口。

  语法：

CREATE [SHARED][PUBLIC] DATABASE LINK dblink_name

[CONNECT TO [ usrer] [ current_user] IDENTIFIED BY  password] [AUTHENTICATED BY user IDENTIFIED BY password] USINGconnect_string

 

   使用方法：

 object_name@dblink_name

 

使用

1.序列往往用作代理主键，即一个表的主键应该是联合主键时，采用序列来建立一个代理作为主键，而联合主键往往定义为唯一索引；

2.同义词是用来简化对其他数据库中对象、或者同一数据库中其它模式的对象访问的，往往与DBLink一起使用；

3.如果前端程序采用Java Hibernate框架，原则上应尽量多的使用同义词，并在其模型xml文件中指定表的代理主键id对应的序列，此举会大大简化编程；

4.基于数据库的不同物理地址的数据库接口开发，一个非常简便的方法就是使用dblink+同义词+存储过程+oracle Job，并定义和记录自己的日志。

 

习题

1.Oracle的优化器分哪两种类型？其对SQL的执行一般包括哪些方法，结合第21课的内容采用执行计划举例说明。

2.绘制Oracle优化器模型结构图，并简单加以说明。

3.为什么要对表和索引等数据库对象进行分析？如何查看自动收集统计信息的任务，并写出启用和禁用该任务的PL/SQL块。

4.如何查看表或者索引是否被分析过？

5.对表和索引进行分析后，其数据分别存放在哪些数据字典中 ？各有哪些重要信息，试举例说明之。

6.不同类型的约束分别建议在什么情况下时候？

7.如何启用和禁用约束，什么情况下对可空约束改成非空约束数据库会报错？

8.举例说明序列和同义词，并解释哪些情况下应该尽量使用序列。

 

转载请注明【http://sishuok.com/forum/blogPost/list/0/6373.html】