查看: 2956|回复: 11

1条SQL的优化

[复制链接]
论坛徽章:
14
季节之章:冬
日期:2010-01-04 13:39:56季节之章:春
日期:2010-03-22 16:42:29ITPUB知识分享者
日期:2010-06-28 10:02:43季节之章:夏
日期:2010-07-16 09:20:442010世博会纪念徽章
日期:2010-07-22 11:35:022010世博会纪念徽章
日期:2010-08-09 10:41:19ITPUB季度 技术新星
日期:2010-08-31 10:47:25季节之章:秋
日期:2010-12-10 16:23:30ITPUB官方微博粉丝徽章
日期:2011-07-20 17:06:48
发表于 2010-8-11 15:03 | 显示全部楼层 |阅读模式
Test Code:

CREATE TABLE a
(
col01 VARCHAR2(10),
col02 NUMBER
);

INSERT INTO a
SELECT 'a',1 FROM dual;

INSERT INTO a
SELECT 'a',2 FROM dual;

INSERT INTO a
SELECT 'b',1 FROM dual;

INSERT INTO a
SELECT 'c',1 FROM dual;

INSERT INTO a
SELECT 'd',2 FROM dual;

INSERT INTO a
SELECT 'c',2 FROM dual;

INSERT INTO a
SELECT 'f',2 FROM dual;

commit;

SELECT * FROM a;

不知道如下语句是否还有其他更好的写法:
SELECT * from a
WHERE col02=1 AND col01 NOT IN (SELECT col01 from a WHERE col02<>1);
论坛徽章:
14
季节之章:冬
日期:2010-01-04 13:39:56季节之章:春
日期:2010-03-22 16:42:29ITPUB知识分享者
日期:2010-06-28 10:02:43季节之章:夏
日期:2010-07-16 09:20:442010世博会纪念徽章
日期:2010-07-22 11:35:022010世博会纪念徽章
日期:2010-08-09 10:41:19ITPUB季度 技术新星
日期:2010-08-31 10:47:25季节之章:秋
日期:2010-12-10 16:23:30ITPUB官方微博粉丝徽章
日期:2011-07-20 17:06:48
 楼主| 发表于 2010-8-11 15:09 | 显示全部楼层
结果只要:
           COL01        COL02
               b        1

使用道具 举报

回复
招聘 : Java研发
认证徽章
论坛徽章:
71
马上加薪
日期:2014-02-19 11:55:14蜘蛛蛋
日期:2012-12-26 18:16:01茶鸡蛋
日期:2012-11-16 08:12:48ITPUB 11周年纪念徽章
日期:2012-10-09 18:05:07奥运会纪念徽章:网球
日期:2012-08-23 14:58:08奥运会纪念徽章:沙滩排球
日期:2012-07-19 17:28:14版主2段
日期:2012-07-07 02:21:02咸鸭蛋
日期:2012-03-23 18:17:482012新春纪念徽章
日期:2012-02-13 15:13:512012新春纪念徽章
日期:2012-02-13 15:13:51
发表于 2010-8-11 15:14 | 显示全部楼层
另一种写法:
SELECT a1.*
from a a1,a a2
WHERE a1.col02=1 AND
a1.col01=a2.col01(+)
and a2.col02(+)<>1
and a2.col01 is null

使用道具 举报

回复
论坛徽章:
14
季节之章:冬
日期:2010-01-04 13:39:56季节之章:春
日期:2010-03-22 16:42:29ITPUB知识分享者
日期:2010-06-28 10:02:43季节之章:夏
日期:2010-07-16 09:20:442010世博会纪念徽章
日期:2010-07-22 11:35:022010世博会纪念徽章
日期:2010-08-09 10:41:19ITPUB季度 技术新星
日期:2010-08-31 10:47:25季节之章:秋
日期:2010-12-10 16:23:30ITPUB官方微博粉丝徽章
日期:2011-07-20 17:06:48
 楼主| 发表于 2010-8-11 15:19 | 显示全部楼层
SQL> SELECT * from a
  2  WHERE col02=1 AND col01 NOT IN (SELECT col01 from a WHERE col02<>1);

COL01           COL02
---------- ----------
b                   1


