求救,大数据量的查询

xjgbbs 2010-12-06 04:57:30

语句是这样的:
select count(1) as count,'25岁及以下' as tab from MV_UT_MEMBER where (sysdate-birthday)/365>=0 and (sysdate-birthday)/365<=25 and INSTR(BELONGDZB,'001.001.032.092')>0
union select count(1) as count,'26到35岁' as tab from MV_UT_MEMBER where (sysdate-birthday)/365>=26 and (sysdate-birthday)/365<=35 and INSTR(BELONGDZB,'001.001.032.092')>0
union select count(1) as count,'36到45岁' as tab from MV_UT_MEMBER where (sysdate-birthday)/365>=36 and (sysdate-birthday)/365<=45 and INSTR(BELONGDZB,'001.001.032.092')>0
union select count(1) as count,'46到54岁' as tab from MV_UT_MEMBER where (sysdate-birthday)/365>=46 and (sysdate-birthday)/365<=55 and INSTR(BELONGDZB,'001.001.032.092')>0
union select count(1) as count,'55到59岁' as tab from MV_UT_MEMBER where (sysdate-birthday)/365>=56 and (sysdate-birthday)/365<=59 and INSTR(BELONGDZB,'001.001.032.092')>0
union select count(1) as count,'60岁以上' as tab from MV_UT_MEMBER where (sysdate-birthday)/365>=60 and INSTR(BELONGDZB,'001.001.032.092')>0 order by TAb
MV_UT_MEMBER 表里有50W+的数据,这样查询一次,一条记录就要比较六次.这样算下来就要比较300W次再求和,这样效率很慢啊,有什么可以提高效率的方法吗??

...全文

133 13 打赏收藏转发到动态举报

写回复

用AI写文章

13 条回复

切换为时间正序

请发表友善的回复…

发表回复

红深呼吸 2010-12-09

打赏
举报

ORACLE只对简单的表提供高速缓冲(cache buffering) ,这个功能并不适用于多表连接查询

mg_chen 2010-12-07

打赏
举报

建个视图，算出每个人属于哪个年龄段的

xjgbbs 2010-12-07

打赏
举报

[Quote=引用 10 楼 zhuomingwang 的回复:]
引用 9 楼 xjgbbs 的回复:
4楼的速度要比5楼的快一倍不到点,但是还是要三秒,还有更快点的方法吗?
INSTR(BELONGDZB,'001.001.032.092')
建个基于函数的索引试试
[/Quote]

INSTR(BELONGDZB,'001.001.032.092')这个不是必须的,我删除这个条件后查询只快了0.1秒

真的不知道取什么名 2010-12-07

打赏
举报

[Quote=引用 9 楼 xjgbbs 的回复:]
4楼的速度要比5楼的快一倍不到点,但是还是要三秒,还有更快点的方法吗?
[/Quote]INSTR(BELONGDZB,'001.001.032.092')
建个基于函数的索引试试

xjgbbs 2010-12-07

打赏
举报

4楼的速度要比5楼的快一倍不到点,但是还是要三秒,还有更快点的方法吗?

gelyon 2010-12-06

打赏
举报

[Quote=引用 7 楼 wkc168 的回复:]
引用 6 楼 gelyon 的回复:
哇，成哥，被你抢先了一步

大家进步才是王道
[/Quote]
对的

心中的彩虹 2010-12-06

打赏
举报

[Quote=引用 6 楼 gelyon 的回复:]
哇，成哥，被你抢先了一步
[/Quote]

大家进步才是王道

gelyon 2010-12-06

打赏
举报

哇，成哥，被你抢先了一步

gelyon 2010-12-06

打赏
举报





--采用并行查询：

SELECT Sum(

       CASE WHEN (sysdate-birthday)/365>=0 and (sysdate-birthday)/365<=25 THEN 1 

            WHEN (sysdate-birthday)/365>=26 and (sysdate-birthday)/365<=35 THEN 1

            WHEN (sysdate-birthday)/365>=36 and (sysdate-birthday)/365<=45 THEN 1

            WHEN (sysdate-birthday)/365>=46 and (sysdate-birthday)/365<=55 THEN 1

            WHEN (sysdate-birthday)/365>=56 and (sysdate-birthday)/365<=59 THEN 1

            WHEN (sysdate-birthday)/365>=60 THEN 1

       END) as count,

       CASE WHEN (sysdate-birthday)/365>=0 and (sysdate-birthday)/365<=25 THEN '25岁及以下'

            WHEN (sysdate-birthday)/365>=26 and (sysdate-birthday)/365<=35 THEN '26到35岁'

            WHEN (sysdate-birthday)/365>=36 and (sysdate-birthday)/365<=45 THEN '36到45岁'

            WHEN (sysdate-birthday)/365>=46 and (sysdate-birthday)/365<=55 THEN '46到54岁'

            WHEN (sysdate-birthday)/365>=56 and (sysdate-birthday)/365<=59 THEN '55到59岁'

            WHEN (sysdate-birthday)/365>=60 THEN '60岁以上'

       END as tab

from MV_UT_MEMBER 

where INSTR(BELONGDZB,'001.001.032.092')>0

