还剩67页未读,继续阅读
本资源只提供10页预览,全部文档请下载后查看!喜欢就下载吧,查找使用更方便
文本内容:
经营分析系统员工培训文档--数据库分册内部资料注意保密经营分析系统员工培训文档--ORACLE数据库分册Vesion
1.
04.SQL
4.
2.PL/SQL
4.
2.
1.PL/SQL来源针对SQL92标准,各数据库厂商在标准基础上做了一定的扩展,SQLSERVER的扩展语言叫T-SQL,PL/SQL是ORACLE对标准数据库语言的扩展,ORACLE公司已经将PL/SQL整合到ORACLE服务器和其他工具中了,近几年中更多的__人员和DBA开始使用PL/SQL,本文将讲述PL/SQL基础语法,结构和组件、以及如何设计并执行一个PL/SQL程序PL/SQL从版本6开始PL/SQL就被可靠的整合到ORACLE中了PL/SQL不是一个__的产品,他是一个整合到ORACLE服务器和ORACLE工具中的技术,可以把PL/SQL看作ORACLE服务器内的一个引擎,sql语句执行者处理单个的sql语句,PL/SQL引擎处理PL/SQL程序块当PL/SQL程序块在PL/SQL引擎处理时,ORACLE服务器中的SQL语句执行器处理pl/sql程序块中的SQL语句PL/SQL的优点如下PL/SQL是一种高性能的基于事务处理的语言,能运行在任何ORACLE环境中,支持所有数据处理命令通过使用PL/SQL程序单元处理SQL的数据定义和数据控制元素PL/SQL支持所有SQL数据类型和所有SQL函数,同时支持所有ORACLE对象类型PL/SQL块可以被命名和存储在ORACLE服务器中,同时也能被其他的PL/SQL程序或SQL命令调用,任何客户/服务器工具都能访问PL/SQL程序,具有很好的可重用性可以使用ORACLE数据工具管理存储在服务器中的PL/SQL程序的安全性可以授权或撤销数据库其他用户访问PL/SQL程序的能力PL/SQL代码可以使用任何ASCII文本编辑器编写,所以对任何ORACLE能够运行的操作系统都是非常便利的对于SQL,ORACLE必须在同一时间处理每一条SQL语句,在网络环境下这就意味作每一个__的调用都必须被oracle服务器处理,这就占用大量的服务器时间,同时导致网络拥挤而PL/SQL是以整个语句块发给服务器,这就降低了网络拥挤
4.
2.
2.PL/SQL基本结构PL/SQL是Oracle对SQL规范的扩展,是一种块结构语言,即构成一个PL/SQL程序的基本单位(过程、函数和无名块)是逻辑块,可包含任何数目的嵌套了快这种程序结构支持逐步求精方法解决问题一个块(或子块)将逻辑上相关的说明和语句组合在一起,其形式为DECLARE---说明BEGIN---语句序列EX__PTION---例外处理程序END;PL/SQL机可执行过程性语句,而将SQL语句发送到ORACLE服务器上的SQL语句执行器在ORACLE预编译程序或OCI程序中可嵌入无名的PL/SQL块如果ORACLE具有PRO__DURAL选件,有名的PL/SQL块(子程序)可单独编译,永久地存储在数据库中,准备执行除非特别指明,SQL不区分大小写SQL可以在一行或者多行输入关键字不能分开子句通常放在单独的行中使用缩进是为了提高代码可读性关键字通常使用大写字母输入,其他文字用小写输入
4.
2.
3.数据类型
4.
2.
3.
1.预定义数据类型Oracle的数据类型可以分为四类,分别是标量类型,复合类型,引用类型和LOB类型标量类型没有内部组件;而复合类型包含了能够被单独操作的内部组件;引用类型类似于3G语言中的指针,能够引用一个值;LOB类型的值就是一个lob定位器,能够指示出大对象如图像的存储位置下图是在PL/SQL中可以使用的预定义类型,其中标量类型又分为四类数字、字符、布尔和日期/时间数字型数字类型可以存储整数、实数和浮点数,可以表示数值的大小,参与计算BINARY_INTEGER我们可以使用BINARY_INTEGER数据类型来存储有符号整数它的范围是-2**31至2**31跟PLS_INTEGER一样,BINARY_INTEGER所需的存储空间也要小于NUMBER但是大多数的BINARY_INTEGER操作要比PLS_INTEGER操作慢BINARY_INTEGER子类型所谓的基类型,就是有子类型继承于它子类型在基类型的基础上添加一些约束限制,也可能重新定义数值范围为了使用方便,PL/SQL预定义了下面几个BINARY_INTEGER的子类NATURALNATURALNPOSITIVEPOSITIVENSIGNTYPE子类型NATURAL和POSITIVE能让我们将一个整数变量的取值范围分别限制在非负数和正整数之内NATURALN和POSITIVEN不允许为整数类型变量赋空值SIGNTYPE把整数的取值范围限定在-1,0,1,这在编程中很适合表示三态逻辑tri-statelogicNUMBER我们可以使用NUMBER数据类型来存储定点或浮点数它的范围是1E-130至10E125如果表达式的值超过这个范围,我们就会得到数字溢出错误anumericoverfloworunderflowerror我们可以为要存储的数字指定精度,包括数字的全长和小数长度语法如下NUMBER[precisionscale]其中precision表示数字的总长度,scale代表可以有几位小数如果要使用浮点数的话,就不能指定长度和精度,像下面这样声明就可以了NUMBER声明整数直接使用下面的语法NUMBERprecision--sameasNUMBERprecision0不可以用常量或变量指定NUMBER的长度和精度NUMBER类型最大的长度是38位如果不指定NUMBER类型的最大长度,就会默认采用这个长度或是使用系统所支持的最大长度scale的范围从-84到127,能够决定舍入规则例如,一个scale值为2的数字,舍入后的小数部分将是最接原小数部分的百分位数
3.456舍入为
3.46如果scale是负数,它就会从小数点左边开始进行舍入操作如scale值为-3的数字舍入后的结果将是最接近原值的千位数3456舍入为3000scale为零的数字舍入后的结果还是本身如果我们不指定scale的值,默认就为0NUMBER子类型为了能和ANSI/ISO和IBM类型兼容或是想使用一个更加有描述性意义的名字,我们就可以使用下面的NUMBER子类型DECDECI__LDOUBLEPRECISIONFLOATINTEGERINTNUMERICREAL__ALLINT使用DEC、DECI__L和NUMBERIC可以声明最大精度为38位十进制数字的定点数字而使用DOUBLEPRECISION和FLOAT可以声明最大精度为126位二进制数字的浮点数字,大约相当于38位十进制数字或是使用REAL声明最大精度为63位二进制数字的浮点数字,大约相当于18位十进制数字INTEGER、INT和__ALLINT可以声明最大精度为38位十进制数字的整数PLS_INTEGER我们可以使用PLS_INTEGER数据类型来存储有符号整数它的取值范围在-2**31至2**31之间PLS_INTEGER所需的存储空间要比NUMBER少,运算的速度要高于NUMBER和BINARY_INTEGER虽然PLS_INTEGER和BINARY_INTEGER的取值范围一样,但它们不完全兼容PLS_INTEGER在运算时如果有溢出,则会有异常抛出,而BIANRY_INTEGER发生溢出时,如果结果是要赋给一个NUMBER类型的变量时,就不会有异常抛出为了考虑兼容性,我们仍可以在旧的应用程序中使用BINARY_INTEGER;但在新的应用程序中,PLS_INTEGER会带来更好的性能字符型字符类型可以存放字符和数字混合的数据,表现词和文章,操作字符串CHAR我们可以使用CHAR类型来存储定长的字符数据但该数据的内部表现形式是取决于数据库字符集的CHAR类型有一个用于指定最大长度的可选参数,长度范围在1到32767字节之间我们可以采用字节或字符的形式来设置该参数语法如下CHAR[__ximum_size[CHAR|BYTE]]__ximum_size不能是常量或变量,只能是范围在1到32767之间的整数文字如果我们不指定最大值,它默认是1如果我们用字节形式指定最大值,有时就会出现空间不足的问题多字节字符会占用多于一个字节的空间为了避免这样的问题发生,我们可以采用按照字符的方式指定其最大值,这样,即使是那些包含多个字节的参数可以被灵活地存储下来按照字符指定长度的方式,上限大小仍旧是32767字节,所以,对于双字节和多字节的字符集,我们可以使用字节最大长度的一半或三分之一作为最大字符个数虽然PL/SQL字符变量的长度相对来说比较长,但CHAR类型在数据库的字段中最大存储长度为2000个字节,所以,我们不能往数据库CHAR类型字段中插入超过2000个字节的字符但是,我们可以把任意CHARn插入LONG类型的字段中,因为LONG的最大长度是2**31字节或是2Ggigabyte如果我们不使用CHAR或BYTE来对字符类型长度进行限制的话,初始化参数NLS_LENGTH_SE__NTICS会决定默认长度大小的CHAR的子类型CHARACTER和CHAR有着相同的取值范围也就是说,CHARACTER只是CHAR的一个别名而已这个子类型能与ANSI/ISO和IBM类型相兼容LONG和LONGRAW我们可以使用LONG类型来存储变长的字符串除了LONG类型的最大长度是32760字节之外,LONG类型和VARCHAR2很相像我们还可以使用LONGRAW类型来存储二进制数据或二进制字符串LONGRAW和LONG类似,但是它不会被PL/SQL解析LONGRAW的最大长度也是32760字节从9i开始,LOB类型变量可以与LONG和LONGRAW类型交换使用Oracle推荐将LONG和LONGRAW都对应的转换成COLB和BLOB类型我们可以将LONG类型的值插入字段类型为LONG的数据库中,因为在数据库中LONG的长度是2**31字节;但是,不可以从LONG字段中检索超过32760字节的字符放到LONG类型变量中去同样,对于LONGRAW类型来说,这条规则同样适用,在数据库中它的最大长度也是2**31字节,而变量的长度在32760字节以内LONG类型字段可以存储文本、字符数组或短文等我们可以对LONG字段进行UPDATE、INSERT和SELECT操作,但不能在表达式、SQL函数调用、或某个SQL子句如WHERE、GROUPBY和CONNECTBY中使用它注意在SQL语句中,PL/SQL会将LONG类型的值绑定成VARCHAR2类型,而不是LONG但是,如果被绑定的VARCHAR2值超过4000个字节,Oracle会自动地将绑定类型转成LONG,但LONG并不能应用在SQL函数中,所以,这时我们就会得到一个错误消息RAW我们可以使用RAW数据类型来存储二进制数据或字节串例如,一个RAW类型的变量可以存储一个数字化图形RAW类型数据和VARCHAR2类型数据类似,只是PL/SQL不对其进行解析而已同样,在我们把RAW数据从一个系统传到另一个系统时,OracleNet也不会对它做字符集转换RAW类型包含了一个可以让我们指定最大长度的可选参数,上限为32767字节,语法如下RAW__ximum_size我们不能使用常量或变量来指定这个参数;而且参数的范围必须是在1到32767范围内在数据库中RAW类型字段的最大长度是2000个字节,所以,不可以把超过2000字节的内容放到RAW类型字段中我们可以把任何RAW类型插入到LONGRAW类型的数据库字段中,因为LONGRAW在数据库中是2**31,但是不能把超过32767字节的LONGRAW类型放入RAW变量中ROWID和UROWID在Oracle内部,每个数据表都有一个伪列ROWID,用于存放被称为ROWID的二进制值每个ROWID代表了一行数据的存储地址物理ROWID能够标识普通数据表中的一行信息,而逻辑ROWID能够标识索引__表index-organizedtable中的一行信息其中ROWID类型只能存储物理内容,而UROWIDuniversalrowid类型可以存储物理,逻辑或外来non-OracleROWID建议只有在旧的应用程序中,为了兼容性我们才使用ROWID数据类型对于新的应用程序,应该使用UROWID数据类型当我们把查询出来的ROWID放到ROWID变量时,可以使用内置函数ROWIDTOCHAR,这个函数能把二进制内容转换成18个字节的字符串;还有一个与之对应的CHARTOROWID函数,可以对该过程进行反操作,如果转换过程中发现字符串并不是一个有效的ROWID时,PL/SQL就会抛出预定义异常SYS_INVALID_ROWIDUROWID变量和字符串之间进行转换也可以直接使用赋值操作符这时,系统会隐式地实现UROWID和字符类型之间的转换物理ROWIDPhysicalRowid可以让我们快速的访问某些特定的行只要行存在,它的物理ROWID就不会改变高效稳定的物理ROWID在查询行__、操作整个__和更新子集是很有用的例如,我们可以在UPDATE或DELETE语句的WHERE子句中比较UROWID变量和ROWID伪列来找出最近一次从游标中取出的行数据物理ROWID有两种形式10字节扩展ROWID格式10-byteextendedrowidfor__t支持相对表空间块地址并能辨识分区表和非分区表中的行记录6字节限定ROWID格式支持向后兼容扩展ROWID使用检索出来的每一行记录的物理地址的base-64编码例如,在SQL*Plus隐式地将ROWID转换成字符串中的查询SQLSELECTROWIDenameSQLFROMempSQLWHEREempno=7788;ROWIDENAME----------------------------AAAAqcAABAAADFNAAHSCOTTOOOOOOFFFBBBBBBRRR这样的形式有四部分组成:000000代表数据对象号datao__ectnumber,如上例中的AAAAqc,能够辨识数据库段同一段中的模式对象,都有着相同的数据对象号FFF代表文件号filenumber,如上例中的AAB,能辨识出包含行的数据文件在数据库中,文件号是唯一的BBBBBB代表块号blocknumber,如上例中的AAADFN,能辨识出包含行的数据块块号是与它们所在的数据文件相关,而不是表空间所以,两个在同一表空间的行数据,如果它们处于不同的数据文件中,也可能有着相同的块号RRR代表了行号rownumber,如上例中的AAH,可以辨识块中的行数据逻辑ROWID为访问特定行提供了最快的访问速度Oracle在索引__表基础上使用它们构建二级索引逻辑ROWID没有持久的物理地址,当新数据__入时,它的值就会在数据块上偏移但是,如果一个行数据的物理位置发生变化,它的逻辑ROWID就无效了VARCHAR2我们可以使用VARCHAR2类型来存储变长的字符数据至于数据在数据库中的内部表现形式要取决于数据库的字符集VARCHAR2类型需要指明数据长度,这个参数的上限是32767字节语法如下VARCHAR2__ximum_size[CHAR|BYTE]我们不能使用常量或变量来指定__ximum_size值,__ximum_size值的有效范围在1到32767之间对于长度不同的VARCHAR2类型数据,PL/SQL对它们的处理方式也是不同的值小的PL/SQL会优先考虑到它的性能问题,而对于值大的PL/SQL会优先考虑到内存的使用效率问题截止点cutoffpoint为2000个字节在2000字节以下,PL/SQL会一次性分配我们声明时所指定大小的空间容纳实际的值;2000字节或2000字节以上时,PL/SQL会动态的分配VARCHAR2的存储容量比如我们声明两个VARCHAR2类型的变量,一个长度是1999字节,一个是2000字节,如果我们把长度为500字节的值分别分配给这两个变量,那么前者占用的空间就是1999字节而后者只需500字节如果我们采用字节形式而非字符形式指定最大值时,VARCHAR2n变量就有可能太小而不能容纳n个多字节字符为了避免这个问题,就要使用VARCHAR2nCHAR形式进行定义,这样,即使字符中包含多字节字符也不会出问题所以,对于双字节或多字节字符集,我们可以指定单字节字符集中字符个数的1/2或1/3虽然PL/SQL字符变量相对比较长,但VARCHAR2类型的数据库字段最大长度为4000个字节所以,不能把字节超过4000的VARCHAR2类型值插入VARCHAR2类型的数据库字段中我们可以把任何VARCHAR2n值插入一个LONG类型的数据库字段,因为LONG字段最大长度为2**31字节但是,不能把LONG字段中检索出来的长度超过32767字节的值放到VARCHAR2n变量中如果声明时不使用CHAR或BYTE限定修饰词,初始化参数NLS_LENGTH_SE__NTICS会决定默认的设置当PL/SQL过程编译时,这个参数的设置就会被记录下来,这样,当过程失效后被重新编译时就会被重新使用VARCHAR2的子类型下面VARCHAR2的子类型的范围与VARCHAR2完全相同,它们只是VARCHAR2的一个别名而已STRINGVARCHAR我们可以使用这些子类型来与ANSI/ISO和IBM类型兼容注意目前,VARCHAR和VARCHAR2有着相同意义,但是在以后的PL/SQL版本中,为了符合SQL标准,VARCHAR有可能会作为一个单独的类型出现所以最好使用VARCHAR2,而不是VARCHAR本地字符型被广泛使用的单字节ASCII和EBCDIC字符集很适合表现罗马字符,但有些亚洲语言,如汉语、日语等包含了成千上万个字符,这些语言中的一个字符就需要用两个或三个字节来表示为了处理这些语言,Oracle提供了全球化支持,允许我们处理单字节和多字节字符数据,并在字符集之间进行数据转换Oracle还能让我们的应用程序运行在不同的语言环境中有了全球化支持,数字和日期格式会根据用户会话中所指定的语言约定languageconvention而自动进行调节因此,全世界的用户可以使用他们母语来使用OracleOracle支持两种数据库字符集和一种国家特有字符集,前者用于标识符和源代码,后者用于国家特有语言数据NCHAR和NVARCHAR2类型用于存储本地字符集注意,当在数据库的不同字符集之间转换CHAR或VARCHAR2数据时,要确保数据保持良好的形式well-formed比较UTF8和AL16UTF16编码国家特有字符集使用Unicode来表现数据,采用UTF8或AL16UTF16编码每个使用AL16UTF16编码的字符都占用2个字节这将简化字符串的长度计算,避免采用混合语言编程时发生截断错误,但是这会比ASCII字符所组成的字符串需要更多空间每个使用UTF8编码的字符占用
1、2或3个字节这就能让我们把更多的字符放到变量或数据表的字段中,但这只是在大多数字符用单字节形式表现的条件下才能做到这种编码在传递数据到字节缓冲器时可能会引起截断错误Oracle公司推荐使用默认的AL16UTF16编码,这样可以获取最大的运行时可靠性如果想知道一个Unicode字符串占用多少字节,就要使用LENGTHB函数,而不是LENGTHNCHAR我们用NCHAR类型来储存定长国家特有字符数据数据的内部表现取决于数据库创建时指定的国家特有字符集,字符集可能采用变长编码UTF8或定长编码AL16UTF16因为这种类型总是与多字节字符兼容,所以我们可以使用它支持任何Unicode字符数据NCHAR数据类型可接受一个可选参数来让我们指定字符的最大长度语法如下NCHAR[__ximum_size]因为物理限制是32767个字节,所以在AL16UTF16编码格式下最大长度为32767/2,UTF8编码格式下是32767/3我们不能使用常量或变量来指定最大值,只能使用整数文字如果我们没有指定最大长度,它默认值就为1这个值总是代表字符的个数,不像CHAR类型,既可以采用字符形式又可以采用字节形式my_stringNCHAR100;--__ximumsizeis100charactersNCHAR在数据库字段中的最大宽度是2000字节所以,我们不能向NCHAR字段中插入值超过2000字节的内容如果NCHAR的值比NCHAR字段定义的宽度要小,Oracle就会自动补上空格,填满定义的宽度我们可以在语句和表达式中交互使用CHAR和NCHAR值从CHAR转到NCHAR总是安全的,但在NCHAR值转换到CHAR的过程中,如果CHAR类型不能完全表现NCHAR类型的值,就会引起数据丢失这样的数据丢失会导致字符看起来像问号NVARCHAR2我们可以使用NVARCHAR2数据类型来存储变长的Unicode字符数据数据的内部表现取决于数据库创建时所指定的国家特有字符集,它有可能采用变长编码UTF8或是定长编码AL16UTF16因为这个类型总与多字节兼容,我们可以用它来支持Unicode字符数据NVARCHAR2数据类型需要接受一个指定最大大小的参数语法如下NVARCHAR2__ximum_size因为物理限制是32767个字节,所以在AL16UTF16编码格式下最大长度为32767/2,UTF8编码格式下是32767/3我们不能使用常量或变量来指定最大值,只能使用整数文字最大值总是代表字符的个数,不像CHAR类型,既可以采用字符形式又可以采用字节形式my_stringNVARCHAR2200;--__ximumsizeis200charactersNVARCHAR2在数据库字段中的最大宽度是4000字节所以,我们不能向NVARCHAR2字段中插入长度超过4000字节的值我们可以在语句和表达式中交互使用VARCHAR2和NVARCHAR2值从VARCHAR2向NVARCHAR2转换总是安全的,但在NVARCHAR2值转换到VARCHAR2的过程中,如果VARCHAR2类型不能完全表现NVARCHAR2类型的值,就会引起数据丢失这样的数据丢失会导致字符看起来像问号LOB类型LOBlargeo__ect数据类型BFILE、BLOB、CLOB和NCLOB可以最大存储4G的无结构数据例如文本、图形、视频剪辑和音频等块并且,它们允许高效地随机地分段访问数据LOB类型和LONG、LONGRAW类型相比有几个不同的地方比如,LOB除了NCOLB可以作为对象类型的一个属性,但LONG类型不可以LOB的最大值是4G,而LONG只有2GLOB支持随机访问数据,但LONG只支持顺序访问LOB类型中可以存储了LOB定位器,它能够指向存放于外部文件中的大对象,in-lineinsidetherow或out-of-lineoutsidetherow的形式BLOB、CLOB、NCLOB或BFILE类型的数据库字段存储了定位器其中BLOB、CLOB和NCLOB的数据存在数据库中,in-lineinsidetherow或out-of-lineoutsidetherow的形式,而BFILE数据存在数据库之外的操作系统文件中PL/SQL是通过定位器来操作LOB的例如,当我们查询出一个BLOB值,只有定位器被返回如果在事务中得到定位器,LOB定位器就会包含事务的ID号,这样我们就不能在另外一个事务中更新LOB内容了同样,我们也不能在一个会话中操作另外一个会话中的定位器从9i开始,我们也可以把CLOB类型转成CHAR和VARCHAR2类型或是把BLOB转成RAW,反之亦然,这样,我们就能在大多数SQL和PL/SQL语句和函数中使用LOB类型了要读、写和分段的操作LOB,我们可以使用Oracle系统包DBMS_LOBBFILEBFILE数据类型用于存储二进制对象,它将存储的内容放到操作系统的文件中,而不是数据库内每个BFILE变量都存储一个文件定位器,它指向服务器上的一个大的二进制文件定位器包含目录别名,该别名给出了文件全路径BFILE类型是只读的,而且它的大小要依赖于系统,不能超过4G我们的DBA要确保给定的BFILE存在且Oracle有读取它的权限BFILE并不参与事务,是不可恢复,不能被__能够被打开的BFILE最大数是由Oracle初始化参数SESSION___X_OPEN_FILES决定的BLOB、CLOB和NCLOBBLOB数据类型可以在数据库中存放不超过4G的大型二进制对象;CLOB和NCLOB可以在数据库中分别存储大块CHAR类型和NCHAR类型的字符数据,都支持定宽和变宽字符集同BFILE一样,这三个类型也都储存定位器,指向各自类型的一个大数据块数据大小都不能超过4GBLOB、CLOB和NCLOB都可以在事务中使用,能够被恢复和__DBMS_LOB包可以对它们更改过的内容进行提交或回滚操作BLOB、CLOB和NCLOB的定位器都可以跨事务使用,但不能跨会话使用布尔类型布尔类型能存储逻辑值TRUE、FALSE和NULLNULL代表缺失、未知或不可用的值只有逻辑操作符才允许应用在布尔变量上数据库SQL类型并不支持布尔类型,只有PL/SQL才支持所以就不能往数据库中插入或从数据库中检索出布尔类型的值Datetime和Interval类型Datetime就是日期时间类型,而Interval指的是时间的间隔Datetime和Interval类型都由几个域组成,下表是对每个域及其它们对应的有效值描述域名称有效日期时间值有效间隔值YEAR-4712到9999不包括0任意非零整数MONTH01到120到11DAY01到31根据当地的历法规则,受MONTH和YEAR值的限制任意非零整数HOUR00到230到23MINUTE00到590到59SECOND00到
59.9n,9n是秒小数部分的精度0to
59.9n,9n间隔秒的小数部分的精度TIMEZONE_HOUR-12到14随日光节约时间的变化而变化不可用TIMEZONE_MINUTE00到59不可用TIMEZONE_REGION查看视图V$TIMEZONE_NAMES不可用TIMEZONE_ABBR查看视图V$TIMEZONE_NAMES不可用除了TIMESTAMPWITHLOCALTIMEZONE以外,剩下的都是SQL92所支持的DATEDATE数据类型能够存储定长的日期时间日期部分默认为当月的第一天;时间部分为午夜时间函数SYSDATE能够返回当前的日期和时间提示如果只进行日期的等值比较,忽略时间部分,可以使用函数TRUNCdate_variable有效的日期范围是从公元前4721年1月1日到公元9999年12月31日儒略日Juliandate是自公元前4712年1月1日起经过的天数我们可以使用日期模式J配合函数TO_DATE和TO_CHAR来把日期值转换成等值的儒略日在日期表达式中PL/SQL会自动地将格式为默认日期格式的字符值转成DATE类型值默认的日期格式由Oracle的初始化参数NLS_DATE_FOR__T决定的例如,默认格式可能是DD-MON-YY,它包含两位数字表示一个月中的第几日,月份名称的缩写和两位记年用的数字我们可以对日期进行加减运算例如,下面的语句就能返回员工自被雇用日起,至今所经过的天数SELECTSYSDATE-hiredateINTOdays_workedFROMempWHEREempno=7499;在算术表达式中,PL/SQL会将整数文字当作日来处理,如SYSDATE+1就代表明天的时间TIMESTAMPTIMESTAMP是对DATE的扩展,包含了年月日时分秒,语法如下TIMESTAMP[precision]precision是可选参数,用于指定秒的小数部分数字个数参数precision不可以是常量或变量,其有效范围是0到9,默认值是6默认的时间戳timestamp格式是由Oracle初始化参数NLS_TIMESTAMP_FOR__T决定的在下面的例子中,我们声明了一个TIMESTAMP类型的变量,然后为它赋值DECLAREcheckoutTIMESTAMP3;BEGINcheckout:=1999-06-2207:48:
53.275;...END;这个例子中,秒的小数部分是
0.275TIMESTAMPWITHTIMEZONETIMESTAMPWITHTIMEZONE扩展了TIMESTAMP,能够表现时区位移time-zonedispla__ment时区位移在本地时间和通用协调时间UTC中是不同的语法如下TIMESTAMP[precision]WITHTIMEZONEprecision的用法同TIMESTAMP语法中的precision默认格式由Oracle初始化参数NLS_TIMESTAMP_TZ_FOR__T决定下例中,我们声明一个TIMESTAMPWITHTIMEZONE类型的变量,然后为其赋值DECLARELOGOFFTIMESTAMP3WITHTIMEZONE;BEGINLOGOFF:=1999-10-3109:42:
37.___+02:00;...END;例子中时区位移是+02:00我们还可以使用符号名称symbolicname来指定时区位移,名称可以是完整形式也可以是缩写形式,如US/Pacific和PDT,或是组合的形式例如,下面的文字全都表现同一时间第三种形式最可靠,因为它指定了切换到日光节约时间时的规则TIMESTAMP1999-04-158:00:00-8:00TIMESTAMP1999-04-158:00:00US/PacificTIMESTAMP1999-10-3101:30:00US/PacificPDT我们可以在数据词典V$TIMEZONE_NAMES的TIMEZONE_REGION和TIMEZONE_ABBR字段中找到相应的时区名称和它的缩写如果两个TIMESTAMPWITHTIMEZONE值在通用协调时间中的值一样,那么系统就会认为它们的值相同而忽略它们的时区位移下例两个值被认为是相同的,因为在通用协调时间里,太平洋标准时间8:00AM和美国东部时区11:00AM是相同的1999-08-2908:00:00-8:001999-08-2911:00:00-5:00TIMESTAMPWITHLOCALTIMEZONETIMESTAMPWITHLOCALTIMEZONE是对TIMESTAMPWITHTIMEZONE的扩展,它的语法如下TIMESTAMP[precision]WITHLOCALTIMEZONETIMESTAMPWITHLOCALTIMEZONE的用途和TIMESTAMPWITHTIMEZONE相似,它们不同之处在于,当我们往数据库中插入TIMESTAMPWITHLOCALTIMEZONE类型数据的时候,数据会被转成数据库的时区,并且时区位移并不会存放在数据库中当进行数据检索时,Oracle会按我们本地会话的时区设置返回值下面就是一个使用TIMESTAMPWITHLOCALTIMEZONE的例子DECLARELOGOFFTIMESTAMP3WITHLOCALTIMEZONE;BEGINNULL;...END;我们不可以用文字值为这种类型的变量赋值INTERVALYEARTOMONTH我们可以使用INTERVALYEARTOMONTH类型用来保存年月的间隔,语法如下INTERVALYEAR[precision]TOMONTHprecision指定间隔的年数参数precision不能是常量或变量,其范围在1到4之间,默认值是2,下面的例子中声明了一个INTERVALYEARTOMONTH类型的变量,并把间隔值101年3个月赋给它DECLARElifetimeINTERVALYEAR3TOMONTH;BEGINlifetime:=INTERVAL101-3YEARTOMONTH;--intervalliterallifetime:=101-3;--implicitconversionfromcharactertypelifetime:=INTERVAL101YEAR;--Canspecifyjusttheyearslifetime:=INTERVAL3MONTH;--Canspecifyjustthemonths...END;INTERVALDAYTOSECOND我们可以使用INTERVALDAYTOSECOND数据类型存储和操作天、小时、分钟和秒,语法如下INTERVALDAY[leading_precision]TOSECOND[fractional_seconds_precision]leading_precision和fractional_seconds_precision分别指定了天数和秒数这两个值都不可以用常量或变量指定,且只能使用范围在0到9之间的整数文字为其赋值它们的默认值分别为2和6下面的例子中,我们声明了一个INTERVALDAYTOSECOND类型的变量DECLARElag_timeINTERVALDAY3TOSECOND3;BEGINIFlag_timeINTERVAL6DAYTHEN......END;
4.
2.
3.
2.用户自定义子类型暂时不考虑
4.
2.
3.基本函数
4.
2.
3.
1.字符函数ASCII返回与指定的字符对应的十进制数;SQLselectasciiAAasciiaaascii0zeroasciispa__fromdual;AAZEROSPA__------------------------------------65974832
2.CHR给出整数返回对应的字符;SQLselectchr54740zhaochr65chr65fromdual;ZHC---赵A
3.CONCAT连接两个字符串;SQLselectconcat010-88888888||转23高乾竞__fromdual;高乾竞__----------------010-88888888转23
4.INITCAP返回字符串并将字符串的第一个字母变为大写;SQLselectinitcap__ithuppfromdual;UPP-----__ith
5.INSTRC1C2IJ在一个字符串中搜索指定的字符返回发现指定的字符的位置;C1被搜索的字符串C2希望搜索的字符串I搜索的开始位置默认为1J出现的位置默认为1SQLselectinstroracletraningra12instringfromdual;INSTRING---------9
6.LENGTH返回字符串的长度;SQLselectnamelengthnameaddrlengthaddrsallengthto_charsalfrom.nchar_tst;NAMELENGTHNAMEADDRLENGTHADDRSALLENGTHTO_CHARSAL---------------------------------------------------------------------------高乾竞3北京市海锭区
69999.997
7.LOWER返回字符串并将所有的字符小写SQLselectlowerAaBbCcDdAaBbCcDdfromdual;AABBCCDD--------aabbccdd
8.UPPER返回字符串并将所有的字符大写SQLselectupperAaBbCcDdupperfromdual;UPPER--------AABBCCDD
9.RPAD和LPAD粘贴字符RPAD在列的右边粘贴字符LPAD在列的左边粘贴字符SQLselectlpadrpadgao10*17*fromdual;LPADRPADGAO1-----------------*******gao*******不够字符则用*来填满
10.LTRIM和RTRIMLTRIM删除左边出现的字符串RTRIM删除右边出现的字符串SQLselectltrimrtrimgaoqian___gfromdual;LTRIMRTRIM-------------gaoqian___g
11.SUBSTRstringstartcount取子字符串从start开始取count个SQLselectsubstr1308888888838fromdual;SUBSTR--------
0888888812.REPLA__strings1s2string希望被替换的字符或变量s1被替换的字符串s2要替换的字符串SQLselectrepla__heloveyouheifromdual;REPLA__HELOVEYOUHEI------------------------------iloveyou
13.SOUNDEX返回一个与给定的字符串读音相同的字符串SQLcreatetabletable1xmvarchar8;SQLinsertintotable1valuesweather;SQLinsertintotable1valueswether;SQLinsertintotable1valuesgao;SQLselectxmfromtable1wheresoundexxm=soundexweather;XM--------weatherwether
14.TRIMsfromstringLEADING剪掉前面的字符TRAILING剪掉后面的字符如果不指定默认为空格符
4.
2.
3.
2.日期型函数常用的时间格式掩码如下掩码元素含义YYYY四位数年份(如2005)yearYY二位数年份(如05)Q季度(1-4)MM月份(01-12)monthWW年的星期数(1-53),其中第一星期为年的第一天至第七天W月的星期数(1-5),其中第一星期为月的第一天至第七天DDD年的日(1-366)DD月的日(1-31)D周的日(1-7),其中周日为1,周六为7dayHH2424小时制(0-23)hourMI分钟(0-59)minuteSS秒(0-59)secondSSSSS自午夜之后的秒(0-86399)日期时间函数add_months日期,number指定日期推迟number个月last_day日期指定日期当月的最后一天new_time日期,时区简写调整时区next_day日期,numbernumber表示周几,星期日是1,指定number的日期(一周内或一周后)months_between日期1,日期2日期1和日期2之间有几个月sysdate系统当期那日期和时间to_char
4.
2.
3.
3.其他函数NvlDecode
4.
2.
4.过程控制
4.
2.
4.
1.分支语句IFconditionTHENSequen___of_statements;ENDIF;IFconditionTHENSequen___of_statement1;ELSESequen___of_statement2;ENDIF;IFcondition1THENSequen___of_statement1;ELSIFcondition2THENSequen___of_statement2;ELSIFcondition3THENSequen___of_statement3;ENDIF;
4.
2.
4.
2.循环语句LOOPSequen___of_statements;IFconditionTHENEXIT;ENDIF;ENDLOOP;WHILEconditionLOOPSequen___of_statements;I=i+1ENDLOOP;FORcounterINlower_bound..higher_boundLOOPSequen___of_statements;ENDLOOP;
4.
2.
5.表连接
4.
2.
5.
1.迪卡尔乘积连接条件被省略连接条件无效第一个表中的所有行与第二个表中的所有行连接
4.
2.
5.
2.等值连接
4.
2.
5.
3.非等值连接
4.
2.
5.
4.外连接
4.
2.
6.groupby什么是分组函数分组函数可以对行集进行操作,并且为每组给出一个结果分组函数基本语法SELECT[column]group_functioncolumn...FROMtable[WHEREcondition][GROUPBYcolumn][H__ING...][ORDERBYcolumn];常用分组函数__GCOUNT__XMINSTDDEVSUMVARIAN__
4.
2.
8.DDL与DML、DCL及动态SQL本节重点掌握如下内容DDLDMLDCL语言的功用以及区别什么时间使用动态SQL,动态SQL语法
4.
2.
8.
1.DDLDMLDCL关系数据库语言分为三类DDL数据描述语言,包括createdropaltertruncateTruncatetablewangjie1;Droptablewangjie1createtabletest_wangjie1vvarchar21000v1varchar21000;altertabletest_wangjie1addv3varchar21000;altertabletest_wangjie1dropcolumnv3;DML数据操作语言,包括selectinsertdeleteupdateDCL数据控制语言,包括grantrevoke
4.
8.
2.动态SQLOracle编译PL/SQL程序块分为两种其一为前期联编(earlybinding),即SQL语句在程序编译期间就已经确定,大多数的编译情况属于这种类型;另外一种是后期联编(latebinding),即SQL语句只有在运行阶段才能建立,例如当查询条件为用户输入时,那么Oracle的SQL引擎就无法在编译期对该程序语句进行确定,只能在用户输入一定的查询条件后才能提交给SQL引擎进行处理通常,静态SQL采用前一种编译方式,而动态SQL采用后一种编译方式Oracle中提供了Executeimmediate语句来执行动态SQL,语法如下Excuteimmediate动态SQL语句using绑定参数列表returninginto输出参数列表;对这一语句作如下说明动态SQL是指DDL和不确定的DML(即带参数的DML)绑定参数列表为输入参数列表,即其类型为in类型,在运行时刻与动态SQL语句中的参数(实际上占位符,可以理解为函数里面的形式参数)进行绑定输出参数列表为动态SQL语句执行后返回的参数列表由于动态SQL是在运行时刻进行确定的,所以相对于静态而言,其更多的会损失一些系统性能来换取其灵活性为了更好的说明其__的过程,下面列举一个实例设数据库的emp表,其数据为如下IDNAMESALARY100Jacky5600101Rose3000102John4500要求1.创建该表并输入相应的数据2.根据特定ID可以查询到其姓名和薪水的信息3.根据大于特定的薪水的查询相应的员工信息根据前面的要求,可以分别创建三个过程(均使用动态SQL)来实现过程一createorrepla__pro__durecreate_tableasbeginexecuteimmediatecreatetableempidnumbernamevarchar210salarynumber;;--动态SQL为DDL语句insertintoempvalues100jacky5600;insertintoempvalues101rose3000;insertintoempvalues102john4500;endcreate_table;过程二createorrepla__pro__durefind_infop_idnumberasv_namevarchar210;v_salarynumber;beginexecuteimmediateselectnamesalaryfromempwhereid=:1usingp_idreturningintov_namev_salary;--动态SQL为查询语句dbms_output.put_linev_name||的收入为||to_charv_salary;ex__ptionwhenothersthendbms_output.put_line找不到相应数据;endfind_info;过程三createorrepla__pro__durefind_empp_salarynumberasr_empemp%rowtype;typec_typeisrefcursor;c1c_type;beginopenc1forselect*fromempwheresalary:1usingp_salary;loopfetchc1intor_emp;exitwhenc1%notfound;dbms_output.put_line薪水大于‘||to_charp_salary||’的员工为‘;dbms_output.put_lineID为to_charr_emp||其姓名为||r_emp.name;endloop;closec1;endcreate_table;
4.
2.
9.索引及约束
4.
2.
9.
1.索引本节重点掌握如下内容使用索引的目的什么情况下需要创建索引索引在什么情况下会被用到,什么情况下不会被用到创建索引的一些基本原则(表空间__,对什么样的字段创建索引等)索引是建立在表的一列或多个列上的辅助对象,目的是加快访问表中的数据;Oracle存储索引的数据结构是B*树,位图索引也是如此,只不过是叶子节点不同B*数索引;索引由根节点、分支节点和叶子节点组成,上级索引块包含下级索引块的索引数据,叶节点包含索引数据和确定行实际位置的rowid使用索引的目的加快查询速度减少I/O操作消除磁盘排序何时使用索引当查询返回的记录数排序表40%非排序表7%表的碎片较多(频繁增加、删除)索引的种类非唯一索引(最常用)唯一索引位图索引局部有前缀分区索引局部无前缀分区索引全局有前缀分区索引散列分区索引基于函数的索引管理索引的准则在表中插入数据后创建索引在用SQL*Loader或import工具插入或装载数据后,建立索引比较有效;索引正确的表和列经常检索排序大表中40%或非排序表7%的行,建议建索引;为了改善多表关联,索引列用于联结;列中的值相对比较唯一;取值范围(大B*树索引,小位图索引);Date型列一般适合基于函数的索引;列中有许多空值,不适合建立索引为性能而安排索引列经常一起使用多个字段检索记录,组合索引比单索引更有效;把最常用的列放在最前面,例dx_groupid_serv_idgroupidserv_id,在where条件中使用groupid或groupidserv_id,查询将使用索引,若仅用到serv_id字段,则索引无效;合并/拆分不必要的索引限制每个表索引的数量一个表可以有几百个索引(你会这样做吗?),但是对于频繁插入和更新表,索引越多系统CPU,I/O负担就越重;建议每张表不超过5个索引删除不再需要的索引索引无效,集中表现在该使用基于函数的索引或位图索引,而使用了B*树索引;应用中的查询不使用索引;重建索引之前必须先删除索引,若用alterindex…rebuild重建索引,则不必删除索引索引数据块空间使用创建索引时指定表空间,特别是在建立主键时,应明确指定表空间;合理设定pctfress,注意不能给索引指定pctused;估计索引的大小和合理地设置存储参数,默认为表空间大小,或initial与next设置成一样大考虑并行创建索引对大表可以采用并行创建索引,在并行创建索引时,存储参数被每个查询服务器进程分别使用,例如initial为1M,并行度为8,则创建索引期间至少要消耗8M空间;考虑用nologging创建索引对大表创建索引可以使用nologging来减少重做日志;节省重做日志文件的空间;缩短创建索引的时间;改善了并行创建大索引时的性能怎样建立最佳索引明确地创建索引createindexindex_nameontable_namefield_nametablespa__tablespa___namepctfree5initrans2__xtrans255storageminextents1__xextents16382pctincrease0;创建基于函数的索引常用与UPPER、LOWER、TO_CHARdate等函数分类上,例createindexidx_funconempUPPERenametablespa__tablespa___name;创建位图索引对基数较小,且基数相对稳定的列建立索引时,首先应该考虑位图索引,例createbit__pindexidx_bitmonclassclassnotablespa__tablespa___name;明确地创建唯一索引可以用createuniqueindex语句来创建唯一索引,例createuniqueindexdept_unique_idxondeptdept_notablespa__idx_1;创建与约束相关的索引可以用usingindex字句,为与unique和pri__rykey约束相关的索引,例如altertabletable_nameaddconstraintPK_pri__ry_keynamepri__rykeyfield_nameusingindextablespa__tablespa___name;如何创建局部分区索引基础表必须是分区表;分区数量与基础表相同;每个索引分区的子分区数量与相应的基础表分区相同;基础表的子分区中的行的索引项,被存储在该索引的相应的子分区中例如:CreateIndexTG_CDR04_SERV_ID_IDXOnTG_CDR04SERV_IDPctfree5Tablespa__TBS_AK01_IDXStorage__xExtents32768PctIncrease0FreeLists1FreeListGroups1local/如何创建范围分区的全局索引基础表可以是全局表和分区表createindexidx_start_dateontg_cdr01start_dateglobalpartitionbyrangestart_datepartitionp01_idxvlaueslessthan‘0106’partitionp01_idxvlaueslessthan‘0111’…partitionp01_idxvlaueslessthan‘0401’/重建现存的索引重建现存的索引的当前时刻不会影响查询;重建索引可以删除额外的数据块;提高索引查询效率;alterindexidx_namerebuildnologging;对于分区索引alterindexidx_namerebuildpartitionpartiton_namenologging;要删除索引的原因不再需要的索引;索引没有针对其相关的表所发布的查询提供所期望的性能改善;应用没有用该索引来查询数据;该索引无效,必须在重建之前删除该索引;该索引已经变的太碎了,必须在重建之前删除该索引;语句dropindexidx_name;dropindexidx_namedroppartitionpartition_name;建立索引的代价基础表维护时,系统要同时维护索引,不合理的索引将严重影响系统资源,主要表现在CPU和I/O上;插入、更新、删除数据产生大量dbfilesequentialread锁等待;
4.
2.
9.
2.OracleIndex的三个问题索引Index是常见的数据库对象,它的设置好坏、使用是否得当,极大地影响数据库应用程序和Database的性能虽然有许多资料讲索引的用法,DBA和Developer们也经常与它打交道,但笔者发现,还是有不少的人对它存在误解,因此针对使用中的常见问题,讲三个问题此文所有示例所用的数据库是Oracle
8.
1.7OPSonHPNseries示例全部是真实数据,读者不需要注意具体的数据大小,而应注意在使用不同的方法后,数据的比较本文所讲基本都是陈词滥调,但是笔者试图通过实际的例子,来真正让您明白事情的关键第一讲、索引并非总是最佳选择如果发现Oracle在有索引的情况下,没有使用索引,这并不是Oracle的优化器出错在有些情况下,Oracle确实会选择全表扫描(FullTableScan)而非索引扫描(IndexScan)这些情况通常有表未做statistics或者statistics陈旧,导致Oracle判断失误根据该表拥有的记录数和数据块数,实际上全表扫描要比索引扫描更快对第1种情况,最常见的例子,是以下这句sql语句selectcount*frommytable;在未作statistics之前,它使用全表扫描,需要读取6000多个数据块(一个数据块是8k)做了statistics之后,使用的是INDEXFASTFULLSCAN,只需要读取450个数据块但是,statistics做得不好,也会导致Oracle不使用索引第2种情况就要复杂得多一般概念上都认为索引比表快,比较难以理解什么情况下全表扫描要比索引扫描快为了讲清楚这个问题,这里先介绍一下Oracle在评估使用索引的代价(cost)时两个重要的数据CFClusteringfactor和FFFilteringfactor.CF:所谓CF通俗地讲,就是每读入一个索引块,要对应读入多少个数据块FF:所谓FF就是该sql语句所选择的结果集,占总的数据量的百分比大约的计算公式是FF*CF+索引块个数,由此估计出,一个查询,如果使用某个索引,会需要读入的数据块块数需要读入的数据块越多,则cost越大,Oracle也就越可能不选择使用index.(全表扫描需要读入的数据块数等于该表的实际数据块数)其核心就是,CF可能会比实际的数据块数量大CF受到索引中数据的排列方式影响,通常在索引刚建立时,索引中的记录与表中的记录有良好的对应关系,CF都很小;在表经过大量的插入、修改后,这种对应关系越来越乱,CF也越来越大此时需要DBA重新建立或者__该索引如果某个sql语句以前一直使用某索引,较长时间后不再使用,一种可能就是CF已经变得太大,需要重新整理该索引了FF则是Oracle根据statistics所做的估计比如mytables表有32万行,其主键myid的最小值是1,最大值是409654,考虑以下sql语句Select*frommytableswheremyid=1;和Select*frommytableswheremyid=400000这两句看似差不多的sql语句,对Oracle而言,却有巨大的差别因为前者的FF是100%,而后者的FF可能只有1%如果它的CF大于实际的数据块数,则Oracle可能会选择完全不同的优化方式而实际上,在我们的数据库上的测试验证了我们的预测.以下是在HP上执行时它们的explainplan: 第一句SQLselect*frommytableswheremyid=1; 已选择325917行ExecutionPlan----------------------------------------------------------0SELECTSTATEMENTOptimizer=CHOOSECost=3132Card=318474Bytes=14140245610TABLEAC__SSFULLOFMYTABLESCost=3132Card=318474Bytes=141402456Statistics----------------------------------------------------------7recursivecalls__dbblockgets41473consistentgets19828physicalreads0redosize1_____563bytessentviaSQL*Nettoclient1760245bytesre__ivedviaSQL*Netfromclient21729SQL*Netroundtripsto/fromclient1sortsmemory0sortsdisk325917rowspro__ssed第二句ExecutionPlan----------------------------------------------------------0SELECTSTATEMENTOptimizer=CHOOSECost=346Card=663Bytes=29437210TABLEAC__SSBYINDEXROWIDOFMYTABLESCost=346Card=663Bytes=29437221INDEXRANGESCANOFPK_MYTABLESUNIQUECost=5Card=663Statistics----------------------------------------------------------1278recursivecalls0dbblockgets6647consistentgets292physicalreads0redosize3544__8bytessentviaSQL*Nettoclient42640bytesre__ivedviaSQL*Netfromclient524SQL*Netroundtripsto/fromclient1sortsmemory0sortsdisk7838rowspro__ssed显而易见,第1句没有使用索引,第2句使用了主键索引pk_mytables.FF的巨大影响由此可见一斑由此想到,我们在写sql语句时,如果预先估计一下FF你就几乎可以预见到Oracle会否使用索引第二讲、索引也有好坏索引有Btree索引,Bit__p索引,Reversebtree索引,等最常用的是Btree索引B的全称是Balan__d其意义是,从tree的root到任何一个leaf,要经过同样多的level.索引可以只有一个字段(Singlecolumn)也可以有多个字段(Composite)最多32个字段,8I还支持Function-basedindex.许多developer都倾向于使用单列B树索引除此之外呢?我们还是来看一个例子吧 在HP(Oracle
8.
1.7)上执行以下语句selectcount1frommytabswherecoid=130000andissuedate=to_date2001-07-20yyyy-mm-dd 一开始,我们有两个单列索引I_mytabs1coidI_mytabs2issuedate下面是执行情况COUNT1----------6427ExecutionPlan----------------------------------------------------------0SELECTSTATEMENTOptimizer=CHOOSECost=384Card=1Bytes=1110SORTAGGREGATE21TABLEAC__SSBYINDEXROWIDOFT_MYTABSCost=384Card=126Bytes=1___32INDEXRANGESCANOFI_MYTABS2NON-UNIQUECost=11Card=126Statistics----------------------------------------------------------172recursivecalls1dbblockgets5054consistentgets2206physicalreads0redosize293bytessentviaSQL*Nettoclient359bytesre__ivedviaSQL*Netfromclient2SQL*Netroundtripsto/fromclient5sortsmemory0sortsdisk1rowspro__ssed可以看到,它读取了7000个数据块来获得所查询的6000多行现在,去掉这两个单列索引,增加一个复合索引I_mytabs_testcoidissuedate重新执行,结果如下COUNT1----------6436ExecutionPlan----------------------------------------------------------0SELECTSTATEMENTOptimizer=CHOOSECost=3Card=1Bytes=1110SORTAGGREGATE21INDEXRANGESCANOFI_MYTABS_TESTNON-UNIQUECost=3Card=126Bytes=1___Statistics----------------------------------------------------------806recursivecalls5dbblockgets283consistentgets76physicalreads0redosize293bytessentviaSQL*Nettoclient359bytesre__ivedviaSQL*Netfromclient2SQL*Netroundtripsto/fromclient3sortsmemory0sortsdisk1rowspro__ssed可以看到,这次只读取了300个数据块7000块对300块,这就是在这个例子中,单列索引与复合索引的代价之比这个例子提示我们,在许多情况下,单列索引不如复合索引有效率可以说,在索引的设置问题上,其实有许多工作可以做正确地设置索引,需要对应用进行总体的分析第三讲、索引再好,不用也是白搭抛开前面所说的,假设你设置了一个非常好的索引,任何傻瓜都知道应该使用它,但是Oracle却偏偏不用,那么,需要做的第一件事情,是审视你的sql语句Oracle要使用一个索引,有一些最基本的条件1,where子句中的这个字段,必须是复合索引的第一个字段;2,where子句中的这个字段,不应该参与任何形式的计算具体来讲,假设一个索引是按f1f2f3的次序建立的,现在有一个sql语句where子句是f2=:var2则因为f2不是索引的第1个字段,无法使用该索引第2个问题,则在我们之中非常严重以下是从实际系统上面抓到的几个例子SelectjobidfrommytabswhereisReq=0andto_dateupdatedate=to_Date2001-7-18YYYY-MM-DD;………以上的例子能很容易地进行改进请注意这样的语句每天都在我们的系统中运行,消耗我们有限的cpu和内存资源除了1,2这两个我们必须牢记于心的原则外,还应尽量熟悉各种操作符对Oracle是否使用索引的影响这里我只讲哪些操作或者操作符会显式(explicitly)地阻止Oracle使用索引以下是一些基本规则1,如果f1和f2是同一个表的两个字段,则f1f2f1=f2f12,f1isnullf1isnotnullf1notinf1!=f1like‘%pattern%;3,Notexist4,某些情况下,f1in也会不用索引;对于这些操作,别无办法,只有尽量避免比如,如果发现你的sql中的in操作没有使用索引,也许可以将in操作改成比较操作+unionall笔者在实践中发现很多时候这很有效但是,Oracle是否真正使用索引,使用索引是否真正有效,还是必须进行实地的测验合理的做法是,对所写的复杂的sql在将它写入应用程序之前,先在产品数据库上做一次explain.explain会获得Oracle对该sql的解析(plan)可以明确地看到Oracle是如何优化该sql的如果经常做explain就会发现,喜爱写复杂的sql并不是个好习惯,因为过分复杂的sql其解析计划往往不尽如人意事实上,将复杂的sql拆开,有时候会极大地提高效率,因为能获得很好的优化当然这已经是题外话了使用索引调整SQLOracle___不使用索引检查被索引的列或组合索引的首列是否出现在PL/SQL语句的WHERE子句中,这是“执行计划”能用到相关索引的必要条件看采用了哪种类型的连接方式ORACLE的共有SortMergeJoin(__J)、HashJoin(HJ)和NestedLoopJoin(NL)在两张表连接,且内表的目标列上建有索引时,只有NestedLoop才能有效地利用到该索引__J即使相关列上建有索引,最多只能因索引的存在,避免数据排序过程HJ由于须做HASH运算,索引的存在对数据查询速度几乎没有影响看连接顺序是否允许使用相关索引假设表emp的deptno列上有索引,表dept的列deptno上无索引,WHERE语句有emp.deptno=dept.deptno条件在做NL连接时,emp做为外表,先被访问,由于连接机制原因,外表的数据访问方式是全表扫描,emp.deptno上的索引显然是用不上,最多在其上做索引全扫描或索引快速全扫描是否用到系统数据字典表或视图由于系统数据字典表都未被分析过,可能导致极差的“执行计划”但是不要擅自对数据字典表做分析,否则可能导致死锁,或系统性能下降索引列是否函数的参数如是,索引在查询时用不上是否存在潜在的数据类型转换如将字符型数据与数值型数据比较,ORACLE会自动将字符型用to_number函数进行转换,从而导致上一种现象的发生是否为表和相关的索引搜集足够的统计数据对数据经常有增、删、改的表最好定期对表和索引进行分析,可用SQL语句“____yzetablexxxxcomputestatisticsforallindexes;”ORACLE掌握了充分反映实际的统计数据,才有可能做出正确的选择索引列的选择性不高 我们假设典型情况,有表emp,共有一百万行数据,但其中的emp.deptno列,数据只有4种不同的值,如
10、
20、
30、40虽然emp数据行有很多,ORACLE缺省认定表中列的值是在所有数据行均匀分布的,也就是说每种deptno值各有25万数据行与之对应假设SQL搜索条件DEPTNO=10,利用deptno列上的索引进行数据搜索效率,往往不比全表扫描的高索引列值是否可为空(NULL)如果索引列值可以是空值,在SQL语句中那些要返回NULL值的操作,将不会用到索引,如COUNT(*),而是用全表扫描这是因为索引中存储值不能为全空看是否有用到并行查询(PQO)并行查询将不会用到索引如果从以上几个方面都查不出原因的话,我们只好用采用在语句中加hint的方式强制ORACLE使用最优的“执行计划” hint采用注释的方式,有行注释和段注释两种方式 如我们想要用到A表的IND_COL1索引的话,可采用以__式 “SELECT/*+INDEX(AIND_COL1)*/*FROMAWHERECOL1=XXX;如何屏蔽索引语句的执行计划中有不良索引时,可以人为地屏蔽该索引,方法数值型在索引字段上加0,例如select*fromempwhereemp_no+0=v_emp_no;字符型在索引字段上加‘’,例如select*fromtg_cdr01wheremsisdn||’’=v_msisdn;
4.
2.
9.
3.Oracle的约束如果某个约束只作用于单独的字段,即可以在字段级定义约束,也可以在表级定义约束,但如果某个约束作用于多个字段,必须在表级定义约束在定义约束时可以通过CONSTRAINT关键字为约束命名,如果没有指定,ORACLE将自动为约束建立默认的名称定义pri__rykey约束单个字段)createtableemployeesempnonumber5pri__rykey...指定约束名createtableemployeesempnonumber5constraintemp_pkpri__rykey...定义pri__rykey约束多个字段在表级定义约束)createtableemployeesempnonumber5deptnonumber3notnullconstraintemp_pkpri__rykeyempnodeptnousingindextablespa__indxstorageinitial64Knext64KORACLE自动会为具有PRI__RYKEY约束的字段主码字段建立一个唯一索引和一个NOTNULL约束定义PRI__RYKEY约束时可以为它的索引指定存储位置和存储参数altertableemployeesaddpri__rykeyempnoaltertableemployeesaddconstraintemp_pkpri__rykeyempnoaltertableemployeesaddconstraintemp_pkpri__rykeyempnodeptno1213notnull约束只能在字段级定义NOTNULL约束在同一个表中可以定义多个NOTNULL约束altertableemployeesmodifydeptnonotnull/nullunique约束createtableemployeesempnonumber5enamevarchar215phonevarchar215e__ilvarchar230uniquedeptnonumber3notnullconstraintemp_ename_phone_ukuniqueenamephonealtertableemployeesaddconstraintemp_ukuniqueenamephoneusingindextablespa__indx定义了UNIQUE约束的字段中不能包含重复值,可以为一个或多个字段定义UNIQUE约束因此,UNIQUE即可以在字段级也可以在表级定义,在UNIQUED约束的字段上可以包含空值.foreignkey约束定义为FOREIGNKEY约束的字段中只能包含相应的其它表中的引用码字段的值或者NULL值可以为一个或者多个字段的组合定义FOREIGNKEY约束定义了FOREIGNKEY约束的外部码字段和相应的引用码字段可以存在于同一个表中,这种情况称为自引用对同一个字段可以同时定义FOREIGNKEY约束和NOTNULL约束定义了FOREIGNKEY约束的字段称为外部码字段被FORGIENKEY约束引用的字段称为引用码字段引用码必须是主码或唯__包含外部码的表称为子表,包含引用码的表称为父表.A:createtableemployees.....deptnonumber3NOTNULLconstraintemp_deptno_fkforeignkeydeptnoreferen__sdeptdeptno如果子表中的外部码与主表中的引用码具有相同的名称,可以写成:B:createtableemployees.....deptnonumber3NOTNULLconstraintemp_deptno_fkreferen__sdept注意上面的例子B中notnull后面没有加逗号因为这一句的contraint是跟在那一列deptno后面的,属于列定义,所以都无需指明列而A例中的是表定义,需要指明那一列,所以要加逗号,不能在列后面定义,还可以写成createtableemployeesempnochar4deptnochar2notnullconstraintemp_deptno_fkreferen__sdeptenamevarchar210表定义contraint的只能写在最后,再看两个例子createtableemployeesempnonumber5enamevarchar210deptnochar2notnullconstraintemp_deptno_fkreferen__sdeptconstraintemp_pkpri__rykeyempnoenamecreatetableemployeesempnonumber5enamevarchar215phonevarchar215e__ilvarchar230uniquedeptnonumber3notnullconstraintemp_pkpri__rykeyempnoenameconstraintemp_phone_ukuniquephone添加foreignkey约束多字段/表级)altertableemployeesaddconstraintemp_jobs_fkforeignkeyjobdeptnoreferen__sjobsjobiddeptnoondeletecascade更改foreignkey约束定义的引用行为deletecascade/deletesetnull/deletenoaction默认是deleteonaction引用行为当主表中一条记录被删除时,确定如何处理字表中的外部码字段)deletecascade:删除子表中所有的相关记录deletesetnull: 将所有相关记录的外部码字段值设置为NULLdeletenoaction:不做任何操作先删除原来的外键约束再添加约束ALTERTABLEemployeesDROPCONSTRAINTemp_deptno_fk;ALTERTABLEemployeesADDCONSTRAINTemp_deptno_fkFOREIGNKEYdeptnoREFEREN__SdeptdeptnoONDELETECASCADE;check约束在CHECK约束的表达式中必须引用到表中的一个或多个字段,并且表达式的计算结果必须是一个布尔值可以在表级或字段级定义对同一个字段可以定义多个CHECK约束,同时也可以定义NOTNULL约束 createtableemployeessalnumber72constraintemp_sal_ck1checksal0altertableemployeesaddconstraintemp_sal_ck2checksal20000删除约束altertabledeptdropuniquednameloc--指定约束的定义内容altertabledeptdropconstraintdept_dname_loc_uk--指定约束名删除约束时,默认将同时删除约束所对应的索引,如果要保留索引,用KEEPINDEX关键字altertableemployeesdroppri__rykeykeepindex如果要删除的约束正在被其它约束引用,通过ALTERTABLE..DROP语句中指定CASCADE关键字能够同时删除引用它的约束利用下面的语句在删除DEPT表中的PRI__RYKEY约束时,同时将删除其它表中引用这个约束的FOREIGNKEY约束:altertabledeptdroppri__rykeycascade禁用/激活约束禁用/激活约束会引起删除和重建索引的操作altertableemployeesdisable/enableuniquee__ilaltertableemployeesdisable/enableconstraintemp_ename_pkaltertabelemployeesmodifyconstraintemp_pkdisable/enablealtertabelemployeesmodifyconstraintemp_ename_phone_ukdisable/enable如果有FOREIGNKEY约束正在引用UNIQUE或PRI__RYKEY约束,则无法禁用这些UNIQUE或PRI__RYKEY约束,这时可以先禁用FOREIGNKEY约束,然后再禁用UNIQUE或PRI__RYKEY约束;或者可以在ALTERTABLE...DISABLE语句中指定CASCADE关键字,这样将在禁用UNIQUE或PRI__RYKEY约束的同时禁用那些引用它们的FOREIGNKEY约束,如altertableemployeesdisablepri__rykeycascade约束数据字典all_constraints/dba_constraints/user_constraints约束的基本信息,包括约束的名称,类型,状态约束类型CCHECK约束P主码约束R外部码约束U唯一码约束all_cons_columns/dba/user约束对应的字段信息
4.
2.
10.UPDATE本节重点掌握如下内容UPDATE语法谨慎使用对全表的UPDATE,要确保部分UPDATE的时候条件的正确性为了方便起见建立了以下简单模型和构造了部分测试数据:在某个业务受理子系统BSS中,--____表createtablecustomerscustomer_idnumber8notnull--客户标示city_namevarchar210notnull--所在城市customer_typechar2notnull--客户类型...createuniqueindexPK_customersoncustomerscustomer_id由于某些原因,客户所在城市这个信息并不什么准确,但是在____部的CRM子系统中,通过主动服务获取了部分客户20%的所在城市等准确信息,于是你将该部分信息提取至一张临时表中createtabletmp_cust_citycustomer_idnumber8notnullcitye_namevarchar210notnull,customer_typechar2notnull1最简单的形式--经确认customers表中所有customer_id小于1000均为北京--1000以内的均是公司走向全国之前的本城市的老客户:updatecustomerssetcity_name=北京wherecustomer_id10002两表多表关联update--仅在where字句中的连接--这次提取的数据都是VIP,且包括新增的所以顺便更新客户类别updatecustomersa--使用别名setcustomer_type=01--01为vip,00为普通whereexistsselect1fromtmp_cust_citybwhereb.customer_id=a.customer_id3两表多表关联update--被修改值由另一个表运算而来updatecustomersa--使用别名setcity_name=selectb.city_namefromtmp_cust_citybwhereb.customer_id=a.customer_idwhereexistsselect1fromtmp_cust_citybwhereb.customer_id=a.customer_id--update超过2个值updatecustomersa--使用别名setcity_namecustomer_type=selectb.city_nameb.customer_typefromtmp_cust_citybwhereb.customer_id=a.customer_idwhereexistsselect1fromtmp_cust_citybwhereb.customer_id=a.customer_id注意在这个语句中,=selectb.city_nameb.customer_typefromtmp_cust_citybwhereb.customer_id=a.customer_id与select1fromtmp_cust_citybwhereb.customer_id=a.customer_id是两个__的子查询,查看执行计划可知,对b表/索引扫描了2篇;如果舍弃where条件,则默认对A表进行全表更新,但由于selectb.city_namefromtmp_cust_citybwherewhereb.customer_id=a.customer_id有可能不能提供足够多值,因为tmp_cust_city只是一部分客户的信息,所以报错(如果指定的列--city_name可以为NULL则另当别论)0140700000cannotupdate%stoNULL//*Cause://*Action:一个替代的方法可以采用updatecustomersa--使用别名setcity_name=nvlselectb.city_namefromtmp_cust_citybwhereb.customer_id=a.customer_ida.city_name或者setcity_name=nvlselectb.city_namefromtmp_cust_citybwhereb.customer_id=a.customer_id未知--当然这不符合业务逻辑了4上述3在一些情况下,因为B表的纪录只有A表的20-30%的纪录数,考虑A表使用INDEX的情况,使用cursor也许会比关联update带来更好的性能setserveroutputondeclarecursorcity_curisselectcustomer_idcity_namefromtmp_cust_cityorderbycustomer_id;beginformy_curincity_curloopupdatecustomerssetcity_name=my_cur.city_namewherecustomer_id=my_cur.customer_id;/**此处也可以单条/分批次提交,避免锁表情况**/--ifmodcity_cur%rowcount_____=0then--dbms_output.put_line----;--commit;--endif;endloop;end;5关联update的一个特例以及性能再探讨在oracle的update语句语法中,除了可以update表之外,也可以是视图,所以有以下1个特例updateselecta.city_nameb.city_nameasnew_namefromcustomersatmp_cust_citybwhereb.customer_id=a.customer_idsetcity_name=new_name这样能避免对B表或其索引的2次扫描,但前提是Acustomer_idbcustomer_id必需是uniqueindex或pri__rykey否则报错0177900000cannotmodifyacolumnwhich__pstoanonkey-preservedtable//*Cause:Anattemptwas__detoinsertorupdatecolumnsofajoinviewwhich//__ptoanon-key-preservedtable.//*Action:Modifytheunderlyingbasetablesdirectly.6)oracle另一个常见错误回到3情况由于某些原因,tmp_cust_citycustomer_id不是唯一index/pri__rykeyupdatecustomersa--使用别名setcity_name=selectb.city_namefromtmp_cust_citybwhereb.customer_id=a.customer_idwhereexistsselect1fromtmp_cust_citybwhereb.customer_id=a.customer_id当对于一个给定的a.customer_idselectb.city_namefromtmp_cust_citybwhereb.customer_id=a.customer_id返回多余1条的情况,则会报如下错误0142700000single-rowsubqueryreturnsmorethanonerow//*Cause://*Action:一个比较简单近似于不负责任的做法是updatecustomersa--使用别名setcity_name=selectb.city_namefromtmp_cust_citybwhereb.customer_id=a.customer_id如何理解01427错误,在一个很复杂的多表连接update的语句,经常因考虑不周,出现这个错误,仍已上述例子来描述,一个比较简便的方法就是将A表代入值表达式中使用groupby和h__ing字句查看重复的纪录selectb.customer_idb.city_namecount*fromtmp_cust_citybcustomersawhereb.customer_id=a.customer_idgroupbyb.customer_idb.city_nameh__ingcount*=
24.
2.
11.COMMIT本节重点掌握如下内容COMMIT和ROLLBACK的关系尽量养成DML语句后面commit的习惯,在确保脚本正确前提下当事务提交时,Oracle分配一个唯一的顺序号SCNSystemChangeNumber给事务数据库恢复总是基于该SCN号来进行处理SCN号是记录在控制文件、数据文件、块头及重做日志文件中1.COMMIT处理步骤Oracle在下面情况提交事务发出一个COMMIT语句执行DDL语句时离开Oracle时2.Oracle处理COMMIT的顺序服务器为每个COMMIT产生一个SCN使改变永久化LGWR进程将日志缓冲区数据并带有SCN一起写到重做日志文件服务器释放表级和行级锁用户被提示COMMIT完成服务器使事务已完成处理ROLLBACK的顺序当下面情况发生时执行回滚发出ROLLBACK命令服务器进程放弃地终止会话被DBA终止ROLLBACK是对数据库的操作进行撤消,步骤有1)服务器进程不做任何的改变2服务器释放表级和行级锁3服务器使事务已完成
4.
2.
12.锁本节重点掌握如下内容什么样的情况会产生锁什么样的情况会产生什么样的锁避免死锁设立封锁机制主要是为了对并发操作进行控制,对干扰进行封锁,保证数据的一致性和准确性Oracle数据库封锁方式有三种共享封锁,独占封锁,共享更新封锁
4.
2.
12.
1.封锁类型Oracle RDBMS的封锁类型可分为如下三类内部级封锁内部级封锁是用于保护ORACLE内部结构,由系统内部实现,用户不能访问,因此我们不必对此做过多的了解DDL级封锁(字典/语法分析封锁)DDL级封锁也是由ORACLE RDBMS来控制,它用于保护数据字典和数据定义改变时的一致性和完整性它是系统在对SQL定义语句作语法分析时自动地加锁,无需用户干予字典/语法分析封锁共分三类(1)、字典操作锁用于对字典操作时,锁住数据字典,此封锁是独占的,从而保护任何一个时刻仅能对一个字典操作(2)、字典定义锁用于防止在进行字典操作时又进行语法分析,这样可以避免在查询字典的同时改动某个表的结构(3)、表定义锁用于 一个SQL语句正当访问某个表时,防止字典中与该表有关的项目被修改DML级封锁DML级封锁用于控制并发事务中的数据操纵,保证数据的一致性和完整性,其封锁对象可以是表或行对用户的数据操纵,Oracle可以自动为操纵的数据进行封锁,但如果有操纵授权,则为满足并发操纵的需要另外实施封锁DML封锁可由一个用户进程以显式的方式加锁,也可通过某些SQL语句隐含方式实现DML锁有如下三种封锁方式
(1)、共享封锁方式(SHARE)
(2)、独占封锁方式(EXCLUSIVE)
(3)、共享更新封锁(SHAREUPDATE)其中SHARE,EXCLUSIVE用于表封锁,SHAREUPDATE用于行封锁
4.
2.
12.
2.共享方式的表封锁共享方式的表封锁是对表中的所有数据进行封锁,该锁用于保护查询数据的一致性,防止其它用户对已封锁的表进行更更新其它用户只能对该表再施加共享方式的锁,而不能再对该表施加独占方式的封锁,共享更新锁可以再施加,但不允许持有共享更新封锁的进程做更新共享该表的所有用户只能查询表中的数据,但不能更新共享方式的表封锁只能由用户用SQL语句来设置,基语句格式如下LOCKTABLE表名[表名]...INSHAREMODE[NOWAIT]执行该语句,对一个或多个表施加共享方式的表封锁当指定了选择项NOWAIT,若该封锁暂时不能施加成功,则返回并由用户决定是进行等待,还是先去执行别的语句持有共享锁的事务,在出现如下之一的条件时,便释放其共享锁A、执行COMMIT或ROLLBACK语句B、退出数据库(LOG OFF)C、程序停止运行共享方式表封锁常用于一致性查询过程,即在查询数据期间表中的数据不发生改变
4.
2.
12.
3.独占方式表封锁独占方式表封锁是用于封锁表中的所有数据,拥有该独占方式表封锁的用户,即可以查询该表,又可以更新该表,其它的用户不能再对该表施加任何封锁(包括共享、独占或共享更新封锁)其它用户虽然不能更新该表,但可以查询该表独占方式的表封锁可通过如下的SQL语句来显示地获得LOCKTABLE表名[表名]....INEXCLUSIVEMODE[NOWAIT]独占方式的表封锁也可以在用户执行DML语句INSERT、UPDATE、DELETE时隐含获得拥有独占方式表封锁的事务,在出现如下条件之一时,便释放该封锁
(1)、执行COMMIT或ROLLBACK语句
(2)、退出数据库(LOGOFF)
(3)、程序停止运行独占方式封锁通常用于更新数据,当某个更新事务涉及多个表时,可减少发生死锁DML锁有如下三种封锁方式
(1)、共享封锁方式(SHARE)
(2)、独占封锁方式(EXCLUSIVE)
(3)、共享更新封锁(SHAREUPDATE)其中SHARE,EXCLUSIVE用于表封锁,SHAREUPDATE用于行封锁
4.
2.
12.
4.共享更新封锁方式共享更新封锁是对一个表的一行或多行进行封锁,因而也称作行级封锁表级封锁虽然保证了数据的一致性,但却减弱了操作数据的并行性行级封锁确保在用户取得被更新的行到该行进行更新这段时间内不被其它用户所修改因而行级锁即可保证数据的一致性又能提高数据操作的迸发性可通过如下的两种方式来获得行级封锁
(1)、执行如下的SQL封锁语句,以显示的方式获得LOCKTABLE表名[表名]....INSHAREUPDATE MODE[NOWAIT]
(2)、用如下的SELECT...FORUPDATE语句获得SELECT列名[列名]...FROM表名WHERE条件FORUPDATEOF列名[列名].....[NOWAIT]一旦用户对某个行施加了行级封锁,则该用户可以查询也可以更新被封锁的数据行,其它用户只能查询但不能更新被封锁的数据行.如果其它用户想更新该表中的数据行,则也必须对该表施加行级锁.即使多个用户对一个表均使用了共享更新,但也不允许两个事务同时对一个表进行更新,真正对表进行更新时,是以独占方式封锁表,一直到提交或复原该事务为止行锁永远是独占方式锁当出现如下之一的条件,便释放共享更新锁(1)、执行提交(COMMIT)语句;(2)、退出数据库(LOG OFF)(3)、程序停止运行执行ROLLBACK操作不能释放行锁从上面讲述可见,ORACLE RDBMS的加锁机制,解决了并发事务的相容与互斥问题相容保证事务的并发性,互斥确保数据的一致性不同用户锁的相容与互斥关系由下图给出其中最后一行最后一列为其它用户提供在不同行上设置SHAREUPDATE锁但当用户1在某行上进行更新操作时,用户2只有等待用户1提交事务后,才能更新自己所封锁的行中最后一行最后一列为其它用户提供在不同行上设置SHAREUPDATE锁但当用户1在某行上进行更新操作时,用户2只有等待用户1提交事务后,才能更新自己所封锁的行
4.
2.
12.
5.死锁封锁虽然能够有效的解决并发操作,但是任何资源的独占都会有死锁的危险例如有两个事务T1,T2,T1对数据A施加独占封锁,T2对数据B施加了独占封锁再假设T1要对数据B加锁,由于B已被T2独占封锁,因此T1置于等待状态,等待B被释放;现在若T2也要对A进行封锁,由于A已被T1独占封锁,因此T2也被置于等待状态这样就形成了两个事务相互等待的状态,而且永远不能结束,此种情况称为死锁在Oracle系统中能自动发现死锁,并选择代价最小的,即完成工作量最少的事务予以撤消,释放该事务所拥有的全部锁,记其它的事务继续工作下去从系统性能上考虑,应该尽可能减少资源竞争,增大吞吐量,因此用户在给并发操作加锁时,应注意以下几点1、对于UPDATE和DELETE操作,应只封锁要做改动的行,在完成修改后立即提交2、当多个事务正利用共享更新的方式进行更新,则不要使用共享封锁,而应采用共享更新封锁,这样其它用户就能使用行级锁,以增加并行性3、尽可能将对一个表的操作的并发事务施加共享更新锁,从而可提高并行性4、在应用负荷较高的期间,不宜对基础数据结构(表、索引、簇和视图)进行修改
4.
2.
12.
6.查看锁SELECT/*+rule*/lpaddecodel.xidusn030||l.oracle_usernameUser_nameo.ownero.o__ect_nameo.o__ect_types.sids.serial#p.spidFROMv$locked_o__ectldba_o__ectsov$sessionsv$pro__sspWHEREl.o__ect_id=o.o__ect_idANDl.session_id=s.sidands.paddr=p.addrORDERBYo.o__ect_idxidusnDESC
4.
2.
13.视图
4.
2.
14.SQL优化器本节重点掌握如下内容如何写出更高效的SQL语句基于规则的优化器总是使用索引总是从驱动表开始(from子句最右边的表)只有在不可避免的情况下,才使用全表扫描任何索引都可以基于成本的优化器需要表、索引的统计资料____yzetablecustomercomputestatistics;____yzetablecustomeresti__testatisticssample5000rows;表中设置并行度、表分区
4.
2.
14.
1.优化器模式rule模式总忽略CBO和统计信息而基于规则choose模式Oracle根据情况选择ruleorfirst_rowsorall_rowsfirst_rows模式基于成本,以最快的速度返回记录,会造成总体查询速度的下降或消耗更多的资源,倾向索引扫描,适合OLTP系统all_rows模式基于成本,确保总体查询时间最短,倾向并行全表扫描例如Selectlast_namefromcustomerorderbylast_name;用first_rows时,迅速返回记录,但I/O量大,用all_rows时,返回记录慢,但使用资源少
4.
2.
14.
2.调整SQL表访问全表扫描返回记录未排序表40%,排序表7%,建议采用并行机制来提高访问速度,DDS;索引访问最常用的方法,包括索引唯一扫描和索引范围扫描,OLTP;快速完全索引扫描访问索引中所有数据块,结果相当于全表扫描,可以用索引扫描代替全表扫描,例如Selectserv_idcount*fromtg_cdr01groupbyserv_id;
4.
2.
14.
3.评估全表扫描的合法性如何实现并行扫描永久并行化(不推荐)altertablecustomerparalleldegree8;单个查询并行化select/*+fullempparallelemp8*/*fromemp;分区表效果明显
4.
2.
14.
4.优化SQL语句排序排序的操作orderby子句groupby子句selectdistinct子句创建索引时union或minus排序合并连接如何避免排序添加索引在索引中使用distinct子句避免排序合并连接使用提示进行调整
4.
2.
14.
5.使用提示的原则语法/*+hint*/使用表别名:select/*+indexedept_idx*/*fromempe检验提示常用的提示ruleall_rowsfirst_rowsuse_nluse_hashuse_mergeindexindex_ascno_indexindex_desc(常用于使用__x内置函数)index_combine强制使用位图索引index_ffs(索引快速完全扫描)use_concat将查询中所有or条件使用unionallparallelnoparallelfullordered(基于成本)
4.
2.
14.
6.调整表连接表连接的类型等连接where条件中用等式连接;外部连接(左、右连接)在where条件子句的等式谓词放置一个+来实现,例如selecta.enameb.commfromempabonusbwherea.ename=b.ename+;该语句返回所有emp表的记录;自连接 Selecta.valuetotalB.valuehardA.value-b.valuesoftRoundb.value/a.value*1001percFromv$sysstatav$sysstatbWherea.statistic#=179andB.statistic#=180;反连接反连接常用于notinornotexists中,是指在查询中找到的任何记录都不包含在结果集中的子查询;不建议使用notinornotexists;半连接查询中使用exists,含义即使在子查询中返回多条重复的记录,外部查询也只返回一条记录嵌套循环连接被连接表中存在索引的情况下使用;使用use_nlhash连接Hash连接将驱动表加载在内存中,并使用hash技术连接第二个表,提高等连接速度适合于大表和小表连接;使用use_hash排序合并连接排序合并连接不使用索引使用原则连接表子段中不存在可用索引;查询返回两个表中大部分的数据快;CBO认为全表扫描比索引扫描执行的更快使用use_merge
4.
2.
14.
7.使用临时/中间表多个大表关联时,可以分别把满足条件的结果集存放到中间表,然后用中间表关联;SQL子查询的调整
4.
2.
14.
8.关联与非关联子查询关联子查询的内部引用的是外部表,每行执行一次;非关联子查询只执行一次,存放在内存中
4.
2.
14.
9.调整notin和notexists语句可以使用外部连接优化notin子句,例如selectenamefromempwheredept_nonotinselectdept_nofromdeptwheredept_name=‘__th’;改为selectenamefromempdeptwhereemp.dept_no=dept.dept_noanddept.dept_nameisnull;
4.
2.
15.ORACLE表分区
4.
2.
15.
1.什么是表分区ORACLE的分区PartitioningOption是一种处理超大型表的技术分区是一种“分而治之”的技术,通过将大表和索引分成可以管理的小块,从而避免了对每个表作为一个大的、单独的对象进行管理,为大量数据提供了可伸缩的性能分区通过将操作分配给更小的存储单元,减少了需要进行管理操作的时间,并通过增强的并行处理提高了性能,通过屏蔽故障数据的分区,还增加了可用性
4.
2.
15.
2.___要使用表分区Oracle分区功能可以提高许多应用程序的可管理性、性能与可用性通过分区功能,可以将表、索引和索引__表进一步细分为段,从而能够更精确地管理和访问这些数据库对象Oracle提供了种类繁多的分区方案以满足每种业务要求而且,因为在SQL语句中分区是完全透明的,所以该功能几乎可应用于任何应用程序分区功能的优势分区功能通过改善可管理性、性能和可用性,从而为各式应用程序带来了极大的好处通常,分区可以使某些查询以及维护操作的性能大大提高此外分区还可以极大简化常见的管理任务通过分区数据库设计人员和___能够解决前沿应用程序带来的一些难题分区是构建千兆字节数据系统或超高可用性系统的关键工具分区功能的基本知识分区功能能够将表、索引或索引__表进一步细分为段这些数据库对象的段叫做分区每个分区有自己的名称,还可以选择自己的存储特性从数据库___的角度来看,一个分区后的对象具有多个段,这些段既可进行__管理,也可单独管理这就使数据库___在管理分区后的对象时有相当大的灵活性但是,从应用程序的角度来看,分区后的表与非分区表完全相同,使用SQLDML命令访问分区后的表时,无需任何修改分区对应用是透明的,可以通过标准的SQL语句对分区表进行操作Oracle的优化器在访问数据时会分析数据的分区情况,在进行查询时,那些不包含任何查询数据的分区将被忽略,从而大大提高系统的性能还可以为表分区创建单独的索引分区,从而减少了需要进行索引维护操作的时间此外,还提供了种类繁多的局部和全局的索引技术分区操作也可以被并行执行分区技术还提高了数据的可用性当部分数据由于故障或其它原因不可用时,其它分区内的数据可用不受影响继续使用分区技术能够提高数据库的可管理性使用分区技术,维护操作可集中于表的特定部分例如,数据库___可以只对表的一部分做备份,而不必对整个表做备份对整个数据库对象的维护操作,可以在每个分区的基础上进行,从而将维护工作分解成更容易管理的小块分区技术提高可管理性的一个典型用法是支持数据仓库中的‘滚动视窗’加载进程假设数据库___每周向表中加载新数据该表可以是范围分区,以便每个分区包含一周的数据加载进程只是简单地添加新的分区添加一个新分区的操作比修改整个表效率高很多,因为数据库___不需要修改任何其他分区从分区后的表中去除数据也是一样你只要用一个很简便快捷的数据字典操作删掉一个分区,而不必发出使用大量资源和调动所有要删除的数据的‘DELETE’命令分区技术能够提高数据库的性能:由于减少了所检查或操作的数据数量,同时允许并行执行,Oracle9i的分区功能提供了性能上的优势这些性能包括分区修整分区修整是用分区技术提高性能的最简单最有价值的手段分区修整常常能够将查询性能提高几个数量级例如,假定应用程序中有包含定单历史记录的定单表,该表用周进行了分区查询一周的定单只需访问该定单表的一个分区如果该定单表包含两年的历史记录,这个查询只需要访问一个而不是一百零四个分区该查询的执行速度因为分区修整而有可能快一百倍分区修整能与所有其他Oracle性能特性协作Oracle公司将把分区修整技术与索引技术、连结技术和并行访问方法一起联合使用分区智能联接分区功能可以通过称为分区智能联接的技术提高多表联接的性能当两个表要联接在一起,而且每个表都用联接关键字来分区时,就可以使用分区智能联接分区智能联接将大型联接分解成较小的发生在各个分区间的联接,从而用较少的时间完成全部联接这就给串行和并行的执行都能带来显著的性能改善更新和删除的并行执行分区功能能够无限地并行执行UPDATE、DELETE与MERGE语句当访问分区或未分区的数据库对象时Oracle将并行处理SELECT与INSERT语句当不使用位图索引时,也可以对分区或未分区的数据库对象并行处理UPDATE、DELETE和MERGE语句为了对有位图索引的对象并行处理那些操作,目标表必须先分区这些SQL语句的并行执行可以大大提高性能,特别是提高UPDATE与DELETE或MERGE操作涉及大量数据时的性能分区技术提高可用性分区的数据库对象具有分区__性该分区__性特点可能是高可用性战略的一个重要部分,例如,如果分区表的分区不能用,但该表的所有其他分区仍然保持在线并可用那么这个应用程序可以继续针对该分区表执行查询和事务处理,只要不是访问那个不可用的分区,数据库操作仍然能够成功运行数据库___可以指定各分区存放在不同的表空间里,从而让_____于其它表分区针对每个分区进行备份与恢复操作还有,分区功能可以减少计划停机时间性能由于分区功能得到了改善,使数据库___在相对较小的批处理窗口完成大型数据库对象的维护工作
4.
2.
15.
3.表分区有哪些种类ORACLE的分区表的划分方法包括按字段值进行划分的范围分区;按字段的HASH函数值进行的划分HASH分区;先按范围划分,再按HASH划分的复合分区;在ORACLE9i中又增强了按字段值列表进行划分的列表Listing分区方法___可以指定每个分区的存储属性,分区在宿主文件系统中的放置情况,这样便增加了对超大型数据库的控制粒度granularity分区可以被单独地删除、卸出或装入、备份、恢复,因此减少了需要进行管理操作的时间Oracle9i提供了如下5种分区方法范围分区Range散列分区Hash列表分区List组合范围-散列分区Range-Hash组合范围-列表分区Range-List可对索引和表分区全局索引只能按范围分区,但可以将其定义在任何类型的分区或非分区表上通常全局索引比局部索引需要更多的维护一般组建局部索引,以便反映其基础表的结构它与基础表是等同分区的,即它与基础表在同样的列上分区,创建同样数量的分区或子分区,设置与基础表相对应的同样的分区边界对局部索引而言,当维护活动影响分区时,会自动维护索引分区这保证了索引与基础表之间的等同分区关于范围分区Range要想将行映射到基于列值范围的分区,就使用范围分区方法当数据可以被划分成逻辑范围时如年度中的月份,这种类型的分区就有用了当数据在整个范围中能被均等地划分时性能最好如果靠范围的分区会由于不均等的划分而导致分区在大小上明显不同时,就需要考虑其他的分区方法每个分区都由一个分区键值范围指定(对于一个以日期列作为分区键的表,“2005年1月”分区包含分区键值为从“2005年1月1日”到“2005年1月31日”的行)关于散列分区Hash如果数据不那么容易进行范围分区,但为了性能和管理的原因又想分区时,就使用散列分区方法散列分区提供了一种在指定数量的分区中均等地划分数据的方法基于分区键的散列值将行映射到分区中创建和使用散列分区会给你提供了一种很灵活的放置数据的方法,因为你可以通过在I/O驱动器之间播撒摘掉这些均等定量的分区,来影响可用性和性能关于列表分区List当你需要明确地控制如何将行映射到分区时,就使用列表分区方法可以在每个分区的描述中为该分区列指定一列离散值,这不同于范围分区,在那里一个范围与一个分区相关,这也不同于散列分区,在那里用户不能控制如何将行映射到分区列表分区方法是特意为遵从离散值的模块化数据划分而设计的范围分区或散列分区不那么容易做到这一点进一步说列表分区可以非常自然地将无序的和不相关的数据集进行分组和__到一起与范围分区和散列分区所不同,列表分区不支持多列分区如果要将表按列分区,那么分区键就只能由表的一个单独的列组成,然而可以用范围分区或散列分区方法进行分区的所有的列都可以用列表分区方法进行分区关于组合范围-散列分区范围和散列技术的组合,首先对表进行范围分区,然后用散列技术对每个范围分区再次分区给定的范围分区的所有子分区加在一起表示数据的逻辑子集关于组合范围-列表分区范围和列表技术的组合,首先对表进行范围分区,然后用列表技术对每个范围分区再次分区与组合范围-散列分区不同的是,每个子分区的所有内容表示数据的逻辑子集,由适当的范围和列表分区设置来描述创建或更改分区表时可以指定行__子句,即ENABLEROWMOVEMENT或者DISABLEROWMOVEMENT,当其键被更改时,该子句启用或停用将行迁移到一个新的分区默认值为DISABLEROWMOVEMENT本产品项目使用ENABLEROWMOVEMENT子句
4.
2.
15.
4.分区索引oracle数据库提供了三种类型的分区索引本地索引本地索引是其分区方式与其所在基础表的分区方式一模一样的索引本地索引的每个分区仅对应于其所在基础表的一个分区全局分区索引全局分区索引是使用不同于其所在表的分区键进行分区的索引,其所在表可以是分区表或非分区表全局分区的索引可以使用范围或散列分区进行分区例如,某个表可以按月份进行范围分区,因此具有十二个分区,而该表上的索引则可以使用不同的分区键进行范围分区,从而具有不同的分区数量全局非分区索引全局非分区索引基本上和非分区表的索引一样索引结构是不分区的
4.
2.
15.
5.表分区原则分区原则表分区的指南表的大小对于大表进行分区,将有益于大表操作的性能和大表的数据维护通常当表的大小超过
1.5GB-2GB,或对于OLTP系统,表的记录超过1000万,都应考虑对表进行分区数据访问特性基于表的大部分查询应用,只访问表中少量的数据对于这样表进行分区,可充分利用分区排除无关数据查询的特性数据维护某些表的数据维护,经常按时间段删除成批的数据,例如按月删除历史数据对于这样的表需要考虑进行分区,以满足维护的需要因为删除(Delete)大量的数据,对系统开销很大,有时甚至是不可接受的只读数据如果一个表中大部分数据都是只读数据,通过对表进行分区,可将只读数据存储在只读表空间中,对于数据库的备份是非常有益的并行数据操作(ParallelDML)对于经常执行并行操作(如ParallelInsertParallelUpdate等)的表应考虑进行分区表的可用性当对表的部分数据可用性要求很高时,应考虑进行表分区选择分区字段(PartitionKey)当确定分区字段时,有两个主要因素特别需要考虑增强表的管理和维护性通过PartitionKey,可以使数据维护基于某个分区进行,如Drop或Truncate一个或多个分区通过ParatitionKey可控制只读的数据存储在相应的分区中,且这些分区存储在只读的表空间里,这将提高数据备份的性能这类PartitionKey通常与时间相关提高访问表的性能通过PartitionKey,可使查询的数据定位在一个或少量的分区中;这需要考虑最常用的查询条件注意在考虑提高查询效率这个因素的同时,还应兼顾数据维护管理的因素,尽可能地避免相互间地冲突
4.
2.
15.
6.表分区维护
1.
1.1分区表的建立某公司的每年产生巨大的销售记录,DBA向公司建议每季度的数据放在一个分区内,以下示范的是该公司1999年的数据假设每月产生30M的数据,操作如下范围分区表CREATETABLEsalesinvoi___noNUMBER...sale_dateDATENOTNULLPARTITIONBYRANGEsale_datePARTITIONsales1999_q1VALUESLESSTHANTO_DATE‘1999-04-01’’YYYY-MM-DD’TABLESPA__ts_sale1999q1PARTITIONsales1999_q2VALUESLESSTHANTO_DATE‘1999-07-01’’YYYY-MM-DD’TABLESPA__ts_sale1999q2PARTITIONsales1999_q3VALUESLESSTHANTO_DATE‘1999-10-01’’YYYY-MM-DD’TABLESPA__ts_sale1999q3PARTITIONsales1999_q4VALUESLESSTHANTO_DATE‘2000-01-01’’YYYY-MM-DD’TABLESPA__ts_sale1999q4;--valueslessthan__xvalue列表分区表createtableempempnonumber4enamevarchar230locationvarchar230partitionbylistlocationpartitionp1values北京partitionp2values__天津重庆partitionp3values广东福建partitionp0valuesdefault;哈希分区createtableempempnonumber4enamevarchar230salnumberpartitionbyhashempnopartitions8storeinemp1emp2emp3emp4emp5emp6emp7emp8;组合分区范围哈希组合分区createtableempempnonumber4enamevarchar230hiredatedatepartitionbyrangehiredatesubpartitionbyhashempnosubpartitions2partitione1valueslessthanto_date20020501YYYYMMDDpartitione2valueslessthanto_date20021001YYYYMMDDpartitione3valueslessthan__xvalue;范围列表组合分区CREATETABLEcustomers_partcustomer_idNUMBER6cust_first_nameVARCHAR220cust_last_nameVARCHAR220nls_territoryVARCHAR230credit_limitNUMBER92PARTITIONBYRANGEcredit_limitSUBPARTITIONBYLISTnls_territorySUBPARTITIONTEMPLATESUBPARTITIONeastVALUESCHINAJAPANINDIATHAILANDSUBPARTITIONwestVALUESAMERICAGERMANYITALYSWITZERLANDSUBPARTITIONotherVALUESDEFAULTPARTITIONp1VALUESLESSTHAN1000PARTITIONp2VALUESLESSTHAN2500PARTITIONp3VALUESLESSTHAN__XVALUE;createtablet1id1numberid2numberpartitionbyrangeid1subpartitionbylistid2partitionp11valueslessthan11subpartitionsubp1values1;索引分区CREATEINDEXmonth_ixONsalessales_monthGLOBALPARTITIONBYRANGEsales_monthPARTITIONpm1_ixVALUESLESSTHAN2PARTITIONpm12_ixVALUESLESSTHAN__XVALUE;
1.
1.2分区表的维护增加分区ALTERTABLEsalesADDPARTITIONsales2000_q1VALUESLESSTHANTO_DATE‘2000-04-01’’YYYY-MM-DD’TABLESPA__ts_sale2000q1;如果已有__xvalue分区,不能增加分区,可以采取__分区的办法增加分区!删除分区ALTERTABLEsalesDROPPARTIONsales1999_q1;截断分区:altertablesalestruncatepartitonsales1999_q2;合并分区altertablesalesmergepartitonssales1999_q2sales1999_q3intosales1999_q23;alterindexind_t2rebuildpartitionp123parallel2;__分区ALTERTABLEsalesSPLITPARTITONsales1999_q4ATTO_DATE‘1999-11-01’’YYYY-MM-DD’INTOpartitionsales1999_q4_p1partitionsales1999_q4_p2;altertablet2splitpartitionp123values12intopartitionp12partitionp3;交换分区:altertablexexchangepartitionp0withtablebsvcbusrundatald;访问指定分区select*fromsalespartitionsales1999_q2select*fromsalessubpartitionsales1999_q2select*fromsaleswherepartition_key=‘xxx’EXPORT指定分区expsales/sales_passwordtables=sales:sales1999_q1file=sales1999_q
1.dmpIMPORT指定分区impsales/sales_passwordFILE=sales1999_q
1.dmpTABLES=sales:sales1999_q1IGNORE=y查看分区信息user_tab_partitionsuser_segmentsselect*fromall_tab_partitionswheretable_namelikeCUSTOMERS_PART%select*fromall_tab_subpartitionswheretable_namelikeCUSTOMERS_PART%select*fromall_part_key_columnswherenamelikeCUSTOMERS_PART%select*fromall_subpart_key_columnswherenamelikeCUSTOMERS_PART%注若分区表跨不同表空间,做导出、导入时目标数据库必须预建这些表空间分表区各区所在表空间在做导入时目标数据库一定要预建这些表空间!这些表空间不一定是用户的默认表空间,只要存在即可如果有一个不存在,就会报错!默认时,对分区表的许多表维护操作会使全局索引不可用,标记成UNUSABLE那么就必须重建整个全局索引或其全部分区如果已被分区,Oracle允许在用于维护操作的ALTERTABLE语句中指定UPDATEGLOBALINDEXES来重载这个默认特性,指定这个子句也就告诉Oracle当它执行维护操作的DDL语句时更新全局索引,这提供了如下好处在操作基础表的同时更新全局索引这就不需要后来单独地重建全局索引;因为没有被标记成UNUSABLE,所以全局索引的可用性更高了,甚至正在执行分区的DDL语句时仍然可用索引来访问表中的其他分区避免了查询所有失效的全局索引的名字以便重建它们;另外在指定UPDATEGLOBALINDEXES之前还要考虑如下性能因素:因为要更新事先被标记成UNUSABLE的索引,所以分区的DDL语句要执行更长时间,当然这要与先不更新索引而执行DDL然后再重建索引所花的时间做个比较,一个适用的规则是如果分区的大小小于表的大小的5%,则更新索引更快一点;DROPTRUNCATE和EXCHANGE操作也不那么快了,同样这必须与先执行DDL然后再重建所有全局索引所花的时间做个比较;要登记对索引的更新并产生重做记录和撤消记录,重建整个索引时可选择NOLOGGING;重建整个索引产生一个更有效的索引,因为这更利于使用空间,再者重建索引时允许修改存储选项注意分区索引结构表不支持UPDATEGLOBALINDEXES子句
1.
1.3普通表变为分区表将已存在数据的普通表转变为分区表,没有办法通过修改属性的方式直接转化为分区表,必须通过重建的方式进行转变,一般可以有三种方法,视不同场景使用用例方法一利用原表重建分区表CREATETABLETIDNUMBERPRI__RYKEYTIMEDATE;INSERTINTOTSELECTROWNUMSYSDATE-ROWNUMFROMDBA_O__ECTSWHEREROWNUM=5000;COMMIT;CREATETABLET_NEWIDTIMEPARTITIONBYRANGETIMEPARTITIONP1VALUESLESSTHANTO_DATE2000-1-1YYYY-MM-DDPARTITIONP2VALUESLESSTHANTO_DATE2002-1-1YYYY-MM-DDPARTITIONP3VALUESLESSTHANTO_DATE2005-1-1YYYY-MM-DDPARTITIONP4VALUESLESSTHAN__XVALUEASSELECTIDTIMEFROMT;RENAMETTOT_OLD;RENAMET_NEWTOT;SELECTCOUNT*FROMT;COUNT*----------5000SELECTCOUNT*FROMTPARTITIONP1;COUNT*----------2946SELECTCOUNT*FROMTPARTITIONP2;COUNT*----------731SELECTCOUNT*FROMTPARTITIONP3;COUNT*----------1096优点方法简单易用,由于采用DDL语句,不会产生UNDO,且只产生少量REDO,效率相对较高,而且建表完成后数据已经在分布到各个分区中了不足对于数据的一致性方面还需要额外的考虑由于几乎没有办法通过手工锁定T表的方式保证一致性,在执行CREATETABLE语句和RENAMET_NEWTOT语句直接的修改可能会丢失,如果要保证一致性,需要在执行完语句后对数据进行检查,而这个代价是比较大的另外在执行两个RENAME语句之间执行的对T的访问会失败适用于修改不频繁的表,在闲时进行操作,表的数据量不宜太大方法二使用交换分区的方法Droptablet;CREATETABLETIDNUMBERPRI__RYKEYTIMEDATE;INSERTINTOTSELECTROWNUMSYSDATE-ROWNUMFROMDBA_O__ECTSWHEREROWNUM=5000;COMMIT;CREATETABLET_NEWIDNUMBERPRI__RYKEYTIMEDATEPARTITIONBYRANGETIMEPARTITIONP1VALUESLESSTHANTO_DATE2005-9-1YYYY-MM-DDPARTITIONP2VALUESLESSTHAN__XVALUE;ALTERTABLET_NEWEXCHANGEPARTITIONP1WITHTABLET;RENAMETTOT_OLD;RENAMET_NEWTOT;优点只是对数据字典中分区和表的定义进行了修改,没有数据的修改或__,效率最高如果对数据在分区中的分布没有进一步要求的话,实现比较简单在执行完RENAME操作后,可以检查T_OLD中是否存在数据,如果存在的话,直接将这些数据插入到T中,可以保证对T插入的操作不会丢失不足仍然存在一致性问题,交换分区之后RENAMET_NEWTOT之前,查询、更新和删除会出现错误或访问不到数据如果要求数据分布到多个分区中,则需要进行分区的SPLIT操作,会增加操作的复杂度,效率也会降低适用于包含大数据量的表转到分区表中的一个分区的操作应尽量在闲时进行操作方法三Oracle9i以上版本,利用在线重定义功能Droptablet;CREATETABLETIDNUMBERPRI__RYKEYTIMEDATE;INSERTINTOTSELECTROWNUMSYSDATE-ROWNUMFROMDBA_O__ECTSWHEREROWNUM=5000;COMMIT;EXECDBMS_REDEFINITION.CAN_REDEF_TABLEUSERT;PL/SQL过程已成功完成CREATETABLET_NEWIDNUMBERPRI__RYKEYTIMEDATEPARTITIONBYRANGETIMEPARTITIONP1VALUESLESSTHANTO_DATE2004-7-1YYYY-MM-DDPARTITIONP2VALUESLESSTHANTO_DATE2005-1-1YYYY-MM-DDPARTITIONP3VALUESLESSTHANTO_DATE2005-7-1YYYY-MM-DDPARTITIONP4VALUESLESSTHAN__XVALUE;表已创建EXECDBMS_REDEFINITION.START_REDEF_TABLEUSERTT_NEW;PL/SQL过程已成功完成EXECDBMS_REDEFINITION.FINISH_REDEF_TABLEUSERTT_NEW;PL/SQL过程已成功完成SELECTCOUNT*FROMT;COUNT*----------5000SELECTCOUNT*FROMTPARTITIONP3;COUNT*----------1096优点保证数据的一致性,在大部分时间内,表T都可以正常进行DML操作只在切换的瞬间锁表,具有很高的可用性这种方法具有很强的灵活性,对各种不同的需要都能满足而且,可以在切换前进行相应的授权并建立各种约束,可以做到切换完成后不再需要任何额外的管理操作不足实现上比上面两种略显复杂适用于各种情况这里只给出了在线重定义表的一个最简单的例子,详细的描述和例子可以参考下面两篇文章Oracle的在线重定义表功能http://blog.itpub.net/post/468/12855Oracle的在线重定义表功能
(二)http://blog.itpub.net/post/468/12962X__:把一个已存在数据的大表改成分区表第一种(表不是太大)
1.把原表改名renamex__1tox__2;
2.创建分区表CREATETABLEx__1PARTITIONBYLISTc_testPARTITIONx__1_p1VALUES1PARTITIONx__1_p2VALUES2PARTITIONx__1_p0VALUESdefaultnologgingASSELECT*FROMx__2;
3.将原表上的触发器、主键、索引等应用到分区表上;
4.删除原表droptablex__2;第二种表很大
1.创建分区表CREATETABLExPARTITIONBYLISTc_test[range]PARTITIONp0VALUES[lessthan]1tablespa__tbs1PARTITIONp2VALUES2tablespa__tbs1PARTITIONx__1_p0VALUES[__xvalue]defaultASSELECT*FROMx__2[where1=2];
2.交换分区altertablexexchangepartitionp0withtablebsvcbusrundatald;
3.原表改名altertablebsvcbusrundataldrenametox0;
4.新表改名altertablexrenametobsvcbusrundatald;
5.删除原表droptablex0;
6.创建新表触发器和索引createindexind_busrundata_lponbsvcbusrundataldlocaltablespa__tbs_brd_ind;或者
1.规划原大表中数据分区的界限,原则上将原表中近期少量数据__至另一表;
2.暂停原大表中的相关触发器;
3.删除原大表中近期数据;
4.改名原大表名称;
5.创建分区表;
6.交换分区;
7.重建相关索引及触发器(先删除之再重建).参考脚本selectcount*fromt1whererecdatesysdate-2createtablex2nologgingasselect*fromt1whererecdatetruncsysdate-2altertrigertrg_t1disabledeletet1whererecdatesysdate-2commitrenamet1tox1createtablet1[nologging]partitionbyrangerecdatepartitionpbeforevalueslessthantruncsysdate-2partitionp__xvalueslessthan__xvalueasselect*fromx1where1=2altertablet1exchangepartitionpbeforewithtablex1altertablet1exchangepartitionp__xwithtablex2droptablex2[重建触发器]droptablex
14.
2.
15.
7.其他如果表中预期的数据量较大,通常都需要考虑使用分区表,确定使用分区表后,还要确定什么类型的分区(rangepartition、hashpartition、listpartition等)、分区区间大小等分区的创建最好与程序有某种默契,偶曾经创建分区表,按自然月份定义分区的,但程序却在查询时默认的开始时间与结束时间是当前日期-30至当前日期,比如当天是
9.18号,那查询条件被产生为
8.18-
9.18,结果分区后并不没有大幅提高性能,后来对程序的查询日期做了调整,按自然月查询,系统的负载小了很多从Oracle
8.0开始支持表分区(MSSQL2005开始支持表分区)Oracle9i分区能够提高许多应用程序的可管理性、性能与可用性分区可以将表、索引及索引编排表进一步划分,从而可以更精细地对这些数据库对象进行管理和访问Oracle提供了种类繁多的分区方案以满足所有的业务需要另外,由于在SQL语句中是完全透明的,所以分区可以用于几乎所有的应用程序分区表允许将数据分成被称为分区甚至子分区的更小的更好管理的块索引也可以这么分区每个分区可以被单独管理,可以不依赖于其他分区而单独发挥作用,因此提供了一个更有利于可用性和性能的结构分区可以提高可管理性、性能与可用性,从而给各种各样的应用程序带来极大的好处通常,分区可以使某些查询以及维护操作的性能大大提高此外,分区还能够在很大程度上简化日常管理任务分区还使数据库设计人员和___能够解决尖端应用程序带来的最难的问题分区是建立上亿万字节数据系统或需要极高可用性系统的关键工具在多CPU配置环境下,如果打算使用并行执行,则分区提供了另一种并行的方法通过给表或索引的不同分区分配不同的并行执行服务器,就可以并行执行对分区表和分区索引的操作表或索引的分区和子分区都共享相同的逻辑属性例如表的所有分区或子分区共享相同的列和约束定义,一个索引的分区或子分区共享相同的索引选项然而它们可以具有不同的物理属性如表空间尽管不需要将表或索引的每个分区或子分区放在不同的表空间,但这样做更好将分区存储到不同的表空间能够减少数据在多个分区中冲突的可能性可以单独备份和恢复每个分区控制分区与磁盘驱动器之间的映射对平衡I/O负载是重要的改善可管理性可用性和性能分区操作对现存的应用和运行在分区表上的标准DML语句来说是透明的但是可以通过在DML中使用分区扩展表或索引的名字来对应用编程,使其利用分区的优点可以使用SQL*Loader、Import和Export工具来装载或卸载分区表中的数据这些工具都是支持分区和子分区的
4.
2.
16.游标
4.
2.
16.
1.游标是什么游标字面理解就是游动的光标用数据库语言来描述游标是映射在结果集中一行数据上的位置实体,有了游标,用户就可以访问结果集中的任意一行数据了,将游标放置到某行后,即可对该行数据进行操作,例如提取当前行的数据等
4.
2.
16.
2.游标的分类显式游标和隐式游标显式游标的使用需要4步
1.声明游标CURSORmycurvartypenumberisselectemp_noemp_zcfromcus_emp_basicwherecom_no=vartype;
2.打开游标openmycur000627注000627是参数
3.读取数据fetchmycurintovarnovarpri__;
4.关闭游标closemycur;
4.
2.
16.
3.游标的属性oracle游标有4个属性%ISOPEN,%FOUND,%NOTFOUND,%ROWCOUNT%ISOPEN判断游标是否被打开,如果打开%ISOPEN等于true否则等于false;%FOUND%NOTFOUND判断游标所在的行是否有效,如果有效,则%FOUNDD等于true,否则等于false;%ROWCOUNT返回当前位置为止游标读取的记录行数
4.
2.
16.
4.示例declarevarnovarchar220;varpri__varchar220;CURSORmycurvartypenumberisselectemp_noemp_zcfromcus_emp_basicwherecom_no=vartype;beginifmycur%isopen=falsethenopenmycur000627;endif;fetchmycurintovarnovarpri__;whilemycur%foundloopdbms_output.put_linevarno||||varpri__;ifmycur%rowcount=2thenexit;endif;fetchmycurintovarnovarpri__;endloop;closemycur;end;PL/SQL记录的结构和C语言中的结构体类似,是由一组数据项构成的逻辑单元PL/SQL记录并不保存在数据库中,它与变量一样,保存在内存空间中,在使用记录时候,要首先定义记录结构,然后声明记录变量可以把PL/SQL记录看作是一个用户自定义的数据类型Declaretypepersonisrecordempnocus_emp_basic.emp_no%typeempzccus_emp_basic.emp_zc%type;person1person;cursormycurvartypenumberisselectemp_noemp_zcfromcus_emp_basicwherecom_no=vartype;beginifmycur%isopen=falsethenopenmycur000627;endif;loopfetchmycurintoperson1;exitwhenmycur%notfound;dbms_output.put_line雇员编号:||person
1.empno||地址:||person
1.empzc;endloop;closemycur;end;
4.
2.
16.
5.典型游标for循环游标for循环示显示游标的一种快捷使用方式,它使用for循环依次读取结果集中的行数据,当form循环开始时,游标自动打开(不需要open),每循环一次系统自动读取游标当前行的数据(不需要fetch,当退出for循环时,游标被自动关闭(不需要使用close)使用游标for循环的时候不能使用open语句,fetch语句和close语句,否则会产生错误declarecursormycurvartypenumberisselectemp_noemp_zcfromcus_emp_basicwherecom_no=vartype;beginforpersoninmycur000627loopdbms_output.put_line雇员编号:||person.emp_no||地址:||person.emp_zc;endloop;end;
4.
2.
16.触发器
4.
2.
17.自定义函数
4.
2.
18.分析函数
4.
2.
16.
1.什么是分析函数
4.
2.
16.
2.ROLLUP用途语法示例
4.
2.
16.
3.CUBE用途语法示例
4.
2.
16.
4.GROUPING用途语法示例
4.
2.
16.
5.RANK用途语法示例
4.
2.
16.
6.DENSE_RANK用途语法示例
4.
2.
16.
7.ROW_NUMBER用途语法示例
4.
2.
16.
8.PARTITIONBY用途语法示例
4.
2.
16.
9.LAG用途语法示例
4.
2.
16.
10.LEAD用途语法示例
4.
2.
16.
11.FIRST用途语法示例
4.
2.
16.
12.LAST用途语法示例
4.
2.
16.
13.PRE__DING和FOLLOWING用途语法示例
4.
2.
16.
14.分位数用途语法示例第4页共69页。