ITPUB论坛-中国最专业的IT技术社区

 
 注册
热搜:
查看: 234|回复: 10

[SQL] sql join 条件与性能

[复制链接]
论坛徽章:
10
2010新春纪念徽章
日期:2010-03-01 11:04:59喜羊羊
日期:2015-03-04 14:52:46优秀写手
日期:2014-04-22 06:00:18马上有对象
日期:2014-02-18 16:44:082014年新春福章
日期:2014-02-18 16:44:082013年新春福章
日期:2013-02-25 14:51:24ITPUB十周年纪念徽章
日期:2011-11-01 16:25:512012新春纪念徽章
日期:2012-01-04 11:56:012011新春纪念徽章
日期:2011-02-18 11:42:472015年新春福章
日期:2015-03-06 11:58:18
跳转到指定楼层
1#
发表于 2018-1-10 04:20 | 只看该作者 回帖奖励 |倒序浏览 |阅读模式
一个sql 连接条件如下:
SELECT A.*
FROM TABLEA A, B
WHERE CASE WHEN A.CODE IN ('ABC')
                                  THEN
                                     TRIM (A.ACCT_CHAR)
                                  ELSE
                                     LTRIM (
                                        TRIM (A.ACCT_CHAR),
                                        0)
                               END = B.ACCT_NMBR(+)
问题是:如果把case statement 写到select 语句里,然后join, 性能会不会好,
改写成下面:
SELECT A.*
FROM (SELECT A.*, CASE WHEN A.CODE IN ('ABC')
                                  THEN
                                     TRIM (A.ACCT_CHAR)
                                  ELSE
                                     LTRIM (
                                        TRIM (A.ACCT_CHAR),
                                        0)
                               END AS ACCT_CHAR FROM TABLE_A) A, B
WHERE A.ACCT_CHAR = B.ACCT_NMBR(+)


论坛徽章:
486
秀才
日期:2015-09-09 10:33:01秀才
日期:2015-11-23 10:03:12秀才
日期:2015-11-23 10:03:12秀才
日期:2015-11-23 10:03:12状元
日期:2015-11-23 10:04:09举人
日期:2015-11-23 10:04:09秀才
日期:2016-01-06 14:10:21秀才
日期:2016-01-06 14:10:21秀才
日期:2016-01-06 14:10:21秀才
日期:2016-01-06 14:10:21
2#
发表于 2018-1-10 05:17 | 只看该作者
应该是一样的。你有数据,有环境,为什么不自己试试?

使用道具 举报

回复
论坛徽章:
10
2010新春纪念徽章
日期:2010-03-01 11:04:59喜羊羊
日期:2015-03-04 14:52:46优秀写手
日期:2014-04-22 06:00:18马上有对象
日期:2014-02-18 16:44:082014年新春福章
日期:2014-02-18 16:44:082013年新春福章
日期:2013-02-25 14:51:24ITPUB十周年纪念徽章
日期:2011-11-01 16:25:512012新春纪念徽章
日期:2012-01-04 11:56:012011新春纪念徽章
日期:2011-02-18 11:42:472015年新春福章
日期:2015-03-06 11:58:18
3#
 楼主| 发表于 2018-1-11 02:27 | 只看该作者
newkid 发表于 2018-1-10 05:17
应该是一样的。你有数据,有环境,为什么不自己试试?

谢谢,这是一个existing query,query写的很复杂, run的时间非常长。
另外,有一个问题想请教一下:我在这个query加上hint use_hash, query runs 不到一分钟。没有hint, query runs 一晚上也出不来结果。好像是hang在什么地方了。问dba, dba说一切正常。
各个表的信息也收集了。没有hint, query就hang在那里超过12小时没反应。

使用道具 举报

回复
论坛徽章:
486
秀才
日期:2015-09-09 10:33:01秀才
日期:2015-11-23 10:03:12秀才
日期:2015-11-23 10:03:12秀才
日期:2015-11-23 10:03:12状元
日期:2015-11-23 10:04:09举人
日期:2015-11-23 10:04:09秀才
日期:2016-01-06 14:10:21秀才
日期:2016-01-06 14:10:21秀才
日期:2016-01-06 14:10:21秀才
日期:2016-01-06 14:10:21
4#
发表于 2018-1-11 04:24 | 只看该作者
你可以查看 V$SESSION 挂在哪里,是不是被阻塞,再查看sql_id, sql_child_number对应的执行计划是不是和你 use_hash 一样。

