这条语句是这样的
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))
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)
这个语句毛病 挺多的啊 ,你能编译通过?? 两个WHERE 条件,且TBL_OPERATION_HISTORY.record_id 既然是主键,那么你第二个条件
{(SELECT MAX(RECORD_ID)
FROM TBL_OPERATION_HISTORY
WHERE OPERATE_STATUS = 'N'
AND OPERATION_RECORD_ID = B.RECORD_ID)}
就能找出唯一记录了,何必在加前面的条件,
TBL_OPERATION_HISTORY 这张表相对主表 数据量相差大不大,如不大不要用子查询,子查询每条计量都要访问改表一次,
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,
d.OPERATE_USER_ID,
TO_CHAR(OPERATE_DATE, 'YYYY/mm/dd') AS 添加日期,
OPERATION_TYPE_CODE
FROM TBL_OPERATION_MANAGE B,(SELECT c.OPERATION_RECORD_ID,MAX(C.OPERATE_USER_ID) AS OPERATE_USER_ID
FROM TBL_OPERATION_HISTORY C
WHERE c.OPERATE_STATUS = 'N'
GROUP BY c.OPERATION_RECORD_ID) D
WHERE TO_CHAR(B.OPERATE_DATE, 'YYYY/mm/dd') BETWEEN '2009/01/16' AND
'2009/02/16'
AND SPECIAL_SIGN <> 'Y'
AND OPERATION_STATUS_CODE = 'U'
AND c.RECORD_ID = d.OPERATION_RECORD_ID(+);
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、设置好还得查看执行计划/统计信息....
有没有方法能查看sql语句使用的资源有没有和其它的语句冲突等?