




已阅读5页,还剩8页未读, 继续免费阅读
版权说明:本文档由用户提供并上传,收益归属内容提供方,若内容存在侵权,请进行举报或认领
文档简介
文章内容来自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
温馨提示
- 1. 本站所有资源如无特殊说明,都需要本地电脑安装OFFICE2007和PDF阅读器。图纸软件为CAD,CAXA,PROE,UG,SolidWorks等.压缩文件请下载最新的WinRAR软件解压。
- 2. 本站的文档不包含任何第三方提供的附件图纸等,如果需要附件,请联系上传者。文件的所有权益归上传用户所有。
- 3. 本站RAR压缩包中若带图纸,网页内容里面会有图纸预览,若没有图纸预览就没有图纸。
- 4. 未经权益所有人同意不得将文件中的内容挪作商业或盈利用途。
- 5. 人人文库网仅提供信息存储空间,仅对用户上传内容的表现方式做保护处理,对用户上传分享的文档内容本身不做任何修改或编辑,并不能对任何下载内容负责。
- 6. 下载文件中如有侵权或不适当内容,请与我们联系,我们立即纠正。
- 7. 本站不保证下载资源的准确性、安全性和完整性, 同时也不承担用户因使用这些下载资源对自己和他人造成任何形式的伤害或损失。
最新文档
- 代办公司年会活动方案
- 代理招商活动方案
- 代言英语游戏活动方案
- 代驾公司引流活动方案
- 以科技少先队活动方案
- 仪征新员工团建活动方案
- 任务宝活动方案
- 企业五一慰问活动方案
- 企业党员先锋活动方案
- 企业公益集市活动方案
- DB37-T 4825.5-2025 药品、医疗器械、化妆品企业日常监督检查管理规范 第5部分:数据管理
- 2025年江苏省常州市新北区外国语学校八下英语期末学业水平测试模拟试题含答案
- 2025-2030年中国运动轮椅行业市场现状供需分析及投资评估规划分析研究报告
- 陪跑企业协议书
- 医学研究生课题研究中期进展报告
- 景区商户安全协议书
- 2024-2030全球超高压HPP灭菌设备行业调研及趋势分析报告
- 2025年军事理论课程考试试卷及答案
- 2025广西桂盛金融信息科技服务有限公司专业技术人员常态化招聘笔试参考题库附带答案详解-1
- 《综合保税区发展战略》课件
- 种养循环计划书
评论
0/150
提交评论