有两张表,erm_order存放一条活动信息,ERM_CONSUME_DETAIL存放活动中每个用户的消费信息,有很多数据
create table ERM_ORDER
(
ROW_ID NUMBER(15) not null,
SERIAL_NUM VARCHAR2(40) not null,
SITE_NAME VARCHAR2(30)
)create table ERM_CONSUME_DETAIL
(
ROW_ID NUMBER(15) not null,
ERM_STATUS VARCHAR2(30), -- 状态
PARENT_ORDER_ROWID NUMBER(15) not null --外键,引用erm_order表的主键row_id
)
现在要查询用户消费的状态,ERM_CONSUME_DETAIL表ERM_STATUS 代表消费中,未消费,下发中等等select
sum(decode(ecd.erm_status, '002', 1, 0)) a,
sum(decode(ecd.erm_status, '003', 1, 0)) b,
sum(decode(ecd.erm_status, '004', 1, 0)) c,
sum(decode(ecd.erm_status, '005', 1, '001', 1, 0)) d,
eo.serial_num
from erm_order eo
left join erm_consume_detail ecd on eo.row_id = ecd.parent_order_rowid
group by eo.serial_num;
当ERM_CONSUME_DETAIL数据量上百万后,查询速度慢,请问有什么方式可以优化吗?谢谢~~~
create table ERM_ORDER
(
ROW_ID NUMBER(15) not null,
SERIAL_NUM VARCHAR2(40) not null,
SITE_NAME VARCHAR2(30)
)create table ERM_CONSUME_DETAIL
(
ROW_ID NUMBER(15) not null,
ERM_STATUS VARCHAR2(30), -- 状态
PARENT_ORDER_ROWID NUMBER(15) not null --外键,引用erm_order表的主键row_id
)
现在要查询用户消费的状态,ERM_CONSUME_DETAIL表ERM_STATUS 代表消费中,未消费,下发中等等select
sum(decode(ecd.erm_status, '002', 1, 0)) a,
sum(decode(ecd.erm_status, '003', 1, 0)) b,
sum(decode(ecd.erm_status, '004', 1, 0)) c,
sum(decode(ecd.erm_status, '005', 1, '001', 1, 0)) d,
eo.serial_num
from erm_order eo
left join erm_consume_detail ecd on eo.row_id = ecd.parent_order_rowid
group by eo.serial_num;
当ERM_CONSUME_DETAIL数据量上百万后,查询速度慢,请问有什么方式可以优化吗?谢谢~~~
ecd.erm_status,
eo.serial_num
from erm_order eo
left join erm_consume_detail ecd on eo.row_id = ecd.parent_order_rowid
group by eo.serial_num;
你这个查询的速度如何?
2、在erm_order上面的列row_id建索引
3、两个表的数据量分别是多少?
4、贴出执行计划
2.根据表数据量增加hint提示,减少计算cost的时间
select /*+ordered use_hash(eo ecd) parallel(eo 4) parallel(ecd 4) */
sum(decode(ecd.erm_status, '002', 1, 0)) a,
sum(decode(ecd.erm_status, '003', 1, 0)) b,
sum(decode(ecd.erm_status, '004', 1, 0)) c,
sum(decode(ecd.erm_status, '005', 1, '001', 1, 0)) d,
eo.serial_num
from erm_order eo,
erm_consume_detail ecd
where eo.row_id = ecd.parent_order_rowid(+)
group by eo.serial_num;