18
回答
oracle大表查询sql优化
终于搞明白,存储TCO原来是这样算的>>>   

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秒左右,查询条件没有一个是索引,如何优化?不加索引的情况,如何能快点?
举报
关山难越
发帖于4年前 18回/1K+阅
共有18个答案 最后回答: 4年前
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

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

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

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

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

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



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

先试试里面的那个表  看看执行速度  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')



--- 共有 1 条评论 ---
关山难越这两个字段都建了索引,估计用like之类的索引就失效了吧? 4年前 回复
顶部