Sql 培训

wlan 贡献于2014-04-04

作者 caolinzhi  创建于2009-07-15 01:19:00   修改者caolinzhi  修改于2009-08-26 07:04:00字数73776

文档摘要:目标对象:适合所有与数据库应用相关的人员。内容:1.Sql语句的基础知识。 2.Sql的基础优化规则。 3.日常问题的sql解决方法。 4.常见的sql错误写法。基本概念一.写SQL语句的一般原则能用1条sql语句完成的,不用2条sql完成。能用2条或多条SQL语句完成的,不用带游标的存储过程完成。能用带游标的存储过程完成的,不用java完成。
关键词:

目标 对象:适合所有与数据库应用相关的人员。 内容:1.Sql语句的基础知识。    2.Sql的基础优化规则。    3.日常问题的sql解决方法。    4.常见的sql错误写法。 基本概念 一.写SQL语句的一般原则 能用1条sql语句完成的,不用2条sql完成。 能用2条或多条SQL语句完成的,不用带游标的存储过程完成。 能用带游标的存储过程完成的,不用java完成。 能用java完成的,不用c完成。 如果用C都不能完成的话,就要考虑这个功能是不是需要了。 能用Oracle提供的功能或特性时,绝不自己写程序来实现相同的功能。 说明: 1. 实现程序功能有多种方法,如用:1条sql语句,多条sql语句,存储过程等等,数据库开发是实是非常容易的。如果考虑程序的性能,可靠性,扩展性,维护性,写sql是最难的。而且随着系统运行时间的增加,表数据的增加,表结构的更改,索引的更改,sql也必须适应相应的调整,这又增加了写sql语句的难度。所以写出高性能,高维护性,高可靠性的sql语句,又能同时满足业务规则的sql语句是非常难的。这个需要长期的训练。 2.写sql语句一定要有集合的思想。绝对不能用写程序一步一步处理数据的思想来写sql语句。这个问题是所有初级,甚至部分中级程序员写sql语句的通病。 3.就程序的性能,维护性,扩展性来讲。一般是1sql>多条sql>带游标的存储过程。但是这个原则不是绝对的。如:实现一个功能用1条非常复杂且效率极低下的sql来完成,就不如用多条简单高效的sql来实现同样的功能。 4.当能利用Oracle提供的功能和特性时,一定要用,不要尝试自己写程序实现。 二.ORACLE优化器介绍 为了得到最优的执行计划,ORACLE必须使用一些实际的指标来衡量每个执行计划使用的资源(I/0次数、CPU、网络等),这些资源也就是我们所说的代价(cost)。而产生相对最优的执行计划的工作就是ORACLE的优化器进行的工作。 RBO:规则优化器。遵循简单的分级方法学,使用15种级别要点,当接收到查询,优化器将评估使用到的要点数目,然后选择最佳级别(最少的数量)的执行路径来运行查询。 15种级别: RBO Path 1: Single Row by Rowid(等级最高) RBO Path 2: Single Row by Cluster Join RBO Path 3: Single Row by Hash Cluster Key with Unique or Primary Key RBO Path 4: Single Row by Unique or Primary Key RBO Path 5: Clustered Join RBO Path 6: Hash Cluster Key RBO Path 7: Indexed Cluster Key RBO Path 8: Composite Index RBO Path 9: Single-Column Indexes RBO Path 10: Bounded Range Search on Indexed Columns RBO Path 11: Unbounded Range Search on Indexed Columns RBO Path 12: Sort Merge Join RBO Path 13: MAX or MIN of Indexed Column RBO Path 14: ORDER BY on Indexed Column RBO Path 15: Full Table Scan(等级最低) CBO:成本优化器。根据对象表、索引等的统计数据,在统计数据的基础上计算出相对准确的执行代价。尝试找到最低成本的访问数据的方法,为了最大的吞吐量或最快的初始响应时间,计算使用不同的执行计划的成本,并选择成本最低的一个,关于表的数据内容的统计被用于确定执行计划。 ALL_ROWS: 为CBO优化器使用的第一种具体的优化方法,是以数据的吞吐量为主要目标,以便可以使用最少的资源完成语句。 FIRST_ROWS: 为优化器使用的第二种具体的优化方法,是以数据的响应时间为主要目标,以便快速查询出开始的几行数据。 FIRST_ROWS_[1 | 10 | 100 | 1000]: 为优化器使用的第三种具体的优化方法,让优化器选择一个能够把响应时间减到最小的查询执行计划,以迅速产生查询结果的前 n 行。 ORACLE的OPTIMIZER_MODE共有3种: a. RULE (基于规则RBO) b. COST (基于成本CBO) c. CHOOSE (选择性)   设置缺省的优化器,可以通过对init.ora/spfile.ora文件中OPTIMIZER_MODE参数进行设置,如RULE,CHOOSE,ALL_ROWS. 你当然也在SQL句级(hint)或是会话(session)级对其进行覆盖. 为了使用基于成本的优化器(CBO, Cost-Based Optimizer) , 你必须经常运行analyze/ 执行Dbms_stats包,以增加数据库中的对象统计信息(object statistics)的准确性. 如果数据库的优化器模式设置为选择性(CHOOSE),那么实际的优化器模式将和是否收集过统计信息有关. 如果table已经收集过统计信息, 优化器模式将自动成为CBO , 反之,数据库将采用RULE形式的优化器. 在缺省情况下,ORACLE采用CHOOSE优化器, 为了避免那些不必要的全表扫描(full table scan) , 你必须尽量避免使用CHOOSE优化器,而直接采用基于规则或者基于成本的优化器. 在9i中缺省的是CHOOSE,10g是ALL_ROWS.今后的发展趋势是的CBO的优化. 三.Table的访问方式: 1.全表扫描 全表扫描:是指顺序读取所有的块。Oracle将从段的开始读到结尾(读到高水位标志处),处理所有的块。全表扫描是Oracle读取大量数据行之有效的方法,因为数据库使用多块读取。由于ORACLE知道它打算读取该段中的每一块,因此它将一次性读取多个块,而非一次一块的顺序读。 需要记住的一点是全表扫描并不代表Oracle的执行计划有问题,即全表扫描并不邪恶。在OLAP系统中,很多情况下全表扫描是获得结果的最快方法。但是全表扫描在OLTP系统中应该避免。也就是说,针对不同的业务需求,应当选择合适的方式.如果要求快速的返回结果集中的第一行数据,全表扫描和大数据量操作都应该避免,但是需要尽快得到很大的全部结果集,则全表扫描和大数据量操作是可行的解决方案。 SQL> select * from dual; DU -- X 已用时间: 00: 00: 00.00 Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 TABLE ACCESS (FULL) OF 'DUAL' 2.rowid方式 本节讨论的是oracle从8i开始引进object的概念后的rowid,即扩展(extended)的rowid。8i以前的rowid不讨论。ROWID是行数据的物理地址。ROWID是伪列,是计算出來的,不存储在表中,但是存储在索引中。ROWID除了表移动,重建,exp/imp之外永远不会改变(本节不讨论分区表的rowid)。表移动,重建之后表上的索引会失效,原因就是表的rowid变了,对应的索引必须重建。 rowid的是基于64位编码的18个字符显示(数据对象编号(6) +文件编号(3) +块编号(6)+       行编号(3)=18位),其中 A-Z <==> 0 - 25 (26) a-z <==> 26 - 51 (26) 0-9 <==> 52 - 61 (10) +/ <==> 62 - 63 (2) ROWID的格式如下: 数据对象编号        文件编号        块编号           行编号 OOOOOO             FFF                BBBBBB  RRR rowid的存储方式是:10 个字节即80位存储,其中数据对象编号需要32 位,相关文件编号需要10 位,块编号需要22,位行编号需要16 位,由此,我们可以得出: 32bit的object number,每个数据库最多有4G个对象 10bit的file number,每个表空间最多有1024个文件 22bit的block number,每个文件最多有4M个BLOCK 16bit的row number,每个BLOCK最多有64K个ROWS 利用系统提供的dbms_rowid包可以提取所有关于rowid的信息。通过ROWID是获取特定一行最快的方法。但是,使用ROWID获取成千上万行并不是最好的方法,则大数据量操作更加又效。 SQL> select * from dept where rowid='AAAHW5AABAAAMUSAAA'; DEPTNO DNAME LOC ---------- ---------------------------- ------------------ 10 ACCOUNTING NEW YORK 已用时间: 00: 00: 00.00 Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 TABLE ACCESS (BY USER ROWID) OF 'DEPT' 四.索引的访问方式: B*TREE结构图: 1.索引全扫描. 索引全扫描不读取索引的所有结构,只读取到达第一个叶块的分枝块,其他的分枝块不读取。它使用单块I/O按顺序读取索引,从根开始,通过分枝块到达第一个叶块。单获取第一个叶块后,按顺序读取每个叶块,同样是每次一块(B*TREE的结构是个双向链表,得到一个索引块后,通过指针,可以倒退或前进)。 SQL> select/*+ index(big_table BIG_TABLE_PK)*/ count(*) from big_table; COUNT(*) ---------- 20000000 已用时间: 00: 00: 01.08 Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=44405 Card=1) 1 0 SORT (AGGREGATE) 2 1 INDEX (FULL SCAN) OF 'BIG_TABLE_PK' (UNIQUE) (Cost=44405 Card=20000000) 读取最大值,最小值: SQL> select max(empno) from emp; MAX(EMPNO) ---------- 7934 已用时间: 00: 00: 00.00 Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=1 Card=1 Bytes=4) 1 0 SORT (AGGREGATE) 2 1 INDEX (FULL SCAN (MIN/MAX)) OF 'EMP_PRIMARY_KEY' (UNIQUE ) (Cost=1 Card=14 Bytes=56) SQL> select min(empno) from emp; MIN(EMPNO) ---------- 7369 已用时间: 00: 00: 00.00 Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=1 Card=1 Bytes=4) 1 0 SORT (AGGREGATE) 2 1 INDEX (FULL SCAN (MIN/MAX)) OF 'EMP_PRIMARY_KEY' (UNIQUE ) (Cost=1 Card=14 Bytes=56)    执行计划表明此处是全索引扫描。但是max/min限定词表示它并不是真正的全表扫描整个索引结构来查找max/min值,而是直接从索引的最前面读取min值或者直接从最后面读取max值。 2.索引快速全扫描. 索引快速全扫描与索引全扫描明显的不同,它有以下的特点: .它读取索引结构中的索引块,包括所有的分枝块。 .它采用多块读取(像全表扫描)。 .它不按排序的顺序检索数据。   因为它使用了多块读,所有比索引全扫描更快读取索引结构。 SQL> select count(*) from big_table; COUNT(*) ---------- 20000000 已用时间: 00: 00: 06.02 Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4272 Card=1) 1 0 SORT (AGGREGATE) 2 1 INDEX (FAST FULL SCAN) OF 'BIG_TABLE_PK' (UNIQUE) (Cost=4272 Card=20000000) 3.索引唯一扫描. 索引是唯一的,从索引中查找的数据最多返回一行。 SQL> select count(*) from big_table where id=100; COUNT(*) ---------- 1 已用时间: 00: 00: 00.00 Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=6) 1 0 SORT (AGGREGATE) 2 1 INDEX (UNIQUE SCAN) OF 'BIG_TABLE_PK' (UNIQUE) (Cost=2 Card=1 Bytes=6) 4.索引范围扫描. 索引列是唯一索引,也可能是非唯一索引。查询的结果集可能是0行,1行或者多行。索引的读取方向可能是升序读取(缺省情况),也可能是降序读取。 顺序读取: SQL> select count(*) from big_table where id >1000 and id <100000; COUNT(*) ---------- 98999 已用时间: 00: 00: 00.01 Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=222 Card=1 Bytes=6) 1 0 SORT (AGGREGATE) 2 1 INDEX (RANGE SCAN) OF 'BIG_TABLE_PK' (UNIQUE) (Cost=222 Card=99000 Bytes=594000) 降序读取: 降序读取,避免排序。排序列上必须有索引,且必须是索引的第一列。 SQL> select empno 2 from emp 3 where empno <5000 4 order by empno desc; 未选定行 已用时间: 00: 00: 00.00 Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=1 Card=1 Bytes=4) 1 0 INDEX (RANGE SCAN DESCENDING) OF 'EMP_PRIMARY_KEY' (UNIQUE ) (Cost=1 Card=1 Bytes=4) 5.索引跳跃扫描. 索引跳跃扫描是9i中增加的新功能。它的原理是:在复合索引中,第一列的值非常少(低基数),如status列,而第二列的值基本上是唯一的(高基数)。查询条件中只有索引第二列的时候,将产生索引跳跃扫描。多个列的复合索引也会产生跳跃扫描。 SQL> select count(*) from big_table where object_id='30507'; COUNT(*) ---------- 687 已用时间: 00: 00: 00.00 Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=86 Card=1 Bytes=5) 1 0 SORT (AGGREGATE) 2 1 INDEX (SKIP SCAN) OF 'IDX_BIG_TABLE_OWNER' (NON-UNIQUE) (Cost=86 Card=688 Bytes=3440)    在以前版本的Oracle中,建立索引的原则是:重复值最少的列为索引的前缀列(高基数),如id;重复值多的列作为非前缀列(低基数),如status。在查询条件中只有status列的时候,执行计划是不会走索引的。在9i及其以后的版本中支持跳跃扫描,如果建立的索引是(status,id),查询条件中只有status,或者只有id,或者 status和id都有,都会走索引。这样的话,我们在创建索引的时候,就可以根据具体的sql语句来确定索引的列,而不是根据索引的重复值(基数)来确定索引列。 五.表连接类型 1.等连接 等连接是指在任何一个引用了两个或两个以上表的sql语句中,在where子句中使用等式为表指定连接条件的连接。等连接是最常用的连接。 连接图: SQL: SQL> select emp.ename, 2 emp.deptno, 3 bonus.comm 4 from emp, 5 bonus 6 where emp.ename=bonus.ename; 未选定行 已用时间: 00: 00: 00.00 Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=5 Card=409 Bytes=16360) 1 0 HASH JOIN (Cost=5 Card=409 Bytes=16360) 2 1 TABLE ACCESS (FULL) OF 'BONUS' (Cost=2 Card=82 Bytes=1640) 3 1 TABLE ACCESS (FULL) OF 'EMP' (Cost=2 Card=409 Bytes=8180) 2.外连接(左连接或者右连接) 外连接是表连接的一种特殊形式,在这种情况下,查询结果中包括表中不匹配的字段。外连接指通过在where子句的等式谓词中放置一个加号(+)来实现的。 连接图: SQL: SQL> select emp.ename, 2 emp.deptno, 3 bonus.comm 4 from emp, 5 bonus 6 where emp.ename=bonus.ename(+); ENAME DEPTNO COMM -------------------- ---------- ---------- BLAKE 30 MARTIN 30 ...... 已选择14行。 已用时间: 00: 00: 00.00 Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=5 Card=409 Bytes=16360) 1 0 HASH JOIN (OUTER) (Cost=5 Card=409 Bytes=16360) **注意此处的outer关键字 2 1 TABLE ACCESS (FULL) OF 'EMP' (Cost=2 Card=409 Bytes=8180) 3 1 TABLE ACCESS (FULL) OF 'BONUS' (Cost=2 Card=82 Bytes=1640) 外连接的本质:构造连接表中的一个记录并输出驱动表中的每个记录,而不管是否匹配。所以上面的驱动表是emp,决不能是bouns.因此外连接限制了优化程序可以用的选项。 在一般的情况下,外连接是可以去掉的。真正需要外联接的查询应该是非常少的。 3.自连接 自连接是指一个表与自己连接的情况。一般用来查询2个时间内变化的值。 连接图: Sql语句: SQL> select a.id,a.value-b.value value 2 from test a, 3 test b 4 where a.id=b.id-1; ID VALUE ---------- ---------- 1 21 2 -149 ...... 已选择13行。 已用时间: 00: 00: 00.00 Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=5 Card=1069 Bytes=55588) 1 0 HASH JOIN (Cost=5 Card=1069 Bytes=55588) 2 1 TABLE ACCESS (FULL) OF 'TEST' (Cost=2 Card=327 Bytes=8502) 3 1 TABLE ACCESS (FULL) OF 'TEST' (Cost=2 Card=327 Bytes=8502) 4.反连接 反连接操作是指使用包含not in 或者not exists子句的子查询时进行的连接。从本质上讲,反连接是指在子查询中找到的任何记录都不包含在结果集中的查询。反连接查询返回在谓词的右边没有匹配谓词左边的记录。 连接图: Sql语句: SQL> select a.ename 2 from emp a 3 where not exists (select 'X' 4 from bonus b 5 where b.ename=a.ename 6 ); ENAME -------------------- BLAKE MARTIN ...... 已选择14行。 已用时间: 00: 00: 00.00 Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=5 Card=1 Bytes=14) 1 0 HASH JOIN (ANTI) (Cost=5 Card=1 Bytes=14) **注意此处的ANTI关键字 2 1 TABLE ACCESS (FULL) OF 'EMP' (Cost=2 Card=409 Bytes=2863) 3 1 TABLE ACCESS (FULL) OF 'BONUS' (Cost=2 Card=82 Bytes=574) 反连接的详细情况,见 not in /not exists/外联接 部分 5.半连接 半连接是在子查询中使用exists子句时进行的操作,它之所以叫做半连接是因为,即使子查询中返回多条重复的记录,在外部查询中也只返回一个匹配的值。在使用exists子句的情况下,即使自查询返回多条记录,半连接也不会复制外部查询中引用的值。 连接图: Sql语句: SQL> select dept.* 2 from dept 3 where exists (select 'X' 4 from emp 5 where dept.deptno=emp.deptno 6 and emp.comm>0 7 ); DEPTNO DNAME LOC ---------- ---------------------------- -------------------------- 30 SALES CHICAGO 已用时间: 00: 00: 00.00 Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=5 Card=21 Bytes=1176) 1 0 HASH JOIN (SEMI) (Cost=5 Card=21 Bytes=1176)           **注意此处的SEMI关键字 2 1 TABLE ACCESS (FULL) OF 'DEPT' (Cost=2 Card=409 Bytes=122 70) 3 1 TABLE ACCESS (FULL) OF 'EMP' (Cost=2 Card=20 Bytes=520) 6.笛卡尔连接 笛卡尔连接是最臭名昭著的连接方式了。Oracle的官方文档中的解释是由于蹩脚的程序员写sql语句的时候忘记了书写表与表之间的连接条件。查询的结果集是2或多个表相乘的结果集。 连接图: Sql语句: SQL> select emp.ename, 2 emp.deptno, 3 bonus.comm 4 from emp , 5 bonus; SQL> select emp.ename, 2 emp.deptno, 3 bonus.comm 4 from emp , 5 bonus; 未选定行 已用时间: 00: 00: 00.00 Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=166 Card=33538 Byt es=1106754) 1 0 MERGE JOIN (CARTESIAN) (Cost=166 Card=33538 Bytes=1106754) **注意此处的CARTESIAN关键字 2 1 TABLE ACCESS (FULL) OF 'BONUS' (Cost=2 Card=82 Bytes=1066) 3 1 BUFFER (SORT) (Cost=164 Card=409 Bytes=8180) 4 3 TABLE ACCESS (FULL) OF 'EMP' (Cost=2 Card=409 Bytes=8180) 我们在写sql语句的时候应当避免笛卡尔连接。如果我们在设计数据库的时候特意用了笛卡尔连接,那就是另外一个问题了。有N个表,表与表之间的连接条件最少是N-1个。否则会形成笛卡尔连接。 六.表连接方法 1.索引连接 概念:索引存储的方式:|索引头|字段1长度|字段1值|字段2长度|字段2值|……|对应表记录的rowid|。如果是非索引连接,就有一个查询索引找到对应记录的rowid,通过rowid访问表得到相应的记录过程。如果是索引连接,只需要访问索引即可,不需要通过rowid到相应表记录的访问了。即索引连接不需要回表操作。 a.1个表访问: SQL> select count(*) from big_table where owner='MT'; COUNT(*) ---------- 2061 已用时间: 00: 00: 00.00 Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=9 Card=1 Bytes=6) 1 0 SORT (AGGREGATE) 2 1 INDEX (RANGE SCAN) OF 'IDX_BIG_TABLE_OWNER' (NON-UNIQUE) (Cost=9 Card=2061 Bytes=12366) b.2个表关联的访问: SQL> select count(b.username) 2 from big_table a, 3 mt_user b 4 where a.object_id=30507 5 and a.owner=b.username; COUNT(B.USERNAME) ----------------- 687 已用时间: 00: 00: 00.00 Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=86 Card=1 Bytes=17) 1 0 SORT (AGGREGATE) 2 1 NESTED LOOPS (Cost=86 Card=344 Bytes=5848) 3 2 INDEX (SKIP SCAN) OF 'IDX_BIG_TABLE_OWNER' (NON-UNIQUE ) (Cost=86 Card=688 Bytes=7568) 4 2 INDEX (UNIQUE SCAN) OF 'IDX_U_MT_USER_USERNAME' (UNIQUE) 2.排序合并连接 排序合并连接通常不如散列连接高效,因为它的实现原理是排序第一个输入集,排序第二个输入集,然后合并结果,而散列连接中数据输出前只有一个输入集被处理。 通常排序合并连接在非相等的自连接中用:在连接条件不是一个等式而是范围比较时或自连接。 如:查询雇员入职的时间早于其他雇员的所有的雇员。 SQL> select a.ename,b.ename,a.hiredate,b.hiredate 2 from emp a,emp b 3 where a.hiredate <= b.hiredate 4 and a.empno <> b.empno; ENAME ENAME HIREDATE HIREDATE -------------------- -------------------- ------------------- SMITH ALLEN 1980-12-17 00:00:00 1981-02-20 00:00:00 SMITH WARD 1980-12-17 00:00:00 1981-02-22 00:00:00 ...... 已选择92行。 已用时间: 00: 00: 00.01 Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=12 Card=8344 Bytes=483952) 1 0 MERGE JOIN (Cost=12 Card=8344 Bytes=483952) 2 1 SORT (JOIN) (Cost=6 Card=409 Bytes=11861) 3 2 TABLE ACCESS (FULL) OF 'EMP' (Cost=2 Card=409 Bytes=11 861) 4 1 FILTER 5 4 SORT (JOIN) 6 5 TABLE ACCESS (FULL) OF 'EMP' (Cost=2 Card=409 Bytes=11861) 3.嵌套循环连接 嵌套循环连接是数据库的主要连接方法,它是最常见的连接技术。擅长从结果集中迅速获得第一行。不适合返回大量的结果集。也不是适合返回结果集中的最后一条记录。 选择其中一个表,循环取出其行,对于每一行,索引探察另一个表并找到相匹配的行。   在所有的OLTP系统中,只有嵌套循环才是最好的连接方式。在OLTP系统中,查询的结果集一般只有几条数据,绝大多数的返回结果集只有1条数据,而且where上的谓词列一般都有索引,系统要求是用极小的IO,极小的响应时间。而满足这些要求的只有嵌套循环连接。哈希连接,排序合并连接都不是适合OLTP系统。一般来说,调整OLTP系统的sql语句,就是调整连接方式为嵌套循环连接。 等连接: SQL> select b.username 2 from big_table a, 3 mt_user b 4 where a.id=1000 5 and a.owner=b.username; USERNAME ------------------------------------------------------------ SYS 已用时间: 00: 00: 00.00 Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=1 Bytes=18) 1 0 NESTED LOOPS (Cost=3 Card=1 Bytes=18) 2 1 TABLE ACCESS (BY INDEX ROWID) OF 'BIG_TABLE' (Cost=3 Card=1 Bytes=12) 3 2 INDEX (UNIQUE SCAN) OF 'BIG_TABLE_PK' (UNIQUE) (Cost=2 Card=20000000) 4 1 INDEX (UNIQUE SCAN) OF 'IDX_U_MT_USER_USERNAME' (UNIQUE) 外连接: SQL> select /*+ USE_NL(emp ) */ 2 ename, 3 dname 4 from emp, 5 dept 6 where emp.deptno(+)=dept.deptno; ENAME DNAME -------------------- ---------------------------- CLARK ACCOUNTING KING ACCOUNTING ...... JAMES SALES OPERATIONS 已选择15行。 已用时间: 00: 00: 00.00 Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=820 Card=409 Bytes=17178) 1 0 NESTED LOOPS (OUTER) (Cost=820 Card=409 Bytes=17178)    **注意此处的outer关键字 2 1 TABLE ACCESS (FULL) OF 'DEPT' (Cost=2 Card=409 Bytes=8998) 3 1 TABLE ACCESS (FULL) OF 'EMP' (Cost=2 Card=1 Bytes=20) 4.哈希连接 哈希连接:将结果集较小的表散列之后放入到内存中。一旦将较小结果集的表散列到内存中,则它全扫描较大结果集的表,然后对于大结果集的每一行,散列检索到的 deptno值,查找表散列中匹配的行,返回连接映象。客户应用系统在得到第一行前需要等待oracle扫描并散列结果集较小的表,然后其他行的获取速度越来越快。得到第一行要花一点时间,而获取全部行的速度非常快。 如果内存够大哈希连接的散列表将完全在私有内存区中(PGA中),不会产生一般逻辑IO的拴锁活动。它降低逻辑IO的主要原因是:Oracle在私有内存区中检索并散列它们,而不用在缓冲区高速缓存中反复读取,这样oracle可以任意访问它们,不需要锁定它们。 如果内存不能完全放入散列表时,处理略有不同。Oracle将使用temp表空间保存散列表的一部分或者将用来查找该散列表的表的一部分。当内存散列区填满并且Oracle需要使用Temp时,它将在散列表中找到最大的分区,将其移到temp中。此过程一直持续到散列表的建立。即内存部分和磁盘部分。Oracle读取结果集较大的表并且把连接键散列,在散列表中查询对应的值。如果散列值是内存中的行,连接完成并且该行从该连接中返回。如果行指向磁盘上的散列分区,则oracle在Temp中存储该行,使用与散列表相同的分区模式。因此,当被连接到内存散列表中的全部行都已经连接并且某些分区保留在temp中。然后,Oracle将逐一处理每个磁盘临时分区,将它们读到内存中并连接它们,得到最终的答案。 等连接: SQL> select ename,dname 2 from emp,dept 3 where emp.deptno=dept.deptno; ENAME DNAME -------------------- ---------------------------- MILLER ACCOUNTING KING ACCOUNTING ...... 已选择14行。 已用时间: 00: 00: 00.00 Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=5 Card=409 Bytes=17178) 1 0 HASH JOIN (Cost=5 Card=409 Bytes=17178) 2 1 TABLE ACCESS (FULL) OF 'EMP' (Cost=2 Card=409 Bytes=8180) 3 1 TABLE ACCESS (FULL) OF 'DEPT' (Cost=2 Card=409 Bytes=8998) 外连接: SQL> select ename,dname 2 from emp,dept 3 where emp.deptno(+)=dept.deptno; ENAME DNAME -------------------- ---------------------------- SMITH RESEARCH ALLEN SALES ...... JAMES SALES FORD RESEARCH MILLER ACCOUNTING OPERATIONS 已选择15行。 已用时间: 00: 00: 00.00 Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=5 Card=409 Bytes=17178) 1 0 HASH JOIN (OUTER) (Cost=5 Card=409 Bytes=17178) 2 1 TABLE ACCESS (FULL) OF 'DEPT' (Cost=2 Card=409 Bytes=8998) 3 1 TABLE ACCESS (FULL) OF 'EMP' (Cost=2 Card=409 Bytes=8180) 处理步骤: .扫描并且散列dept表。 .针对每一行扫描emp表。 .查找dept散列表是否存在匹配。当发现匹配时,数据行输入而且散列表中的项标记为与某行匹配。 .当扫描完emp表后,再次检查dept散列表,未标记与emp列相匹配的行输出为NULL值。 SQL> select ename from emp; ENAME -------------------- SMITH ALLEN ...... JAMES FORD MILLER 已选择14行。 已用时间: 00: 00: 00.00 SQL> 哈希连接的外连接和其他连接方式的外连接略有不同。上面的结果集表示:哈希外连接的结果集是按emp表的顺序提供数据行,然后结果集的末尾是dept的所有未匹配的行。 哈希连接擅长连接两个大的结果集或者一大一小两个结果集。读取第一行时比较慢,因为在返回第一行之前,其中一个数据源必须散列到内存中(最好的情况),或者内存和磁盘中。但是读取其他行数据的性能极好,特别是散列表在内存中时。哈希连接非常适合OLAP系统,不适合OLTP系统。 5.位图连接 位图介绍: 位图索引是为数据仓库/特定查询环境设计的。它不是专门设计用在OLTP系统或许多并行会话经常更新数据的系统。位图索引是这样的一种结构,它用来存储带有单个索引码条目的许多行的指针,而在b*tree结构中,索引码和表中的行需要一一对应。在位图索引中,索引的条目的数量非常少,每个条目指向许多行。在b*tree中,是一对一的,即一个索引条目指向一行。位图索引只适合低基数的列。不适合频繁更新的系统。否则并行更新时,可能产生死锁。 如: SQL> create table bitmap_emp as select * from emp; 表已创建。 已用时间: 00: 00: 00.08 SQL> create bitmap index bidx_bit_emp_job on bitmap_emp(job); 索引已创建。 已用时间: 00: 00: 00.00 SQL> bitmap索引的存储如下所示如下所示: 位图连接: SQL> select empno,ename,deptno 2 from bitmap_emp 3 where job='SALESMAN'; EMPNO ENAME DEPTNO ---------- -------------------- ---------- 7499 ALLEN 30 7521 WARD 30 7654 MARTIN 30 7844 TURNER 30 已用时间: 00: 00: 00.00 Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=3 Bytes=117) 1 0 TABLE ACCESS (BY INDEX ROWID) OF 'BITMAP_EMP' (Cost=2 Card=3 Bytes=117) 2 1 BITMAP CONVERSION (TO ROWIDS) 3 2 BITMAP INDEX (SINGLE VALUE) OF 'BIDX_BIT_EMP_JOB' SQL> 七.SQL语句的执行过程 1.SQL语句的类型: DDL语句: 他们是从来不会共享使用的,也就是每次执行都需要进行硬解析。 DML语句:他们会根据情况选择要么进行硬解析,要么进行软解析。 DML:select ,insert,update,delete。 DDL:除去DML语句,其他的都是DDL语句。 2.SQL语句的执行过程: a.DML语句的处理 本节给出一个例子来说明在DML语句处理的各个阶段到底发生了什么事情。假设你使用Pro*C程序来为指定部门的所有职员增加工资。程序已经连到正确的用户,你可以在你的程序中嵌入如下的SQL语句: EXEC SQL UPDATE employees SET salary = 1.10 * salary WHERE department_id = :var_department_id; var_department_id是程序变量,里面包含部门号,我们要修改该部门的职员的工资。当这个SQL语句执行时,使用该变量的值。 每种类型的语句都需要如下阶段: • 第1步: Create a Cursor 创建游标 • 第2步: Parse the Statement 分析语句 • 第5步: Bind Any Variables 绑定变量 • 第7步: Run the Statement 运行语句 • 第9步: Close the Cursor 关闭游标 如果是查询语句,则需要以下几个额外的步骤,如图 3所示: • 第3步: Describe Results of a Query 描述查询的结果集 • 第4步: Define Output of a Query 定义查询的输出数据 • 第8步: Fetch Rows of a Query 取查询出来的行 如果使用了并行功能,还会包含下面这个阶段: • 第6步: Parallelize the Statement 并行执行语句 第1步: 创建游标(Create a Cursor) 由程序接口调用创建一个游标(cursor)。任何SQL语句都会创建它,特别在运行DML语句时,都是自动创建游标的,不需要开发人员干预。多数应用中,游标的创建是自动的。然而,在预编译程序(pro*c)中游标的创建,可能是隐含的,也可能显式的创建。在存储过程中也是这样的。 第2步:分析语句(Parse the Statement) 在分析期间,SQL语句从用户进程传送到Oracle,SQL语句经分析后,SQL语句本身与分析的信息都被装入到共享SQL区。在该阶段中,可以解决许多类型的错误。 步骤如下 语法检测 (selet * from tmp) 语义检查 (权限、表、列等检查) 生成HASH VALUE 检查PGA(如命中则结束,进行第5步) 检查SGA (如命中则结束,进行第5步) 进行hard parse分析,将代码放入shared pool共享(进行第5步) 数据转换、约束条件、死琐等在分析阶段是无法检查出来的。 第3步: 描述查询结果(Describe Results of a Query) 描述阶段只有在查询结果的各个列是未知时才需要;例如,当查询由用户交互地输入需要输出的列名。在这种情况要用描述阶段来决定查询结果的特征(数据类型,长度和名字)。 第4步: 定义查询的输出数据(Define Output of a Query) 在查询的定义阶段,你指定与查询出的列值对应的接收变量的位置、大小和数据类型,这样我们通过接收变量就可以得到查询结果。如果必要的话,Oracle会自动实现数据类型的转换。这是将接收变量的类型与对应的列类型相比较决定的。 第5步: 绑定变量(Bind Any Variables) 此时,Oracle知道了SQL语句的意思,但仍没有足够的信息用于执行该语句。Oracle 需要得到在语句中列出的所有变量的值。在该例中,Oracle需要得到对department_id列进行限定的值。得到这个值的过程就叫绑定变量(binding variables) 。此过程称之为将变量值捆绑进来。程序必须指出可以找到该数值的变量名(该变量被称为捆绑变量,变量名实质上是一个内存地址,相当于指针)。应用的最终用户可能并没有发觉他们正在指定捆绑变量,因为Oracle 的程序可能只是简单地指示他们输入新的值,其实这一切都在程序中自动做了。 因为你指定了变量名,在你再次执行之前无须重新捆绑变量。你可以改变绑定变量的值,而Oracle在每次执行时,仅仅使用内存地址来查找此值。 如果Oracle 需要实现自动数据类型转换的话(除非它们是隐含的或缺省的),你还必须对每个值指定数据类型和长度。 第6步: 并行执行语句(Parallelize the Statement ) ORACLE 可以在SELECTs, INSERTs, UPDATEs, MERGEs, DELETEs语句中执行相应并行查询操作,对于某些DDL操作,如创建索引、用子查询创建表、在分区表上的操作,也可以执行并行操作。并行化可以导致多个服务器进程(oracle server processes)为同一个SQL语句工作,使该SQL语句可以快速完成,但是会耗费更多的资源,所以除非很有必要,否则不要使用并行查询。 第7步: 执行语句(Run the Statement) 到了现在这个时候,Oracle拥有所有需要的信息与资源,因此可以真正运行SQL语句了。如果该语句为SELECT查询或INSERT语句,则不需要锁定任何行,因为没有数据需要被改变。然而,如果语句为UPDATE或DELETE语句,则该语句影响的所有行都被锁定(同时锁定),防止该用户提交或回滚之前,别的用户对这些数据进行修改。这保证了数据的一致性。 第8步: 取出查询的行(Fetch Rows of a Query) 在fetch阶段,行数据被取出来,每个后续的存取操作检索结果集中的下一行数据,直到最后一行被取出来。上面提到过,批量的fetch是优化的技巧之一。 第9步: 关闭游标(Close the Cursor) SQL语句处理的最后一个阶段就是关闭游标 b.DDL语句的处理(DDL Statement Processing) DDL语句的执行不同与DML语句和查询语句的执行,这是因为DDL语句执行成功后需要对数据字典数据进行修改。对于DDL语句,语句的分析阶段实际上包括分析、查找数据字典信息和执行。 事务管理语句、会话管理语句、系统管理语句只有分析与执行阶段,为了重新执行该语句,会重新分析与执行该语句。 八.执行计划 1.执行计划解读 阅读顺序: 从上往下, 从右往左。 SQL> set autot on SQL> select * from dual; DU -- X 已用时间: 00: 00: 00.00 Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=11 Card=8168 Bytes=16336) 1 0 TABLE ACCESS (FULL) OF 'DUAL' (Cost=11 Card=8168 Bytes=16336) Cost:CBO赋予查询计划的每个步骤的成本。CBO通过对查询生成许多不同的执行计划/执行路径进行。选择最低成本的执行计划。上面的执行计划的成本是10。 Card:基数。是指查询计划步骤将输出的记录行数的估计。上面的可以看出是8168行。 Bytes:CBO预测的执行计划返回的数据字节数量。上面的是16336。 Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 3 consistent gets 0 physical reads 0 redo size 377 bytes sent via SQL*Net to client 503 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed recursive calls:递归调用的数目。 db block gets:用当前方式从缓冲区高速缓存中读取的总块数。 consistent gets:在缓冲区高速缓存中一个块被请求进行一致性读取的次数。一致性读取也可能需要读取(撤销/回滚)的信息。这些也算在内。 physical reads:从数据文件到缓冲区高速缓存物理读取的数目。 redo size:在该语句执行过程中产生的重做信息的字节数。 bytes sent via SQL*Net to client:从服务器发送到客户机的字节总数。 bytes received via SQL*Net from client: 从客户机接收的字节数。 SQL*Net roundtrips to/from client:从客户机发送和接受的SQL*Net消息的总数,包括从多行的结果集中提取的往返消息。 sorts (memory):在用户会话缓存中进行的排序。 sorts (disk):由于排序超过用户的排序区尺寸而使用磁盘的排序(临时表空间)。 rows processed:update/select语句返回的行数。 2.执行计划查看的方法 1.SET AUTOT[RACE] {OFF | ON | TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]] 2. SQL> explain plan for select * from dual; SQL> select * from table(dbms_xplan.display); 九.Sql语句跟踪文件 1.启用跟踪输出 必须设置time_statistics参数为true否则输出结果无意义。 time_statistics=true *系统缺省为true 启用跟踪: alter session set sql_trace=true; 停用跟踪: alter session set sql_trace=false; 跟踪其他的用户: exec dbms_system.set_sql_trace_in_session(324,8,true); 2.定位跟踪文件 create or replace view v_trace_all as select b.sid,b.serial#,b.username,b.machine,b.program,rtrim(c.value)||'/'||d.instance_name||'_ora'||ltrim(to_char(a.spid))||'.trc' trace_name from v$process a, v$session b, v$parameter c, v$instance d where a.addr=b.paddr --and b.audsid=sys_context('userenv','sessionid') and b.username is not null and c.name='user_dump_dest'; grant select on v_trace_all to public; create synonym v_trace_all for v_trace_all; create or replace view v_trace as select rtrim(c.value)||'/'||d.instance_name||'_ora'||ltrim(to_char(a.spid))||'.trc' trace_name from v$process a, v$session b, v$parameter c, v$instance d where a.addr=b.paddr and b.audsid=sys_context('userenv','sessionid') and c.name='user_dump_dest'; grant select on v_trace to public; create synonym v_trace for v_trace; 3.Tkprof TKPROF: call : 表示sql语句执行的每个阶段,每个sql语句的活动被分成以下3部分: Parse: 语句被解析的次数,主要是生成执行计划。包含hard parse与 soft parse。 需要做的工作:权限检查,表、列、引用的表的存在性检查; 比较执行计划,选出最好的一个等等。 Execute: 真正执行语句的时间,对于DML语句,在此阶段中修改数据; 对于select语句,这步只是标识出查询出的行。 Fetch : 只对select语句有效,DML语句的执行并没有该阶段 其它列的值都是与sql执行三个阶段中所耗费的资源的统计值 COUNT 一个语句被parsed、executed、fetched的次数 CPU 执行这个语句的每个阶段耗费的cpu时间 ELAPSED 执行这个语句的每个阶段耗费的总时间(包括上面的cpu时间与其它时间,如读数据) DISK (=physical reads) 每个阶段读硬盘的次数(有可能从file system buffer中取得数据) 对于该参数,我们希望该值越小越好,如果该值很大,该sql需要调整, 建立相关索引或看是否正确的使用了索引 QUERY (=Consistent Gets ) 每个阶段以consistent mode 方式从数据库buffer中查询的buffers数。 对于查询,其buffer一般都是以consistent mode模式被读取 CURRENT (=db block gets) 每个阶段以current mode方式从数据库buffer中查询的buffers数。 对于DML语句,需要的buffer是以current mode模式被读取的。 QUERY + CURRENT 的和是该sql语句总的存取的buffer数目 ROWS 这个sql语句最后处理的行数,不包括子查询中查询出来的行数。 对于select语句,该值产生于fetch阶段;对于dml该值产生于execute阶段。 Sql语句的基础优化规则 一.绑定变量 1.概念: 为了不重复解析相同的SQL语句,在第一次解析之后, ORACLE将SQL语句存放在内存中.这块位于系统全局区域SGA(system global area)的共享池(shared buffer pool)中的内存可以被所有的数据库用户共享. 因此,当你执行一个SQL语句(有时被称为一个游标)时,如果它和之前的执行过的语句完全相同, ORACLE就能很快获得已经被解析的语句以及执行路径. ORACLE的这个功能大大地提高了SQL的执行性能并节省了内存和CPU的使用. 数据库管理员必须在init.ora(spfile)中为这个区域设置合适的参数,当这个内存区域越大,就可以保留更多的语句,当然被共享的可能性也就越大了.当你向ORACLE 提交一个SQL语句,ORACLE会首先在这块内存中查找相同的语句.这里需要注明的是,ORACLE对两者采取的是一种严格匹配,要达成共享,SQL语句必须完全相同(包括空格,换行等). 共享的语句必须满足三个条件:  A.      字符级的比较: 当前被执行的语句和共享池中的语句必须完全相同. 例如: SELECT * FROM EMP; 和下列每一个都不同 SELECT * from EMP; Select * From Emp; SELECT * FROM EMP; B.      两个语句所指的对象必须完全相同: 例如: 用户 对象名 如何访问 Jack sal_limit private synonym Work_city public synonym Plant_detail public synonym Jill sal_limit private synonym Work_city public synonym Plant_detail table owner   考虑一下下列SQL语句能否在这两个用户之间共享.  SQL 能否共享 原因 select max(sal_cap) from sal_limit; 不能 每个用户都有一个private synonym - sal_limit , 它们是不同的对象 select count(*0 from work_city where sdesc like 'NEW%'; 能 两个用户访问相同的对象public synonym - work_city select a.sdesc,b.location from work_city a , plant_detail b where a.city_id = b.city_id 不能 用户jack 通过private synonym访问plant_detail 而jill 是表的所有者,对象不同.  C.      两个SQL语句中必须使用相同的名字的绑定变量(bind variables) 例如:第一组的两个SQL语句是相同的(可以共享),而第二组中的两个语句是不同的(即使在运行时,赋于不同的绑定变量相同的值) a. select pin , name from people where pin = :blk1.pin; select pin , name from people where pin = :blk1.pin; b. select pin , name from people where pin = :blk1.ot_ind; select pin , name from people where pin = :blk1.ov_ind; 2.绑定变量的优点: 不使用绑定变量,性能受损失: 测试代码: --不是用绑定变量。 CREATE OR REPLACE procedure p_test1 as vsql varchar2(2000); nid number; begin for i in 1..50000 loop vsql:='select id from big_table where id='||i; execute immediate vsql into nid; end loop; end; / --使用绑定变量: CREATE OR REPLACE procedure p_test2 as vsql varchar2(2000); nid number; begin for i in 1..50000 loop vsql:='select id from big_table where id=:x'; execute immediate vsql into nid using i; end loop; end; / 测试结果: SQL> exec p_test1; PL/SQL 过程已成功完成。 已用时间: 00: 00: 23.00   SQL> exec p_test2; PL/SQL 过程已成功完成。 已用时间: 00: 00: 02.05 SQL> 系统cpu负载图: **红色是运行p_test1是的负载。没有使用绑定变量。 **黄色是运行p_test2时的负载。使用绑定变量。 结论: *是用了绑定变量的存储过程运行时间是2s,没有使用绑定变量的存储过程运行时间是23s。相差11.5倍。    *没有使用绑定变量的存储过程的平均负载要比使用了绑定变量的存储高20%左右。 不使用绑定变量,系统不可伸缩: 从上面的cpu负载图可以得出另外一个问题。即硬分析不仅是一种CPU密集型的操作,而且更是一种不可伸缩的操作。DML的分析和优化是不能与其他许多操作并发进行的。ORACLE必须控制SGA中共享数据结构的访问,不允许他人修改我们会话需要读取的数据结构,否则会引起系统崩溃。ORACLE利用拴锁(串行)来保护这些共享结构。访问SGA中的各个部分,必须给他们上“拴锁”。在锁定它们后,数据库的并发操作要受到影响。同时分析的人越多,保护共享池的拴锁就越多,所进行的并发工作就越少。系统的拴锁是和硬件相关的。一旦硬件确定后,系统支持的拴锁数量就确定了。 不使用绑定变量,代码难于编写: 静态SQL语句: SELECT id FROM big_table WHERE object_name ='caolinzhi'; 不使用绑定变量的动态SQL语句: Execute immediate 'select id from big_table where object_name='''||'caolinzhi'||'''' into nid; 使用了绑定了变量的动态SQL语句: ...... Execute immediate 'select id from big_table where object_name=:x’ into nid using ‘caolinzhi’; ...... 结论: *使用绑定变量的代码非常容易写。也更容易阅读。 *如果上面的字符变量是 caolinzhi’s ,请自己尝试下上面的SQL语句应当怎样写? 不使用绑定变量,代码不安全: Sql语句注入的问题,是非常严重的安全问题。使用绑定变量完全可以避免SQL注入的问题。 --使用退换变量来代替前端应用的输入 SQL> select username,password from dba_users where username='MT'; USERNAME PASSWORD -----------------------------  ------------------------------- MT 0080BA31FB125FA7 已用时间: 00: 00: 00.00 SQL> select count(*) from dba_users where username=&name and password=&pwd; 输入 name 的值: 'MT' 输入 pwd 的值: 'xxx' or 1=1 原值 1: select count(*) from dba_users where username=&name and password=&pwd 新值 1: select count(*) from dba_users where username='MT' and password='xxx' or 1=1 COUNT(*) ---------- 31 已用时间: 00: 00: 00.00 SQL> 在SQL中注入or 1=1后,即使其他的条件都错误,都可以查到信息。 --使用了绑定变量: SQL> variable uname varchar2(30); SQL> variable pwd varchar2(100); SQL> exec :uname:='MT'; PL/SQL 过程已成功完成。 已用时间: 00: 00: 00.00 SQL> exec :pwd:=''''||'xxx'' or 1=1'; PL/SQL 过程已成功完成。 已用时间: 00: 00: 00.00 SQL> print uname; UNAME -------------------------------------------------- MT SQL> print pwd; PWD -------------------------------------------------- 'xxx' or 1=1 SQL> select count(*) from dba_users where username=:uname and password=:pwd; COUNT(*) ---------- 0 已用时间: 00: 00: 00.00 SQL> 结论: *能完全避免SQL注入的问题。 3.绑定变量的例外: OLAP系统应当避免使用绑定变量: 绑定变量在OLTP系统中使用可以有很多好处。但是在OLAP系统中就应当避免绑定变量的使用。因为,OLAP系统的并发数相对较少,系统要求的是运行结果,而不是响应速度。同时,SQL语句分析时间,相对于执行时间,基本上可以忽略不计。 *绑定变量综合症。 二.使用表的别名(Alias) 当在SQL语句中连接多个表时, 请使用表的别名并把别名前缀于每个Column上。这样一来,就可以减少解析的时间并减少那些由Column歧义引起的语法错误(Column歧义指的是由于SQL中不同的表具有相同的Column名,当SQL语句中出现这个Column时,SQL解析器无法判断这个Column的归属)。当有多个相同名字的列时,请把列也用别名。 单个SQL语句执行: SQL> select a.deptno,b.deptno from emp a ,dept b where a.deptno=b.deptno; DEPTNO DEPTNO ---------- ---------- 10 10 10 10 ...... 已选择14行。 已用时间: 00: 00: 00.00 SQL> 在存储过程中: create or replace procedure p_test3 as vsql varchar2(2000); nid number; cursor c_s is select a.deptno,b.deptno from scott.emp a ,scott.dept b where a.deptno=b.deptno; begin for c in c_s loop null; end loop; end; / 编译时出现下列错误: PLS-00402: 要求在游标的 SELECT 列表中具有别名,以避免列名重复。 正确的写法: SQL> select a.deptno adeptno,b.deptno bdeptno from emp a ,dept b where a.deptno=b.deptno; 三.SELECT子句中避免使用 ‘ * ‘ 当你想在SELECT子句中列出所有的COLUMN时,使用动态SQL列引用 ‘*’ 是一个方便的方法。不幸的是,这是一个非常低效的方法。 实际上,ORACLE在解析的过程中, 会将’*’ 依次转换成所有的列名, 这个工作是通过查询数据字典完成的, 这意味着解析时将耗费更多的时间。 同时查询的结果占用更多的内存资源和更多的网络带宽。 如:查询每个雇员对应的部门号 错误的写法: select * from scott.emp; 正确的写法: select ename,deptno from scott.emp; 四.使用DECODE函数整合SQL语句 使用DECODE函数可以避免重复扫描相同记录或重复连接相同的表。 例如: SELECT COUNT(*),SUM(SAL) FROM EMP WHERE DEPTNO = 0020 AND ENAME LIKE 'SMITH%'; SELECT COUNT(*),SUM(SAL) FROM EMP WHERE DEPTNO = 0030 AND ENAME LIKE 'SMITH%'; 你可以用DECODE函数高效地得到相同结果: SELECT COUNT(DECODE(DEPTNO,0020,'X',NULL)) D0020_COUNT, COUNT(DECODE(DEPTNO,0030,'X',NULL)) D0030_COUNT, SUM(DECODE(DEPTNO,0020,SAL,NULL)) D0020_SAL, SUM(DECODE(DEPTNO,0030,SAL,NULL)) D0030_SAL FROM EMP WHERE ENAME LIKE 'SMITH%'; 五.用TRUNCATE替代DELETE 当删除表中的记录时,在通常情况下, 回滚段(rollback segments ) 用来存放可以被恢复的信息。 如果你没有COMMIT事务,ORACLE会将数据恢复到删除之前的状态(准确地说是恢复到执行删除命令之前的状况),而当运用TRUNCATE时, 回滚段不再存放任何可被恢复的信息。当命令运行后,数据不能被恢复。因此很少的资源被调用,执行时间也会很短。只有在做全表数据删除时,TRUNCATE才能替代delete。TRUNCATE是DDL不是DML。 六.用Where子句替换HAVING子句   避免使用HAVING子句, HAVING 只会在检索出所有记录之后才对结果集进行过滤。 这个处理需要排序,总计等操作。 如果能通过WHERE子句限制记录的数目,那就能减少这方面的开销。 如:查询20和30部门的员工数 低效: select deptno ,count(*) from emp group by deptno having deptno=20 or deptno=30; 高效: select deptno ,count(*) from emp where deptno=20 or deptno=30 group by deptno; **HAVING 中的条件一般用于对一些集合函数的比较,如COUNT() 等等。 除此而外,一般的条件应该写在WHERE子句中 七.多字段关联的查询 在含有子查询的SQL语句中,要特别注意减少对表的查询。 如:查询 低效: SELECT TAB_NAME FROM TABLES WHERE TAB_NAME = ( SELECT TAB_NAME FROM TAB_COLUMNS WHERE VERSION = 604 ) AND DB_VER= ( SELECT DB_VER FROM TAB_COLUMNS WHERE VERSION = 604 ); 高效: SELECT TAB_NAME FROM TABLES WHERE (TAB_NAME,DB_VER) = ( SELECT TAB_NAME,DB_VER FROM TAB_COLUMNS WHERE VERSION = 604    ); 如:Update 多个Column 低效: UPDATE EMP SET EMP_CAT = (SELECT MAX(CATEGORY) FROM EMP_CATEGORIES), SAL_RANGE = (SELECT MAX(SAL_RANGE) FROM EMP_CATEGORIES) WHERE EMP_DEPT = 0020; 高效: UPDATE EMP SET (EMP_CAT, SAL_RANGE) = (SELECT MAX(CATEGORY) , MAX(SAL_RANGE) FROM EMP_CATEGORIES) WHERE EMP_DEPT = 0020    );   *上面的2个例子是标准的SQL语法。一定要记住。 八.通过内部函数提高SQL效率  SELECT H.EMPNO, E.ENAME, H.HIST_TYPE, T.TYPE_DESC, COUNT ( * ) FROM HISTORY_TYPE T, EMP E, EMP_HISTORY H WHERE H.EMPNO = E.EMPNO AND H.HIST_TYPE = T.HIST_TYPE GROUP BY H.EMPNO, E.ENAME, H.HIST_TYPE, T.TYPE_DESC; --通过调用下面的函数可以提高效率. FUNCTION LOOKUP_HIST_TYPE (TYP IN NUMBER) RETURN VARCHAR2 AS TDESC VARCHAR2 (30); CURSOR C1 IS SELECT TYPE_DESC FROM HISTORY_TYPE WHERE HIST_TYPE = TYP; BEGIN OPEN C1; FETCH C1 INTO TDESC; CLOSE C1; RETURN (NVL (TDESC, '?')); END; FUNCTION LOOKUP_EMP (EMP IN NUMBER) RETURN VARCHAR2 AS ENAME VARCHAR2 (30); CURSOR C1 IS SELECT ENAME FROM EMP WHERE EMPNO = EMP; BEGIN OPEN C1; FETCH C1 INTO ENAME; CLOSE C1; RETURN (NVL (ENAME, '?')); END; SELECT H.EMPNO, LOOKUP_EMP (H.EMPNO), H.HIST_TYPE, LOOKUP_HIST_TYPE (H.HIST_TYPE), COUNT ( * ) FROM EMP_HISTORY H GROUP BY H.EMPNO, H.HIST_TYPE; *经常在论坛中看到如 ‘能不能用一个SQL写出…..’的贴子,殊不知复杂的SQL往往牺牲了执行效率。 能够掌握上面的运用函数解决问题的方法在实际工作中是非常有意义的。 *用函数替代部分SQL语句之后,SQL语句由以前的集操作,变成了行操作。 *只有即少数复杂的sql语句通过在sql语句中嵌入函数后,性能得到了提高。大部分的SQL语句嵌入函数后,性能反而下降非常大。 *在SQL语句中能正确使用函数从而提高性能,是非常难的。 九.用EXISTS替换DISTINCT 当提交一个包含一对多表信息(比如部门表和雇员表)的查询时,避免在SELECT子句中使用DISTINCT。 一般可以考虑用EXIST替换  例如: 低效: SELECT DISTINCT DEPT_NO,DEPT_NAME FROM DEPT D,EMP E WHERE D.DEPT_NO = E.DEPT_NO; 高效: SELECT DEPT_NO, DEPT_NAME FROM DEPT D WHERE EXISTS (SELECT 'X' FROM EMP E WHERE E.DEPT_NO = D.DEPT_NO ); *EXISTS 使查询更为迅速,因为RDBMS核心模块将在子查询的条件一旦满足后,立刻返回结果。 十.避免在索引列上使用NOT/<> 通常我们要避免在索引列上使用NOT/<>, NOT/<>会产生在和在索引列上使用函数相同的影响。当ORACLE遇到NOT/<>,他就会停止使用索引转而执行全表扫描。即,索引只能告诉我们存在什么数据,而不能告诉我们不存在什么数据。 举例: 低效: 没有使用索引 SQL> select empno,ename,deptno from emp where deptno <> 0; EMPNO ENAME DEPTNO ---------- -------------------- ---------- 7369 SMITH 20 7499 ALLEN 30 ...... 7934 MILLER 10 已选择14行。 已用时间: 00: 00: 00.00 Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=9 Bytes=117) 1 0 TABLE ACCESS (FULL) OF 'EMP' (Cost=2 Card=9 Bytes=117) 高效: (这里,使用了索引) SQL> select empno,ename,deptno from emp where deptno > 0; EMPNO ENAME DEPTNO ---------- -------------------- ---------- 7782 CLARK 10 7839 KING 10 ...... 7844 TURNER 30 7900 JAMES 30 已选择14行。 已用时间: 00: 00: 00.00 Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=14 Bytes=182) 1 0 TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (Cost=2 Card=14 Bytes=182) 2 1 INDEX (RANGE SCAN) OF 'IDX_EMP_DEPTNO' (NON-UNIQUE) (Cost=1 Card=14) 十一.避免在索引列上使用IS NULL和IS NOT NULL 避免在索引中使用任何可以为空的列,ORACLE将无法使用该索引 .对于单列索引,如果列包含空值,索引中将不存在此记录。 对于复合索引,如果每个列都为空,索引中同样不存在此记录。 如果至少有一个列不为空,则记录存在于索引中. 如果唯一性索引建立在表的A列和B列上, 并且表中存在一条记录的A,B值为(123,null) , ORACLE将不接受下一条具有相同A,B值(123,null)的记录(插入)。 然而如果所有的索引列都为空,ORACLE将认为整个键值为空而空不等于空。 因此你可以插入1000条具有相同键值的记录,当然它们都是空!单列唯一索引也存在上面的情况。 单列唯一索引测试: SQL> create table test2 (id number,id2 number); 表已创建。 已用时间: 00: 00: 00.00 SQL> create unique index idx_test2_id2 on test2 (id2); 索引已创建。 已用时间: 00: 00: 00.00 SQL> insert into test2 values (1,1); 已创建 1 行。 已用时间: 00: 00: 00.00 SQL> insert into test2 values (1,2); 已创建 1 行。 已用时间: 00: 00: 00.00 SQL> insert into test2 values (1,3); 已创建 1 行。 已用时间: 00: 00: 00.00 SQL> commit; 提交完成。 已用时间: 00: 00: 00.00 SQL> insert into test2 values (1,1); insert into test2 values (1,1) * ERROR 位于第 1 行: ORA-00001: 违反唯一约束条件 (SCOTT.IDX_TEST2_ID2) 已用时间: 00: 00: 00.00 SQL> insert into test2(id) values (1); 已创建 1 行。 已用时间: 00: 00: 00.00 SQL> insert into test2(id) values (2); 已创建 1 行。 已用时间: 00: 00: 00.00 SQL> insert into test2(id) values (3); 已创建 1 行。 已用时间: 00: 00: 00.00 SQL> commit; 提交完成。 已用时间: 00: 00: 00.00 SQL> select * from test2; ID ID2 ---------- ---------- 1 1 1 2 1 3 1 2 3 已选择6行。 已用时间: 00: 00: 00.00 SQL> 空值不存在于索引列中,所以WHERE子句中对索引列进行空值比较将使ORACLE停用该索引。 举例: 低效: 不使用索引 SQL> select empno,ename from emp where deptno is not null; EMPNO ENAME ---------- -------------------- 7369 SMITH 7499 ALLEN ...... 7934 MILLER 已选择14行。 已用时间: 00: 00: 00.00 Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=14 Bytes=18 2) 1 0 TABLE ACCESS (FULL) OF 'EMP' (Cost=2 Card=14 Bytes=182) SQL> select empno,ename from emp where deptno is null; 未选定行 已用时间: 00: 00: 00.00 Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=1 Bytes=13) 1 0 TABLE ACCESS (FULL) OF 'EMP' (Cost=2 Card=1 Bytes=13) 高效: 使用索引 SQL> select empno,ename from emp where deptno >=0; EMPNO ENAME ---------- -------------------- 7782 CLARK 7839 KING ...... 已选择14行。 已用时间: 00: 00: 00.00 Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=14 Bytes=182) 1 0 TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (Cost=2 Card=14 Bytes=182) 2 1 INDEX (RANGE SCAN) OF 'IDX_EMP_DEPTNO' (NON-UNIQUE) (Cost=1 Card=14) 十二.用>=替代> 如果DEPTNO上有一个索引, 高效: SELECT * FROM EMP WHERE DEPTNO >=10 低效: SELECT * FROM EMP WHERE DEPTNO >9; 两者的区别在于, 前者DBMS将直接跳到第一个DEPT等于10的记录而后者将首先定位到DEPTNO=9的记录并且向前扫描到第一个DEPT大于3的记录。 十三.用UNION替换OR (适用于索引列) 通常情况下, 用UNION替换WHERE子句中的OR将会起到较好的效果。 对索引列使用OR将造成全表扫描。 注意, 以上规则只针对多个索引列有效。 如果有column没有被索引, 查询效率可能会因为你没有选择OR而降低。 在下面的例子中,job 和deptno上都建有索引。 低效: SELECT empno,ename,job,deptno FROM EMP WHERE DEPTNO =30 or job='MANAGER'; 高效: SELECT empno,ename,job,deptno FROM EMP WHERE DEPTNO =30 union SELECT empno,ename,job,deptno FROM EMP WHERE job='MANAGER'; 测试: SQL> alter tablespace users offline; 表空间已更改。 已用时间: 00: 00: 00.04 SQL> alter tablespace users online; 表空间已更改。 已用时间: 00: 00: 00.01 SQL> SELECT empno,ename,job,deptno 2 FROM EMP 3 WHERE DEPTNO =30 4 or job='MANAGER'; EMPNO ENAME JOB DEPTNO ---------- -------------------- ------------------ ---------- 7499 ALLEN SALESMAN 30 7521 WARD SALESMAN 30 7566 JONES MANAGER 20 7654 MARTIN SALESMAN 30 7698 BLAKE MANAGER 30 7782 CLARK MANAGER 10 7844 TURNER SALESMAN 30 7900 JAMES CLERK 30 已选择8行。 已用时间: 00: 00: 00.00 Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=7 Bytes=147) 1 0 TABLE ACCESS (FULL) OF 'EMP' (Cost=2 Card=7 Bytes=147) Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 8 consistent gets 6 physical reads 0 redo size 739 bytes sent via SQL*Net to client 503 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 8 rows processed SQL> alter tablespace users offline; 表空间已更改。 已用时间: 00: 00: 00.00 SQL> alter tablespace users online; 表空间已更改。 已用时间: 00: 00: 00.00 SQL> SELECT empno,ename,job,deptno 2 FROM EMP 3 WHERE DEPTNO =30 4 union 5 SELECT empno,ename,job,deptno 6 FROM EMP 7 WHERE job='MANAGER'; EMPNO ENAME JOB DEPTNO ---------- -------------------- ------------------ ---------- 7499 ALLEN SALESMAN 30 7521 WARD SALESMAN 30 7566 JONES MANAGER 20 7654 MARTIN SALESMAN 30 7698 BLAKE MANAGER 30 7782 CLARK MANAGER 10 7844 TURNER SALESMAN 30 7900 JAMES CLERK 30 已选择8行。 已用时间: 00: 00: 00.00 Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=8 Card=8 Bytes=168) 1 0 SORT (UNIQUE) (Cost=8 Card=8 Bytes=168) 2 1 UNION-ALL 3 2 TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (Cost=2 Card=5 Bytes=105) 4 3 INDEX (RANGE SCAN) OF 'IDX_EMP_DEPTNO' (NON-UNIQUE)(Cost=1 Card=5) 5 2 TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (Cost=2 Card=3Bytes=63) 6 5 INDEX (RANGE SCAN) OF 'IDX_EMP_JOB' (NON-UNIQUE) (Cost=1 Card=3) Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 4 consistent gets 3 physical reads 0 redo size 739 bytes sent via SQL*Net to client 503 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 1 sorts (memory) 0 sorts (disk) 8 rows processed SQL>    *or :8 consistent gets , 6 physical reads。Union :4 consistent gets , 3 physical reads 。可以看出使用了union 的物理读和逻辑读都只有使用了or的一半。 十四.总是使用索引的第一个列 如果索引是建立在多个列上, 只有在它的第一个列(leading column)被where子句引用时,优化器才会选择使用该索引。 *这个规则适合所有非跳跃扫描的场景。 十五.用UNION-ALL 替换UNION ( 如果有可能的话) 当SQL语句需要UNION两个查询结果集合时,这两个结果集合会以UNION-ALL的方式被合并, 然后在输出最终结果前进行排序。如果用UNION ALL替代UNION, 这样排序就不是必要了。 效率就会因此得到极大提高。 举例: 低效:     SELECT ACCT_NUM, BALANCE_AMT FROM DEBIT_TRANSACTIONS WHERE TRAN_DATE = '31-DEC-95' UNION SELECT ACCT_NUM, BALANCE_AMT FROM DEBIT_TRANSACTIONS WHERE TRAN_DATE = '31-DEC-95' 高效: SELECT ACCT_NUM, BALANCE_AMT FROM DEBIT_TRANSACTIONS WHERE TRAN_DATE = '31-DEC-95' UNION ALL SELECT ACCT_NUM, BALANCE_AMT FROM DEBIT_TRANSACTIONS WHERE TRAN_DATE = '31-DEC-95'; *虽然性能能提高很多,但是必须得满足union all后的结果是唯一的。 十六.ORDER BY也能使用索引 ORDER BY 子句只在两种严格的条件下使用索引。 ORDER BY中所有的列必须包含在相同的索引中并保持在索引中的排列顺序。 ORDER BY中所有的列必须定义为非空。 WHERE子句使用的索引和ORDER BY子句中所使用的索引不能并列。 如: SQL> alter table emp add constraint pk_emp primary key (empno); 表已更改。 已用时间: 00: 00: 00.00 SQL> select ename from emp order by empno; ENAME -------------------- SMITH ... FORD MILLER 已选择14行。 已用时间: 00: 00: 00.00 Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=14 Bytes=140) 1 0 TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (Cost=2 Card=14 Bytes=140) 2 1 INDEX (FULL SCAN) OF 'PK_EMP' (UNIQUE) (Cost=1 Card=14) SQL> 日常问题的sql解决方法 一.标量子查询 子查询有以下三种情况: select 后面的子查询(标量子查询),from 后面的子查询,where后面的子查询。 select (select max(empno) from emp) max_empno, (select min(empno) from emp) min_empno from dual; SQL> select b.deptno, 2 b.dname, 3 (select ename from emp a where b.deptno=a.deptno) emane 4 from dept b; (select ename from emp a where b.deptno=a.deptno) emane * ERROR 位于第 3 行: ORA-01427: 单行子查询返回多于一个行 已用时间: 00: 00: 00.00 SQL> *子查询至多返回一行。即查询结果不能 >=2行。 一般用来处理下面的问题: *消除对外连接。 *在单个表中选择不同的表/行。 消除对外连接: SQL> update emp set deptno = null where empno=7934; 已更新 1 行。 已用时间: 00: 00: 00.00 SQL> set autot on exp SQL> select a.ename, 2 a.deptno, 3 (select dname from dept b where b.deptno=a.deptno) dname 4 from emp a; ENAME DEPTNO DNAME -------------------- ---------- ---------------------------- SMITH 20 RESEARCH ...... FORD 20 RESEARCH MILLER 已选择14行。 已用时间: 00: 00: 00.00 Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=14 Bytes=126) 1 0 TABLE ACCESS (FULL) OF 'EMP' (Cost=2 Card=14 Bytes=126) SQL> select a.ename, 2 a.deptno, 3 b.dname 4 from emp a, 5 dept b 6 where a.deptno=b.deptno(+) 7 ; ENAME DEPTNO DNAME -------------------- ---------- ---------------------------- KING 10 ACCOUNTING ...... ALLEN 30 SALES MILLER 已选择14行。 已用时间: 00: 00: 00.00 Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=5 Card=14 Bytes=308) 1 0 HASH JOIN (OUTER) (Cost=5 Card=14 Bytes=308) 2 1 TABLE ACCESS (FULL) OF 'EMP' (Cost=2 Card=14 Bytes=126) 3 1 TABLE ACCESS (FULL) OF 'DEPT' (Cost=2 Card=4 Bytes=52) SQL> 带聚合函数时: select a.username,count(*) from all_users a, all_objects b where a.username=b.owner(+) group by a.username; select a.username,(select count(*) from all_objects b where a.username=b.owner ) from all_users a; *当用子查询替代外连接时,不仅使sql语句更容易阅读,而性能也经常可以得到改善。 *在9i中看不到子查询的执行计划。但是在10g中能看到执行计划。 在单个表中选择不同的表/行 select object_type, object_name, decode(status,'INVALID','*','') status, decode(object_type, 'TABLE',(select tablespace_name from user_tables where table_name=object_name ), 'TABLE PARTITION',(select tablespace_name from user_tab_partitions where partition_name=object_name ), 'INDEX',(select tablespace_name from user_indexes where index_name=object_name ), null ) tablespace_name from user_objects a order by object_type,object_name; *在应用decode逐行查看不复杂的视图,则查询经常以毫秒级运行,甚至在大数据量时也是如此。 二.not in SQL> alter table emp modify deptno null; 表已更改。 已用时间: 00: 00: 00.00 SQL> select * from dept where deptno not in (select deptno from emp); DEPTNO DNAME LOC ---------- ---------------------------- -------------------------- 40 OPERATIONS BOSTON 已用时间: 00: 00: 00.00 Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=4 Card=1 Bytes=20) 1 0 FILTER 2 1 TABLE ACCESS (FULL) OF 'DEPT' (Cost=2 Card=1 Bytes=20) 3 1 TABLE ACCESS (FULL) OF 'EMP' (Cost=2 Card=1 Bytes=3) 即使emp.deptno列上有索引,当emp.deptno列可以为null时,emp还是走全表扫描。 SQL> select * from dept where deptno not in (select deptno from emp where deptno is not null); DEPTNO DNAME LOC ---------- ---------------------------- -------------------------- 40 OPERATIONS BOSTON 已用时间: 00: 00: 00.00 Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=1 Bytes=23) 1 0 NESTED LOOPS (ANTI) (Cost=2 Card=1 Bytes=23) 2 1 TABLE ACCESS (FULL) OF 'DEPT' (Cost=2 Card=4 Bytes=80) 3 1 INDEX (RANGE SCAN) OF 'IDX_EMP_DEPTNO' (NON-UNIQUE) 当emp.deptno列可以为null时,但是加了where deptno is not null后,上面的执行计划走索引了。 SQL> alter table emp modify deptno not null; 表已更改。 已用时间: 00: 00: 00.00 SQL> select * from dept where deptno not in (select deptno from emp); DEPTNO DNAME LOC ---------- ---------------------------- -------------------------- 40 OPERATIONS BOSTON 已用时间: 00: 00: 00.00 Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=1 Bytes=23) 1 0 NESTED LOOPS (ANTI) (Cost=2 Card=1 Bytes=23) 2 1 TABLE ACCESS (FULL) OF 'DEPT' (Cost=2 Card=4 Bytes=80) 3 1 INDEX (RANGE SCAN) OF 'IDX_EMP_DEPTNO' (NON-UNIQUE) emp.deptno列不可以为null时,走索引。 SQL> alter table emp modify deptno null; 表已更改。 已用时间: 00: 00: 00.00 SQL> update emp set deptno =null where empno=7934; 已更新 1 行。 已用时间: 00: 00: 00.00 Execution Plan ---------------------------------------------------------- 0 UPDATE STATEMENT Optimizer=ALL_ROWS (Cost=1 Card=1 Bytes=7) 1 0 UPDATE OF 'EMP' 2 1 INDEX (UNIQUE SCAN) OF 'PK_EMP' (UNIQUE) SQL> select * from dept where deptno not in (select deptno from emp); 未选定行 已用时间: 00: 00: 00.00 Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=4 Card=1 Bytes=20) 1 0 FILTER 2 1 TABLE ACCESS (FULL) OF 'DEPT' (Cost=2 Card=1 Bytes=20) 3 1 TABLE ACCESS (FULL) OF 'EMP' (Cost=2 Card=1 Bytes=3) SQL> 当emp.deptno中有null值,并且where条件中没有过滤,就会产生上面查询不到数据的问题。 三.反连接下not in /not exists/外连接 之间的转换 只测试一种情况,即子查询存在空值记录的情况。其他的情况类似。 SQL> select count(*) from mt_object where object_name is null; COUNT(*) ---------- 1 已用时间: 00: 00: 00.00 SQL> alter tablespace test offline; 表空间已更改。 已用时间: 00: 00: 01.06 SQL> alter tablespace test online; 表空间已更改。 已用时间: 00: 00: 00.07 SQL> select count(*) 2 from big_table 3 where object_name not in (select object_name 4 from mt_object 5 where object_name is not null 6 ); from big_table * ERROR 位于第 2 行: ORA-00028: 您的会话己被删去 已用时间: 00: 06: 21.07 此处的运行时间估计在18小时以上。实在难等。不得已杀了... SQL> connect mt/mt 已连接。 SQL> alter tablespace test offline; 表空间已更改。 已用时间: 00: 00: 00.08 SQL> alter tablespace test online; 表空间已更改。 已用时间: 00: 00: 00.04 SQL> select count(*) 2 from big_table a 3 where not exists (select 1 4 from mt_object b 5 where a.object_name=b.object_name 6 ); COUNT(*) ---------- 2062 已用时间: 00: 01: 48.00 Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=27909 Card=1 Bytes=66) 1 0 SORT (AGGREGATE) 2 1 FILTER 3 2 TABLE ACCESS (FULL) OF 'BIG_TABLE' (Cost=27908 Card=1000000 Bytes=66000000) 4 2 INDEX (RANGE SCAN) OF 'IDX_MT_OBJECT_OBJECT_NAME' (NON -UNIQUE) (Cost=1 Card=2 Bytes=48) Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 38556982 consistent gets 289534 physical reads 660 redo size 382 bytes sent via SQL*Net to client 503 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed SQL> alter tablespace test offline; 表空间已更改。 已用时间: 00: 00: 01.00 SQL> alter tablespace test online; 表空间已更改。 已用时间: 00: 00: 00.03 SQL> select count(a.id) 2 from big_table a, 3 mt_object b 4 where a.object_name=b.object_name(+) 5 and b.object_name is null; COUNT(A.ID) ----------- 2062 已用时间: 00: 02: 29.05 Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=51757 Card=1 Bytes=90) 1 0 SORT (AGGREGATE) 2 1 FILTER 3 2 HASH JOIN (OUTER) 4 3 TABLE ACCESS (FULL) OF 'BIG_TABLE' (Cost=27908 Card=20000000 Bytes=1320000000) 5 3 INDEX (FAST FULL SCAN) OF 'IDX_MT_OBJECT_OBJECT_NAME' (NON-UNIQUE) (Cost=17 Card=30670 Bytes=736080) Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 289575 consistent gets 366284 physical reads 660 redo size 385 bytes sent via SQL*Net to client 503 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed   虽然mt_object.object_name有空值记录,但是结果不受影响。即结果是2062,而不是1或者2063. *从上面的测试结果可以看出,执行时间是: not exists>外连接>not in . *not exists : 00: 01: 48.00;外联接:00: 02: 29.05。虽然not exists比外联接快了1/3,但是却没有意义。 *在大数据量的反连接查询下,一般都采用外联接来替代not in/not exists。 四.in /exists/子查询 之间的转换。 SQL> alter tablespace test offline; 表空间已更改。 已用时间: 00: 00: 01.06 SQL> alter tablespace test online; 表空间已更改。 已用时间: 00: 00: 00.08 SQL> select count(*) 2 from mt_object 3 where object_name in (select object_name 4 from big_table 5 ); COUNT(*) ---------- 29082 已用时间: 00: 00: 17.04 Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=214707 Card=1 Bytes=90) 1 0 SORT (AGGREGATE) 2 1 NESTED LOOPS (SEMI) (Cost=214707 Card=30670 Bytes=2760300) 3 2 INDEX (FAST FULL SCAN) OF 'IDX_MT_OBJECT_OBJECT_NAME' (NON-UNIQUE) (Cost=17 Card=30670 Bytes=736080) 4 2 INDEX (RANGE SCAN) OF 'IDX_BIG_TABLE_OBJECT_NAME' (NON -UNIQUE) (Cost=7 Card=20000000 Bytes=1320000000) Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 88800 consistent gets 18061 physical reads 0 redo size 383 bytes sent via SQL*Net to client 503 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed SQL> SQL> alter tablespace test offline; 表空间已更改。 已用时间: 00: 00: 00.09 SQL> alter tablespace test online; 表空间已更改。 已用时间: 00: 00: 00.08 SQL> SQL> select count(*) 2 from mt_object a 3 where exists (select 1 4 from big_table b 5 where a.object_name=b.object_name 6 ); COUNT(*) ---------- 29082 已用时间: 00: 00: 17.01 Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=214707 Card=1 Bytes=90) 1 0 SORT (AGGREGATE) 2 1 NESTED LOOPS (SEMI) (Cost=214707 Card=30670 Bytes=2760300) 3 2 INDEX (FAST FULL SCAN) OF 'IDX_MT_OBJECT_OBJECT_NAME' (NON-UNIQUE) (Cost=17 Card=30670 Bytes=736080) 4 2 INDEX (RANGE SCAN) OF 'IDX_BIG_TABLE_OBJECT_NAME' (NON -UNIQUE) (Cost=7 Card=20000000 Bytes=1320000000) Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 88800 consistent gets 18061 physical reads 0 redo size 383 bytes sent via SQL*Net to client 503 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed SQL> *当嵌套只有1层而且条件列上有索引时,查询的结果集与子查询的结果集是1对N的关系时(半连接)。无论是执行计划和执行时间都没有区别。 SQL> alter tablespace test offline; 表空间已更改。 已用时间: 00: 00: 00.09 SQL> alter tablespace test online; 表空间已更改。 已用时间: 00: 00: 00.03 SQL> SQL> select count(*) 2 from ( 3 select distinct a.* 4 from mt_object a, 5 big_table b 6 where a.object_name=b.object_name 7 ); COUNT(*) ---------- 29082 已用时间: 00: 01: 30.01 Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=1799800 Card=1) 1 0 SORT (AGGREGATE) 2 1 VIEW (Cost=1799800 Card=36592495) 3 2 SORT (UNIQUE) (Cost=1799800 Card=36592495 Bytes=5708429220) 4 3 HASH JOIN (Cost=33192 Card=36592495 Bytes=5708429220) 5 4 TABLE ACCESS (FULL) OF 'MT_OBJECT' (Cost=44 Card=30670 Bytes=2760300) 6 4 INDEX (FAST FULL SCAN) OF 'IDX_BIG_TABLE_OBJECT_NA ME' (NON-UNIQUE) (Cost=9399 Card=20000000 Bytes=1320000000) Statistics ---------------------------------------------------------- 275 recursive calls 0 db block gets 97937 consistent gets 97860 physical reads 0 redo size 383 bytes sent via SQL*Net to client 503 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 1 sorts (memory) 0 sorts (disk) 1 rows processed SQL> *用子查询来替代in/exists没有意义。 五.分页 1.ROWNUM介绍 Rownum是查询中的一个伪列,rownum被分配数字1,2,3,4…N是以rownum方式使用的集合中的行数。Rownum不赋给行。由于表中没有行号,因此不能查询表中的第五行或者大于5行的行。Rownum是在查询谓词之后,order by或者group by 之前。 SQL> select empno,ename from emp where rownum <=5 order by empno; EMPNO ENAME ---------- -------------------- 7369 SMITH 7499 ALLEN 7521 WARD 7566 JONES 7654 MARTIN 已用时间: 00: 00: 00.00 SQL> select empno,ename,rownum rn 2 from emp 3 where deptno=30 4 order by empno; EMPNO ENAME RN ---------- -------------------- ---------- 7499 ALLEN 1 7521 WARD 2 7654 MARTIN 3 7698 BLAKE 4 7844 TURNER 5 7900 JAMES 6 已选择6行。 已用时间: 00: 00: 00.00 SQL> 在上面的例子中,可以明显的看出,rownum是在谓词之后,order by之前。 SQL> select count(*) from emp where rownum =5 order by empno; COUNT(*) ---------- 0 已用时间: 00: 00: 00.00 SQL> select count(*) from emp where rownum <=5 order by empno COUNT(*) ---------- 5 已用时间: 00: 00: 00.00 SQL> select count(*) from emp where rownum <5 order by empno; COUNT(*) ---------- 4 已用时间: 00: 00: 00.00 SQL> select count(*) from emp where rownum >=5 order by empno COUNT(*) ---------- 0 已用时间: 00: 00: 00.00 SQL> select count(*) from emp where rownum >5 order by empno; COUNT(*) ---------- 0 已用时间: 00: 00: 00.00 SQL> Rownum 在>=5,=5,>5条件都不为真,因此上面的查询都没有结果。因为在rownum谓词之后,所有rownum中的1,2,3,4都没有,就不会直接进入5.所以上面的Rownum 在>=5,=5,>5查询没有结果。 Rownum一般用来做处理以下的问题: *分页 *执行前N行查询 实际上前N行查询也可以看成是分页处理的第一页的N行。 2.分页 分页是我们在报表中查询中时时遇到的问题。分页处理碰到的问题一般是下面的2个问题: 1.设计问题: 大多数报表查询的设计成下面的样式: 对应的分别是(第一页,前一页,后一页,最后一页)。 如果报表的页数超过1000页,90%以上的人不会查看10页以后的页面。100%人不会一页一页查看到最后一页。绝大多数人的习惯是,如果前10页没有想要的结果,就会从新输入条件再次查询。也有少数部分的人会查看20页,30页甚至40页以后的页面。但是绝对没有人每页都查看。可以想象下,如果报表的页数有1000页,每页20条记录,谁会每页都查看?除非脑袋不正常。 在设计报表分页的时候,建议采用google或者baidu的分页技术. Google初始页面: Google 超过10个页面: 淘宝的页面: 即初始页面最多只有10个页面,当页面超过10个页面时最多只有20页面的标签。无第一页,最后一页的标签。 2.分页SQL的写法 查询N行到M行的一般写法是: select * from (select a.*,rownum rn from (SQL查询语句)a where rownum <= :max_rownum ) where rownum >=:min_row; 最好是添加first_rows提示: select * from (select /*+ first_rows */ a.*,rownum rn from (SQL查询语句)a where rownum <= :max_rownum ) where rn >=:min_row; 例子: SQL> set autot on exp SQL> set autot on SQL> select * 2 from (select /*+ first_rows */ a.*,rownum rn 3 from (select a.id, 4 a.object_id, 5 a.object_name, 6 a.owner, 7 b.user_id 8 from big_table a, 9 mt_user b 10 where a.owner=b.username 11 order by a.owner 12 )a 13 where rownum <= 50 14 ) 15 where rn >=1; ID OBJECT_ID OBJECT_NAME OWNER USER_ID RN ---------- ---------- ------------------ ---------------- ------------------ ------ 637586 26407 DR$PARAMETER CTXSYS 33 1 666668 26407 DR$PARAMETER CTXSYS 33 2 695750 26407 DR$PARAMETER CTXSYS 33 3 ...... 已选择50行。 已用时间: 00: 00: 00.05 Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=40058870 Card=50 Bytes=6750) 1 0 VIEW (Cost=40058870 Card=50 Bytes=6750) 2 1 COUNT (STOPKEY) 3 2 VIEW (Cost=40058870 Card=10000000 Bytes=1220000000) 4 3 NESTED LOOPS (Cost=40058870 Card=10000000 Bytes=1020000000) 5 4 TABLE ACCESS (BY INDEX ROWID) OF 'BIG_TABLE' (Cost =20058870 Card=20000000 Bytes=1660000000) 6 5 INDEX (FULL SCAN) OF 'IDX_BIG_TABLE_OWNER' (NON-UNIQUE) (Cost=58870 Card=20000000) 7 4 TABLE ACCESS (BY INDEX ROWID) OF 'MT_USER' (Cost=1 Card=1 Bytes=19) 8 7 INDEX (UNIQUE SCAN) OF 'IDX_U_MT_USER_USERNAME'(UNIQUE) Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 118 consistent gets 49 physical reads 360 redo size 1782 bytes sent via SQL*Net to client 536 bytes received via SQL*Net from client 5 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 50 rows processed STOPKEY:是rownum <=N在执行计划上的反应。表示sql查询的结果集一旦满足了要求,立即返回。如1000行记录中取id最小的100条记录,原理是这样的:排序前100行数据,取第101行数据的id ,如果id小于第100行的id,则替换原有的行,如果大于则丢弃。处理第102行……直至结束。可以看出,实际上排序的只有100行而不是整个结果集的1000行,相比1000行的全排序,无论是内存,cpu等资源的消耗都会小的多。同时也发现另外一个问题,如果rownum <=N,当N与结果集的行数越接近,排序的记录也就越多,与结果集的全排序消耗的资源排序的时间越接近。报表设计的时候采用类似google的设计,分页的sql语句采用上面的语法,能极大提搞性能同时提高吞吐量。 第10页: SQL> select * 2 from (select /*+ first_rows */ a.*,rownum rn 3 from (select a.id, 4 a.object_id, 5 a.object_name, 6 a.owner, 7 b.user_id 8 from big_table a, 9 mt_user b 10 where a.owner=b.username 11 order by a.owner 12 )a 13 where rownum <= 50*10 14 ) 15 where rn >=50*(10-1); ...... 已选择51行。 已用时间: 00: 00: 00.01 第100页: SQL> select * 2 from (select /*+ first_rows */ a.*,rownum rn 3 from (select a.id, 4 a.object_id, 5 a.object_name, 6 a.owner, 7 b.user_id 8 from big_table a, 9 mt_user b 10 where a.owner=b.username 11 order by a.owner 12 )a 13 where rownum <= 50*100 14 ) 15 where rn >=50*(100-1); ...... 已选择51行。 已用时间: 00: 00: 00.01 第10万页: SQL> select * 2 from (select /*+ first_rows */ a.*,rownum rn 3 from (select a.id, 4 a.object_id, 5 a.object_name, 6 a.owner, 7 b.user_id 8 from big_table a, 9 mt_user b 10 where a.owner=b.username 11 order by a.owner 12 )a 13 where rownum <= 50*100000 14 ) 15 where rn >=50*(100000-1); from big_table a, * ERROR 位于第 8 行: ORA-00028: 您的会话己被删去 已用时间: 00: 11: 07.02 SQL> 许多人用下面语法替代上面的语法。虽然看起来类似,但是是错的。 select * from (select /*+ first_rows */ a.*,rownum rn from (SQL查询语句)a ) where rn between :min_row and :max_row; SQL> select * 2 from (select /*+ first_rows */ a.*,rownum rn 3 from (select a.id, 4 a.object_id, 5 a.object_name, 6 a.owner, 7 b.user_id 8 from big_table a, 9 mt_user b 10 where a.owner=b.username 11 order by a.owner 12 )a 13 ) 14 where rn between 1 and 50; ...... 已选择45行。 SP2-0612: 生成AUTOTRACE报告时出错 已用时间: 00: 06: 56.08                *时间超过了6分钟。 ERROR: ORA-00022: 无效的会话 ID;访问被拒绝 SP2-0612: 生成AUTOTRACE EXPLAIN报告时出错 SP2-0612: 生成AUTOTRACE STATISTICS报告时出错 *可以看出上面的是全排序。 六.分析函数介绍 分析函数是Oracle特有的函数能够解决用标准SQL语句由于效率问题或者用标准SQL语句不能实现或者用标准SQL太难实现的查询.但是在实际应用中普遍存在的查询. 如: 计算运行的总数 前N个查询. 查找一组内的百分比 计算流动的平均值 例子: 表结构: create table t as select rownum id, a.object_name ename, b.user_id deptno, round(dbms_random.value(3000,30000)) sal from dba_objects a, dba_users b where a.owner=B.USERNAME; SQL> desc T; 名称 是否为空? 类型 ---------------------- -------- ---------------------------- ID NUMBER (主键,员工工号) ENAME VARCHAR2(30) (姓名) DEPTNO NUMBER (部门id) SAL NUMBER (工资) create table a as select * from ( select * from t order by DBMS_RANDOM.RANDOM ) where rownum <=500; A表的结构和T表相同,数据量只有500.T表的数据量有46526 1.查询每个部门的工资最高前3位的人: select * from ( select id ,ename ,deptno ,sal ,rank() over (partition by deptno order by sal desc nulls first )temp from t ) where temp <4; rank存在跳跃的情况,dense不存在跳跃的情况. 标准SQL语句的写法: select * from a where (deptno,sal) in ( select t1.deptno deptno ,max(t1.sal) sal from a t1, (select deptno,max(sal) sal from a group by deptno )t2 where (t1.deptno,t1.sal) not in ( select deptno,max(sal)sal from a where a.deptno=t2.deptno and a.sal!=t2.sal group by deptno ) and t1.deptno=t2.deptno and t1.sal!=t2.sal group by t1.deptno union select t1.deptno deptno ,max(t1.sal) sal from a t1, (select deptno,max(sal) sal from a group by deptno )t2 where t1.deptno=t2.deptno and t1.sal!=t2.sal group by t1.deptno union select deptno,max(sal) sal from a group by deptno ); 注意:标准SQL语句没有考虑去掉相同工资的情况,否则SQL语句更复杂. 效率比较: 数据量 | 写法 标准SQL 分析函数 500行 0.1(秒) 0.04(秒) 46526 6分59.26(秒) 0.5(秒) 2:计算部门工资的连续的总和: select id,ename,deptno,sal, sum(sal) over (order by deptno,ename) running_total, sum(sal) over (partition by deptno order by ename) department_total, row_number() over (partition by deptno order by ename)seq from t; 标准SQL语句的写法: select ename,deptno,sal, (select sum(sal) from a e2 where e2.deptno ) 说明: partition-clause 数据记录集分组 order-by-clause 数据记录集排序 windowing clause 功能非常强大、比较复杂,定义分析函数在操作行的集合 1.1 partition-clause 数据记录集分组。 1.2 order-by-clause a 、要和查询对应的记录集排序一致,否则统计数据交叉比较很理解。 b 、如果查询条件表达式没有排序语句,返回记录集会按照 order-by-clause 排序 1.3 windowing-clause 个人理解其为分析函数统计数据范围设定。 a、窗口使用前提:分析函数必须有order-by-clause语句 b、默认窗口范围:RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW c、窗口有三种:range、row、specifying range窗口的例子: 只对日期和数字类型数据生效,只能按照一个字段排序,在对应的字段数据范围内进行搜索.说明范围和排序的升降有关 a、升序,查找[本行字段数据值-range值,本行数据值]数据集合 b、降序, 查找[本行数据值,本行字段数据值+range值]数据集合 1.1:计算范围的第一值(升序) select ename,sal,created, created-100 "created-100", first_value(ename) over (order by created asc range 100 preceding ) ename_prec, first_value(created) over (order by created asc range 100 preceding ) ename_prec from b order by created asc; 说明:最靠近created-100的值 1.2:计算范围的第一值(降序) select ename,sal,created, created+100 "created+100", first_value(ename) over (order by created desc range 100 preceding ) ename_prec, first_value(created) over (order by created desc range 100 preceding ) ename_prec from b order by created desc; 说明:最靠近created+100的值 1.3: select deptno,ename,sal,created, created+100 "created+100", first_value(ename) over (partition by deptno order by created desc range 100 preceding ) ename_prec, first_value(created) over (partition by deptno order by created desc range 100 preceding ) created_prec , count(*) over (partition by deptno order by created desc range 100 preceding)cnt_range from b order by created desc; 2.rows窗口例子: row 窗口是设定分析函数的数据行数,使用该窗口基本没有限制 rows n preceding 即为该窗口数据包括本行前的 n 行以及本行共 (n+1) 行数据 2.1:根据行偏移量计算值:(计算按名字排序的前2行和本行的和,平均值) select deptno,ename,sal, sum(sal) over (partition by deptno order by ename rows 2 preceding ) sliding_total, round(avg(sal) over (partition by deptno order by ename rows 2 preceding ),2) sliding_avg from a order by deptno,ename; 2.2: 根据行偏移量计算值:(查询按日期排序的相邻5组数据的第一行的值):升序 select ename,sal,created, first_value(ename) over (order by created asc rows 5 preceding ) ename_prec, first_value(created) over (order by created asc rows 5 preceding ) created_prec from b order by created asc; 降序 select ename,sal,created, first_value(ename) over (order by created desc rows 5 preceding ) ename_prec, first_value(created) over (order by created desc rows 5 preceding ) created_prec from b order by created desc ; 3: specifying 实际上统计的函数都是由 specifying 窗口设定, range 、 row 窗口实际是指定了分析的对象(字段、数据行),而具体的行数由 specifying 窗口设定,常用表达式如下: unbounded preceding 从当前分区第一行开始,结束于处理的当前行 current row 从当前行开始 ( 并结束 ) numberic expression preceding 从当前行的数字表达式之前的行开始 numberic expression following 从当前行的数字表达式之前的行结束 3.1 preceding说明: select ename,created, created-100 "created-100", first_value(ename) over (order by created asc range 100 preceding ) f_ename_prec, first_value(created) over (order by created asc range 100 preceding ) f_created_prec, last_value(ename) over (order by created asc range 100 preceding ) l_ename_prec, last_value(created) over (order by created asc range 100 preceding ) l_created_prec from b order by created asc; 说明:当用preceding时,第一行为preceding的第一行.最后一行为本行,如果created值相同时,由order by的顺序决定.而与order by的desc或asc无关. 3.3 following说明: select ename,created, first_value(ename ) over (order by created asc range between 100 preceding and 100 following) f_ename_prec, first_value(created) over (order by created asc range between 100 preceding and 100 following ) f_created_prec, last_value(ename ) over (order by created asc range between 100 preceding and 100 following) l_ename_prec, last_value(created) over (order by created asc range between 100 preceding and 100 following ) l_created_prec from b order by created asc; 说明:following不能单独使用,必须在between中实用. First_value由 preceding参数决定,last_value由following决定.first_value和last_value有order by 的desc或asc决定. 3.4计算和自己工资相差100元的人数(和自己相同工资的看着比自己大) select ename,sal,"sal+100",sal-100 "sal-100", (greater_num-1)+(lower_num-1)-(same_num-1) num from ( select ename,sal,sal+100 "sal+100", sal-100 "sal-100", first_value( ename) over ( order by sal desc range 100 preceding) as first_ename , first_value( created) over ( order by sal desc range 100 preceding) as first_created , count(ename) over ( order by sal desc range 100 preceding) as greater_num , count(ename) over ( order by sal asc range 100 preceding) as lower_num , count(ename) over ( order by sal asc range 0 preceding) as same_num from b order by sal desc ); 在这边可以简化以前的前面的 sql: select ename,sal, count( ename) over ( order by sal asc range between 100 preceding and 100 following)-1 num from b order by sal asc; 常用的分析函数: 1.avg(distinct|all expression) 计算组内平均值, distinct 可去除组内重复数据 2.count(<*>) 对组内数据进行计数 (参见 #例二 )。 3.dense_rank() 根据 order by 子句表达式的值,从查询返回的每一行,计算和其他行的相对位置,序号从 1 开始,有重复值时序号不跳号。 4. first_value(显示的表达式),last_value(显示的表达式) 返回组内第一个值,最后一个值。 如: 查询每个部门工资高和最低的人 一般查询sql select max(sal),min(sal),deptno from a groupby deptno 但是无法查询对应人员名称,通过分析函数可以变通实现 select distinct deptno, first_value(ename||' : ' ||sal) over ( partitionby deptno orderby sal) asfirst, first_value(ename||' : ' ||sal) over ( partitionby deptno orderby sal desc) aslast from emp; 要说明的last_value()并不类似于max函数,从分析函数语法解析知道,默认窗口下的last_value分析的是当前组的当前的以前数据行以及当前行,因此 first_value(ename||' : ' ||sal) over ( partitionby deptno orderby sal) 并不等同于 last_value(ename||' : ' ||sal) over (partitionby deptno orderby sal desc) 5.min(expression),max(expression) 返回组内最小,最大值 select distinct max(sal) over (partitionby deptno),min(sal) over(partitionby deptno),deptno from a 该sql和 select max(sal),min(sal),deptno from a groupby deptno有点类似 查看过两者的执行计划,采用分析函数多做了一次排序(在大数据量下没有做测试)。 6.rank() 和 dense_rank ()函数功能类似,但是有重复值时序号是跳号的。 7.row_number() 返回有序组中的一行的偏移量,也就是对应的序号。 8.sum(expression) 计算组中表达式的累计和 七.分析函数之分组查询 1.查询出部门最高工资的3个人: . 给出工资最高的前3位,如果工资相同的话,也显示相同的情况,也就是按工资排序,取工资最高的3个人. . 给出工资最高的前3位,如果工资相同的话,也不显示相同的情况.假如部门最高工资有4个人,且工资相同,就只显示4个人的工资,而排在后面的将不做处理.也就是考虑相同工资的情况. . 最多给出3个人的工资,如果同时有4个人的工资最多,则不显示,如果有2个人的工资最高,2个人的工资次高,将只显示最高的2个人的工资. . 对工资排序,只给出前3行记录,如果部门少于3人,则全部显示. A给出工资最高的前3位,如果工资相同的话,也显示相同的情况,也就是安工资排序,取工资最高的3个值. SQL> select * 2 from (select deptno,ename,sal, 3 dense_rank() over(partition by deptno order by sal desc ) dr 4 from scott.emp 5 ) 6 where dr <=3 7 order by deptno,sal desc; DEPTNO ENAME SAL DR ---------- -------------------- ---------- ---------- 10 KING 5000 1 10 CLARK 2450 2 10 MILLER 1300 3 20 SCOTT 3000 1 20 FORD 3000 1 20 JONES 2975 2 20 ADAMS 1100 3 30 BLAKE 2850 1 30 ALLEN 1600 2 30 TURNER 1500 3 已选择10行。 已用时间: 00: 00: 00.00 SQL> B给出工资最高的前3位,如果工资相同的话,也不显示相同的情况.假如部门最高工资有4个人,且工资相同,就只显示4个人的工资,而排在后面的将不做处理.也就是考虑相同工资的情况. SQL> select * 2 from (select deptno,ename,sal, 3 rank() over(partition by deptno order by sal desc ) dr 4 from scott.emp 5 ) 6 where dr <=3 7 order by deptno,sal desc; DEPTNO ENAME SAL DR ---------- -------------------- ---------- ---------- 10 KING 5000 1 10 CLARK 2450 2 10 MILLER 1300 3 20 SCOTT 3000 1 20 FORD 3000 1 20 JONES 2975 3 30 BLAKE 2850 1 30 ALLEN 1600 2 30 TURNER 1500 3 已选择9行。 已用时间: 00: 00: 04.01 SQL> rank和dense_rank的比较. SQL> select deptno,ename,sal, 2 rank() over(partition by deptno order by sal desc ) rank_dr, 3 dense_rank() over(partition by deptno order by sal desc ) dense_rank_dr 4 from emp; DEPTNO ENAME SAL RANK_DR DENSE_RANK_DR ---------- -------------------- ---------- ---------- ------------- 10 KING 5000 1 1 10 CLARK 2450 2 2 10 MILLER 1300 3 3 20 SCOTT 3000 1 1 20 FORD 3000 1 1 20 JONES 2975 3 2 20 ADAMS 1100 4 3 20 SMITH 800 5 4 30 BLAKE 2850 1 1 30 ALLEN 1600 2 2 30 TURNER 1500 3 3 30 WARD 1250 4 4 30 MARTIN 1250 4 4 30 JAMES 950 6 5 已选择14行。 已用时间: 00: 00: 00.00 SQL> C 最多给出3个人的工资,如果同时有4个人的工资最多,则不显示,如果有2个人的工资最高,2个人的工资次高,将只显示最高的2个人的工资. 先考察下面SQL语句的运行: SQL> select deptno,ename,sal, 2 count(*) over(partition by deptno order by sal desc ) dr_sal, 3 count(*) over(partition by deptno order by sal desc, ename ) dr_sal_ename 4 from emp; DEPTNO ENAME SAL DR_SAL DR_SAL_ENAME ---------- -------------------- ---------- ---------- ------------ 10 KING 5000 1 1 10 CLARK 2450 2 2 10 MILLER 1300 3 3 20 FORD 3000 2 1 20 SCOTT 3000 2 2 20 JONES 2975 3 3 20 ADAMS 1100 4 4 20 SMITH 800 5 5 30 BLAKE 2850 1 1 30 ALLEN 1600 2 2 30 TURNER 1500 3 3 30 MARTIN 1250 5 4 30 WARD 1250 5 5 30 JAMES 950 6 6 已选择14行。 已用时间: 00: 00: 00.01 SQL> 实现本功能的sql: SQL> select * 2 from ( 3 select deptno,ename,sal, 4 count(*) over(partition by deptno order by sal desc ) dr_sal 5 from emp 6 ) 7 where dr_sal <=3; DEPTNO ENAME SAL DR_SAL ---------- -------------------- ---------- ---------- 10 KING 5000 1 10 CLARK 2450 2 10 MILLER 1300 3 20 SCOTT 3000 2 20 FORD 3000 2 20 JONES 2975 3 30 BLAKE 2850 1 30 ALLEN 1600 2 30 TURNER 1500 3 已选择9行。 已用时间: 00: 00: 00.01 SQL> D 对工资排序,只给出前3行记录,如果部门少于3人,则全部显示. SQL> select * 2 from ( 3 select deptno,ename,sal, 4 row_number() over(partition by deptno order by sal desc ) dr 5 from emp 6 ) 7 where dr <=3; DEPTNO ENAME SAL DR ---------- -------------------- ---------- ---------- 10 KING 5000 1 10 CLARK 2450 2 10 MILLER 1300 3 20 SCOTT 3000 1 20 FORD 3000 2 20 JONES 2975 3 30 BLAKE 2850 1 30 ALLEN 1600 2 30 TURNER 1500 3 已选择9行。 已用时间: 00: 00: 00.00 SQL> 八.分析函数之行转列 原始表数据: C1 C2 C3 -- -- -- a1 b1 x1 a1 b1 x2 a1 b1 x3 a12 b12 x21 ...... 转换后的结果: C1 C2 C3(1) C3(2) C3(3) -- -- -- -- -- a1 b1 x1 x2 x3 a12 b12 X21 ....... 转换公式: select c1,C2 max(decode(rn,1,c2,null)) c3_1, max(decode(rn,2,c2,null)) c3_1, ...... max(decode(rn,N,c2,null)) c3_N from (select c1,c2, row_number() over (partition by c1,c2 order by .....) rn from tab_name where ... ) group by c1,c2; 将emp表转换成: Deptno ename(1),ename(2),ename(3) ename(4),ename(5),ename(6) SQL> select deptno, 2 max(decode(rn,1,ename,null)) c2_1, 3 max(decode(rn,2,ename,null)) c2_2, 4 max(decode(rn,3,ename,null)) c2_3, 5 max(decode(rn,4,ename,null)) c2_4, 6 max(decode(rn,5,ename,null)) c2_5, 7 max(decode(rn,6,ename,null)) c2_6, 8 max(decode(rn,7,ename,null)) c2_7 9 from (select deptno,ename, 10 row_number() over (partition by deptno order by ename nulls last) rn 11 from emp 12 ) 13 group by deptno; DEPTNO C2_1 C2_2 C2_3 C2_4  C2_5  C2_6 C2_7 ---------- --------- ------ ------ ------- --------- ---- ------ 30 ALLEN BLAKE JAMES MARTIN TURNER WARD 10 CLARK KING MILLER 20 ADAMS FORD JONES SCOTT SMITH 已用时间: 00: 00: 00.01 SQL> 九.分析函数之访问当前行周围的行 create table b as select rownum id, a.object_name ename, b.user_id deptno, round(dbms_random.value(3000,30000)) sal, a.created from dba_objects a, dba_users b where a.owner=B.USERNAME; 查询每个部门的员工,距离上个员工来部门的时间间隔,和距离下个员工来部门的时间间隔. select deptno,ename,created, lag(created,1,null) over(partition by deptno order by created,ename) last_hire, created-lag(created,1,null) over (partition by deptno order by created,deptno) days_last, lead(created,1,null) over (partition by deptno order by created,ename) next_hire, lead(created,1,null) over (partition by deptno order by created,ename)-created days_next from b order by deptno,created; DEPTNO ENAME CREATED   LAST_HIRE DAYS_LAST NEXT_HIRE DAYS_NEXT ------ ---------------------------------------   ----------  --------- - -------- ---------- ...... 60 RC_DATABASE_BLOCK_CORRUPTION 12-5月 -02 12-5月 -02 0 12-5月 -02 0 60 RC_OFFLINE_RANGE 12-5月 -02 12-5月 -02 0 12-5月 -02 0 60 RC_PROXY_CONTROLFILE 12-5月 -02 12-5月 -02 0 12-5月 -02 0 60 RC_PROXY_DATAFILE 12-5月 -02 12-5月 -02 0 12-5月 -02 0 60 RC_RMAN_CONFIGURATION 12-5月 -02 12-5月 -02 0 12-5月 -02 0 60 RC_STORED_SCRIPT 12-5月 -02 12-5月 -02 0 12-5月 -02 0 60 RC_STORED_SCRIPT_LINE 12-5月 -02 12-5月 -02 .000011574 12-5月 -02 .000034722 60 DBMS_RCVMAN 12-5月 -02 12-5月 -02 .000034722 12-5月 -02 .00005787 60 DBMS_RCVCAT 12-5月 -02 12-5月 -02 .00005787 12-5月 -02 .00005787 60 DBMS_RCVMAN 12-5月 -02 12-5月 -02 .00005787 61 PARTITION_TABLE 03-7月 -09 06-7月 -09 2.94763889 61 PARTITION_TABLE_TMP 06-7月 -09 03-7月 -09 2.94763889 15-7月 -09 8.8796875 61 BIG_TABLE 15-7月 -09 06-7月 -09 8.8796875 15-7月 -09 .005856481 61 BIG_TABLE_PK 15-7月 -09 15-7月 -09 .005856481 06-8月 -09 21.9452199 61 IDX_BIG_TABLE_OWNER 06-8月 -09 15-7月 -09 21.9452199 06-8月 -09 .035520833 61 MT_USER 06-8月 -09 06-8月 -09 .035520833 06-8月 -09 .000358796 61 PK_MT_USER_ID 06-8月 -09 06-8月 -09 .000358796 06-8月 -09 .001041667 61 IDX_U_MT_USER_USERNAME 06-8月 -09 06-8月 -09 .001041667 06-8月 -09 .033576389 61 TOAD_PLAN_TABLE 06-8月 -09 06-8月 -09 .033576389 13-8月 -09 6.96313657 61 P_TEST1 13-8月 -09 06-8月 -09 6.96313657 13-8月 -09 .000219907 61 P_TEST2 13-8月 -09 13-8月 -09 .000219907 13-8月 -09 .018958333 61 P_TEST3 13-8月 -09 13-8月 -09 .018958333 15-8月 -09 2.24905093 61 IDX_BIG_TABLE_OBJECT_NAME 15-8月 -09 13-8月 -09 2.24905093 15-8月 -09 .005173611 61 MT_OBJECT 15-8月 -09 15-8月 -09 .005173611 15-8月 -09 .00037037 61 IDX_MT_OBJECT_OBJECT_NAME 15-8月 -09 15-8月 -09 .00037037 63 T 26-8月 -09 26-8月 -09 .000428241 63 A 26-8月 -09 26-8月 -09 .000428241 已选择18014行。 已用时间: 00: 00: 25.04 SQL> 函数的作用: Lag: Lag可以访问结构集中的其他行而不用进行自连接.它允许去处理游标,就好像游标是一个数组一样.在给定组中可参考当年行之前的行,这样就可以从组中与当前行一起选择以前的行. 语法:lag (expression,,) 说明:offset默认是1.也就是访问当前组中当前行前面的一行.如果访问的数据超出窗口的范围,就返回默认值.(默认值是组中的第一行). Lead: Lead与lag相反,lag让你可访问组中当前行之前的行,而lead是让你访问组中当前行之后的行. 语法:lag (expression,,) 说明:offset默认是1.也就是访问当前组中当前行前面的一行.如果访问的数据超出窗口的范围,就返回默认值.(默认值是组中的最后一行). 说明:lag,lead可以是看作一种对被分区的组上加索引的方法.是用这样函数可以访问任何个别的行. 十.分析函数之冗余数据的查找和删除 冗余数据:是二维表中那些包含有相同字段或字段值的集合.冗余数据不但给数据的准确性和可信度带来负面影响,当冗余数据量很大的时候,还严重影响数据库的性能. 例如T表,id是主键,但是ename有相同的记录,如果ename要求是唯一的话,那么group by enema 大于2条的都是有冗余记录的. Select ename,count(*) from t group by ename having count(*) >1; 查询部门有2个同名人的情况: Select deptno,ename,count(*) from t group by deptno,ename having count(*) >1; 冗余记录一般是如何形成的: 从不同的数据源加载数据或合并数据 从前端输入数据,并自动用sequence为主键,这样容易输入字段的冗余记录. 上面的情况都是缺少保证数据唯一性的约束条件,极其容易产生冗余数据.注意冗余数据,在任何设备上都是不可避免的. 冗余数据的查找: A:查询有冗余记录的deptno,ename: Select deptno,ename,count(*) from t group by deptno,ename having count(*) >1; B:A的查询没有带行的ID,要下面的实现才可以: Select a.id,a.deptno,a.ename,a.sal from t a, (select deptno,ename from t group by deptno,ename having count(*) >1 ) b where a.deptno=b.deptno and a.ename=b.ename order by deptno,ename; Oracle提供的解决方法: A:标准SQL语句: 查询出没有带冗余数据的记录: Select min(id)id,deptno,ename from t group by deptno,ename; 说明:min和max的作用是一样的,不过取的ID不同而已. 删除冗余数据: Delete from t where id not in (select min(id) from t group by deptno,ename); B:Pl/sql解决方法: 核心的SQL语句: Select deptno,ename from t group by deptno,ename having count(*) >1; 定义上面的SQL语句为游标,查询出deptno,ename有冗余的记录,然后再处理. C:用分析函数RANK() select * from ( select id,deptno,ename, rank() over(partition by deptno,ename order by id) seq from t ) where seq >1; 所以删除的SQL语句如下: delete from t where id in ( select id from ( select id,deptno,ename, rank() over(partition by deptno,ename order by id) seq from t ) where seq >1 ); 这部分的效率不高,是对整个表的全表扫描,可以改写只对有冗余数据的记录来rank() select id from ( select id,deptno,ename, rank() over(partition by deptno,ename order by id) seq from t ) where seq >1; 改写之后如下: select id from ( select id,deptno,ename, rank() over(partition by deptno,ename order by id) seq from (select id,deptno,ename from t where (deptno,ename) in(select deptno,ename from t group by deptno,ename having count(*) >1 ) ) ) where seq >1; 所以删除冗余数据的记录如下: delete from t where id in ( select id from ( select id,deptno,ename, rank() over(partition by deptno,ename order by id) seq from (select id,deptno,ename from t where (deptno,ename) in(select deptno,ename from t group by deptno,ename having count(*) >1 ) ) ) where seq >1 ); 效率比较: 数据量|处理方法 标准SQL 没有改写的RANK 改写后的RANK 50万(4.5万的冗余数据) 最少2个小时 77秒 47秒 其他特殊情况:如每个部门同一个名的人数不许超过3人:(典型的应用是:每个人只许购买3件特价商品或等等) delete from t where id in ( select id from ( select id,deptno,ename, rank() over(partition by deptno,ename order by id) seq from (select id,deptno,ename from t where (deptno,ename) in(select deptno,ename from t group by deptno,ename having count(*) >N ) ) ) where seq >N ); 只需把N改成对应的值就可以了. 十一.分析函数之使用注意事项 1. pl/sql中. 如果在816以前的版本,PL/SQL程序中使用分析函数必须以动态SQL语句的方法实现.在9i后不需要考虑了. 2. Where条件中的分析函数: select * from (select deptno,ename,sal, dense_rank() over (partition by deptno order by sal desc )dr from t ) where dr <=3 order by deptno,sal desc; where条件必须放在外面的嵌套中,也就是必须使用内联视图,这个是Oracle分析的公共操作. 3. Null与排序: 默认的情况,Null是最大的. select * from c order by sal desc; Null是最大的,在分组排序中也一样: select * from (select deptno,ename,sal, dense_rank() over (partition by deptno order by sal desc )dr from c ) where deptno=0 or deptno=10 or deptno=5 order by deptno,sal desc; 在Where条件中用 sal is not null条件去掉Null. select * from (select deptno,ename,sal, dense_rank() over (partition by deptno order by sal desc )dr from c where sal is not null ) where deptno=0 or deptno=10 or deptno=5 order by deptno,sal desc; 在Where条件中用sal is not null条件有限制.如果整个部门工资都是Null的情况下,就不实用了. 或者在Order by中使用nulls last扩展:(将Null排在最后,也就是Null当着最小值来处理) select * from (select deptno,ename,sal, dense_rank() over (partition by deptno order by sal desc nulls last )dr from c ) where deptno=0 or deptno=10 or deptno=5 order by deptno,dr; 对Null值的处理在order by 中有2个方法: Nulls last: Null值最小,排在最后. First last: Null最大,排在最前(缺省的情况). 常见的sql错误写法 一. 滥用函数 查询员工的部门名字。显示:员工号,员工姓名,部门号,部门名。 错误实现方法: create or replace function f_dname (ndeptno in dept.deptno%type) return varchar2 as vdname dept.dname%type; cursor c_s is select dname from dept where deptno=ndeptno; begin for c in c_s loop vdname:=c.dname; end loop; return vdname; end; / select empno,ename,deptno,f_dname(deptno) dname from emp; 上面正确的实现方法应该是用SQL语句实现: select a.empno,a.ename,a.deptno,b.dname from emp a, dept b where a.deptno=b.deptno; *经常在论坛中看到如 ‘能不能用一个SQL写出…..’的贴子,殊不知复杂的SQL往往牺牲了执行效率。 能够掌握上面的运用函数解决问题的方法在实际工作中是非常有意义的。 *用函数替代部分SQL语句之后,SQL语句由以前的集操作,变成了行操作。 *只有即少数复杂的sql语句通过在sql语句中嵌入函数后,性能得到了提高。大部分的SQL语句嵌入函数后,性能反而下降非常大。 *在SQL语句中能正确使用函数从而提高性能,是非常难的。 二.滥用in 查询发了有员工发了奖金的部门。显示:部门的详细信息。 错误实现方法: SQL> select * 2 from dept 3 where deptno in (select deptno 4 from emp 5 where empno in (select ename 6 from bonus 7 ) 8 ); 未选定行 已用时间: 00: 00: 00.00 SQL> 正确写法: SQL> select a.* 2 from dept a, 3 emp b, 4 bonus c 5 where a.deptno=b.deptno 6 and b.ename=c.ename; 未选定行 已用时间: 00: 00: 00.00 SQL> *查询非1对1的关系,则结果集需要distinct或者group by 去重。   *多层嵌套in会严重影响性能。我以前遇到过三层嵌套in后,查询的时间要3个多小时,用子查询改写后,查询的时间降到了10秒以内。 三.索引列上的隐式转换和计算 查询比工号7566小于100 的员工。显示:员工号,员工姓名。 错误实现方法: SQL> select empno,ename from emp where empno -100 >7566; EMPNO ENAME ---------- -------------------- 7839 KING 7698 BLAKE 7782 CLARK 7844 TURNER 7900 JAMES 7902 FORD 7788 SCOTT 7876 ADAMS 7934 MILLER 已选择9行。 已用时间: 00: 00: 00.00 Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=1 Bytes=10) 1 0 TABLE ACCESS (FULL) OF 'EMP' (Cost=2 Card=1 Bytes=10) 正确的实现方法: SQL> select empno,ename from emp where empno >7566+100; EMPNO ENAME ---------- -------------------- 7698 BLAKE 7782 CLARK 7788 SCOTT 7839 KING 7844 TURNER 7876 ADAMS 7900 JAMES 7902 FORD 7934 MILLER 已选择9行。 已用时间: 00: 00: 00.00 Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=7 Bytes=70) 1 0 TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (Cost=2 Card=7 Byte=70) 2 1 INDEX (RANGE SCAN) OF 'EMP_PRIMARY_KEY' (UNIQUE) (Cost=1Card=7) SQL> *当在索引列上执行计算或隐式转换时,索引会失效。这个是个经典的规则一定要记住。 *类似的错误还有: 索引列上的大小转换,时间转字符串,空值转换等等。只要索引列上存在计算或者隐式转换,一定不会再走索引,而走全表扫描。 *如果索引列上一定要存在计算或转换,可以创建函数索引来避免不走索引。 *如果需要强制索引失效,在索引列上添加计算,是非常好的处理方法。如:empno+0=7934。 四.Max,min问题 查询big_table表中id的最大值和最小值。 SQL> select max(id) from big_table; MAX(ID) ---------- 20000000 已用时间: 00: 00: 00.00 Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=1 Bytes=6) 1 0 SORT (AGGREGATE) 2 1 INDEX (FULL SCAN (MIN/MAX)) OF 'BIG_TABLE_PK' (UNIQUE) ( Cost=3 Card=20000000 Bytes=120000000) Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 3 consistent gets 3 physical reads 0 redo size 380 bytes sent via SQL*Net to client 503 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed SQL> select min(id) from big_table; MIN(ID) ---------- 1 已用时间: 00: 00: 00.00 Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=1 Bytes=6) 1 0 SORT (AGGREGATE) 2 1 INDEX (FULL SCAN (MIN/MAX)) OF 'BIG_TABLE_PK' (UNIQUE) ( Cost=3 Card=20000000 Bytes=120000000) Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 3 consistent gets 2 physical reads 0 redo size 380 bytes sent via SQL*Net to client 503 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed SQL> select max(id),min(id) from big_table; MAX(ID) MIN(ID) ---------- ---------- 20000000 1 已用时间: 00: 00: 12.03 Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=4272 Card=1 Bytes=6) 1 0 SORT (AGGREGATE) 2 1 INDEX (FAST FULL SCAN) OF 'BIG_TABLE_PK' (UNIQUE) (Cost= 4272 Card=20000000 Bytes=120000000) Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 44496 consistent gets 44468 physical reads 0 redo size 438 bytes sent via SQL*Net to client 503 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed *走的执行路径是快速索引全扫描。 SQL> select (select max(id) from big_table) max_id, 2 (select min(id) from big_table) min_id 3 from dual; MAX_ID MIN_ID ---------- ---------- 20000000 1 已用时间: 00: 00: 00.00 Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=11 Card=8168) 1 0 TABLE ACCESS (FULL) OF 'DUAL' (Cost=11 Card=8168) Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 10 consistent gets 4 physical reads 0 redo size 436 bytes sent via SQL*Net to client 503 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed SQL> 五.where中放入select …子查询 查询工号大于7600并且部门为SALES的员工信息 错误实现方法: select a.* from emp a where empno >7600 and (select dname from dept b where b.deptno=a.deptno )='SALES'; 正确的实现方法: select a.* from emp a, dept b where empno >7600 and b.deptno=a.deptno and b.dname='SALES'; 六.delete 全表删:除使用truncate 代替delete。 大数据量的删除:在满足业务的同时,可以采用分而治之的方法。即每次删除一小部分而后提交。 一般的语法是: Delete from table where…而且where后面一般是子查询。Delete 的优化除去上面的2部分之外,就是where后子查询的优化。如果子查询优化后,delete一般不会有什么问题。 七.滥用order by Order by存在的问题一般是以下2个: 1. 排序的字段过多。    假如有根据业务要求结果按M个字段排序。如果我们能确定前N个字段的结果是唯一的,那排序的时候只需要排序N个字段就可以取到相同的结果,而不需要排序M个字段。排序的字段越多对内存和cpu的资源消耗越多。 2. 不必要的排序。    严格按照业务需求做,要求排序的就排序,不要求排序的,一律不排序。 八.滥用distinct 1. 如果能确定不用distinct结果集也是唯一的,去掉distinct. 2. 如果能用半连接替代,就可以省掉distinct。也就是说,如果能改写SQL语句避免distinct,而性能不影响,尽量改写。 3.严格按照业务需求做,要求去重的就distinct,不要求去重的,一律不distinct。 九.滥用group by   和order by类似。 十.滥用外连接   前面已经讲过,当SQL采用外联接之后对优化器存在着种种限制。在理解需求后真正需要外联接的时候应该是非常少的。非必要的外联接一律去掉。这个问题我在以前查看开发人员的sql代码时也发现过,开发人员不管是什么sql语句,只要是感觉需要加上外联接的一律给他加上,不管性能如何,结果集如何。当发现这个问题之后,要求开发人员认真读懂需求后再更改了部分数据库设计,外联接去掉80%以上。性能提供了1个数量级。 十一.滥用COMMIT 先看一个滥用commit的procedure: create or replace procedure p_commit as begin for c in (select empno from emp) loop update emp set deptno=deptno+10 where empno=c.empno; commit; end loop; end; / *事务难以控制,造成数据不一致。如运行此存储过程的时候,程序突然中断,能知道上次更新到了那儿吗? *消耗更到的内存和cpu资源。   *消耗更多的拴锁资源,降到系统的并发性。初看似乎是降低了拴锁资源,其实不然。   *提交是个常量。 正确的写法应该是: create or replace procedure p_commit as begin for c in (select empno from emp) loop update emp set deptno=deptno+10 where empno=c.empno; end loop; commit; end; /   *在程序设计的时候一定要认真的考虑事务的划分。除了事务难以控制外,甚至造成死锁。 讨论:连接表过多的写法(如连接的表超过20个) 1.数据库表结构: 2.A,B,C之间的关系: 3.表属性说明: A. A表: a 1~a8的属性可以从对应的表A1~A8表中查到,关系是1――1。如:国家,地区,邮编等等类似的属性。 A9~a10的属性可以从对应的表A9~A10表中查到。关系是1――N。如:干系人,一般有项目经理,财务经理,副项目经理。  B.AB表:    是A,B表的关联表。  C.B表: b 1~b8的属性可以从对应的表b1~b8表中查到,关系是1――1。如:国家,地区,邮编等等类似的属性。 b9~b10的属性可以从对应的表b9~b10表中查到。关系是1――N。如:干系人,一般有项目经理,财务经理,副项目经理。 D.C表: c1~c8的属性可以从对应的表c1~c8表中查到,关系是1――1。如:国家,地区,邮编等等类似的属性。 c9~c10的属性可以从对应的表c9~c10表中查到。关系是1――N。如:干系人,一般有项目经理,财务经理,副项目经理。 4.输出的查询列要求: 说明: *可以单选任何一个表。 *选取多表的时候,除了选A必须选B外,其他的情况都允许。如选择ABC,AB,BC都可以。但是不允许只选择AC表中的列,而B中的列不选。 *字段的个数任意选取。 5.这个SQL语句要怎样写,在满足上面要求的同时,便于维护,便于修改,同时能满足性能的要求? 注意:A,B,C表的列可能随时会变动.

下载文档到电脑,查找使用更方便

文档的实际排版效果,会与网站的显示效果略有不同!!

需要 10 金币 [ 分享文档获得金币 ] 0 人已下载

下载文档