语句如下:
SELECT a.scaa_fee_id, MAX(b.scas_create_time) scas_create_time,
SUM(CASE WHEN b.scas_currency_code = 'CNY' THEN a.scaa_wo_amount ELSE 0 END) scaa_wo_amount_cny,
SUM(CASE WHEN b.scas_currency_code = 'USD' THEN a.scaa_wo_amount ELSE 0 END) scaa_wo_amount_usd,
SUM(CASE WHEN b.scas_currency_code = 'CNY' THEN a.scaa_assign_amount ELSE 0 END) scaa_assign_amount_cny,
SUM(CASE WHEN b.scas_currency_code = 'USD' THEN a.scaa_assign_amount ELSE 0 END) scaa_assign_amount_usd,
SUM(a.scaa_wo_amount)scaa_wo_amount,
MAX(b.SCAS_APPLY_STATUS)SCAS_APPLY_STATUS,
MAX((select c.cusr_name
from cuser c
where c.cusr_user_id = A.SCAA_CREATOR)) SCAA_CREATOR,
MAX(B.scas_cash_no) scas_cash_no,
MAX(B.SCAS_CURRENCY_CODE)SCAS_CURRENCY_CODE,
MAX(B.SCAS_OCCUR_TIME)SCAS_OCCUR_TIME,
MAX(B.SCAS_REMARK)SCAS_REMARK
FROM scash b,scash_apply a WHERE a.scaa_cash_id = b.scas_cash_id GROUP BY a.scaa_fee_id执行计划如下:
SELECT STATEMENT, GOAL = CHOOSE 21952 1187508 124688340
TABLE ACCESS BY INDEX ROWID JLHYPROD CUSER 2 1 12
INDEX UNIQUE SCAN JLHYPROD PK_CUSER 1 1
SORT GROUP BY 21952 1187508 124688340
HASH JOIN 2722 1188976 124842480
TABLE ACCESS FULL JLHYPROD SCASH 79 47362 2841720
TABLE ACCESS FULL JLHYPROD SCASH_APPLY 1561 1188976 53503920我的表scash_apply上有个索引,字段为scaa_fee_id和scaa_cash_id,我想问下为什么会出这样的执行计划
scash_apply表的记录数为1189100,scash表记录数为47453用NL方式会不会更快,要怎么写,请各位大侠帮帮忙,谢谢
SELECT a.scaa_fee_id, MAX(b.scas_create_time) scas_create_time,
SUM(CASE WHEN b.scas_currency_code = 'CNY' THEN a.scaa_wo_amount ELSE 0 END) scaa_wo_amount_cny,
SUM(CASE WHEN b.scas_currency_code = 'USD' THEN a.scaa_wo_amount ELSE 0 END) scaa_wo_amount_usd,
SUM(CASE WHEN b.scas_currency_code = 'CNY' THEN a.scaa_assign_amount ELSE 0 END) scaa_assign_amount_cny,
SUM(CASE WHEN b.scas_currency_code = 'USD' THEN a.scaa_assign_amount ELSE 0 END) scaa_assign_amount_usd,
SUM(a.scaa_wo_amount)scaa_wo_amount,
MAX(b.SCAS_APPLY_STATUS)SCAS_APPLY_STATUS,
MAX((select c.cusr_name
from cuser c
where c.cusr_user_id = A.SCAA_CREATOR)) SCAA_CREATOR,
MAX(B.scas_cash_no) scas_cash_no,
MAX(B.SCAS_CURRENCY_CODE)SCAS_CURRENCY_CODE,
MAX(B.SCAS_OCCUR_TIME)SCAS_OCCUR_TIME,
MAX(B.SCAS_REMARK)SCAS_REMARK
FROM scash b,scash_apply a WHERE a.scaa_cash_id = b.scas_cash_id GROUP BY a.scaa_fee_id执行计划如下:
SELECT STATEMENT, GOAL = CHOOSE 21952 1187508 124688340
TABLE ACCESS BY INDEX ROWID JLHYPROD CUSER 2 1 12
INDEX UNIQUE SCAN JLHYPROD PK_CUSER 1 1
SORT GROUP BY 21952 1187508 124688340
HASH JOIN 2722 1188976 124842480
TABLE ACCESS FULL JLHYPROD SCASH 79 47362 2841720
TABLE ACCESS FULL JLHYPROD SCASH_APPLY 1561 1188976 53503920我的表scash_apply上有个索引,字段为scaa_fee_id和scaa_cash_id,我想问下为什么会出这样的执行计划
scash_apply表的记录数为1189100,scash表记录数为47453用NL方式会不会更快,要怎么写,请各位大侠帮帮忙,谢谢
解决方案 »
- 一个oracle日志文件
- oracle触发器insert时,数据的连接符问题[在线给分]
- 求助:表中有数据,使用OCI接口却查询不到!
- 在Oracle中查詢SQL Server上的表某個字段要加""(雙引號)才行﹐嗚嗚嗚~~
- 调用function出错
- 怎么这个SQL语句会报错!
- 如何更改apps和applsys密码呀?
- oradebug 如何用?
- 如何将7.03.02版本Oracle数据库导出的文件导入到8.1.6的Oracle数据库中?
- case when 判断添加一个字段和新建一个关系表外连接添加一个字段哪个效率高
- oracle 异构服务通用连接怎么连db2用db_link
- oralce9i 支持 层次查询吗?
1.scash.scaa_cash_id,scash_apply.scas_cash_id均建索引了吗?
2.同4楼,需要过滤条件。