SQL 21日自学通(V3.0)(PDF格式)-第43部分

快捷操作: 按键盘上方向键 ← 或 → 可快速上下翻页 按键盘上的 Enter 键可回到本书目录页 按键盘上方向键 ↑ 可回到本页顶部! 如果本书没有阅读完，想下次继续接着阅读，可使用上方 "收藏到我的浏览器" 功能 和 "加入书签" 功能！





据库的作用更明显　　　　也就是说当你还没有将　SQL　应用本身调整好时你不要期望在对数据库　



的调整上得到太多的回报　　以调整数据库为专职的人员会专攻一种数据库产品以学习尽可　



能多的关于它的特点和特性　　尽管数据库的调整经常被认为是一项痛苦的工作　　　　　　　　　　　　　但是它可　



以让真正了解它的人对它更充分地利用　



　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　323　


…………………………………………………………Page　324……………………………………………………………

SQL　21　　日自学通（V1。0）　　　　　　　　　　　　　　　　　　　　　　　　　　　翻译人　　笨猪　



性能的障碍　



我们已经说不许多的对一个数据库性能产生影响的可能方面　　　　　　　　　　最具有代表性的瓶颈涉及到　



系统级的维护　　数据库的维护和对　SQL　语句的管理　



这里总结了常见的大多数对系统性能和数据库响应时间有影响的因素　



没有很好地利用服务器上的可用设备—　—　　电脑可能基于某种原因已经购买了多个磁盘　　如　



果你没有将一些数据库的重要组件分布在多个磁盘上的话性能就会受到影响　　　　　　　　　　　　要让系统的　



能力和数据库服务的能力尽可能地发挥　



没有经常性的执行　MIT　命令　—　—　懒于使用　MIT　命令和　ROLLBACK　命令的结果　



会导致你的服务器出现瓶颈　



允许批量载入于日常进程同时工作　　　　　　当数据库还存在其它用户时运行批量载入会对每个人　



都产生影响　　批处理进行会由于成为最终用户和系统资源之间的瓶颈　



在创建　SQL　语句时不认真　—　—　不负责任地创建复合语句会比标准语句耗用更多的系统时　



间　



技巧　　你可以用不同的来优化你的　SQL　　语句的结构　　　　具体的步骤依据　SQL　　服务器处理你　



的过程的步骤而定　



对于有索引的表运行批处理任务时　—　—　你可以会需要几天几夜才能完成一个批处理的任　



务　　而一般的批量载入任务也许只要几个小时　　索引会对批量载入任务访问表中更多的行　



造成障碍　



可分配的存储空间中有更多的并发用户　　　　　　　随着当前数据户用户和系统用户的增多　　你可以　



会因为过程的共享而需要更多的内存　



创建索引的列只存在不多的可选数值　—　—　对如性别这样的列创建索引并不会非常有效　　　　　　　　　　所　



以　　你所创建的索引返回的行数应该占表的并行数比较低　



对小表创建了索引　—　—　这时使用全表扫描可能会更有效　



没有有效地管理系统的资源—　—　对系统资源的管理不善如数据库初始化　　　　　　　　　表的创建　　　不受　



控制的碎片　　　和不正确的系统维护会浪费大量的数据空间　



表和索引的大小不合适　—　—　在大型数据库中对表和索引的大小估计错误片如果护理不当会　



带来严重的碎片问题　　　　并会由于而引发更严重的问题　



　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　324　


…………………………………………………………Page　325……………………………………………………………

SQL　21　　日自学通（V1。0）　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　翻译人　　笨猪　



内置的调整工具　



　　　在确认需要进行调整时要征徇　DBA　　和你的数据库供应商以得知哪些工具对你是可用　



的　　你可以使用调整工具来确定你的数据库在数据访问上的不足之处　　此外　　　　　　　　　　　　　　　这些工具对　



对于特定的　SQL　语句提出更改建议以提高系统的性能　



　　　ORACLE　有两个流行的工具用以管理　SQL　语句的性能　这两个工具是EXPLAIN　　　　PLAN　



和　TKPROF　EXPLAIN　　　　PLAN　可以在　SQL　语句执行时检查它的访问路径　　TKPROF　　可以　



给出每个　SQL　　语句在执行时所需的时间　　ORACLE　　公司也提供了其它的工具用以对　SQL　



