执行计划小弟我已经截图传到附件里面去了
SQL是这样写的:
select 会员卡号,会员姓名,生日,性别,手机号,
(select ty.id_type_desc from t_C_Customer_Certificate cer , t_sd_id_type ty
where cer.id_type_id=ty.id_type_id and cer.cust_certificate_no=a.证件号码 and rownum=1 ) 证件类别,
证件号码,
( SELECT MAX(INS_INVALID_DATE) FROM T_C_INSURANCE_RECORD ,T_MC_CARD_USED
WHERE T_C_INSURANCE_RECORD.CUSTOMER_ID=T_MC_CARD_USED.CUSTOMER_ID AND CARD_ID=A.会员卡号) 失效日期 from (
select 会员卡号,会员姓名,生日,性别,手机号,max(证件号码)证件号码
from (
SELECT distinct mc.card_id 会员卡号, c.customer_name 会员姓名,c.birthday 生日,
(case c.customer_sex when 1 then '女' when 2 then '男' else '未知' end) 性别,
f_GetCustomerContactMeans(mc.customer_id) 手机号,
cer.cust_certificate_no 证件号码
FROM t_mc_card_used mc , t_c_customer c
, t_C_Customer_Certificate cer
WHERE
mc.customer_id=c.customer_id and
cer.customer_id(+)=c.Customer_ID and
mc.card_id in ( '9001347708','90200174778','9001587738'
)) group by 会员卡号,会员姓名,生日,性别,手机号)a
执行计划:
Description 对象所有者 对象名称 耗费 基数 字节
SELECT STATEMENT, GOAL = ALL_ROWS 24 3 7332
COUNT STOPKEY
NESTED LOOPS 5 1 33
TABLE ACCESS BY INDEX ROWID GC T_C_CUSTOMER_CERTIFICATE 4 1 21
INDEX RANGE SCAN GC INDEX_T_C_CUSTOMER_CE_NAME 3 1
TABLE ACCESS BY INDEX ROWID GC T_SD_ID_TYPE 1 1 12
INDEX UNIQUE SCAN GC PK_T_SD_ID_TYPE 0 1
SORT AGGREGATE 1 33
TABLE ACCESS BY INDEX ROWID GC T_C_INSURANCE_RECORD 3 1 13
NESTED LOOPS 7 1 33
TABLE ACCESS BY INDEX ROWID GC T_MC_CARD_USED 4 1 20
INDEX RANGE SCAN GC IDX$$_0C510001 3 1
INDEX RANGE SCAN GC CLIENTBINFO_INSURANCELOG_FK 2 1
VIEW GC 24 3 7332
HASH GROUP BY 24 3 201
NESTED LOOPS OUTER 23 3 201
NESTED LOOPS 14 3 132
INLIST ITERATOR
TABLE ACCESS BY INDEX ROWID GC T_MC_CARD_USED 8 3 60
INDEX RANGE SCAN GC IDX$$_0C510001 5 3
TABLE ACCESS BY INDEX ROWID GC T_C_CUSTOMER 2 1 24
INDEX UNIQUE SCAN GC PK_T_C_CUSTOMER 1 1
TABLE ACCESS BY INDEX ROWID GC T_C_CUSTOMER_CERTIFICATE 3 1 23
INDEX RANGE SCAN GC CLIENTBINFO_CERTIFICATE_FK 2 1
附件是执行计划的截图,看着比较清楚点
22.jpg
(121.15 KB, 下载次数: 6)
2011-5-6 17:02 上传
执行计划截图