?′DD????
----------------------------------------------------------
Plan hash value: 3505017636

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    20 |     5   (0)| 00:00:01 |
|*  1 |  FILTER            |      |       |       |            |          |
|*  2 |   TABLE ACCESS FULL| A    |     3 |    60 |     3   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| A    |     4 |    80 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter( NOT EXISTS (SELECT /*+ */ 0 FROM "A" "A" WHERE
              "COL02"<>1 AND LNNVL("COL01"<>:B1)))
   2 - filter("COL02"=1)
   3 - filter("COL02"<>1 AND LNNVL("COL01"<>:B1))

Note
-----
   - dynamic sampling used for this statement


----------------------------------------------------------
          0  recursive calls
          0  db block gets
         29  consistent gets
          0  physical reads
          0  redo size
        233  bytes sent via SQL*Net to client
        239  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 a1.*
  2  from a a1,a a2
  3  WHERE a1.col02=1 AND
  4  a1.col01=a2.col01(+)
  5  and a2.col02(+)<>1
  6  and a2.col01 is NULL;

COL01           COL02
---------- ----------
b                   1


----------------------------------------------------------
Plan hash value: 2703851244

----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |     3 |   120 |     7  (15)| 00:00:01 |
|*  1 |  FILTER             |      |       |       |            |          |
|*  2 |   HASH JOIN OUTER   |      |     3 |   120 |     7  (15)| 00:00:01 |
|*  3 |    TABLE ACCESS FULL| A    |     3 |    60 |     3   (0)| 00:00:01 |
|*  4 |    TABLE ACCESS FULL| A    |     4 |    80 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("A2"."COL01" IS NULL)
   2 - access("A1"."COL01"="A2"."COL01"(+))
   3 - filter("A1"."COL02"=1)
   4 - filter("A2"."COL02"(+)<>1)

Note
-----
   - dynamic sampling used for this statement


í3??D??¢
----------------------------------------------------------
          7  recursive calls
          0  db block gets
         30  consistent gets
          0  physical reads
          0  redo size
        250  bytes sent via SQL*Net to client
        239  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
          1  rows processed

使用道具 举报

回复
论坛徽章:
14
季节之章:冬
日期:2010-01-04 13:39:56季节之章:春
日期:2010-03-22 16:42:29ITPUB知识分享者
日期:2010-06-28 10:02:43季节之章:夏
日期:2010-07-16 09:20:442010世博会纪念徽章
日期:2010-07-22 11:35:022010世博会纪念徽章
日期:2010-08-09 10:41:19ITPUB季度 技术新星
日期:2010-08-31 10:47:25季节之章:秋
日期:2010-12-10 16:23:30ITPUB官方微博粉丝徽章
日期:2011-07-20 17:06:48
 楼主| 发表于 2010-8-11 15:20 | 显示全部楼层
都有2次TABLE ACCESS FULL

使用道具 举报

回复
论坛徽章:
14
季节之章:冬
日期:2010-01-04 13:39:56季节之章:春
日期:2010-03-22 16:42:29ITPUB知识分享者
日期:2010-06-28 10:02:43季节之章:夏
日期:2010-07-16 09:20:442010世博会纪念徽章
日期:2010-07-22 11:35:022010世博会纪念徽章
日期:2010-08-09 10:41:19ITPUB季度 技术新星
日期:2010-08-31 10:47:25季节之章:秋
日期:2010-12-10 16:23:30ITPUB官方微博粉丝徽章
日期:2011-07-20 17:06:48
 楼主| 发表于 2010-8-11 15:24 | 显示全部楼层
有什么分析函数可以实现么?

使用道具 举报

回复
招聘 : Java研发
认证徽章
论坛徽章:
71
马上加薪
日期:2014-02-19 11:55:14蜘蛛蛋
日期:2012-12-26 18:16:01茶鸡蛋
日期:2012-11-16 08:12:48ITPUB 11周年纪念徽章
日期:2012-10-09 18:05:07奥运会纪念徽章:网球
日期:2012-08-23 14:58:08奥运会纪念徽章:沙滩排球
日期:2012-07-19 17:28:14版主2段
日期:2012-07-07 02:21:02咸鸭蛋
日期:2012-03-23 18:17:482012新春纪念徽章
日期:2012-02-13 15:13:512012新春纪念徽章
日期:2012-02-13 15:13:51
发表于 2010-8-11 16:31 | 显示全部楼层
原帖由 MicroJoey 于 2010-8-11 15:20 发表
都有2次TABLE ACCESS FULL

有真实数据吗,拿真实数据执行一下看看区别

从你的sql来看,下面的的sql应该也能满足你的需求

select b.col01,b.col02 from (
select a.col01,a.col02,count(1) over (partition by col01 ) ac
from a  ) b where b.ac=1 and b.col02=&n