语句和数据库进行分析　　但是这两种是最流行的　　　　　　　　　　如果你想测试一下一个　ORACLE　　中的单　



个的　SQL　语句的运行时间　　　　你可以使用　SQL*PLUS　　中的命令　SET　TIMING　ON　



　　　这个命令和其它更多的　SET　命令会在第　20　天的　　　　　SQL*PLUS　中提及　



　　　SYBASER　　的　SQL　　SERVER　也有用于诊断　SQL　　的工具　　你可以将　SET　　命令作为选项　



加入到你的　SQL　　　语句中　　　这些命令与　ORACLE　　　的命令类似　　　　　一些常用的命令　SET　



SHOWPLAN　ON　SET　　STATISTIC　　IO　　ON　和　SET　　STATISTICS　　TIME　　ON　这些　SET　命令　



可以在查询执行时显示每一步的输出内容　　　　　　　　　查询的读写要求和一些常用的　SQL　语句分析用　



的信息　　SQL　SERVER　的　SET　命令将会在第　19　天的　　TRANSCAT　SQL　简介　中提到　



总结　



　　　流化　　或者说是调整　　　　中两个主要的因素可以直接地影响　SQL　语句的性能　　对应用的　



调整和对数据库的调整　　　　　　它们中的每一个都有自己的规则　　但是没有另外一方它们自己是　



不能完全调整工作的　　　　　　首先你要让技术队伍与系统工程师一同为平衡资源和让数据库充分　



发挥它自己的特性而工作以有助于系统性能的提高　　　　　　　　　　　其中的许多特性已经被数据库软件的　



供应商内置于其中了　



　　　应用程序开发人员必需知道这些数据　　　　　　　　　知道应用数据是优化数据库设计的关键所在　



开发人员和编程人员必须知道什么时候就使用索引　　　　　　　　　　　什么时候应该增加索引　　　　　　　什么时候允　



许批量载入运行　　　　　一定要让批量载入与日常进行的工作分开　



　　　对数据库的调整可以提高特定的应用程序访问它的速度　　数据库管理员必须了解每天　



的操作和数据库的性能　　此外　　在后台调试时必须小心　　DBA　　通常会给出一些创造性的建　



议以使对数据的访问更加有效　　　　　　　比如使用索引或重新构造　SQL　　语句　　DBA　　也应该熟悉在　



　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　325　


…………………………………………………………Page　326……………………………………………………………

SQL　21　　日自学通（V1。0）　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　翻译人　　笨猪　



数据库软件中所给出的工具来测量性能和提出建议　



问与答　



　　　问　　如果我对我的　SQL　进行了优化　　　　　可以得到多大的性能提升　



　　　答　　性能的增加是与你的表的大小相关的　　　　　　　　　与表有没有索引还是其它的相关数据无关　



在大型的表中　　　　复杂的查询可能由几个小时减为只需几分钟　　在事务处理过程中　　　　　　　　　　　　　　对　SQL　



语句的优化可能为最终用户节省更多的时间　



　　　问　　我应该在什么时候运行我的批量载入任务　



　　　答　　听你的　DBA　　的建议　　DBA　需要知道你需要插入　　更新或删除多少数据　　在其它　



批量载入任务活动时也应该经常执行　MIT　命令　



　　　问　　我是否应该将我的表分于不同的磁盘　



　　　答　　这在你的表很大或你的表访问任务很重时有用　



校练场　



　　　1　　SQL　语句的流化是什么意思　



　　　2　　表和它的索引是否应该放在同一个磁盘上　



　　　3　　为什么说对　SQL　语句中各个元素的安排是非常重要的　



　　　4　　当全表扫描时会发生什么情况　



　　　5　　你如何才能避免全表扫描　



　　　6　　常见的对性能的障碍有哪些　



练习　



1　　让下边的　SQL　语句更易读　



　　　SELECT　　　　　　　EMPLOYEE。LAST_NAME；　　　　　EMPLOYEE。FIRST_NAME；　



EMPLOYEE。MIDDLE_NAME；EMPLOYEE。ADDRESS；　　EMPLOYEE。PHONE_NUMBER；　



