1. analyze schema
2. ALTER SESSION SET OPTIMIZER_GOAL=CHOOSE
3. Run again .
2. ALTER SESSION SET OPTIMIZER_GOAL=CHOOSE
3. Run again .
解决方案 »
- 求数据库迁移(Oracle to MySQL)的工具/方法
- 数据插入问题,在线等.急,谢!
- 请教赋予权限grant问题
- 大家帮帮忙啦!!!
- 版本是否兼容的问题
- 高人?高手?敢试吗?ODBC connect Oracle 9i,高分送
- ORACLE里不能用LEFT语句吗?帮我看看
- 这个是什么意思?
- 【delphi+oracle报表解决方案(二)】临时表的使用---关键字:oracle,delphi,报表,临时表,存储过程
- 谁知道哪里有oracle 内部函数一览(包含帮助)下载?
- ORACLE能不能象MS SQL Server2000一样? 直接生成XML?
- 哪里有比较完整的sql server与oracle的sql语法比较?
(select a.mtr_id as mtr_id,round(sum(mtr_num*mtr_price)/sum(mtr_num),2) as mtr_price from mtr_stk_rec a, acc_mtr_master b where a.corp='TW23722471' and a.corp = b.corp and doc_date>='20020701'and doc_date<='20020731'and rsn_id= '1'and mtr_status = '+' and a.mtr_id = b.mtr_id and (b.mtr_type = '1' or b.mtr_type ='4') and
a.mtr_id not in(select mtr_id from mon_stk_rec_cost where corp = 'TW23722471'
and mtr_date = '20020601' and rsn_id = 'END')
group by a.mtr_id
)
我们先撇开它,你的语句就变成了
update mon_stk_rec_cost
set mtr_price =
(select mtr_price from a
where a.mtr_id = mon_stk_rec_cost.MTR_ID)
where corp = 'TW23722471' and mtr_date = '20020701'
and mtr_id not in
(select mtr_id from mon_stk_rec_cost where corp = 'TW23722471'
and mtr_date = '20020601' and rsn_id = 'END')
你再看看
select mtr_id from mon_stk_rec_cost where corp = 'TW23722471'
and mtr_date = '20020601' and rsn_id = 'END'
的执行时间是多少
select * from mon_stk_rec_cost
where corp = 'TW23722471' and mtr_date = '20020701'
的执行时间是多少。
一样的表
你的where这么写好一点,不要用not in
where corp = 'TW23722471' and mtr_date = '20020701'
and (corp = 'TW23722471' and mtr_date = '20020601'
and rsn_id = 'END')
不可以吗?
not in不仅慢,还很危险,如果子查询中的字段有NULL,则主查询的记录为0。
还有,你该把mon_stk_rec_cost 的字段corp和mtr_date 建立索引
where corp = 'TW23722471' and mtr_date = '20020701'
and not (corp = 'TW23722471' and mtr_date = '20020601'
and rsn_id = 'END')