但是在某些数据分布情况下,上面的sql效率未必会更好

[ 本帖最后由 anlinew 于 2010-8-11 16:33 编辑 ]

使用道具 举报

回复
论坛徽章:
14
季节之章:冬
日期:2010-01-04 13:39:56季节之章:春
日期:2010-03-22 16:42:29ITPUB知识分享者
日期:2010-06-28 10:02:43季节之章:夏
日期:2010-07-16 09:20:442010世博会纪念徽章
日期:2010-07-22 11:35:022010世博会纪念徽章
日期:2010-08-09 10:41:19ITPUB季度 技术新星
日期:2010-08-31 10:47:25季节之章:秋
日期:2010-12-10 16:23:30ITPUB官方微博粉丝徽章
日期:2011-07-20 17:06:48
 楼主| 发表于 2010-8-11 16:44 | 显示全部楼层
原帖由 anlinew 于 2010-8-11 16:31 发表

有真实数据吗,拿真实数据执行一下看看区别

从你的sql来看,下面的的sql应该也能满足你的需求

select b.col01,b.col02 from (
select a.col01,a.col02,count(1) over (partition by col01 ) ac
from a  ) b where b.ac=1 and b.col02=&n

但是在某些数据分布情况下,上面的sql效率未必会更好





谢谢老大,这是这样的SQL!

使用道具 举报

回复
招聘 : Java研发
认证徽章
论坛徽章:
71
马上加薪
日期:2014-02-19 11:55:14蜘蛛蛋
日期:2012-12-26 18:16:01茶鸡蛋
日期:2012-11-16 08:12:48ITPUB 11周年纪念徽章
日期:2012-10-09 18:05:07奥运会纪念徽章:网球
日期:2012-08-23 14:58:08奥运会纪念徽章:沙滩排球
日期:2012-07-19 17:28:14版主2段
日期:2012-07-07 02:21:02咸鸭蛋
日期:2012-03-23 18:17:482012新春纪念徽章
日期:2012-02-13 15:13:512012新春纪念徽章
日期:2012-02-13 15:13:51
发表于 2010-8-11 16:53 | 显示全部楼层
真实数据
col01,col02都有索引的情况下,对比一下吧

使用道具 举报

回复
论坛徽章:
14
季节之章:冬
日期:2010-01-04 13:39:56季节之章:春
日期:2010-03-22 16:42:29ITPUB知识分享者
日期:2010-06-28 10:02:43季节之章:夏
日期:2010-07-16 09:20:442010世博会纪念徽章
日期:2010-07-22 11:35:022010世博会纪念徽章
日期:2010-08-09 10:41:19ITPUB季度 技术新星
日期:2010-08-31 10:47:25季节之章:秋
日期:2010-12-10 16:23:30ITPUB官方微博粉丝徽章
日期:2011-07-20 17:06:48
 楼主| 发表于 2010-8-11 17:13 | 显示全部楼层

回复 #9 anlinew 的帖子

测试正在修改SQL

使用道具 举报

回复

您需要登录后才可以回帖 登录 | 注册

本版积分规则 发表回复

SACC2019中国系统架构师大会

【数字转型 架构演进】SACC2019中国系统架构师大会,7折限时优惠重磅来袭!
2019年10月31日~11月2日第11届中国系统架构师大会(SACC2019)将在北京隆重召开。四大主线并行的演讲模式,1个主会场、20个技术专场、超千人参与的会议规模,100+来自互联网、金融、制造业、电商等领域的嘉宾阵容,将为广大参会者提供一场最具价值的技术交流盛会。

限时七折期:2019年8月31日前


----------------------------------------

大会官网>>
TOP技术积分榜 社区积分榜 徽章 团队 统计 知识索引树 积分竞拍 文本模式 帮助
  ITPUB首页 | ITPUB论坛 | 数据库技术 | 企业信息化 | 开发技术 | 微软技术 | 软件工程与项目管理 | IBM技术园地 | 行业纵向讨论 | IT招聘 | IT文档
  ChinaUnix | ChinaUnix博客 | ChinaUnix论坛
CopyRight 1999-2011 itpub.net All Right Reserved. 北京盛拓优讯信息技术有限公司版权所有 联系我们 
京ICP备09055130号-4  北京市公安局海淀分局网监中心备案编号:11010802021510 广播电视节目制作经营许可证:编号(京)字第1149号
  
快速回复 返回顶部 返回列表