PAYROLL。SALARY；　　PAYROLL。POSITION；EMPLOYEE。SSN；　　PAYROLL。START_DATE　



FROM　　EMPLOYEE；　　PAYROLL　　WHEREEMPLOYEE。SSN　　=　　PAYROLL。SSN　　AND　



EMPLOYEE。LAST_NAME　LIKE　'S％'　AND　　PAYROLL。SALARY　》　20000；　



　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　326　


…………………………………………………………Page　327……………………………………………………………

SQL　21　　日自学通（V1。0）　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　翻译人　　　　　笨猪　



2　　　　重新安排下边的查询条件以减少数据返回所需要的时间　　并使用下边的统计　　对整个表　



　　　　以决定这些条件的次序　



　　　　　593　individuals　have　the　last　name　SMITH。　



　　　　　712　individuals　live　in　INDIANAPOLIS。　



　　　　　3；492　individuals　are　MALE。　



　　　　　1；233　individuals　earn　a　salary　》=　30；000。　



　　　　　5；009　individuals　are　single。　



　　　　　Individual_id　is　the　primary　key　for　both　tables。　



　　　　　SELECT　M。INDIVIDUAL_NAME；　M。ADDRESS；　M。CITY；　M。STATE；　M。ZIP_CODE；　



　　　　　　　　　　　　　　　　　　　S。SEX；　　S。MARITAL_STATUS；　　S。SALARY　



　　　　　FROM　　MAILING_TBL　　M；　　　　INDIVIDUAL_STAT_TBL　　S　



　　　　　WHERE　M。NAME　LIKE　'SMITH％'　



　　　　　　　AND　M。CITY　=　'INDIANAPOLIS'　



　　　　　　　AND　S。SEX　=　'MALE'　



　　　　　　　AND　S。SALARY　》=　30000　



　　　　　　　AND　S。MARITAL_STATUS　=　'S'　



　　　　　　　AND　M。INDIVIDUAL_ID　=　S。INDIVIDUAL_ID；　



　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　327　


…………………………………………………………Page　328……………………………………………………………

SQL　21　　日自学通（V1。0）　　　　　　　　　　　　　　　　　　　　　　　　　翻译人　　笨猪　



第　16　天　　　　用视图从数据字典中获得信息　



目标　



　　　今天我们来讨论数据字典　　　　也就是通常所说的系统目录　　在今天中我们会对以下的内　



容有充分的了解　



　　　　l　　数据字典的定义　



　　　　l　　数据字典中都包括有哪些方面的信息　



　　　　l　　在数据字典中有几种不同类型的表　



　　　　l　　从数据字典中获得有用信息的有效方法　



数据字典简介　



　　　每一种数据库都有它自己的数据字典　　　　　或系统目录　　　在今天的介绍中这两个词我们都　



会使用　　数据字典是在数据库环境中的一个系统区域　　　　　　　它包含着关于数据库成份的信息　



数据字典中包括的信息如数据库设计　　存储的　SQL　代码　　　　用户统计　　　数据库过程　　数据库　



的增长情况和对数据库性能的统计　



　　　数据字典中包括有数据库设计信息的表　　它们是由数据库设计语言　　DDL　　　　　如　CREATE　



TABLE　所创建的　这一部分的系统目录存储着善于表列和属性的信息　　表的大小　　　　　　　表的权　



限和表的增长情况信息　　其它的对象也存储在了数据字典中如索引　　触发机制　　　　　　　　过程　　包　



和视图　



　　　使用统计表可以给出对于特定的用户的数据库库联接和权限信息　　权限主要可以分成　



两个部分　　系统级权限和对象级权限　　具有创建其它用户的权限是系统权限　　　　　　　　但是访问表　



的权限属于对象权限　　在数据库中也强制性地使用了任务来确保安全性　　　　　　　　　这些信息也都在　



数据字典中存储着　



　　　数据字典是数据库中众多有用工具之一　　　　　　它是一种保证数据库组织的方法　　　　　而且特别　



像库存零售中的明细账　　　　它是一种确保数据库完整性的机制　　　　　例如　　当你创建表的时候　



数据库是如何知道你所创建的表在数据库中是否有已经存在的相同名字的呢　　　　　　　　　　　当你在创建　



一个查询从表中选择数据的时候　　　　　数据库又是如何知道你是否有访问这个表的权限的呢　



　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　328　