使用道具 举报

回复
论坛徽章:
10
2010新春纪念徽章
日期:2010-03-01 11:04:59喜羊羊
日期:2015-03-04 14:52:46优秀写手
日期:2014-04-22 06:00:18马上有对象
日期:2014-02-18 16:44:082014年新春福章
日期:2014-02-18 16:44:082013年新春福章
日期:2013-02-25 14:51:24ITPUB十周年纪念徽章
日期:2011-11-01 16:25:512012新春纪念徽章
日期:2012-01-04 11:56:012011新春纪念徽章
日期:2011-02-18 11:42:472015年新春福章
日期:2015-03-06 11:58:18
5#
 楼主| 发表于 2018-1-11 10:38 | 只看该作者
本帖最后由 KEN6503 于 2018-1-11 12:14 编辑
newkid 发表于 2018-1-11 04:24
你可以查看 V$SESSION 挂在哪里,是不是被阻塞,再查看sql_id, sql_child_number对应的执行计划是不是和你  ...

谢谢。我用v$session_longops 的到下面的信息:
query在scan这个表的时候特别慢,这是一个分区表,执行full table scan。 一般造成扫描慢的原因是什么呢?
TIME_REMAINING        ELAPSED_SECONDS        MESSAGE
3722871        59379        Table Scan:  SCHEMA_NAME.TABLE_NAME: 3944 out of 251220 Blocks done

25 |         NESTED LOOPS OUTER                     |                                |   202K|    11M| 30763  (10)| 00:02:14 |       |       |
|  26 |          MERGE JOIN OUTER                      |                                |   202K|  7898K| 30758  (10)| 00:02:14 |       |       |
|  27 |           PARTITION RANGE SINGLE               |                                |   202K|  5726K| 30754  (10)| 00:02:14 |   KEY |   KEY |
|  28 |            PARTITION LIST SINGLE               |                                |   202K|  5726K| 30754  (10)| 00:02:14 |       |       |
|* 29 |             TABLE ACCESS FULL                  | TABLE_NAME               |   202K|  5726K| 30754  (10)| 00:02:14 |   KEY |   KEY |
|  30 |              VIEW                              |                                |     1 |     9 |     2   (0)| 00:00:01 |       |       |
|  31 |               TABLE ACCESS FULL                | SYS_TEMP_0FD9FE0A3_882B2F5F    |     1 |    34 |     2   (0)| 00:00:01 |       |       |

--这个搞明白了。这个表要和另外一个inline view (join 4 个表, 结果大概有200万) 作nested loop join, 所以,scan 的很慢。

使用道具 举报

回复
论坛徽章:
78
生肖徽章2007版:牛
日期:2012-08-02 22:43:00紫蛋头
日期:2012-12-08 09:43:38鲜花蛋
日期:2012-11-17 12:02:07鲜花蛋
日期:2013-02-05 21:53:34复活蛋
日期:2012-11-17 12:02:07SQL极客
日期:2013-12-09 14:13:35SQL数据库编程大师
日期:2013-12-06 13:59:43SQL大赛参与纪念
日期:2013-12-06 14:10:50ITPUB季度 技术新星
日期:2012-11-27 10:16:10最佳人气徽章
日期:2013-03-19 17:24:25
6#
发表于 2018-1-11 11:18 | 只看该作者
论坛几大从不进步,您和那个什么lik什么star的也是一时瑜亮了。。。

使用道具 举报

回复
论坛徽章:
10
2010新春纪念徽章
日期:2010-03-01 11:04:59喜羊羊
日期:2015-03-04 14:52:46优秀写手
日期:2014-04-22 06:00:18马上有对象
日期:2014-02-18 16:44:082014年新春福章
日期:2014-02-18 16:44:082013年新春福章
日期:2013-02-25 14:51:24ITPUB十周年纪念徽章
日期:2011-11-01 16:25:512012新春纪念徽章
日期:2012-01-04 11:56:012011新春纪念徽章
日期:2011-02-18 11:42:472015年新春福章
日期:2015-03-06 11:58:18
7#
 楼主| 发表于 2018-1-11 12:08 | 只看该作者
