执行计划怪异的问题,求助大神解答,么么哒

qq_30570675 2015-08-13 11:41:12

SQL是统计工单维修材料(tt_ddc_ro_repair_part)的材料费,要关联工单(tt_ddc_repair_order)的维修类型"repair_type_code"
select tl.part_sales_amount as ct,
tl.from_entity,
to_char(tl.send_time, 'yyyy-mm-dd') as index_date
from tt_ddc_ro_repair_part tl
left join tt_ddc_repair_order tro on tl.from_entity = tro.from_entity
and tro.ro_no = tl.ro_no
where tl.is_finished = 12781001
and tro.repair_type_code = 'PTWX'
===========================================================================
SELECT STATEMENT, GOAL = ALL_ROWS 75572 1 93
NESTED LOOPS
NESTED LOOPS 75572 1 93
TABLE ACCESS FULL INFODDC TT_DDC_REPAIR_ORDER 75572 4420 132600
INDEX RANGE SCAN INFODDC FK_RO_PART1 0 1
TABLE ACCESS BY INDEX ROWID INFODDC TT_DDC_RO_REPAIR_PART 0 1 63

单独这么查询时间非常长,说是全表扫描了.

然后我自己瞎试,居然变快了,但是我完全不懂其中的区别,就是我给数据量比较大的表加上了一个"店面"的关联,但是其实本身他也就那么多店面,信息也都是完整的,从文字来看就是所有店等于所有店. (如下) tm_brand_dealer_map 记录所有店面的表
select tl.part_sales_amount as ct,
tl.from_entity,
to_char(tl.send_time, 'yyyy-mm-dd') as index_date
from tt_ddc_ro_repair_part tl
left join tt_ddc_repair_order tro on tl.from_entity = tro.from_entity
and tro.ro_no = tl.ro_no
where tl.is_finished = 12781001
and tro.repair_type_code = 'PTWX'
/*-----------------------------------------------------*/
and exists (select tm.dealer_code
from tm_brand_dealer_map tm
where tm.dr = 0
and tl.from_entity = tm.dealer_code)
/*-----------------------------------------------------------------*/
SELECT STATEMENT, GOAL = ALL_ROWS 10 1 112
NESTED LOOPS
NESTED LOOPS 10 1 112
NESTED LOOPS 10 1 82
SORT UNIQUE 9 13 247
TABLE ACCESS FULL NC60 TM_BRAND_DEALER_MAP 9 13 247
TABLE ACCESS BY INDEX ROWID INFODDC TT_DDC_RO_REPAIR_PART 0 1 63
INDEX RANGE SCAN INFODDC FK_RO_PART1 0 1
INDEX RANGE SCAN INFODDC INX_RO_RO_NO1 0 1
TABLE ACCESS BY INDEX ROWID INFODDC TT_DDC_REPAIR_ORDER 0 2 60

=====================
想求教大神为什么会有这样的区别!!!!此致敬礼

...全文