…………………………………………………………Page　329……………………………………………………………

SQL　21　　日自学通（V1。0）　　　　　　　　　　　　　　　　　　　　　　　　　　翻译人　　笨猪　



数据字典　　是数据库的心脏　　所以你应该知道如何去使用它　



用户的数据字典　



　　　最终用户　　系统工程师和数据库管理员都在使用数据字典　　那么他们是否对它了解呢　



他们的访问是直接的还是间接的呢　



　　　最终用户　　通常是为了他们而创建数据库的客户　　　　　　是间接地访问系统的目录的　　　　　当一　



个用户尝试去登录数据库的时候　　　　　　数据字典将会将该用户的名字　　　　　密码　　和权限做为联接　



数据库的参考　　数据库也会通过它来确定是否用户具有访问特定数据的权限　　　　　　　　　　最终用户最　



常用的访问数据库的方法是通过前端应用程序　　现在已经开发出了许多的图形用户界面工　



具可以允许让用户非常容易地构建　SQL　语句　　　　　当登录到数据库中以后　　前端应用程序会立　



即执行对数据字典的查询以定义用户可以访问的表　　然后前端应用程序可能其于从数据字　



典中获得的数据来创建一个本地化的系统目录　　用户则可以用这个本地化的目录来从他或　



好想要查询的表中获得数据　



　　　系统工程师是有着创建和设计数据库的模块　　应用程序的开发以及应用程序的管理任　



务的用户　　　在一些公司中会使用另外一种称呼　　如程序员　　程序分析员　　　　　　　数据模块师来称　



呼他们的系统工程师　　　　　系统工程师是直接地使用数据字典来管理和开发过程的　　　　　　　　当访问一　



个已经存在的工程的时候　　访问也可以通过数据库前终应用程序　　　　　　　　　开发工具以及计算机工　



程帮助工具来获得　　　　对于这些用户来说常用的系统目录是查询处于他的计划组中的模块　



查询应用任务及权限和查询模块开发情况的统计　　在特定的计划中系统工程师也可以将数　



据字典应用于系统工程师的专用对象上　



　　　数据库管理员　　　DBAS　是数据字典中所定义的具有最大权限的用户　　　　　　　与其它的两组　



用户偶而也会使用系统目录不同　　DBAS　　　　将使用数据字典作为他们的日常工作　　　　　　访问通常　



是通过查询的　　但也可以通过数据库管理工具如　ORACLE　的　SERVER　MANGER　DBA　使　



用数据字典中的信息来管理用户和资源以达到数据库优化运行的目的　



　　　如你所见　　所有的数据库用户都需要使用数据字典　　　　　　　更为重要的是　　　关系型数据库系　



统没有数据字典就无法存在了　



数据字典中的内容　



　　　这一部分使用两种关系型数据库系统的系统目录　　Oracle　　和　Sybase　　的　　尽管这两种数　



　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　329　


…………………………………………………………Page　330……………………………………………………………

SQL　21　　日自学通（V1。0）　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　翻译人　笨猪　



据库系统都有着他自己的特点　　　　　　　但是他们提供的功能是相同的　　　　　　不要关心它们在名字上的　



不同　　你只要明白数据字典的概念和它的内容就行了　



Oracle　　的数据字典　



　　　由于每一个表都必须有一个所有者　　系统目录在数据字典中的所有者是　SYS　　　　　　　　　　　　Oracle　



的数据字典可以分为三个基本类　　用户访问视图　　DBA　　视图　　　　　　　以及动态执行表—　—　它也会　



以视图的形式出现　　视图可以查询用户在数据字典中的用户账号信息　　如权限和目录表的　



创建　　DBA　可以帮助数据库管理员完成日常的工作　　它允许　DBA　　来管理用户和数据库中　



的其它对象　　在　ORALCE　　中的动态执行表也可以为　DBA　所使用并提供了对数据库的更深　



层的监视　　　这些视图提供了储如对过程的执行　　对　ROLLBACK　　段的动态使用　　　　　　　内存的使　



用等储如此类的统计信息　　动态执行表都以　V为前缀　



Sybase　　的数据字典　



　　　与　ORACLE　　一样　Sybase　中系统表的所有者也是　SYS　该表可以被分为两个部分　