本帖最后由 KEN6503 于 2018-1-11 12:12 编辑
udfrog 发表于 2018-1-11 11:18
论坛几大从不进步,您和那个什么lik什么star的也是一时瑜亮了。。。

谢谢。想进步,可就是脑袋不灵。
我也是慢慢进步滴,上面那个问题我好像是高明白了。

使用道具 举报

回复
认证徽章
论坛徽章:
8
2009新春纪念徽章
日期:2009-01-04 14:52:28祖国60周年纪念徽章
日期:2009-10-09 08:28:002010新春纪念徽章
日期:2010-03-01 11:07:24ITPUB9周年纪念徽章
日期:2010-10-08 09:32:25ITPUB十周年纪念徽章
日期:2011-11-01 16:23:262013年新春福章
日期:2013-02-25 14:51:24沸羊羊
日期:2015-03-04 14:51:522015年新春福章
日期:2015-03-06 11:57:31
8#
发表于 2018-1-12 12:50 | 只看该作者
KEN6503 发表于 2018-1-11 10:38
谢谢。我用v$session_longops 的到下面的信息:
query在scan这个表的时候特别慢,这是一个分区表,执行f ...

你这计划不全,看起来似乎也有点问题,所以,你似乎没明白或明白错了。

使用道具 举报

回复
论坛徽章:
10
2010新春纪念徽章
日期:2010-03-01 11:04:59喜羊羊
日期:2015-03-04 14:52:46优秀写手
日期:2014-04-22 06:00:18马上有对象
日期:2014-02-18 16:44:082014年新春福章
日期:2014-02-18 16:44:082013年新春福章
日期:2013-02-25 14:51:24ITPUB十周年纪念徽章
日期:2011-11-01 16:25:512012新春纪念徽章
日期:2012-01-04 11:56:012011新春纪念徽章
日期:2011-02-18 11:42:472015年新春福章
日期:2015-03-06 11:58:18
9#
 楼主| 发表于 2018-1-18 20:51 | 只看该作者
sqysl 发表于 2018-1-12 12:50
你这计划不全,看起来似乎也有点问题,所以,你似乎没明白或明白错了。

