一个SQL的优化，谢谢！

manchun 2009-08-04 03:42:26 
select t.wp_status, t.is_send, count(1) "assignCount"

     from (select t1.wp_status, t1.is_send

             from t_crm_wp t1

            where EXISTS (SELECT area_id

                     FROM t_area a

                    WHERE area_id = t1.area_id

                    START WITH a.area_id = 'GZ0000000000'

                   CONNECT BY PRIOR a.area_id = a.parent_area_id)

              AND NOT EXISTS

            (select 1

                     from t_crm_wp t

                    where t.wp_id = t1.wp_id

                      and (EXISTS

                           (select 1

                              from bst_sys_sysuser a

                             where (a.userid = t.user_id or

                                   a.userid = t.check_id)

                               and EXISTS

                             (select 1

                                      from t_area c

                                     where a.area_id = c.area_id

                                       and c.area_id <> 'GZ0000000000'

                                     start with c.area_id = 'GZ0000000000'

                                    connect by prior c.parent_area_id = c.area_id)))

                      and t.is_send = '1')) t

    group by rollup(t.wp_status, t.is_send)



这个sql在本地执行需要1.6秒，在生产环境上是6分钟，怎么优化啊，我用的是ibatis，用##带变量，所以算是绑定变量方式传入的。

本地数据10万左右吧，生产库上的有几百万吧！
...全文
264 16 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
16 条回复
切换为时间正序
请发表友善的回复…
发表回复
非萨范德萨发 2009-08-12
  • 打赏
  • 举报
 回复
[Quote=引用 7 楼 oraclelogan 的回复:]
随便写了下，由于没有环境无法测试，楼主自己测试下吧!


SQL codeselect t.wp_status, t.is_send,count(1) "assignCount"from (select t1.wp_status, t1.is_sendfrom t_crm_wp t1whereEXISTS (SELECT area_idFROM t_area aWHERE area_id= t1.area_id
STARTWITH a.area_id='GZ0000000000'
CONNECTBY PRIOR a.area_id= a.parent_area_id)AND
(NOTEXISTS
(select1from bst_sys_sysuser awhere (a.userid= t1.user_idor
a.userid= t1.check_id)andEXISTS
(select1from t_area cwhere a.area_id= c.area_idand c.area_id<>'GZ0000000000'
startwith c.area_id='GZ0000000000'
connectby prior c.parent_area_id= c.area_id))or t1.is_send='1'
)/*(select 1
from t_crm_wp t
where t.wp_id = t1.wp_id
and (EXISTS
(select 1
from bst_sys_sysuser a
where (a.userid = t.user_id or
a.userid = t.check_id)
and EXISTS
(select 1
from t_area c
where a.area_id = c.area_id
and c.area_id <> 'GZ0000000000'
start with c.area_id = 'GZ0000000000'
connect by prior c.parent_area_id = c.area_id)))
and t.is_send = '1')*/
) tgroupby rollup(t.wp_status, t.is_send)
[/Quote]

下面这段条件写错了吧：
AND 

              (NOT EXISTS

                   (select 1

                              from bst_sys_sysuser a

                             where (a.userid = t1.user_id or

                                   a.userid = t1.check_id)

                               and EXISTS

                             (select 1

                                      from t_area c

                                     where a.area_id = c.area_id

                                       and c.area_id <> 'GZ0000000000'

                                     start with c.area_id = 'GZ0000000000'

                                    connect by prior c.parent_area_id = c.area_id))

                 or t1.is_send = '1'

               )

