ITPub博客

今天在测试一个SQL的时候发现，居然使用UNION要比使用UNION ALL的效率高。具体SQL语句如下：SQL> select count(*) 2 from 3 ( 4 select a.id, c.plat_name, a.plat_id, a.substitute_flag 5 from cat_auth_price a, plt_plat c 6 where a.id in 7 ( 8 (9 select d12.price_id 10 from cat_auth_price_drug12 d12,cat_drug d 11 where d12.drug_id=d.id and d.name_chn like '%'12 ) 13 union all14 (15 select d20.price_id from cat_auth_price_drug20 d20 ,cat_drug d 16 where d20.drug_id=d.id and d.name_chn like '%'17 ) 18 ) 1

在一般的优化思想中，都是要尽可能减少嵌套的层数，减少不必要的操作。但是今天发现有的时候并非实际情况却并非如此。看下面的例子：SQL> CREATE TABLE T AS SELECT * FROM DBA_OBJECTS;表已创建。SQL> INSERT INTO T SELECT * FROM T;已创建32774行。SQL> INSERT INTO T SELECT * FROM T;已创建65548行。SQL> INSERT INTO T SELECT * FROM T;已创建131096行。SQL> INSERT INTO T SELECT * FROM T;已创建262192行。SQL> COMMIT;提交完成。SQL> SET AUTOT TRACESQL> SET TIMING ONSQL> COL PLAN_PLUS_EXP FORMA

只有增加DISTINCT关键字，Oracle必然需要对后面的所有字段进行排序。以前也经常发现由于开发人员对SQL不是很理解，在SELECT列表的20多个字段前面添加了DISTINCT，造成查询基本上不可能执行完成，甚至产生ORA-7445错误。所以一直向开发人员强调DISTINCT给性能带来的影响。没想到开发人员在测试一条大的SQL的时候，告诉我如果加上了DISTINCT，则查询大概需要4分钟左右可以执行完，如果不加DISTINCT，则查询执行了10多分钟，仍然得不到结果。首先想到的是可能DISTINCT是在子查询中，由于加上了DISTINCT，将第一步结果集缩小了，导致查询性能提

大家都对删除对象比较敏感，对增加对象一般会放松警惕。而增加索引所带来的危害和删除索引同样大。由于添加了一个新的索引，使得以前运行正常的SQL错误的选择了新建索引，导致SQL运行时间成倍的增长，从而使整个数据库无法相应。这种现象已经屡见不鲜了。所以说增加一个索引改变SQL的执行计划，这并不奇怪。如果增加了索引之后，相关查询的执行计划没有改变，那么增加索引的意义何在。但是奇怪的是，当增加一个索引时，查询的执行计划发生了变化，但是执行计划确并没有使用新增的索引。首先建立模拟环境：SQL> CREATE TABLE T1 AS SELE

有的时候开发人员为了方便会在WHERE语句后面添加一个1=1，这样在处理页面传入的条件是就可用不用判断直接添加AND 条件。一直认为添加一个恒等条件，不会对Oracle的查询造成什么影响，最多不过影响一下Oracle的性能，但是今天突然发现，这个恒等的查询条件居然可以影响Oracle的执行计划。首先看一个简单的例子：SQL> CREATE TABLE T1 AS SELECT * FROM DBA_OBJECTS 2 WHERE OWNER = 'SYS' 3 AND OBJECT_TYPE NOT LIKE '%BODY'4 AND OBJECT_TYPE NOT LIKE 'JAVA%';Table created.SQL> CREATE TABLE T2 AS SELECT * FROM DBA_SEGMENTS

很多执行计划本身就是需要排序的，通过特定的执行计划可以避免排序的产生。前两天在论坛中看到类似的问题。这里简单总结一下。利用执行计划来避免排序操作（一）：http://yangtingkun.itpub.net/post/468/242836上面讨论了单表查询如何利用执行计划来避免排序，这篇文章简单一下包含连接的查询如何通过执行计划来避免排序。MERGE JOIN连接方式由于是先排序后连接，因此查询的结果本是就是包含排序的。SQL> CREATE TABLE T1 (ID NUMBER PRIMARY KEY, NAME VARCHAR2(30) NOT NULL);表已创建。SQL> CREATE TABLE T2 (ID NUMBER, NAME V

很多执行计划本身就是需要排序的，通过特定的执行计划可以避免排序的产生。前两天在论坛中看到类似的问题。这里简单总结一下。利用执行计划来避免排序操作（二）：http://yangtingkun.itpub.net/post/468/243463索引全扫描可以避免排序，相信大部分人都很清楚：SQL> CREATE TABLE T_NOSORT (ID NUMBER PRIMARY KEY, NAME VARCHAR2(30) NOT NULL);表已创建。SQL> CREATE INDEX IND_T_NOSORT_NAME ON T_NOSORT(NAME);索引已创建。SQL> INSERT INTO T_NOSORT SELECT ROWNUM, TABLE_NAME FROM USER_TABLES;已创建15行。SQL> COMMI

今天和同事讨论了一下索引扫描避免排序的问题，感觉比较有意思，就简单总结一下。首先要强调的是，这并不是标准的或者推荐的一种分页语句的写法，这种方法需要对表、索引的结构有清晰的认识。而且这种方法的限制条件很多。因此，这里只是单独讨论一下，没用将其放到分页专题中去。下面是分页标准写法和利用HINT的方式的对比：SQL> CREATE TABLE T (ID NUMBER PRIMARY KEY, NAME VARCHAR2(30) NOT NULL);表已创建。SQL> INSERT INTO T SELECT ROWNUM, OBJECT_NAME FROM DBA_OBJECTS;已创建50418行。SQL> CREATE INDEX IND_T_NAME

今天在进行查询一个SQL的执行计划的时候，发现查询的SQL效率比较低，于是就优化了一下。原始SQL如下：SQL> set timing onSQL> set autot traceSQL> select * from v$sql_plan where hash_value in2 (select hash_value from v$sql where sql_text like '%惠氏%where cm.molecule_orgid = c.id%');14 rows selected.Elapsed: 00:00:50.64Execution Plan----------------------------------------------------------0 SELECT STATEMENT Optimizer=CHOOSE1 0 MERGE JOIN2 1 SORT (JOIN)3 2 NESTED LOOPS (OUTER)4 3 NESTED LOOPS (O

在第一篇文章中介绍过了，当对全表扫描执行ORDER BY STOPKEY操作时，如果排序列是不唯一的，那么排序结果是不稳定的。正好PUB上有个帖子在讨论这个问题，这里就简单描述一下对Oracle排序的一点研究。PUB上的原贴：http://www.itpub.net/showthread.php?s=&threadid=850977分页查询的排序问题：http://yangtingkun.itpub.net/post/468/112274为了描述问题，首先要再现问题。为了更好的说明问题，在下面的例子中，排序列仅包括两个不同的值。SQL> CREATE TABLE T AS SELECT ROWNUM ID, A.* FROM DBA_OBJECTS A;表已创建。SQL> SEL

前两天刚刚看完Tom的EFFECTIVE ORACLE BY DESIGN的第八章，从里面学到不是东西。同时也发现一个问题：Tom给出的关于分页以及ROWNUM的时候，几乎包含了我前几篇关于分页文章中的所有关键点。于是，打算补充一篇关于分页排序问题的文章，这篇文章中的内容是Tom书中没有的。SQL> CREATE TABLE TEST AS SELECT ROWNUM ID, A.* FROM DBA_OBJECTS A;表已创建。SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'TEST')PL/SQL 过程已成功完成。SQL> SET AUTOT ON EXPSQL> SELECT ID, OBJECT_NAME, OWNER2 FROM 3 (4 SELECT ROWNUM RN,

Oracle的分页查询语句基本上可以按照本文给出的格式来进行套用。这篇介绍HASH SORT CLUSTER表对分页查询的帮助。Oracle分页查询格式（一）：http://yangtingkun.itpub.net/post/468/100278Oracle分页查询格式（二）：http://yangtingkun.itpub.net/post/468/101703Oracle分页查询格式（三）：http://yangtingkun.itpub.net/post/468/104595Oracle分页查询格式（四）：http://yangtingkun.itpub.net/post/468/104867Oracle分页查询格式（五）：http://yangtingkun.itpub.net/post/468/107934Oracle分页查询格式（六）：http://yangtingkun.

Oracle的分页查询语句基本上可以按照本文给出的格式来进行套用。这篇介绍分析函数用于分页，且排序字段上包含索引的情况。Oracle分页查询格式（一）：http://yangtingkun.itpub.net/post/468/100278Oracle分页查询格式（二）：http://yangtingkun.itpub.net/post/468/101703Oracle分页查询格式（三）：http://yangtingkun.itpub.net/post/468/104595Oracle分页查询格式（四）：http://yangtingkun.itpub.net/post/468/104867Oracle分页查询格式（五）：http://yangtingkun.itpub.net/post/468/107934Oracle分页查询格式（六）：http://yan

Oracle的分页查询语句基本上可以按照本文给出的格式来进行套用。这篇介绍分析函数用于分页。Oracle分页查询格式（一）：http://yangtingkun.itpub.net/post/468/100278Oracle分页查询格式（二）：http://yangtingkun.itpub.net/post/468/101703Oracle分页查询格式（三）：http://yangtingkun.itpub.net/post/468/104595Oracle分页查询格式（四）：http://yangtingkun.itpub.net/post/468/104867Oracle分页查询格式（五）：http://yangtingkun.itpub.net/post/468/107934Oracle分页查询格式（六）：http://yangtingkun.itpub.net/post/468/

Oracle10g的新功能GROUP BY STOPKEY，使得Oracle10g解决了上一篇文章中提到的GROUP BY操作无法分页的问题。在10g以前，Oracle的GROUP BY操作必须完全执行完，才能将结果返回给用户。但是Oracle10g增加了GROUP BY STOPKEY执行路径，使得用户在执行GROUP BY操作时，可以根据STOPKEY随时中止正在运行的操作。这使得标准分页函数对于GROUP BY操作重新发挥了作用。SQL> CREATE TABLE T AS SELECT ROWNUM ID, A.* FROM DBA_OBJECTS A;表已创建。SQL> CREATE INDEX IND_T_CREATED ON T (CREATED);索引已创建。SQL> ALTER TABLE T MODIFY

本文简单讨论一下包含GROUP BY、DISTINCT、UNIQUE等操作的分页查询语句。由于包含上述的集操作，Oracle必须处理完所有的数据才会将结果全部的提交给用户。即使包含了ROWNUM，Oracle也只是在处理完所有的数据之后，将需要的数据进行返回。不过并不是说前面提到的标准写法在这里没有意义，而是最耗时的部分已经处理完毕。通过ROWNUM来限制意义不大。虽然标准分页的写法对于GROUP BY之类的操作意义不大，但是如果在执行分页之前需要计算总数的话，那么可以对分页的查询语句稍做修改，将计算总数和查询第一页的SQL结合起来，避免一次计算总数的

前面提到了对于集操作和聚集操作，使用标准的分页函数没有太大的意义，下面通过几篇文章专门讨论集操作和聚集操作的情况。这里首先讨论集操作的情况。当查询需要分页时，大多数情况都会包含排序操作，因为如果缺少排序操作，很难保证分页返回的数据是连续不重复的。因此这里只考虑包含排序的情况：SQL> CREATE TABLE T AS SELECT * FROM DBA_TABLES;表已创建。SQL> CREATE TABLE T1 AS SELECT * FROM DBA_INDEXES;表已创建。SQL> CREATE INDEX IND_T_OWNER ON T(OWNER);索引已创建。SQL> CREATE INDEX IND_T1_OWNER ON T1(OWN

在文章的最后看一下ORDER BY STOPKEY和ORDER BY在翻页查询的最后几页的性能差异：SQL> CREATE TABLE T AS SELECT A.* FROM DBA_OBJECTS A, DBA_USERS B, TAB;表已创建。SQL> SELECT COUNT(*) FROM T;COUNT(*)----------458064SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'T')PL/SQL 过程已成功完成。SQL> SET AUTOT TRACESQL> SET TIMING ONSQL> SELECT OBJECT_ID, OBJECT_NAME2 FROM 3 (4 SELECT ROWNUM RN, OBJECT_ID, OBJECT_NAME5 FROM 6 (7 SELECT OBJECT_ID, OBJECT_NAME FROM T ORDER BY TIMESTAMP8 )9 WHER

这篇文章通过例子说明分页查询使用的NESTED LOOP操作，在分页查询翻到最后几页时的性能问题：SQL> CREATE TABLE T AS SELECT * FROM DBA_USERS;表已创建。SQL> CREATE TABLE T1 AS SELECT * FROM DBA_SOURCE;表已创建。SQL> ALTER TABLE T ADD CONSTRAINT PK_T PRIMARY KEY (USERNAME);表已更改。SQL> ALTER TABLE T1 ADD CONSTRAINT FK_T1_OWNER FOREIGN KEY (OWNER)2 REFERENCES T(USERNAME);表已更改。SQL> CREATE INDEX IND_T1_OWNER ON T1(NAME);索引已创建。SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'T')P

前面的各种例子已经说明了分页查询语句的标准写法所带来的性能提升。这里简单总结一下，并简单的说明分页查询语句在何时无法带来性能提升。分页查询语句之所以可以很快的返回结果，是因为它的目标是最快的返回第一条结果。如果每页有20条记录，目前翻到第5页，那么只需要返回前100条记录都可以满足查询的要求了，也许还有几万条记录也符合查询的条件，但是由于分页的限制，在当前的查询中可以忽略这些数据，而只需尽快的返回前100条数据。这也是为什么在标准分页查询语句中经常会使用FIRST_ROWS提示的原因。对于行操作，可以在得到结果的同