GROUP BY (

       CASE WHEN (sysdate-birthday)/365>=0 and (sysdate-birthday)/365<=25 THEN '25岁及以下'

            WHEN (sysdate-birthday)/365>=26 and (sysdate-birthday)/365<=35 THEN '26到35岁'

            WHEN (sysdate-birthday)/365>=36 and (sysdate-birthday)/365<=45 THEN '36到45岁'

            WHEN (sysdate-birthday)/365>=46 and (sysdate-birthday)/365<=55 THEN '46到54岁'

            WHEN (sysdate-birthday)/365>=56 and (sysdate-birthday)/365<=59 THEN '55到59岁'

            WHEN (sysdate-birthday)/365>=60 THEN '60岁以上'

       END )

心中的彩虹 2010-12-06

打赏
举报

[Quote=引用 3 楼 wkc168 的回复:]
引用楼主 xjgbbs 的回复:
语句是这样的:
select count(1) as count,'25岁及以下' as tab from MV_UT_MEMBER where (sysdate-birthday)/365>=0 and (sysdate-birthday)/365<=25 and INSTR(BELONGDZB,'001.001.032.092')>0
union sel……
[/Quote]



select 

case when (sysdate-birthday)/365>=0 and (sysdate-birthday)/365<=25 then '25岁及以下'

when (sysdate-birthday)/365>=26 and (sysdate-birthday)/365<=35 then '26到35岁'

when (sysdate-birthday)/365>=36 and (sysdate-birthday)/365<=45 then '36到45岁'

when (sysdate-birthday)/365>=46 and (sysdate-birthday)/365<=55 then '46到54岁'

when (sysdate-birthday)/365>=56 and (sysdate-birthday)/365<=59 then '55到59岁'

when (sysdate-birthday)/365>=60 then '60岁以上' end,

count(1) as cnt

from MV_UT_MEMBER

where INSTR(BELONGDZB,'001.001.032.092')>0

group by case when (sysdate-birthday)/365>=0 and (sysdate-birthday)/365<=25 then '25岁及以下'

when (sysdate-birthday)/365>=26 and (sysdate-birthday)/365<=35 then '26到35岁'

when (sysdate-birthday)/365>=36 and (sysdate-birthday)/365<=45 then '36到45岁'

when (sysdate-birthday)/365>=46 and (sysdate-birthday)/365<=55 then '46到54岁'

when (sysdate-birthday)/365>=56 and (sysdate-birthday)/365<=59 then '55到59岁'

when (sysdate-birthday)/365>=60 then '60岁以上' end

心中的彩虹 2010-12-06

打赏
举报

[Quote=引用楼主 xjgbbs 的回复:]
语句是这样的:
select count(1) as count,'25岁及以下' as tab from MV_UT_MEMBER where (sysdate-birthday)/365>=0 and (sysdate-birthday)/365<=25 and INSTR(BELONGDZB,'001.001.032.092')>0
union select count(1) a……
[/Quote]



----INSTR(BELONGDZB,'001.001.032.092') 建个函数索引 



---我的 你试试

select 

case when (sysdate-birthday)/365>=0 and (sysdate-birthday)/365<=25 then '25岁及以下'

when (sysdate-birthday)/365>=26 and (sysdate-birthday)/365<=35 then '26到35岁'

when (sysdate-birthday)/365>=36 and (sysdate-birthday)/365<=45 then '36到45岁'

when (sysdate-birthday)/365>=46 and (sysdate-birthday)/365<=55 then '46到54岁'

when (sysdate-birthday)/365>=56 and (sysdate-birthday)/365<=59 then '55到59岁'

when (sysdate-birthday)/365>=60 then '60岁以上' end,

count(1) as cnt

from MV_UT_MEMBER

where INSTR(BELONGDZB,'001.001.032.092')>0



---你的 把union  改成union all



select '25岁及以下' as tab,count(1) as cnt  from MV_UT_MEMBER 

where (sysdate-birthday)/365>=0 and (sysdate-birthday)/365<=25 and INSTR(BELONGDZB,'001.001.032.092')>0

union all

select '26到35岁' as tab,count(1) as cnt from MV_UT_MEMBER where (sysdate-birthday)/365>=26 and (sysdate-birthday)/365<=35 and INSTR(BELONGDZB,'001.001.032.092')>0

union all

select '36到45岁' as tab,count(1) as cnt from MV_UT_MEMBER where (sysdate-birthday)/365>=36 and (sysdate-birthday)/365<=45 and INSTR(BELONGDZB,'001.001.032.092')>0

union all

select '46到54岁' as tab,count(1) as cnt from MV_UT_MEMBER where (sysdate-birthday)/365>=46 and (sysdate-birthday)/365<=55 and INSTR(BELONGDZB,'001.001.032.092')>0

union all

select '55到59岁' as tab,count(1) as cnt from MV_UT_MEMBER where (sysdate-birthday)/365>=56 and (sysdate-birthday)/365<=59 and INSTR(BELONGDZB,'001.001.032.092')>0

union all

select '60岁以上' as tab,count(1) as cnt from MV_UT_MEMBER 

where (sysdate-birthday)/365>=60 and INSTR(BELONGDZB,'001.001.032.092')>0