oracle大表查询sql优化

关山难越 发布于 2014/01/03 08:58
阅读 2K+
收藏 3

select a.province_code, b.cust_user_type, count(1) as cnt
               from USER_SUBSCRIPTION a, CUST_USER b
              where a.create_time between '2013-12-01 00:00:00' and '2013-12-08 00:00:00'
                and a.cust_user_account not like '189%'
                and a.cust_user_account not like '153%'
                and a.cust_user_account not like '181%'
                and a.cust_user_account not like '180%'
                and a.cust_user_account not like '133%'
                and a.product_code in ('1000006', '1000012', '1000015',
                     '1000016', '1000017', '1000019')
                and a.state = '1'
                and a.cust_user_id = b.cust_user_id
                and b.cust_user_role = '1'
                and b.cust_user_type is not null
              group by a.province_code, b.cust_user_type

USER_SUBSCRIPTION a, CUST_USER b都是140多万的表,sql执行完耗时47秒左右,查询条件没有一个是索引,如何优化?不加索引的情况,如何能快点?
加载中
0
Vincent.feng
Vincent.feng
select a.province_code, b.cust_user_type, count(1) as cnt
               from USER_SUBSCRIPTION a, CUST_USER b
              where a.create_time between '2013-12-01 00:00:00' and '2013-12-08 00:00:00'
   and a.state = '1'
                and a.cust_user_id = b.cust_user_id
                and b.cust_user_role = '1'
                and b.cust_user_type is not null
and a.product_code in ('1000006', '1000012', '1000015',
                     '1000016', '1000017', '1000019')
                and a.cust_user_account not like '189%'
                and a.cust_user_account not like '153%'
                and a.cust_user_account not like '181%'
                and a.cust_user_account not like '180%'
                and a.cust_user_account not like '133%'
              group by a.province_code, b.cust_user_type

试一下,不行我也没办法了 !

0
关山难越
关山难越

跟原来差不多。where条件执行顺序是从左到右还是从右到左?

0
apsaras
apsaras
ORACLE 什么版本? 这个不同的版本或者参数配置,执行计划都不一样。
0
xiaohe24
xiaohe24
oracle是右到左
0
excepiton
excepiton
贴下执行计划出来分析
0
calvary
calvary

你这个。 语句上没什么优化的。。

     只是. 给需要的条件加些索引. 然后看下执行计划.

  看是怎么执行的。 就知道该做哪些优化了.. 

0
蘑菇喵
蘑菇喵

select a.province_code, b.cust_user_type, count(1) as cnt from CUST_USER b, 
(select cust_user_id, province_code from USER_SUBSCRIPTION 
where cust_user_account not like '189%'
and cust_user_account not like '153%'
and cust_user_account not like '181%'
and cust_user_account not like '180%'
and cust_user_account not like '133%'
and product_code in ('1000006', '1000012', '1000015',
     '1000016', '1000017', '1000019')
and state = '1'
and (create_time between '2013-12-01 00:00:00' and '2013-12-08 00:00:00')) a 
where a.cust_user_id = b.cust_user_id and b.cust_user_role = '1' and b.cust_user_type is not null
group by a.province_code, b.cust_user_type



关山难越
关山难越
执行了下结果差不多啊
0
关山难越
关山难越
一直有个疑问,where条件从右到左执行,也就是说可以过滤掉最大数据的条件放到最后面,可每次一执行,没啥区别啊?还有多表关联查询,数据少的表放后面,但执行出来也没啥区别啊?哪位大牛能给解答下么?
0
蘑菇喵
蘑菇喵

先试试里面的那个表  看看执行速度  cust_user_account和product_code都加上索引



select cust_user_id, province_code from USER_SUBSCRIPTION 
where cust_user_account not like '189%'
and cust_user_account not like '153%'
and cust_user_account not like '181%'
and cust_user_account not like '180%'
and cust_user_account not like '133%'
and product_code in ('1000006', '1000012', '1000015',
     '1000016', '1000017', '1000019')
and state = '1'
and (create_time between '2013-12-01 00:00:00' and '2013-12-08 00:00:00')



关山难越
关山难越
这两个字段都建了索引,估计用like之类的索引就失效了吧?
0
IdleMan
IdleMan
执行计划才有指导意义,别想当然啦
返回顶部
顶部