一个SQL的优化，谢谢！-CSDN论坛

⋅EXCEL数据，如何导入oracle数据表里面去？ ⋅一个SQL的优化，谢谢！ ⋅建表的时候，如何指定表所在的表空间？ 更多帖子 关注私信空间博客 manchun 本版专家分：672 结帖率 100%

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万左右吧，生产库上的有几百万吧！

0 2009-08-04 15:42:26

回复数 16 只看楼主引用举报楼主

xugh 本版专家分：149: NOT EXISTS好像效率挺低的

0 2009-08-04 15:45:46

只看TA 引用举报 #1 得分 3

⋅求oracle关于执行计划方面的考试试题(10/t) ⋅升星散分 ⋅节日散分 更多帖子 关注私信空间博客 majy 本版专家分：7107 黄花 2009年8月 Oracle大版内专家分月排行榜第二 蓝花 2005年11月扩充话题大版内专家分月排行榜第三: 1. 大数据量查询不要用绑定变量
2. 把你的生产环境的执行计划贴出来，这样是看不出什么来的
3. 不要管别他说什么 not exists效率低之类的说法 :-)

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

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

这样，你的问题基本上就解决了

0 2009-08-04 15:50:49

只看TA 引用举报 #2 得分 15

⋅EXCEL数据，如何导入oracle数据表里面去？ ⋅一个SQL的优化，谢谢！ ⋅建表的时候，如何指定表所在的表空间？ 更多帖子 关注私信空间博客 manchun 本版专家分：672: 这个start with ... connect by ...不好拉出来，因为这是条件啊，条件就是需要满足在GZ000000节点下面的子节点就行，这样的话如果不用connect by遍历的话，如何搞定呢？

还有没有别的方法来优化做啊！

0 2009-08-04 16:32:41

只看TA 引用举报 #3 得分 0

⋅求oracle关于执行计划方面的考试试题(10/t) ⋅升星散分 ⋅节日散分 更多帖子 关注私信空间博客 majy 本版专家分：7107 黄花 2009年8月 Oracle大版内专家分月排行榜第二 蓝花 2005年11月扩充话题大版内专家分月排行榜第三: 你不把执行计划贴出来，没法搞啊，都不知道你的系统是怎么执行的，能调出啥东东来？？？

试试把这个语句改一下：
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);

0 2009-08-04 16:52:45

只看TA 引用举报 #4    得分 15

⋅创建一个角色，赋予一个用户的读写权限，一个用户的只读权限 ⋅在html可以直接在ie页面通过超链接打开word文档，为什么在jsp里面不行呢？ ⋅求一个实例，jsp页面中，点击文件的超链接，就在ie中显示word、excel、pdf、txt、html文档的内容！ 更多帖子 关注私信空间博客 oraclelogan 本版专家分：12342 红花 2009年5月 Oracle大版内专家分月排行榜第一 黄花 2009年3月 Oracle大版内专家分月排行榜第二 2009年2月 Oracle大版内专家分月排行榜第二 蓝花 2009年6月 Oracle大版内专家分月排行榜第三 2009年5月专题开发/技术/项目大版内专家分月排行榜第三 2009年4月 Oracle大版内专家分月排行榜第三: 引用楼主 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之后的条件判断也是比较消耗资源的啊！

0 2009-08-05 09:24:00

只看TA 引用举报 #5    得分 0

⋅EXCEL数据，如何导入oracle数据表里面去？ ⋅一个SQL的优化，谢谢！ ⋅建表的时候，如何指定表所在的表空间？ 更多帖子 关注私信空间博客 manchun 本版专家分：672: 引用 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之后的条件判断也是比较消耗资源的啊！

哦，那我应该怎么改呢，这个sql不是我写的啊！写的那个人已经离职了！

0 2009-08-05 09:25:05

只看TA 引用举报 #6    得分 0