系统表和数据库表　



　　　系统表只能为数据库所有者所拥有　　这些表定义的对象　　如表和索引　　　　　　　　　　　为多个数据库　



所共有　　　另一部分表在　Sybase　　SQL　　Server　　中的称为数据库表　　这些表只与每个特定的数据　



库相关联　



ORACLE　数据字典的内部结构　



　　　这一部分的例子会告诉你如何从数据字典中获得信息以及如何将它应用于广大关系型　



数据库的用户也就是　　　　　最终用户　　　系统工程师和　DBA　　　ORACLE　数据字典中有大量的关　



于所有类型的数据库用户的表和视图　　这是为什么我们要进一步研究　ORACLE　数据字典的　



原因　



用户视图　



　　　用户视图是在数据字典中的可以为全部用户所拥有的视图　　　　　　　　　　　一个用户对其它的用户只　



有　CREATE　SESSION　的权限　　所有的用户都是这样　



　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　330　


…………………………………………………………Page　331……………………………………………………………

SQL　21　　日自学通（V1。0）　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　翻译人　　　笨猪　



你是谁　



　　　在进行对数据库的无穷尽的知识的探险之前　　你应该确切地知道你是谁　　　　　　　　　　　　　　　　　　　　　　　　　在数据库中　



的字眼　　和你能做什么　　　　　　　　下边的两个例子中给出两个表中的　SELECT　　　　　　　　　　　　语句　　　其中一个会告　



诉你是谁而另一个会告诉你可以使用哪些数据库　



INPUT　



　　　　SQL》　SELECT　*　FROM　USER_USERS　



OUTPUT　



　　　　USERNAME　　USER_ID　DEFAULT_TABLESPACE　TEMPORARY　TABLESPACE　CREATED　



　　　　JSMITH　　　　29　　　　　USERS　　　　　　　　　　　TEMP　　　　　　14…MAR…97　



分析　



　　　USER_USERS　　视图可以告诉你你在　ORACLE　中的　ID　　是如何设置的　　它也可以显示其　



它用户的详细情况　　　　　　　　以及对用户的统计　　默认的表大小和临时表的大小也可以被显示　　　　　　　　　　　　　　　　　　　　　　默　



认的　USER　　的表的大小是指由该用户所创建的表的大小　　临时表大小是对　JSMITH　所指定　



的进行排序和分组空间的大小　



INPUT/OUTPUT　



　　　　SQL》　　SELECT　　*　　　　FROM　　ALL_USERS；　



　　　　　USERNAME　　　　　USER_ID　　　　CREATED　



　　　　　SYS　　　　　　　　　　0　　　　　　　　　　01…JAN…97　



　　　　　SYSTEM　　　　　　　5　　　　　　　　　　01…JAN…97　



　　　　　SCOTT　　　　　　　　8　　　　　　　　　　01…JAN…97　



　　　　　JSMITH　　　　　　　10　　　　　　　　　14…MAR…97　



　　　　　TJONES　　　　　　　11　　　　　　　　　15…MAR…97　



　　　　　VJOHNSON　　　　　12　　　　　　　　　15…MAR…97　



　　　　正如你在上边的查询结果中所看到的那样　　你可以使用　ALL_USERS　　　　　　　　　　　　　　　　　　视图你可以看到　



所有存在于数据库中的用户　　　　　　　　　　　但是　　　它不会给出像上边的那个视图那么详细的信息　　　　　　　　　　　　　　　　　因为　



对于这一级用户来说是没有必要的　　　　　　　　　　　　　只有对于系统级用户才会需要更详细的信息　



你有哪些权限　



　　　　现在你已经知道你是谁了　　　　　　　　　　如果你可以知道你能做什么就太好了　　有好几个视图都可　



以为你提供这样的信息　　USER_SYS_PRIVS　　　　　　　　视图和　USER_ROLE_PRIVS　　　　视图可以给你最　



　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　331　


…………………………………………………………Page　332……………………………………………………………

SQL　21　　日自学通（V1。0）　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　翻译人　　笨猪　



为权威的信息　



　　　你可以使用　USER_SYS_PRIVS　　　来查看系统的权限　　　　　　　切记　　　系统权限是指你对于特定　



数据库的整体权限