楼主原语句的本意是：（统计）查出t_crm_wp中area_id='GZ0000000000'及其下属组织的数据，并排除在t_crm_wp中is_send='1'的check_id或user_id对应bst_sys_sysuser中User_id的组织为area_id='GZ0000000000'上属组织的数据。
楼主测试下，你原来的SQL结果和，oraclelogan 的优化SQL,以及下面的SQL结果是否一致：）
SELECT t1.wp_status, t1.is_send

         FROM   t_crm_wp t1

         WHERE  EXISTS (SELECT area_id

                                     FROM   t_area a

                                     WHERE  area_id = t1.area_id

                                     START  WITH a.area_id = 'GZ0000000000'

                                     CONNECT BY PRIOR a.area_id = a.parent_area_id)

             AND    NOT EXISTS (SELECT 1

         									FROM   bst_sys_sysuser a

                                            WHERE  (a.userid = t.user_id OR a.userid = t.check_id)

                                            AND    EXISTS  (SELECT 1

                                                                      FROM   t_area c

                                                                      WHERE  a.area_id = c.area_id

                                                                      AND    c.area_id <> 'GZ0000000000'

                                                                      START  WITH c.area_id = 'GZ0000000000'

                                                                      CONNECT BY PRIOR c.parent_area_id = c.area_id)

                                            AND    t.is_send = '1')

vber1010 2009-08-12
  • 打赏
  • 举报
 回复
d
oracletoad 2009-08-12
  • 打赏
  • 举报
 回复
关注，学习，谢谢！
zzwei321 2009-08-07
  • 打赏
  • 举报
 回复
select t.wp_status, t.is_send, count(1) "assignCount"
from (select t1.wp_status, t1.is_send
from t_crm_wp t1
where EXISTS (SELECT area_id
FROM t_area a
WHERE area_id = t1.area_id
START WITH a.area_id = 'GZ0000000000'
CONNECT BY PRIOR a.area_id = a.parent_area_id)
AND
(NOT EXISTS
(select 1
from bst_sys_sysuser a
where (a.userid = t1.user_id or
a.userid = t1.check_id)
and EXISTS
(select 1
from t_area c
where a.area_id = c.area_id
and c.area_id <> 'GZ0000000000'
start with c.area_id = 'GZ0000000000'
connect by prior c.parent_area_id = c.area_id))
or t1.is_send = '1'
)
/*(select 1
from t_crm_wp t
where t.wp_id = t1.wp_id
and (EXISTS
(select 1
from bst_sys_sysuser a
where (a.userid = t.user_id or
a.userid = t.check_id)
and EXISTS
(select 1
from t_area c
where a.area_id = c.area_id
and c.area_id <> 'GZ0000000000'
start with c.area_id = 'GZ0000000000'
connect by prior c.parent_area_id = c.area_id)))
and t.is_send = '1')*/
) t
group by rollup(t.wp_status, t.is_send)


7樓的或者樓主能解釋下：爲什麽刪掉紅色的這句呢？
manchun 2009-08-06
  • 打赏
  • 举报
 回复
谢谢大家，我已经改定了，谢谢！
kiuyongfa218 2009-08-06
  • 打赏
  • 举报
 回复
SQL语句的优化，确实是一个问题，特别是当数据量非常大的时候，要注意表之间的关联关系，减少对表的扫描次数，减少表之间的相互关系。
manchun 2009-08-05
  • 打赏
  • 举报
 回复
[Quote=引用 7 楼 oraclelogan 的回复:]
随便写了下，由于没有环境无法测试，楼主自己测试下吧!


SQL codeselect t.wp_status, t.is_send,count(1) "assignCount"from (select t1.wp_status, t1.is_sendfrom t_crm_wp t1whereEXISTS (SELECT area_idFROM t_area aWHERE area_id= t1.area_id
STARTWITH a.area_id='GZ0000000000'
CONNECTBY PRIOR a.area_id= a.parent_area_id)AND
(NOTEXISTS
(select1from bst_sys_sysuser awhere (a.userid= t1.user_idor
a.userid= t1.check_id)andEXISTS
(select1from t_area cwhere a.area_id= c.area_idand c.area_id<>'GZ0000000000'
startwith c.area_id='GZ0000000000'
connectby prior c.parent_area_id= c.area_id))or t1.is_send='1'
)/*(select 1
from t_crm_wp t
where t.wp_id = t1.wp_id
and (EXISTS
(select 1
from bst_sys_sysuser a
where (a.userid = t.user_id or
a.userid = t.check_id)
and EXISTS
(select 1
from t_area c
where a.area_id = c.area_id
and c.area_id <> 'GZ0000000000'
start with c.area_id = 'GZ0000000000'
connect by prior c.parent_area_id = c.area_id)))
and t.is_send = '1')*/
) tgroupby rollup(t.wp_status, t.is_send)
[/Quote]

