数据库表间关系(java私塾).docVIP

文章内容来自Java私塾2013-12-27数据库表间关系目录：Oracle数据完整性和锁机制索引及优化之表分析表分析、约束及表间关系Oracle体系结构1Oracle体系结构2海量数据库及分区1海量数据库及分区2海量数据库及分区3海量数据库及分区4高级SQL优化(一)高级SQL优化(二)高级SQL优化(三) 常用优化工具表分析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系统权限。表分析语法ANALYZETABLE schema.table PARTITION ( partition ) | SUBPARTITION ( subpartition ) | INDEX schema. index PARTITION ( partition ) | SUBPARTITION ( subpartition ) |CLUSTER schema. clusterCOMPUTE SYSTEM STATISTICS for_clause|ESTIMATE SYSTEM STATISTICS for_clauseSAMPLE 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_ROWSlDELETE表示删除统计数据，如果不再需要统计数据时，可以使用该项，指定SYSTEM表示删除Oracle数据库系统的统计数据（不含用户统计的）方法1.写ANALYZE语句 2.使用TOAD工具表分析举例约束常用的数据完整性约束规则包括：1.NOT NULL2.唯一关键字3.主关键字4.外键5.检查项Check由于本部分内容再前面的章节中已经穿插讲解，本处不再赘述约束条件l使用约束的目的是为了保持数据的完整性；l约束条件是定义一个或多个条件的一种方法，用户的输入在被Oracle接收进数据表之前，必须满足这些约束条件；l约束条件作为表的定义的一部分被存储，以备将来自动执行l不符合约束条件的数据将被Oracle中断;lDML中只有Insert、Update语句能触发约束条件；l约束数据存放在USER_CONSTRAINS、DBA_CONSTRAINS、ALL_CONSTRAINS中。NULL & NOT NULLl建立数据库表时，字段的默认值是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同主键，增加和修改建议使用工具。 （语法在表间关系部分讲）CHECKl检查约束要求取值符合特定的范围；l一般用在取值基数少的情况下使用，如状态、性别等；l检查约束上建立位图索引会是一个很不错的主意；l增加检查约束的语法：ALTER TABLE table_name ADD CONSTRAINT constraint_nameCHECK(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_nameEmployees and Their ManagersFROM employees e1, employees e2WHERE 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 SEQUENCEsequence_name;语法2:CREATE SEQUENCEsequence_nameINCREMENT BYincrement_valueSTART WITHstart_valueMAXVALUEmax_valueMINVALUEmin_valueCYCLE;使用方法：SELECT sequence_name.nextval FROM DUAL;在INSERT语句的VALUE部分可以直接使用sequence_name.nextval取值同义词 同义词就是别名，作用是将应用的对象加以简化。访问其它用户已经授权的表，需要用格式：user_name.table_name的格式，因此可以使用同义词将简化对表的访问。 语法：CREATE PUBLIC SYNONYMsynonym_name FORobject_name；其中PUBLIC选项表示是公共同义词。修改同义词原则：先删除后建立语法： DROP PUBLIC SYNON