⋅创建一个角色，赋予一个用户的读写权限，一个用户的只读权限 ⋅在html可以直接在ie页面通过超链接打开word文档，为什么在jsp里面不行呢？ ⋅求一个实例，jsp页面中，点击文件的超链接，就在ie中显示word、excel、pdf、txt、html文档的内容！ 更多帖子 关注私信空间博客 oraclelogan 本版专家分：12342 红花 2009年5月 Oracle大版内专家分月排行榜第一 黄花 2009年3月 Oracle大版内专家分月排行榜第二 2009年2月 Oracle大版内专家分月排行榜第二 蓝花 2009年6月 Oracle大版内专家分月排行榜第三 2009年5月专题开发/技术/项目大版内专家分月排行榜第三 2009年4月 Oracle大版内专家分月排行榜第三

随便写了下，由于没有环境无法测试，楼主自己测试下吧!

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)

0 2009-08-05 09:32:06

只看TA 引用举报 #7 得分 0

⋅EXCEL数据，如何导入oracle数据表里面去？ ⋅一个SQL的优化，谢谢！ ⋅建表的时候，如何指定表所在的表空间？ 更多帖子 关注私信空间博客 manchun 本版专家分：672

引用 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);

我的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

怎么搞？

0 2009-08-05 09:37:34

只看TA 引用举报 #8 得分 0

⋅EXCEL数据，如何导入oracle数据表里面去？ ⋅一个SQL的优化，谢谢！ ⋅建表的时候，如何指定表所在的表空间？ 更多帖子 关注私信空间博客 manchun 本版专家分：672: 引用 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)

我去试了试，本地速度提高了8倍。在本地执行需要0.23秒，待会让pm部署到生产环境上去试试看吧！

0 2009-08-05 09:39:32

只看TA 引用举报 #9    得分 0

sweetbug 本版专家分：26: 学习 mark!

0 2009-08-05 17:31:49

只看TA 引用举报 #10 得分 5

⋅aix下的cx_Oracle ⋅web.py+apache+mod_wsgi没分了 ⋅mod_python 更多帖子 关注私信空间博客 kiuyongfa218 本版专家分：17: SQL语句的优化，确实是一个问题，特别是当数据量非常大的时候，要注意表之间的关联关系，减少对表的扫描次数，减少表之间的相互关系。

0 2009-08-06 10:26:09

只看TA 引用举报 #11 得分 10

manchun 本版专家分：672: 谢谢大家，我已经改定了，谢谢！

0 2009-08-06 12:30:35

只看TA 引用举报 #12 得分 0

⋅怎么与一个数组里的值比较，想知道如何用pl/sql写 ⋅oracle 8i中能在select list中用嵌套查询吗? ⋅刚安装的ORCL中的数据库名 更多帖子 关注私信空间博客 zzwei321 本版专家分：36: 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樓的或者樓主能解釋下：爲什麽刪掉紅色的這句呢？

0 2009-08-07 16:40:05

只看TA 引用举报 #13    得分 6

oracletoad 本版专家分：170: 关注，学习，谢谢！

0 2009-08-12 19:39:28

只看TA 引用举报 #14 得分 5

⋅求助：怎么在事件里面给触发该事件的按钮增加一个弹出窗口？ ⋅结婚回来，第一天上班，散分！ ⋅解决ORA-04091变异表的问题 更多帖子 关注私信空间博客 vber1010 本版专家分：3643: d

0 2009-08-12 22:21:12

只看TA 引用举报 #15 得分 1

⋅求助高手:闪回查询遇到ORA-01555错误！(补救数据，急哦！) ⋅filter 可否排序 ⋅新手请教帮忙解决一个简单用户登录页面的出错 更多帖子 关注私信空间博客 非萨范德萨发 本版专家分：661

引用 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)

下面这段条件写错了吧：

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')

0 2009-08-12 23:54:42

只看TA 引用举报 #16 得分 10

一个SQL的优化，谢谢！ [问题点数：70分，结帖人manchun]