约束oracle数据库表间关系

数据库表间关系

目录

Oracle数据完整性和锁机制

索引及优化之表分析

表分析、约束及表间关系

Oracle体系结构1

Oracle体系结构2

海量数据库及分区1

海量数据库及分区2

海量数据库及分区3

海量数据库及分区4

高级SQL优化(一)

高级SQL优化(二)

高级SQL优化(三)常用优化工具

PPT和源码下载 http://sishuok.com/forum/posts/l ist/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的开销。 C BO的依据包括

表分析原理

C BO要基于统计数据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_SIZEfrom dba_tables awhere owner='XJGL„ order by a.LAST_ANALYZED desc

如何查看自动收集统计信息的任务lselect cl ient_name,status from dba_autotask_cl ient其中cl ient_name为auto optimizerstats col lection的即代表自动收集任务l而10g中通过查询d ba_sched uler_jobs视图来查看 11g和10g的此不同点在部分11g书籍中描述是错误的应以0racle官方文档为准。

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

B EG I N

DBMS_AUTO_TASK_ADMIN.ENABLE(cl ient_name=>'auto optimizer stats col lection',operation=> NULL,window_name=>NULL);

END;

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

B EG I N

DBMS_AUTO_TASK_ADMIN.DISABLE(cl ient_name=>'auto optimizer stats col lection',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_data base_stats 为数据库的所有对象收集统计数据gather_schema_stats 为用户(模式)的所有对象收集统计数据最常用gather_ta ble_stats 为某个表及其索引收集统计数据gather_index_stats 为某个索引收集统计数据

表分析的目的和作用l收集或删除索引或索引分区、分区表或表、索引组织表、聚簇、或标量对象属性的统计信息l验证结构索引或索引分区、表或表分区、索引组织表、聚簇、或对象的引用 REF  l识别表、聚簇中行迁移。

针对a na lyze的搜集和删除统计信息功能而言 o ra cle推荐使用DB MS_STATS包来搜集优化信息 D B MS_STATS可以并行的搜集信息可以搜集分区表的全局信息进一步来说按成本的优化器只会使用DBMS_STATS包所统计出来的信息。使用ANALYZE在两方面不依赖于CBO

(1).使用VALIDATE或LISTCHAINED ROWS子句(2)收集freel ist的块信息

表分析需要的权限l模式的表分析模式首先必须是本地的且该模式要么是自己的、要么需有ANALYZEANY系统权限l聚簇或表的行迁移列出分析列表对象是属于当前模式自己的、或在列表对象上具有I NS ERT权限、或具有I NS ERTANYTABLE系统权限。l分区表验证将你分析的rowid写入的表上具有INSERT权限或者或具有INSERT ANY TABLE系统权限。表分析语法

ANALYZE

{TABLE[schema.]ta ble

[PARTITION(partition ) |SUBPARTITION(subpartition) ]

| INDEX[sche ma. ]index

[PARTITION(partition ) |SUBPARTITION(subpartition) ]

|CLUSTER[schema. ]cluster

}

{COMPUTE[SYSTE M]STATISTICS[for_cla use]

| ESTIMATE[SYSTEM]STATISTICS[for_clause][SAMPLE integer{ROWS|PERCENT}]

|val idation_clauses

| LIST CHAINED ROWS[ into_clause]

|DE LETE[SYSTE M]STATISTICS

} ;

表l对指定的表进行分析 同步也会分析该表上基于函数的索引分析的结果会放在US ER_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对指定的聚簇进行分析分析的结果会放在US ER_CLUSTERS,ALL_C LUSTERS,DBA_CLUSTERS中l如果一定要用聚簇请参阅相关文档。

统计lCOUMPUTE全分析统计为CBO服务lESTIMATE按抽样的比例进行分析也是为CBO服务

其它lLIST CHAI NED ROWS列出行迁移数据可以使用数据库自带的脚本 UTLC HAI N.SQL 建立结果写入的表CHAINED_ROWSlDELETE表示删除统计数据如果不再需要统计数据时可以使用该项指定SYSTEM表示删除Oracle数据库系统的统计数据不含用户统计的

方法

1.写ANALYZ E语句 2.使用TOAD工具

表分析举例

约束

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

1.NOTNULL

2.唯一关键字

3.主关键字

4.外键

5.检查项C heck

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

约束条件l使用约束的目的是为了保持数据的完整性l约束条件是定义一个或多个条件的一种方法用户的输入在被Oracle接收进数据表之前必须满足这些约束条件l约束条件作为表的定义的一部分被存储 以备将来自动执行l不符合约束条件的数据将被Oracle中断;lDML中只有Insert、 Update语句能触发约束条件l约束数据存放在US ER_CO NSTRAI NS、 DBA_CONSTRAI NS、 ALL_CO NSTRAI NS中。

NULL&NOT NULLl建立数据库表时字段的默认值是NULL如果要约定非空需要指定l可以修改字段属性语法

ALTER TABLE table_name MODIFY(column_name NULL|NOT NULL)

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

唯一约束l唯一约束禁止一个列或者联合列的数据重复但允许某些列为nul ll数据库在创建唯一约束时强制创建或者重用列上的索引。如果列上无索引那么强制创建一个唯一索引否则就重用之前的索引l增加唯一约束语法

ALTERTABLE table_name ADD CONSTRAINT constrain_name UNIQUE(column_name)

主键l主键实际上是非空约束和唯一约束的组合体l虽然Oracle也可以通过Alter语句修改、增加主键但我们还是建议先删除再增加建议使用CAS E工具 以避免死记语法。 语法在表间关系部分讲

外键l外键是保证数据完整性的表示数据间关系代数的关系但不建议大量使用而采用替代的方法l同主键增加和修改建议使用工具。 语法在表间关系部分讲

C H EC Kl检查约束要求取值符合特定的范围l一般用在取值基数少的情况下使用如状态、性别等l检查约束上建立位图索引会是一个很不错的主意

l增加检查约束的语法

ALTERTABLE table_name ADD CONSTRAINT constraint_name

CHECK(column_name and conditaion);l原则上检查约束应该在数据库设计阶段就已经定义好 因此在创建表时就应建立

XB CHAR(1)default '男'constraintCKC_XB_STUDENT check(XB is nul l or (XB in ('男','女') ))

启用、禁用、删除和重命名约束l启用语法详细参考Oracle官方文档 

ALTERTABLE 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 MODIFYCONSTRAINT constraint_name;l重命名

ALTER TABLEtable_name RENAME CONSTRAINT old_nameTO new_name;

重要提醒强烈建议此类管理功能一律使用工具如TOA D来实现。

约束使用经验谈l能定义为NOT NULL的一律定义为NOT NULL如果确实可能取值为NULL建议使用默认值后再定义为NOT NULL如日期型和数字型 此举好处很多l网络连接速度慢客户端要求响应时间快的约束最好放在前台程序实现反之最好放在后台实现l由于唯一约束实际上是唯一索引扫描最快所以能建议唯一约束的一定要使用唯一约束l能命名的约束要尽量命名 以便于维护和分析索引 lCHCEK约束尽量使用NOT NULL如检索较多建立位图索引。

表间关系

主从表

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