谢谢:
下面是完整的query 和 计划。麻烦大师们帮忙看看:

  1. Plan hash value: 373728234

  2. -------------------------------------------------------------------------------------------------------------------------------------
  3. | Id  | Operation                            | Name                         | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
  4. -------------------------------------------------------------------------------------------------------------------------------------
  5. |   0 | SELECT STATEMENT                     |                              |       |       | 32046 (100)|          |       |       |
  6. |   1 |  NESTED LOOPS OUTER                  |                              |   690 | 32430 | 32046  (10)| 00:02:19 |       |       |
  7. |   2 |   PARTITION RANGE SINGLE             |                              |   690 | 20010 | 32046  (10)| 00:02:19 |   KEY |   KEY |
  8. |   3 |    PARTITION LIST SINGLE             |                              |   690 | 20010 | 32046  (10)| 00:02:19 |       |       |
  9. |*  4 |     TABLE ACCESS FULL                | PART_INS                     |   690 | 20010 | 32046  (10)| 00:02:19 |   KEY |   KEY |
  10. |*  5 |   VIEW PUSHED PREDICATE              |                              |     1 |    18 |     0   (0)|          |       |       |
  11. |   6 |    NESTED LOOPS                      |                              |       |       |            |          |       |       |
  12. |   7 |     NESTED LOOPS                     |                              |     1 |    92 |   121K (14)| 00:08:46 |       |       |
  13. |*  8 |      HASH JOIN                       |                              |     1 |    53 |   121K (14)| 00:08:46 |       |       |
  14. |   9 |       MERGE JOIN CARTESIAN           |                              |    31 |   651 |     5   (0)| 00:00:01 |       |       |
  15. |* 10 |        TABLE ACCESS FULL             | ACCT_CRIT                    |     1 |    11 |     2   (0)| 00:00:01 |       |       |
  16. |  11 |        BUFFER SORT                   |                              |    61 |   610 |     3   (0)| 00:00:01 |       |       |
  17. |* 12 |         TABLE ACCESS FULL            | ACCT_TYP                     |    61 |   610 |     3   (0)| 00:00:01 |       |       |
  18. |  13 |       PARTITION RANGE SINGLE         |                              |    48 |  1536 |   121K (14)| 00:08:46 |   KEY |   KEY |
  19. |  14 |        PARTITION LIST SINGLE         |                              |    48 |  1536 |   121K (14)| 00:08:46 |       |       |
  20. |* 15 |         TABLE ACCESS FULL            | INST_DEL                     |    48 |  1536 |   121K (14)| 00:08:46 |   KEY |   KEY |
  21. |  16 |      PARTITION LIST SINGLE           |                              |     1 |       |     0   (0)|          |   KEY |   KEY |
  22. |* 17 |       INDEX UNIQUE SCAN              | XPK_INST                     |     1 |       |     0   (0)|          |   123 |   123 |
  23. |* 18 |     TABLE ACCESS BY LOCAL INDEX ROWID| INST                         |     1 |    39 |     1   (0)| 00:00:01 |   123 |   123 |
  24. -------------------------------------------------------------------------------------------------------------------------------------

  25. Predicate Information (identified by operation id):
  26. ---------------------------------------------------

  27.    4 - filter(("A"."SRC_SYS_CODE"='QUT' AND "A"."SRC_CODE"='BCF' AND
  28.               "A"."RUN_DT"='17-JAN-18'))
  29.    5 - filter("B"."INS_ACCT_NMBR"=CASE "A"."SRC_SYS_CODE" WHEN 'TSS' THEN
  30.               TRIM("A"."INS_ACCT_NMBR_CHAR") ELSE LTRIM(TRIM(UPPER("A"."INS_ACCT_NMBR_CHAR")),'0') END )
  31.    8 - access("DT"."ACCT_TYP_CD"="ATP"."ACCOUNT_TYP_CD" AND "DT"."INSTR_STATUS_CD"="ST"."INSTR_STATUS_CD")
  32.   10 - filter("ST"."SRC_SYS_CODE"='QUT')
  33.   12 - filter("ATP"."INC_IND"<>'E')
  34.   15 - filter(("DT"."ACCT_TYP_CD" IS NOT NULL AND "DT"."INSTR_STATUS_CD" IS NOT NULL AND
  35.               "DT"."SRC_CODE"='QUT' AND "DT"."RUN_DT"='17-JAN-18'))
  36.   17 - access("B"."SRC_SYS_CODE"='QUT' AND "DT"."INST_KEY"="B"."INST_KEY")
  37.        filter("B"."SRC_SYS_CODE"="A"."SRC_SYS_CODE")
  38.   18 - filter(("B"."ORIG_INS_ACCT_NMBR" IS NULL OR ("B"."SRC_SYS_CODE"='SITE' AND
  39.               "B"."INST_KEY"<>140382305410519 AND "B"."INST_KEY"<>140382477860519 AND "B"."INST_KEY"<>140382671970519
  40.               AND "B"."INST_KEY"<>140381720520519 AND "B"."INST_KEY"<>140382147600519 AND
  41.               "B"."INST_KEY"<>140381934160519 AND "B"."INST_KEY"<>140382175310519)))
