这条语句是这样的
select RECORD_ID,
MEMBER_ID,
MEMBER_NAME,
MEMBER_LEVEL,
decode(OPERATION_STATUS_CODE,'N','创建','U','上呈','A','已初审','B','已复审','Y','已一次处理','E','拒绝','X','已删除','F','已结案','创建') as OPERATION_STATUS_CODE,
LOCK_SIGN,
LOCK_USER_ID,
(select OPERATE_USER_ID from TBL_OPERATION_HISTORY c where OPERATE_STATUS='N' and OPERATION_RECORD_ID= b.RECORD_ID where RECORD_ID=(select max(RECORD_ID) from TBL_OPERATION_HISTORY where OPERATE_STATUS='N' and OPERATION_RECORD_ID= b.RECORD_ID)) as OPERATE_USER_ID ,
to_char(OPERATE_DATE,'YYYY/mm/dd') as 添加日期,
OPERATION_TYPE_CODE from TBL_OPERATION_MANAGE b
where to_char(OPERATE_DATE,'YYYY/mm/dd')
between '2009/01/16' and '2009/02/16'and
special_sign <> 'Y' and OPERATION_STATUS_CODE='U'
现在发现是这个语句慢,如果没有下面这个子查询就很快
(select OPERATE_USER_ID from TBL_OPERATION_HISTORY c where OPERATE_STATUS='N' and OPERATION_RECORD_ID= b.RECORD_ID where RECORD_ID=(select max(RECORD_ID) from TBL_OPERATION_HISTORY where OPERATE_STATUS='N' and OPERATION_RECORD_ID= b.RECORD_ID)) as OPERATE_USER_ID
TBL_OPERATION_MANAGE.record_id是主键,TBL_OPERATION_HISTORY.record_id是主键
怎样优化?如果给表加索引怎么加?
select RECORD_ID,
MEMBER_ID,
MEMBER_NAME,
MEMBER_LEVEL,
decode(OPERATION_STATUS_CODE,'N','创建','U','上呈','A','已初审','B','已复审','Y','已一次处理','E','拒绝','X','已删除','F','已结案','创建') as OPERATION_STATUS_CODE,
LOCK_SIGN,
LOCK_USER_ID,
(select OPERATE_USER_ID from TBL_OPERATION_HISTORY c where OPERATE_STATUS='N' and OPERATION_RECORD_ID= b.RECORD_ID where RECORD_ID=(select max(RECORD_ID) from TBL_OPERATION_HISTORY where OPERATE_STATUS='N' and OPERATION_RECORD_ID= b.RECORD_ID)) as OPERATE_USER_ID ,
to_char(OPERATE_DATE,'YYYY/mm/dd') as 添加日期,
OPERATION_TYPE_CODE from TBL_OPERATION_MANAGE b
where to_char(OPERATE_DATE,'YYYY/mm/dd')
between '2009/01/16' and '2009/02/16'and
special_sign <> 'Y' and OPERATION_STATUS_CODE='U'
现在发现是这个语句慢,如果没有下面这个子查询就很快
(select OPERATE_USER_ID from TBL_OPERATION_HISTORY c where OPERATE_STATUS='N' and OPERATION_RECORD_ID= b.RECORD_ID where RECORD_ID=(select max(RECORD_ID) from TBL_OPERATION_HISTORY where OPERATE_STATUS='N' and OPERATION_RECORD_ID= b.RECORD_ID)) as OPERATE_USER_ID
TBL_OPERATION_MANAGE.record_id是主键,TBL_OPERATION_HISTORY.record_id是主键
怎样优化?如果给表加索引怎么加?
第一个from 后面有两个where ,在语法上面已经有问题了,是不是把第二个where去掉?
(select OPERATE_USER_ID
from TBL_OPERATION_HISTORY c
where -->第一个where
OPERATE_STATUS='N' and OPERATION_RECORD_ID= b.RECORD_ID
where -->第二个where
RECORD_ID=(select max(RECORD_ID) from TBL_OPERATION_HISTORY where OPERATE_STATUS='N' and OPERATION_RECORD_ID= b.RECORD_ID))
1、子查询语法已有错...两个Where
2、where to_char(OPERATE_DATE,'YYYY/mm/dd') between '2009/01/16' and '2009/02/
不要加入函数..改为
where OPERATE_DATE between to_date( '2009/01/16','YYYYMMDD') and to_date( '2009/02/16','YYYYMMDD')
3、索引字段OPERATE_DATE、special_sign、OPERATION_STATUS_CODE、OPERATE_STATUS、OPERATION_RECORD_ID...
4、设置好还得查看执行计划/统计信息....