请教各位大侠,为何我在写SQL的时候查看解释计划上面显示都是走索引了,但是为何耗费还是那么大呢?还有请大家介绍点资料教教怎么看解释计划来优化SQL,谢谢.下面是我的解释计划
------------------------------------------------------------------------------------------------------------
SELECT STATEMENT, GOAL = ALL_ROWS 11859 1 226
TABLE ACCESS BY INDEX ROWID ICARE_F2 T_BSE_CHARGEITEMEXTYPE 2 1 13
NESTED LOOPS 11859 1 226
NESTED LOOPS 11857 1 213
NESTED LOOPS 11856 1 187
NESTED LOOPS 11852 1 106
NESTED LOOPS 6487 2681 190351
TABLE ACCESS BY INDEX ROWID ICARE_F2 T_OPR_OUTPATIENTRECIPEINV 1122 2681 109921
INDEX RANGE SCAN ICARE_F2 I_IDX_OUTPATIENTRECIPEBDATE 10 2682
TABLE ACCESS BY INDEX ROWID ICARE_F2 T_OPR_OUTPATIENTRECIPE 2 1 30
INDEX UNIQUE SCAN ICARE_F2 SYS_C0080542 1 1
TABLE ACCESS BY INDEX ROWID ICARE_F2 T_OPR_PATIENTREGISTER 2 1 35
INDEX UNIQUE SCAN ICARE_F2 SYS_C0080584 1 1
TABLE ACCESS BY INDEX ROWID ICARE_F2 T_OPR_OUTPATIENTCHKRECIPEDE 4 6 486
INDEX RANGE SCAN ICARE_F2 I_IDX_OUTPATIENTCHK 2 6
TABLE ACCESS BY INDEX ROWID ICARE_F2 T_BSE_CHARGEITEM 1 1 26
INDEX UNIQUE SCAN ICARE_F2 SYS_C0078816 0 1
INDEX RANGE SCAN ICARE_F2 SYS_C0078828 1 1
------------------------------------------------------------------------------------------------------------
SELECT STATEMENT, GOAL = ALL_ROWS 11859 1 226
TABLE ACCESS BY INDEX ROWID ICARE_F2 T_BSE_CHARGEITEMEXTYPE 2 1 13
NESTED LOOPS 11859 1 226
NESTED LOOPS 11857 1 213
NESTED LOOPS 11856 1 187
NESTED LOOPS 11852 1 106
NESTED LOOPS 6487 2681 190351
TABLE ACCESS BY INDEX ROWID ICARE_F2 T_OPR_OUTPATIENTRECIPEINV 1122 2681 109921
INDEX RANGE SCAN ICARE_F2 I_IDX_OUTPATIENTRECIPEBDATE 10 2682
TABLE ACCESS BY INDEX ROWID ICARE_F2 T_OPR_OUTPATIENTRECIPE 2 1 30
INDEX UNIQUE SCAN ICARE_F2 SYS_C0080542 1 1
TABLE ACCESS BY INDEX ROWID ICARE_F2 T_OPR_PATIENTREGISTER 2 1 35
INDEX UNIQUE SCAN ICARE_F2 SYS_C0080584 1 1
TABLE ACCESS BY INDEX ROWID ICARE_F2 T_OPR_OUTPATIENTCHKRECIPEDE 4 6 486
INDEX RANGE SCAN ICARE_F2 I_IDX_OUTPATIENTCHK 2 6
TABLE ACCESS BY INDEX ROWID ICARE_F2 T_BSE_CHARGEITEM 1 1 26
INDEX UNIQUE SCAN ICARE_F2 SYS_C0078816 0 1
INDEX RANGE SCAN ICARE_F2 SYS_C0078828 1 1
解决方案 »
- 急!!!改ip后,出现协议适配器不可加载的错误,但是配置文件使用的是host名,不解为什么会出现这个错误,请高人解答
- 记录profile的数据表是哪个呢?
- 这个sql怎么写。
- create
- 安装plsqlDeveloper 问题
- 如何提高批量更新某一列的速度??
- 请教oracle表名中的前缀、后缀的含义。
- oracle 安装时错误请教??急!!
- 我用的是oracle 8.1.6,实例创建完成后,可以启动,但运行一段时间后,在把该实例关掉,服务无法启动,但在svrmgrl中可以手工启动,是什
- 8.1.6升级到8.1.7后出现监听程序无法启动专用服务器进程
- 错哪了???提示缺少右括号。。T_T
- 查询树的问题,急啊~!
以前看过这个:http://wenku.baidu.com/view/10678c4733687e21af45a92c.html
讲的比较详细
具体情况具体分析
给出具体的SQL及数据量大小,查询出的数据量大小等。
select a.registerid_chr,
a.patientcardid_chr,
c.lastname_vchr,
d.code_vchr,
d.deptname_vchr,
a.registerdate_dat,
e.empno_chr,
e.lastname_vchr doctor
from t_opr_patientregister a,
t_bse_patientcard b,
t_bse_patient c,
t_bse_deptdesc d,
t_bse_employee e,
(select distinct (f.registerid_chr) as registerid_chr
from t_opr_outpatientrecipe f, t_opr_outpatientrecipeinv j
where f.outpatrecipeid_chr = j.outpatrecipeid_chr
and f.registerid_chr is not null
and j.balance_dat between to_date('2007-12-16 0:00:00', 'yyyy-mm-dd hh24:mi:ss') and
to_date('2007-12-16 23:59:59', 'yyyy-mm-dd hh24:mi:ss')) h
where c.patientid_chr = a.patientid_chr
and d.deptid_chr = a.diagdept_chr(+)
and a.diagdoctor_chr = e.empid_chr(+)
and b.patientcardid_chr = a.patientcardid_chr
and a.registerid_chr = h.registerid_chr数据量:查询结果200条
其中几个大表的数据量为:
t_opr_patientregister ; --200W ;
t_bse_patientcard ; --80W
t_bse_patient ; --80W
t_opr_outpatientrecipe ;--350W
t_opr_outpatientrecipeinv ; --320W
解释计划为:
SELECT STATEMENT, GOAL = ALL_ROWS 12491 1371 207021
NESTED LOOPS 12491 1371 207021
HASH JOIN RIGHT OUTER 9754 1368 181944
TABLE ACCESS FULL ICARE_F2 T_BSE_EMPLOYEE 15 1248 26208
HASH JOIN 9738 1368 153216
TABLE ACCESS FULL ICARE_F2 T_BSE_DEPTDESC 15 233 6524
HASH JOIN 9723 1368 114912
NESTED LOOPS 9181 1369 99937
VIEW ICARE_F2 6488 2681 53620
HASH UNIQUE 6488 2681 152817
NESTED LOOPS 6487 2681 152817
TABLE ACCESS BY INDEX ROWID ICARE_F2 T_OPR_OUTPATIENTRECIPEINV 1122 2681 72387
INDEX RANGE SCAN ICARE_F2 I_IDX_OUTPATIENTRECIPEBDATE 10 2682
TABLE ACCESS BY INDEX ROWID ICARE_F2 T_OPR_OUTPATIENTRECIPE 2 1 30
INDEX UNIQUE SCAN ICARE_F2 SYS_C0080542 1 1
TABLE ACCESS BY INDEX ROWID ICARE_F2 T_OPR_PATIENTREGISTER 2 1 53
INDEX UNIQUE SCAN ICARE_F2 SYS_C0080584 1 1
INDEX FAST FULL SCAN ICARE_F2 I_IDX_PATIENTCARD 538 789687 8686557
TABLE ACCESS BY INDEX ROWID ICARE_F2 T_BSE_PATIENT 2 1 18
INDEX UNIQUE SCAN ICARE_F2 IDX$$_EF500002 1 1