81,092
社区成员
发帖
与我相关
我的任务
分享
SELECT
DEALER_ID,
DEALER_BUSCODE,
DEALER_NAME,
DEALER_STATUS,
OFFER_ID,
OFFER_NAME,
CHANNEL_ID,
SUB_CHANNEL_ID,
DISTY_ID,
DISTY_BUSCODE,
DISTY_NAME,
REGION_CODE,
REGION_NAME,
O_ACTIVATION_TIME,
P_ACTIVATION_TIME,
ACTIVATION_DATE,
STATE_CODE,
STATE_NAME,
PACKAGE_ID,
PACKAGE_NAME,
SALES_PIC,
MSISDN,
COUNT(1)
FROM UM2_ACTI_DTL_201305
WHERE OFFER_TYPE IN(1,3)
AND ACTIVATION_TYPE = 3
AND OFFER_ID IN(20000001,20000031,20010010)
AND PACKAGE_ID IN(322,197,326,566,318,576,277,374,377,581)
AND CHANNEL_ID IN(1,2,4)
GROUP BY DEALER_ID,PACKAGE_ID,ACTIVATION_DATE
[/quote]
OFFER_ID CHANNEL_ID 有建索引的话 试下这样:
像这样 内容不多的in你用 = 试试看,如果不行我也没办法了额,不了解业务,也不知道应用场景,不好说。
另: 你这查询返回多少数据?[/quote]
in语句的地方用=我也试过,没什么效果。
该查询返回的数据在17w左右[/quote]
17W 的话,慢是正常的,不慢反而不正常了哦~~ [/quote]
谢谢,晚上我自己搞了一下,结果出来了SELECT DEALER_ID, DEALER_BUSCODE, DEALER_NAME, DEALER_STATUS, OFFER_ID, OFFER_NAME, CHANNEL_ID, SUB_CHANNEL_ID, DISTY_ID, DISTY_BUSCODE, DISTY_NAME, REGION_CODE, REGION_NAME, O_ACTIVATION_TIME, P_ACTIVATION_TIME, ACTIVATION_DATE, STATE_CODE, STATE_NAME, PACKAGE_ID, PACKAGE_NAME, SALES_PIC, MSISDN, COUNT(distinct DEALER_ID,PACKAGE_ID,ACTIVATION_DATE),count(*) FROM UM2_ACTI_DTL_201305WHERE OFFER_TYPE IN(1,3) AND ACTIVATION_TYPE = 3 AND OFFER_ID IN(20000001,20000031,20010010) AND PACKAGE_ID IN(322,197,326,566,318,576,277,374,377,581) AND CHANNEL_ID IN(1,2,4) GROUP BY DEALER_ID,PACKAGE_ID,ACTIVATION_DATE
这样效率就高了很多SELECT
DEALER_ID,
DEALER_BUSCODE,
DEALER_NAME,
DEALER_STATUS,
OFFER_ID,
OFFER_NAME,
CHANNEL_ID,
SUB_CHANNEL_ID,
DISTY_ID,
DISTY_BUSCODE,
DISTY_NAME,
REGION_CODE,
REGION_NAME,
O_ACTIVATION_TIME,
P_ACTIVATION_TIME,
ACTIVATION_DATE,
STATE_CODE,
STATE_NAME,
PACKAGE_ID,
PACKAGE_NAME,
SALES_PIC,
MSISDN,
COUNT(1)
FROM UM2_ACTI_DTL_201305
WHERE OFFER_TYPE IN(1,3)
AND ACTIVATION_TYPE = 3
AND OFFER_ID IN(20000001,20000031,20010010)
AND PACKAGE_ID IN(322,197,326,566,318,576,277,374,377,581)
AND CHANNEL_ID IN(1,2,4)
GROUP BY DEALER_ID,PACKAGE_ID,ACTIVATION_DATE
[/quote]
OFFER_ID CHANNEL_ID 有建索引的话 试下这样:
像这样 内容不多的in你用 = 试试看,如果不行我也没办法了额,不了解业务,也不知道应用场景,不好说。
另: 你这查询返回多少数据?[/quote]
in语句的地方用=我也试过,没什么效果。
该查询返回的数据在17w左右[/quote]
17W 的话,慢是正常的,不慢反而不正常了哦~~ SELECT
DEALER_ID,
DEALER_BUSCODE,
DEALER_NAME,
DEALER_STATUS,
OFFER_ID,
OFFER_NAME,
CHANNEL_ID,
SUB_CHANNEL_ID,
DISTY_ID,
DISTY_BUSCODE,
DISTY_NAME,
REGION_CODE,
REGION_NAME,
O_ACTIVATION_TIME,
P_ACTIVATION_TIME,
ACTIVATION_DATE,
STATE_CODE,
STATE_NAME,
PACKAGE_ID,
PACKAGE_NAME,
SALES_PIC,
MSISDN,
COUNT(1)
FROM UM2_ACTI_DTL_201305
WHERE OFFER_TYPE IN(1,3)
AND ACTIVATION_TYPE = 3
AND OFFER_ID IN(20000001,20000031,20010010)
AND PACKAGE_ID IN(322,197,326,566,318,576,277,374,377,581)
AND CHANNEL_ID IN(1,2,4)
GROUP BY DEALER_ID,PACKAGE_ID,ACTIVATION_DATE
[/quote]
OFFER_ID CHANNEL_ID 有建索引的话 试下这样:
像这样 内容不多的in你用 = 试试看,如果不行我也没办法了额,不了解业务,也不知道应用场景,不好说。
另: 你这查询返回多少数据?[/quote]
in语句的地方用=我也试过,没什么效果。
该查询返回的数据在17w左右SELECT
DEALER_ID,
DEALER_BUSCODE,
DEALER_NAME,
DEALER_STATUS,
OFFER_ID,
OFFER_NAME,
CHANNEL_ID,
SUB_CHANNEL_ID,
DISTY_ID,
DISTY_BUSCODE,
DISTY_NAME,
REGION_CODE,
REGION_NAME,
O_ACTIVATION_TIME,
P_ACTIVATION_TIME,
ACTIVATION_DATE,
STATE_CODE,
STATE_NAME,
PACKAGE_ID,
PACKAGE_NAME,
SALES_PIC,
MSISDN,
COUNT(1)
FROM UM2_ACTI_DTL_201305
WHERE OFFER_TYPE IN(1,3)
AND ACTIVATION_TYPE = 3
AND OFFER_ID IN(20000001,20000031,20010010)
AND PACKAGE_ID IN(322,197,326,566,318,576,277,374,377,581)
AND CHANNEL_ID IN(1,2,4)
GROUP BY DEALER_ID,PACKAGE_ID,ACTIVATION_DATE
[/quote]
OFFER_ID CHANNEL_ID 有建索引的话 试下这样:
像这样 内容不多的in你用 = 试试看,如果不行我也没办法了额,不了解业务,也不知道应用场景,不好说。
另: 你这查询返回多少数据?SELECT
DEALER_ID,
DEALER_BUSCODE,
DEALER_NAME,
DEALER_STATUS,
OFFER_ID,
OFFER_NAME,
CHANNEL_ID,
SUB_CHANNEL_ID,
DISTY_ID,
DISTY_BUSCODE,
DISTY_NAME,
REGION_CODE,
REGION_NAME,
O_ACTIVATION_TIME,
P_ACTIVATION_TIME,
ACTIVATION_DATE,
STATE_CODE,
STATE_NAME,
PACKAGE_ID,
PACKAGE_NAME,
SALES_PIC,
MSISDN,
COUNT(1)
FROM UM2_ACTI_DTL_201305
WHERE OFFER_TYPE IN(1,3)
AND ACTIVATION_TYPE = 3
AND OFFER_ID IN(20000001,20000031,20010010)
AND PACKAGE_ID IN(322,197,326,566,318,576,277,374,377,581)
AND CHANNEL_ID IN(1,2,4)
GROUP BY DEALER_ID,PACKAGE_ID,ACTIVATION_DATE