《RACLE特色功能介绍.ppt》由会员分享，可在线阅读，更多相关《RACLE特色功能介绍.ppt（40页珍藏版）》请在三一办公上搜索。
1、For Oracle employees and authorized partners only.Do not distribute to third parties.2008 Oracle Corporation Proprietary and Confidential,第1章 CREATE SEQUENCE创建序列,1.1序列 在很多数据库系统中都存在一个自动增长的列，如果现在要想在Oracle中完成自动增长的功能，则只能依靠序列完成，所有的自动增长操作，需要用户手工完成处理。1.1.1创建序列 语法格式：CREATE SEQUENCE 序列名 INCREMENT BY n START
2、WITH n MAXVALUE/MINVALUE n|NOMAXVALUE CYCLE|NOCYCLE CACHE n|NOCACHE;,第1章 CREATE SEQUENCE创建序列,1.1.1其中：INCREMENT BY 用于定义序列的步长，如果省略，则默认为1，如果出现负值，则代表序列的值是按照此步长递减的。START WITH 定义序列的初始值(即产生的第一个值)，默认为1。MAXVALUE 定义序列生成器能产生的最大值。选项NOMAXVALUE是默认选项，代表没有最大值定义，这时对于递增序列，系统能够产生的最大值是10的27次方;对于递减序列，最大值是-1。MINVALUE定义序列
3、生成器能产生的最小值。选项NOMAXVALUE是默认选项，代表没有最小值定义，这时对于递减序列，系统能够产生的最小值是?10的26次方;对于递增序列，最小值是1。CYCLE和NOCYCLE 表示当序列生成器的值达到限制值后是否循环。CYCLE代表循环，NOCYCLE代表不循环。如果循环，则当递增序列达到最大值时，循环到最小值;对于递减序列达到最小值时，循环到最大值。如果不循环，达到限制值后，继续产生新值就会发生错误。CACHE(缓冲)定义存放序列的内存块的大小，默认为20。NOCACHE表示不对序列进行内存缓冲。对序列进行内存缓冲，可以改善序列的性能。,第1章 序列的使用,1.2如果已经创建了
4、序列，怎样才能引用序列呢?方法是使用CURRVAL和NEXTVAL来引用序列的值。调用NEXTVAL将生成序列中的下一个序列号，调用时要指出序列名，即用以下方式调用:序列名.NEXTVALCURRVAL用于产生序列的当前值，无论调用多少次都不会产生序列的下一个值。如果序列还没有通过调用NEXTVAL产生过序列的下一个值，先引用CURRVAL没有意义。调用CURRVAL的方法同上，要指出序列名，即用以下方式调用:序列名.CURRVAL.,1.1.1创建序列并在插入表记录时使用,【例1.1】创建一个序列articleseq 并通过articleseq.nextval来使用:create SEQUE
5、NCE articleseq increment by 1 start with 1;create table article(id NUMBER,pid NUMBER,rootid NUMBER,title varchar(255),cont varchar(2000),pdate date,isleaf NUMBER,PRIMARY KEY(id);insert into article values(articleseq.nextval,0,1,蚂蚁大战大象,内容蚂蚁大战大象,sysdate,1);,第2章 oracle伪列 rowid介绍,【2.1】rowid简介rowid就是唯一标志
6、记录物理位置的一个id，rowid确定了每条记录是在Oracle中的哪一个数据对象，数据文件、块、行上。ROWID 的格式如下：数据对象编号 文件编号 块编号 行编号 OOOOOO FFF BBBBBB RRR 由 data_object_id#+rfile#+block#+row#组成，占用10个bytes的空间，rowid的显示方式：基于64位编码的18个字符显示，其实rowid的存储方式是：10 个字节即80位存储，其中数据对象编号需要32 位，相关文件编号需要10 位，块编号需要22，位行编号需要16 位，由此，我们可以得出：32bit的object number，每个数据库最多有4G
7、个对象10bit的file number，每个对象最多有1022个文件（2个文件预留）22bit的block number，每个文件最多有4M个BLOCK16bit的row number，每个BLOCK最多有64K个ROWSoracle中数据文件的大小是有限制的，就是每个数据文件最多只能包含222-1个数据块 对于2k的块的数据文件，最大的数据文件是8G32k块的数据文件，最大的数据文件是16*8G,第2章 oracle伪列 rowid介绍,【2.1】rowid简介oracle 10g开始支持bigfile大文件的表空间，这种表空间只能有一个数据文件。rowid中省去了rfile#的10bit
8、,将这10bit给block#，所以bigfile文件可以支持更大的文件，可支持4g的block。如果系统一个block的大小为8k，则最大文件为：4g(block#)*8k(blocksize)=32T，不过还要受操作系统文件大小的限制。理论上的 BFT 可以达到下面所列的值：数据块大小(单位：K)BFT 最大值(单位：T)2k 8T 4k 16T 8k 32T 16k 64T 32k 128T,第2章 oracle伪列 rowid介绍,【2.1】rowid简介通过dbms_rowid这个包，可以直接的得到具体的rowid包含的信息：select dbms_rowid.rowid_objec
9、t(rowid)object_id,dbms_rowid.rowid_relative_fno(rowid)file_id,dbms_rowid.rowid_block_number(rowid)block_id,dbms_rowid.rowid_row_number(rowid)num,A.id from A Rowid伪列有以下重要用途：1）能以最快的方式访问表中的一行；2）能显示表的行是如何存储的。3）可以作为表中行的唯一标识。(查找和删除重复记录),第2章 oracle伪列 和伪表,伪列：指的是在物理上这个列并不存在，只是在查询时才构造出来。伪列通常是自由分配的，用户无法执行修改等操作
10、。比如Oracle中的RowID字段，使用RowID可以快速地定位到某个对应的数据，因为它标记了记录所对应的物理地址，是唯一的。CURRVAL AND NEXTVAL 使用序列号的保留字LEVEL 查询数据所对应的级ROWID 记录的唯一标识ROWNUM 限制查询结果集的数量伪表DUAL该表主要目的是为了保证在使用SELECT语句中的语句的完整性而提供的。,第2章 oracle伪列 rownum用法详解,【2.2】rownum用法详解对于rownum来说它是oracle系统顺序分配为从查询返回的行的编号，返回的第一行分配的是1，第二行是2，依此类推，这个伪字段可以用于限制查询返回的总行数，且r
11、ownum不能以任何表的名称作为前缀。(2.2.1)rownum 对于等于某值的查询条件如果希望找到学生表中第一条学生的信息，可以使用rownum=1作为条件。但是想找到学生表中第二条学生的信息，使用rownum=2结果查不到数据。因为rownum都是从1开始，但是1以上的自然数在rownum做等于判断是时认为都是false条件，所以无法查到rownum=n（n1的自然数）。SQL select rownum,id,name from student where rownum=1;（可以用在限制返回记录条数的地方，保证不出错，如：隐式游标）SQL select rownum,id,name f
12、rom student where rownum=2;ROWNUM ID NAME-,第2章 oracle伪列 rownum用法详解,【2.2】rownum用法详解（2.2.2）rownum对于大于某值的查询条件 如果想找到从第二行记录以后的记录，当使用rownum2是查不出记录的，原因是由于rownum是一个总是从1开始的伪列，Oracle 认为rownum n(n1的自然数)这种条件依旧不成立，所以查不到记录。查找到第二行以后的记录可使用以下的子查询方法来解决。注意子查询中的rownum必须要有别名，否则还是不会查出记录来，这是因为rownum不是某个表的列，如果不起别名的话，无法知道ro
13、wnum是子查询的列还是主查询的列。SQLselect*from(select rownum no,id,name from student)where no2;NO ID NAME-3 200003 李三 4 200004 赵四,第2章 oracle伪列 rownum用法详解,【2.2】rownum用法详解（2.2.3）rownum对于小于某值的查询条件rownum对于rownum1的自然数）的条件认为是成立的，所以可以找到记录。SQL select rownum,id,name from student where rownum select*from(select rownum no,i
14、d,name from student where rownum=2;NO ID NAME-2 200002 王二 3 200003 李三,第2章 oracle伪列 rownum用法详解,【2.2】rownum用法详解（2.2.4）采用嵌套3层的查询语句结合rownum来实现分页看net.sf.hibernate.dialect.Oracle9Dialect:代码:public boolean supportsLimit()return true;public String getLimitString(String sql)StringBuffer pagingSelect=new Stri
15、ngBuffer(100);pagingSelect.append(select*from(select row_.*,rownum rownum_from();pagingSelect.append(sql);pagingSelect.append()row_ where rownum?);return pagingSelect.toString();Oracle采用嵌套3层的查询语句结合rownum来实现分页，这在Oracle上是最快的方式，如果只是一层或者两层的查询语句的rownum不能支持order by。,第2章 oracle伪列 rownum用法详解,【2.2】rownum用法详解
16、（2.2.5）分页查询格式SELECT*FROM(SELECT A.*,ROWNUM RN FROM(SELECT*FROM TABLE_NAME)A WHERE ROWNUM=21其中最内层的查询SELECT*FROM TABLE_NAME表示不进行翻页的原始查询语句。ROWNUM=21控制分页查询的每页的范围。上面给出的这个分页查询语句，在大多数情况拥有较高的效率。分页的目的就是控制输出结果集大小，将结果尽快的返回。在上面的分页查询语句中，这种考虑主要体现在WHERE ROWNUM=40这句上。除了这些操作以外，分页查询还有一个很明显的特点，就是处理的页数越小，效率就越高，越到后面，查询速
17、度越慢。幸运的是，在很多应用中，98%的用户将只关心前5页的数据，使得这些应用仍能得益于这个方法。当我们把order by子句改为order by.desc，同时创建逆索引，我们甚至可以把某些用户关心最后5页数据的需求改变为关心前5页。,第3章 oracle层次查询语句,【3.1】用如下语句实现层次查询：oracle中的select语句可以用START WITH.CONNECT BY PRIOR子句实现递归查询，其基本语法是：select*from tablename start with cond1 connect by cond2 where cond3;简单说来是将一个树状结构存储在一张
18、表里，比如一个表中存在两个字段:id,parentid那么通过表示每一条记录的parent是谁，就可以形成一个树状结构。用上述语法的查询可以取得这棵树的所有记录。其中COND1是根结点的限定语句，当然可以放宽限定条件，以取得多个根结点，实际就是多棵树。COND2是连接条件，其中用PRIOR表示上一条记录，比如 CONNECT BY PRIOR ID=PRAENTID就是说上一条记录的ID是本条记录的PRAENTID，即本记录的父亲是上一条记录。,第3章 oracle层次查询语句,【3.2】示例：创建示例表：CREATE TABLE TBL_TEST(ID NUMBER,NAME VARCHAR
19、2(100 BYTE),PID NUMBER DEFAULT 0);插入测试数据：INSERT INTO TBL_TEST(ID,NAME,PID)VALUES(1,10,0);INSERT INTO TBL_TEST(ID,NAME,PID)VALUES(2,11,1);INSERT INTO TBL_TEST(ID,NAME,PID)VALUES(3,20,0);INSERT INTO TBL_TEST(ID,NAME,PID)VALUES(4,12,1);INSERT INTO TBL_TEST(ID,NAME,PID)VALUES(5,121,2);从Root往树末梢递归select*
20、from TBL_TEST start with id=1 connect by prior id=pid,第3章 oracle层次查询语句,从末梢往树ROOT递归select*from TBL_TEST start with id=5 connect by prior pid=id使用伪列Level显示表中节点的层次关系：这个伪列的作用是在递归查询的结果中用来表示节点在整个结构中所处的层次select level,id,pid from TBL_TEST start with id=1 connect by prior id=pid order by level;,第4章 Oracle左右全
21、连接总结,-建立测试数据 create table a(id number);create table b(id number);insert into a values(1);insert into a values(2);insert into a values(3);insert into b values(1);insert into b values(2);insert into b values(4);commit;,第4章 Oracle左右全连接总结,-左连接:-主流数据库通用的方法 select*from a left join b on a.id=b.id;-Oracle特有
22、的方法 select*from a,b where a.id=b.id(+);ID ID-1 1 2 2 3,第4章 Oracle左右全连接总结,-右连接：-主流数据库通用的方法 select*from a right join b on a.id=b.id;-Oracle特有的方法 select*from a,b where a.id(+)=b.id;ID ID-1 1 2 2 4,第4章 Oracle左右全连接总结,-内连接-主流数据库通用的方法 select*from a join b on a.id=b.id;-where关联 select*from a,b where a.id=b.
23、id;ID ID-1 1 2 2,第4章 Oracle左右全连接总结,-全外连接-主流数据库通用的方法 select*from a full join b on a.id=b.id;-Oracle特有的方法 select*from a,b where a.id=b.id(+)union select*from a,b where a.id(+)=b.id;ID ID-1 1 2 2 3 4,第5章 Oracle日期与时间,DATE这是ORACLE最常用的日期类型，它可以保存日期和时间，常用日期处理都可以采用这种类型。DATE表示的日期范围可以是公元前4712年1月1日至公元9999年12月31
24、日date类型在数据库中的存储固定为7个字节，格式为：第1字节：世纪+100 第2字节：年+100 第3字节：月 第4字节：天 第5字节：小时+1 第6字节：分+1 第7字节：秒+1,第5章 Oracle日期与时间,日期类型长度是7，7个字节分别表示世纪、年、月、日、时、分和秒。由于不会出现0的情况，月和日都是按照原值存储的，月的范围是112，日的范围是131。由于时、分、秒都会出现0的情况，因此存储时采用原值加1的方式。0时保存为1，13时保存为14，23时保存为24。分和秒的情况和小时类似。小时的范围是023，在数据库中以124保存。分和秒的范围都是059，在数据库中以160保存。年和世纪
25、的情况相对比较复杂，可分为公元前和公元后两种情况。由于最小的世纪的值是-47（公元前4712年），最大值是99（公元9999年）。为了避免负数的产生，oracle把世纪加100保存在数据库中。公元2000年，世纪保存为120，公元9999年，世纪保存为199，公元前101年，世纪保存为99（100+(-1)），公元前4712年，世纪保存为53（100+(-47)）。,第5章 Oracle日期与时间,公元 2008-06-27 10:35:00 Len=7:120,108,6,27,11,36,1 公元前 4712-01-01 00:00:00 Len=7:53,88,1,1,1,1,1 取当前
26、日期sysdate-返回当前系统日期和时间，精确到秒如何进行日期运算日期型数据可以与数值加减得到新的日期，加减数值单位为天sysdate+1-取明天的当前时间sysdate-1/24-取当前时间的前一个小时,第5章 Oracle日期与时间,TIMESTAMP(p)这也是ORACLE常用的日期类型，它与date的区别是不仅可以保存日期和时间，还能保存小数秒，小数位数可以指定为0-9，默认为6位，所以最高精度可以到ns(纳秒)，数据库内部用7或者11个字节存储，如果精度为0，则用7字节存储，与date类型功能相同，如果精度大于0则用11字节存储。格式为：第1字节：世纪+100 第2字节：年+100
27、 第3字节：月 第4字节：天 第5字节：小时+1 第6字节：分+1 第7字节：秒+1 第8-11字节：纳秒，采用4个字节存储，内部运算类型为整形,第5章 Oracle日期与时间,常用日期函数TO_CHAR(DATE,FORMATSTR)-格式化日期成字符SQL select to_char(sysdate,YYYY-MM-DD HH24:MI:SS)d1 from dual;D1-2010-05-13 22:56:38TO_DATE(CHAR,FORMATSTR)-将字符转换成日期to_date(2010-02-24 15:01:54,YYYY-MM-DD HH24:MI:SS)格式备注：HH
28、表示12小时进制，HH24表示采用24小时进制，MM表示月份，MI表示分钟。,第5章 Oracle日期与时间,常用日期函数TRUNC(DATE)-返回DATE的日期部分，时间为0点0分0秒SQL select sysdate d1,trunc(sysdate)d2 from dual;D1 D2-2010-5-13 下午 10:59:18 2010-5-13ADD_MONTHS(DATE,MONTHS)-在DATE增加月份得到新日期ADD_MONTHS(sysdate,3)-当前日期加3个月,第5章 Oracle日期与时间,常用日期函数TRUNC(DATE)-返回DATE的日期部分，时间为0点
29、0分0秒SQL select sysdate d1,trunc(sysdate)d2 from dual;D1 D2-2010-5-13 下午 10:59:18 2010-5-13ADD_MONTHS(DATE,MONTHS)-在DATE增加月份得到新日期ADD_MONTHS(sysdate,3)-当前日期加3个月,第5章 Oracle日期与时间,The datetime functions are:ADD_MONTHS、CURRENT_DATECURRENT_TIMESTAMP、DBTIMEZONEEXTRACT(datetime)、FROM_TZLAST_DAY、LOCALTIMESTAM
30、PMONTHS_BETWEEN、NEW_TIMENEXT_DAY、NUMTODSINTERVALNUMTOYMINTERVAL、ROUND(date)SESSIONTIMEZONE、SYS_EXTRACT_UTCSYSDATE、SYSTIMESTAMPTO_CHAR(datetime)、TO_TIMESTAMPTO_TIMESTAMP_TZ、TO_DSINTERVALTO_YMINTERVAL、TRUNC(date)TZ_OFFSET 上述函数的用法可参见Oracle Database SQL Reference 10g Release 1(10.1).chm,第6章 oracle性能分析,o
31、racle在执行sql时如果使用成本方式分析则所有的成本分析信息来源依靠于系统的统计分析表（dba_tables、dba_indexes、dba_tab_columns）数据，如果说统计分析的数据是不准确的，那可能会使oracle分析出来的路径执行性能极差，所以统计分析数据是影响oracle性能极重要的信息。统计分析主要包括产生表及索引的统计信息表的统计信息主要包括表的行数，每行的平均长度（字节），空闲块，统计时间等信息索引的统计信息主要包括行数、层数、叶块数、统计时间等信息。另外oracle还可以统计列及数据不对称信息，9i还可以统计系统信息（cpu，i/o）,第6章 oracle性能分析,
32、oracle推荐用户采用sys.dbms_stats包体进行分析，因为在oracle9i及其以上的版本全面扩充的此包体的功能。sys.dbms_utility包体进行分析时会对所有的信息全部分析一遍，时间比较长，而在9i中sys.dbms_stats可以利用表修改监控技术来判断需统计分析的表进行，节省了用户的分析资源。DBMS_STATS.GATHER_TABLE_STATS的语法如下:DBMS_STATS.GATHER_TABLE_STATS(ownname VARCHAR2,tabname VARCHAR2,partname VARCHAR2,estimate_percent NUMBER
33、,block_sample BOOLEAN,method_opt VARCHAR2,degree NUMBER,granularity VARCHAR2,cascade BOOLEAN,stattab VARCHAR2,statid VARCHAR2,statown VARCHAR2,no_invalidate BOOLEAN,force BOOLEAN);,第6章 oracle性能分析,参数说明:ownname:要分析表的拥有者tabname:要分析的表名.partname:分区的名字,只对分区表或分区索引有用.estimate_percent:采样行的百分比,取值范围0.000001,10
34、0,null为全部分析,不采样.常量:DBMS_STATS.AUTO_SAMPLE_SIZE是默认值,由oracle决定最佳取采样值.block_sapmple:是否用块采样代替行采样.,第6章 oracle性能分析,例：SET AUTOT ON;COL OBJECT_NAME FORMAT A30;EXEC DBMS_STATS.GATHER_TABLE_STATS(USER,T);SELECT OBJECT_ID,OBJECT_NAMEFROM(SELECT ROWNUM RN,OBJECT_ID,OBJECT_NAME FROM(select*from T)WHERE RN BETWEE
35、N 20021 and 20040,第6章 oracle性能分析,返回：Execution Plan-0 SELECT STATEMENT Optimizer=CHOOSE(Cost=864 Card=20 Bytes=1840)1 0 VIEW(Cost=864 Card=20 Bytes=1840)2 1 COUNT(STOPKEY)3 2 TABLE ACCESS(FULL)OF T(Cost=864 Card=457992 Bytes=9617832),第6章 oracle性能分析,返回：Statistics-0 recursive calls0 db block gets5 cons
36、istent gets0 physical reads0 redo size758 bytes sent via SQL*Net to client503 bytes received via SQL*Net from client2 SQL*Net roundtrips to/from client0 sorts(memory)0 sorts(disk)10 rows processed,第6章 oracle性能分析,翻译：recursive calls：在用户级和系统级产生的递归调用的数目。Oracle数据库维护用于内部处理的表。当它需要改变那些表时，Oracle数据库生成一个内部SQL语
37、句，该语句反过来产生一个递归调用。简而言之，递归调用就是代表你的SQL执行的SQL语句。因此，如果你必须解析该查询，例如，你可能必须运行一些其他的查询来得到数据字典的信息。这就是递归调用。空间管理、安全性检查、从SQL中调用PL/SQL-所有这些都会引起递归SQL调用。db block gets：当前块被请求的次数。当存在时，当前模式块将被立即检索，而不会以一致读的方式检索。通常，查询检索的块如果在查询开始时存在，它们就被检索。当前模式块如果存在就立即被检索，而不是从一个以前的时间点检索。在一个SELECT期间，你可以看到当前模式检索，因为对于需要进行全面扫描的表来说，需要读数据字典来找到范围
38、信息（因为你需要立即信息，而不是一致读）。在修改期间，为了向块中写入内容，你要以当前模式访问块。,第6章 oracle性能分析,翻译：consistent gets：对于一个块一致读被请求的次数。这是以一致读模式处理的块数。为了回滚一个块，这将包括从回滚段读取的块的数目。例如，这是你在SELECT语句中读取块的模式。当你进行一个指定的UPDATE/DELETE操作时，你也以一致读模式读取块，然后以当前模式获得块以便实际进行修改physical reads：从磁盘读取的数据块的总数。这个数等于physical reads direct（物理上直接读取的块数）的值加上读入缓存区的所有块数。redo
39、 size：所产生的以字节为单位的redo（重做日志）总数。bytes sent via SQL*Net to client：从前台进程发送到客户端的字节总数。一般来说，这是结果集的整体大小。bytes received via SQL*Net from client：通过网络从客户端收到的字节总数。一般来说，这是通过网络传输的你的查询的大小。,第6章 oracle性能分析,翻译：SQL*Net roundtrips to/from client：发送到客户端和从客户端接收的网络消息总数。一般来说，这是为了得到回答在你和服务器间发生的交互次数。当你在SQL*Plus中增加ARRAYSIZE设置
40、值时，你将看到对于返回多条记录的SELECT语句，这个数字会下降（更少的来回交互，因为每获取N条记录是一个来回）。当你减少你的ARRAYSIZE值时，你将看到这个数字增加。sorts(memory)：完全在内存中执行、且不需要任何磁盘写的排序操作的数目。没有比在内存中排序更好的排序了，除非根本没有排序。排序通常是由表连接SQL操作的选择条件设定所引起的。sorts(disk)：至少需要一次磁盘写的排序操作的次数。需要磁盘输入/输出的排序操作需要耗费大量资源。rows processed：这是由SELECT语句所返回的或是由INSERT、UPDATE或DELETE语句修改的总行数。,For Oracle employees and authorized partners only.Do not distribute to third parties.2008 Oracle Corporation Proprietary and Confidential,