复制代码

  1. SELECT A.SRC_CODE,
  2.        A.SRC_SYS_CODE,
  3.        A.BUS_APPL_ID,
  4.        A.RUN_DT
  5.   FROM PART_INS A,
  6.        (SELECT B.SRC_SYS_CODE,
  7.                DT.SRC_CODE,
  8.                DT.RUN_DT,
  9.                CASE
  10.                   WHEN B.SRC_SYS_CODE = 'TSS'
  11.                   THEN
  12.                      '01' || B.INS_ACCT_NMBR
  13.                   WHEN B.SRC_SYS_CODE = 'SITE'
  14.                   THEN
  15.                      LTRIM (TRIM (UPPER (B.ORIG_INS_ACCT_NMBR)), 0)
  16.                   ELSE
  17.                      LTRIM (TRIM (B.INS_ACCT_NMBR), 0)
  18.                END
  19.                   INS_ACCT_NMBR,
  20.                B.SRC_SYS_TRANSITE_NMBR
  21.           FROM INST B,
  22.                INST_DEL DT,
  23.                ACCT_TYP ATP,
  24.                ACCT_CRIT ST
  25.          WHERE     B.SRC_SYS_CODE = 'QUT'
  26.                AND DT.SRC_CODE = 'QUT'
  27.                AND DT.RUN_DT = '17-JAN-18'
  28.                AND DT.INST_KEY = B.INST_KEY
  29.                AND DT.ACCT_TYP_CD = ATP.ACCOUNT_TYP_CD
  30.                AND ATP.INC_IND != 'E'
  31.                AND B.SRC_SYS_CODE = ST.SRC_SYS_CODE
  32.                AND DT.INSTR_STATUS_CD = ST.INSTR_STATUS_CD
  33.                AND (   B.ORIG_INS_ACCT_NMBR IS NULL
  34.                     OR (    B.SRC_SYS_CODE = 'SITE'
  35.                         AND B.INST_KEY NOT IN (140382305410519,
  36.                                                140382477860519,
  37.                                                140382671970519,
  38.                                                140381720520519,
  39.                                                140382147600519,
  40.                                                140381934160519,
  41.                                                140382175310519)))) B
  42. WHERE     A.SRC_CODE = 'BCF'
  43.        AND A.RUN_DT = '17-JAN-18'
  44.        AND A.SRC_SYS_CODE = B.SRC_SYS_CODE(+)
  45.        AND CASE
  46.               WHEN A.SRC_SYS_CODE IN ('TSS') THEN TRIM (A.INS_ACCT_NMBR_CHAR)
  47.               ELSE LTRIM (TRIM (UPPER (A.INS_ACCT_NMBR_CHAR)), 0)
  48.            END = B.INS_ACCT_NMBR(+)
  49.        AND A.SRC_SYS_CODE = 'QUT';
复制代码

使用道具 举报

回复
认证徽章
论坛徽章:
8
2009新春纪念徽章
日期:2009-01-04 14:52:28祖国60周年纪念徽章
日期:2009-10-09 08:28:002010新春纪念徽章
日期:2010-03-01 11:07:24ITPUB9周年纪念徽章
日期:2010-10-08 09:32:25ITPUB十周年纪念徽章
日期:2011-11-01 16:23:262013年新春福章
日期:2013-02-25 14:51:24沸羊羊
日期:2015-03-04 14:51:522015年新春福章
日期:2015-03-06 11:57:31
10#
发表于 2018-1-18 22:01 | 只看该作者
KEN6503 发表于 2018-1-18 20:51
谢谢:
下面是完整的query 和 计划。麻烦大师们帮忙看看:

嗯,这个计划是完整的,也没异常。
其实,只给出计划和SQL语句,并不能确定问题所在,数据环境也很关键,例如:各表的数据量,各条件列选择性及数据分布,各表上所有索引情况,甚至表结构等,楼主别嫌烦,没办法,优化就是需要了解很多细节信息。

使用道具 举报

回复

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

本版积分规则

DTCC2018购票6.8折优惠进行时

中国数据库技术大会是国内数据库及大数据领域规模最大、最受欢迎的技术交流盛会。 2018年5月10-12日,第九届中国数据库技术大会将如约而至。本届大会以“数领先机•智赢未来”为主题,设定2大主会场及20个技术专场,邀请来自国内外互联网、金融、教育等行业百余位技术专家,共同探讨Oracle、MySQL、NoSQL、大数据等领域的前瞻性热点话题与技术。
----------------------------------------
优惠时间:2018年2月13日前

TOP技术积分榜 社区积分榜 徽章 团队 统计 知识索引树 积分竞拍 文本模式 帮助
  ITPUB首页 | ITPUB论坛 | 数据库技术 | 企业信息化 | 开发技术 | 微软技术 | 软件工程与项目管理 | IBM技术园地 | 行业纵向讨论 | IT招聘 | IT文档 |
  | | |
CopyRight 1999-2011 itpub.net All Right Reserved. 北京盛拓优讯信息技术有限公司版权所有 联系我们 网站律师 隐私政策 知识产权声明
 北京市公安局海淀分局网监中心备案编号:11010802021510 广播电视节目制作经营许可证:编号(京)字第1149号
  
快速回复 返回顶部 返回列表
北京赛车pk10 申博官网 北京赛车pk10 六台宝典现场开奖 北京赛车pk10历史记录 北京赛车开奖 北京赛车预测 949494开奖结果今晚 威尼斯人线上娱乐 手机投注平台 幸运28投注技巧 pk10助赢软件 北京pk10百度鼎盛彩票网 北京赛车聚彩 北京pk10如何稳杀3码 pk10稳赢方法 pk10定位计划 pk10包赢计划群