我去试了试，本地速度提高了8倍。在本地执行需要0.23秒，待会让pm部署到生产环境上去试试看吧！
manchun 2009-08-05
  • 打赏
  • 举报
 回复
[Quote=引用 4 楼 majy 的回复:]
你不把执行计划贴出来，没法搞啊，都不知道你的系统是怎么执行的，能调出啥东东来？？？

试试把这个语句改一下：
select t1.wp_status, t1.is_send
            from t_crm_wp t1
            where EXISTS (SELECT area_id
                    FROM t_area a
                    WHERE area_id = t1.area_id
                    START WITH a.area_id = 'GZ0000000000'
                  CONNECT BY PRIOR a.area_id = a.parent_area_id

－－－－－－
select t1.wp_status, t1.is_send
  from t_crm_wp t1,
      (SELECT area_id
          FROM t_area a
        START WITH a.area_id = 'GZ0000000000'
        CONNECT BY PRIOR a.area_id = a.parent_area_id) t2
where t1.area_id = t2.area_id

下面的connect by语句也这么改

然后，在t_area表上加上字段 area_id的索引，我看，你用bitmap索引会非常好，你的area_id的distinct值会比较小，我想

做完索引，运行
dbms_stats.gather_table_stat(user,'T_AREA', method_opt=>'for all indexed columns', cascade=>true);
[/Quote]

我的area_id索引原来已经存在了！

我执行你的sql语句报如下错误啊：

SQL>  dbms_stats.gather_table_stat(user,'T_AREA', method_opt=>'for all indexed columns', cascade=>true);



dbms_stats.gather_table_stat(user,'T_AREA', method_opt=>'for all indexed columns', cascade=>true)



ORA-00900: invalid SQL statement


怎么搞？
oraclelogan 2009-08-05
  • 打赏
  • 举报
 回复
随便写了下，由于没有环境无法测试，楼主自己测试下吧!


select t.wp_status, t.is_send, count(1) "assignCount"

     from (select t1.wp_status, t1.is_send

             from t_crm_wp t1

            where EXISTS (SELECT area_id

                     FROM t_area a

                    WHERE area_id = t1.area_id

                    START WITH a.area_id = 'GZ0000000000'

                   CONNECT BY PRIOR a.area_id = a.parent_area_id)

              AND 

              (NOT EXISTS

                   (select 1

                              from bst_sys_sysuser a

                             where (a.userid = t1.user_id or

                                   a.userid = t1.check_id)

                               and EXISTS

                             (select 1

                                      from t_area c

                                     where a.area_id = c.area_id

                                       and c.area_id <> 'GZ0000000000'

                                     start with c.area_id = 'GZ0000000000'

                                    connect by prior c.parent_area_id = c.area_id))

                 or t1.is_send = '1'

               )

            /*(select 1

                     from t_crm_wp t

                    where t.wp_id = t1.wp_id

                      and (EXISTS

                           (select 1

                              from bst_sys_sysuser a

                             where (a.userid = t.user_id or

                                   a.userid = t.check_id)

                               and EXISTS

                             (select 1

                                      from t_area c

                                     where a.area_id = c.area_id

                                       and c.area_id <> 'GZ0000000000'

                                     start with c.area_id = 'GZ0000000000'

                                    connect by prior c.parent_area_id = c.area_id)))

                      and t.is_send = '1')*/

         ) t

    group by rollup(t.wp_status, t.is_send)
manchun 2009-08-05
  • 打赏
  • 举报
 回复
[Quote=引用 5 楼 oraclelogan 的回复:]
引用楼主 manchun 的回复:
SQL codeselect t.wp_status, t.is_send,count(1) "assignCount"from (select t1.wp_status, t1.is_sendfrom t_crm_wp t1whereEXISTS (SELECT area_idFROM t_area aWHERE area_id= t1.area_id
                    STARTWITH a.area_id='GZ0000000000'
                  CONNECTBY PRIOR a.area_id= a.parent_area_id)ANDNOTEXISTS
            (select1from t_crm_wp twhere t.wp_id= t1.wp_idand (EXISTS
                          (select1from bst_sys_sysuser awhere (a.userid= t.user_idor
                                  a.userid= t.check_id)andEXISTS
                            (select1from t_area cwhere a.area_id= c.area_idand c.area_id <>'GZ0000000000'
                                    startwith c.area_id='GZ0000000000'
                                    connectby prior c.parent_area_id= c.area_id)))and t.is_send='1')) tgroupby rollup(t.wp_status, t.is_send)


这个sql在本地执行需要1.6秒，在生产环境上是6分钟，怎么优化啊，我用的是ibatis，用##带变量，所以算是绑定变量方式传入的。

本地数据10万左右吧，生产库上的有几百万吧！


你的主表是t_crm_wp，你说在本地数据有10多万，生产环境有几百万吧！

但是你的no exists嵌套 查询：
not exists(select 1
                    from t_crm_wp t
                    where t.wp_id = t1.wp_id

这样一搞，不就是几百万*几百万的开销吗？这样很消耗资源的，建议把no exists里面对t_crm_wp的条件语句移到外面来，就是不要t_crm_wp*t_crm_wp 的遍历，否则就麻烦了！

当然以上几位说的connect by 用着where之后的条件判断也是比较消耗资源的啊！
[/Quote]

哦，那我应该怎么改呢，这个sql不是我写的啊！写的那个人已经离职了！
oraclelogan 2009-08-05
  • 打赏
  • 举报
 回复
[Quote=引用楼主 manchun 的回复:]
SQL codeselect t.wp_status, t.is_send,count(1) "assignCount"from (select t1.wp_status, t1.is_sendfrom t_crm_wp t1whereEXISTS (SELECT area_idFROM t_area aWHERE area_id= t1.area_id
STARTWITH a.area_id='GZ0000000000'
CONNECTBY PRIOR a.area_id= a.parent_area_id)ANDNOTEXISTS
(select1from t_crm_wp twhere t.wp_id= t1.wp_idand (EXISTS
(select1from bst_sys_sysuser awhere (a.userid= t.user_idor
a.userid= t.check_id)andEXISTS
(select1from t_area cwhere a.area_id= c.area_idand c.area_id<>'GZ0000000000'
startwith c.area_id='GZ0000000000'
connectby prior c.parent_area_id= c.area_id)))and t.is_send='1')) tgroupby rollup(t.wp_status, t.is_send)


这个sql在本地执行需要1.6秒，在生产环境上是6分钟，怎么优化啊，我用的是ibatis，用##带变量，所以算是绑定变量方式传入的。

本地数据10万左右吧，生产库上的有几百万吧！
[/Quote]

你的主表是t_crm_wp，你说在本地数据有10多万，生产环境有几百万吧！

但是你的no exists嵌套 查询：
not exists(select 1
from t_crm_wp t
where t.wp_id = t1.wp_id

这样一搞，不就是几百万*几百万的开销吗？这样很消耗资源的，建议把no exists里面对t_crm_wp的条件语句移到外面来，就是不要t_crm_wp*t_crm_wp 的遍历，否则就麻烦了！

当然以上几位说的connect by 用着where之后的条件判断也是比较消耗资源的啊！
sweetBug 2009-08-05
  • 打赏
  • 举报
 回复
学习 mark!
majy 2009-08-04
  • 打赏
  • 举报
 回复
你不把执行计划贴出来，没法搞啊，都不知道你的系统是怎么执行的，能调出啥东东来？？？

试试把这个语句改一下：
select t1.wp_status, t1.is_send
from t_crm_wp t1
where EXISTS (SELECT area_id
FROM t_area a
WHERE area_id = t1.area_id
START WITH a.area_id = 'GZ0000000000'
CONNECT BY PRIOR a.area_id = a.parent_area_id

－－－－－－
select t1.wp_status, t1.is_send
from t_crm_wp t1,
(SELECT area_id
FROM t_area a
START WITH a.area_id = 'GZ0000000000'
CONNECT BY PRIOR a.area_id = a.parent_area_id) t2
where t1.area_id = t2.area_id

下面的connect by语句也这么改

然后，在t_area表上加上字段 area_id的索引，我看，你用bitmap索引会非常好，你的area_id的distinct值会比较小，我想

做完索引，运行
dbms_stats.gather_table_stat(user,'T_AREA', method_opt=>'for all indexed columns', cascade=>true);
manchun 2009-08-04
  • 打赏
  • 举报
 回复
这个start with ... connect by ...不好拉出来，因为这是条件啊，条件就是需要满足 在GZ000000节点下面的子节点就行，这样的话如果不用connect by遍历的话，如何搞定呢？

还有没有别的方法来优化做啊！
majy 2009-08-04
  • 打赏
  • 举报
 回复
1. 大数据量查询不要用绑定变量
2. 把你的生产环境的执行计划贴出来，这样是看不出什么来的
3. 不要管别他说什么 not exists效率低之类的说法 :-)

当然，你的语句里多少可以看出一点问题来：
你用了 start with...connect by，这个语句用在单表会运行的非常好，但是，你用在两表关联中就会有问题，所以，第一，你要做的就是，把start with...connect by 从内层关联中拉出来：

strart with ... connect by ...
(内层关联)


这样，你的问题基本上就解决了
xugh 2009-08-04
  • 打赏
  • 举报
 回复
NOT EXISTS好像效率挺低的
明小子4.3.3网站漏洞检测工具
Domain4.3(明小子)10.10.10更新版主要更新内容如下： 1.驱除部分死连接，恢复部分官方连接 2.改变默认显示页面为本人BLOG 3.修正程序在检测时出错的BUG 4.修正程序在部分系统中无法启动的BUG 5.加了一个功能模块，但还不成熟，隐藏了，高手的话可以用OD调出来！>!< 6.修复前段时间一些朋友反映的错误和程序宏 7.增加四款SKN皮肤！ 10.10.11紧急更新： 1.修正新版程序在部分系统中启动后直接导致系统蓝屏的BUG 2.修正域名查询的几个错误 3.修正程序批量检测查询到域名的时候无反映的BUG！ Domain4.2(明小子)10.01.16更新版主要更新内容如下： 1.删除所有无关数据 2.优化急速代码 3.改变启动线程 4.增加SQL数据批量导出功能 5.加入几个优化进程模块,非后门.后台运行. 6.锁定主程序栏目宽和高，不在恢复 7.删除“辅助工具”模块中全部代码 8.修正XP SP3 程序下兼容问题 9.重新改写域名查询旁站线程 程序于2010年01月16号调试完毕! 提别提示:第4个功能只能运行在完整版的windows XP 平台系统中,其他平台无法运行,如果出现恢复按钮并不能保存请自行调试与本程序无关.谢谢合作! 宝宝的话：有朋友们的鼓励才会有这个版本的发布，这当是我送给大家2010年里的第一个礼物吧。 Domain4.1(明小子)0901更新版主要更新内容如下： 1.增加新系统兼容模块,全面兼容2000-2008各系统 （部分精简版系统除外） 2.优化网站打开速度,删减部分无用内核信息,全面加快程序启动速度. 3.基于09.05.20号内核提升,改变搜查循环的一个死BUG! 4.删除在0520上增加的一个补针函数,并使用新的函数替换.保证不报错. 5.不在兼容xpsp3,因为没时间去修正这个错误。所以可能部分SP3系统运行不起来本程序 6.增加部分程序扩充空格代码，为下个版本更新做好铺垫 小提示：保留旧的IP反查连接系统,原因没有找到新的和稳定的IP反差服务器！如果有能提供的请与本人联系！谢谢合作 Domain4.1(明小子)0520更新版主要更新内容如下： 1.基于08.10.01号内核提升,修正一次关键错误。 2.替换原程序自带的那个小马,该为最新美化免杀版的小马！ 3.添加一个服务器挂钩程序(非插件),使修改后的新内核兼容vista系统。 4.程序数据库默认名为：SetData.mdb 请大家使用3.5版本以上的MDB数据.其他低版本程序可能不兼容！ 5.删除后台下载统计代码(以前没发现,本人也是从网站上面下的软件,排除作者自添加行为) 6.加了一个补针,针对sql注入功能中的一个小BUG。 7.删除了很多没有用的代码,最大限度的优化程序启动速度。 8.更新部分在xpsp3下出错的几个小BUG(小问题小解决,这个错误仅针对部分优化过度的XPSP3系统)。 提记：很多朋友说需要修改下程序IP域名查询功能,这里说明下.由于内核找不到关键句柄导致无法更新,下个版本在仔细看看！ Domain4.0(明小子)1001更新版主要更新内容如下： 1.更新部分过度优化过的系统不兼容问题。 2.修改内存识别函数，使其降低软件在使用期间所占用的内存大小。 3.基于08.09.15号内核提升，修正因修改软件所出现的几个导致软件崩溃的几个BUG。 4.换了软件自带的那个垃圾皮肤。加入兼容美化皮肤！更美观！ 5.兼容前版本数据库支持功能 6.去掉了没有用的PHP入侵选项和几处小BUG...... 转贴是一种美德！分享也是一种快乐，工具仅做辅助作用请勿用在非法用途上！ 程序自带本BLOG连接，启动比较慢！不喜勿用！谢谢合作！
SQL优化的21个小技巧(荣耀典藏版)
大家好，我是月夜枫，每一个好习惯都是为自己积累的一笔财富，今天分享SQL性能优化，SQL规范优雅等等等等的21个好习惯，谢谢阅读，加油哈~
Java 后端面试必备：对于 SQL 优化的理解
SQL 优化是 Java 后端开发人员必备技能，通过合理运用索引、优化查询语句、处理好 order by 和 in/exists 操作以及配置好数据库参数，能显著提升系统性能和资源利用率。在面试中，深入理解 SQL 优化原理和方法，结合实际案例阐述，能让面试官看到你的专业能力和解决问题的能力。如果后续你还想了解更多关于数据库优化的深入知识，如执行计划分析工具使用、存储过程优化等，欢迎随时告诉我。
20种sql语句优化方案
大家写sql语句中，是不是常有死锁、超时等问题出现，那就说明你的索引、查询语句规范并不完美。以下是小主整列20种sql语句优化方案，可以让你的sql语句，查询删除更加快速。当然看起来更加容易理解！
SQL优化 10大经典案例场景实战
SQL优化 10大经典案例场景实战 转录于后端架构师公众号 前言 在应用开发的早期，数据量少，开发人员开发功能时更重视功能上的实现，随着生产数据的增长，很多SQL语句开始暴露出性能问题，对生产的影响也越来越大，有时可能这些有问题的SQL就是整个系统性能的瓶颈。 SQL优化一般步骤 1、通过慢查日志等定位那些执行效率较低的SQL语句 2、explain 分析SQL的执行计划 需要重点关注type、rows、filtered、extra。 type由上至下，效率越来越高 ALL..
Oracle

17,140

社区成员

55,259

社区内容

发帖
与我相关
我的任务
社区描述
Oracle开发相关技术讨论
社区管理员
  • 开发
  • Lucifer三思而后行
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章