oracle存储过程，使用变量时会执行的特别慢

[凌空寻宇]

语句为
insert into kwdl
select ... from klt
inner join mwl
on klt.loan_no = mwl.loan_no
and klt.datadate between to_date('2018-07-20','yyyy-mm-dd') and to_date('2018-07-20','yyyy-mm-dd')
and mwl.weightingend = to_date('2999,12,31','yyyy-mm-dd')
where klt.calc_flag = 'y'
and klt.separateness = 'n'
order by klt.loan_no,mwl.weighting_index
其中klt表大概有140W行数据，calc_flag,separateness,datadate均有索引

当单独执行这段语句时，1秒钟就能跑完，插入11138行。
但是如果将上述加粗字段在存储过程中用变量代入，就需要跑5-7分钟。（前后输出时间计算得出）

查看存储计划，当写死时KLT将会走索引，cardinality为3332
但是如果用变量则为full，cardinality为625232

请问这是什么原因？应该如何解决？

[凌空寻宇]

引用 1 楼 minsic78 的回复:

一定要写成between吗？不能写等于？是不是可以取一个范围，而这个范围是不可控的？
如果可以确定每次实际插入数据量并不多，那么可以考虑添加INDEX提示

我在select后试了两种index提示：
/*+ index(klt klt_calc_separate_datadate)*/（复合索引）
/*+ index(klt klt_calc，klt_separate，klt_datadate)*/（单独索引）
查看执行计划，calc和separate的索引确实都有了，但最关键的datadate的索引都还是没有。
时间仍然是5分钟左右，属于正常波动范围，并没有比原先快

[minsic78]

引用 3 楼 a553942623 的回复:

[quote=引用 1 楼 minsic78 的回复:]
一定要写成between吗？不能写等于？是不是可以取一个范围，而这个范围是不可控的？
如果可以确定每次实际插入数据量并不多，那么可以考虑添加INDEX提示

我在select后试了两种index提示：
/*+ index(klt klt_calc_separate_datadate)*/（复合索引）
/*+ index(klt klt_calc，klt_separate，klt_datadate)*/（单独索引）
查看执行计划，calc和separate的索引确实都有了，但最关键的datadate的索引都还是没有。
时间仍然是5分钟左右，属于正常波动范围，并没有比原先快[/quote]

如果你想使用datadate上的索引，那就只要指定这一条索引就够了，不要列那么多

[凌空寻宇]

引用 4 楼 minsic78 的回复:

[quote=引用 3 楼 a553942623 的回复:]
[quote=引用 1 楼 minsic78 的回复:]
一定要写成between吗？不能写等于？是不是可以取一个范围，而这个范围是不可控的？
如果可以确定每次实际插入数据量并不多，那么可以考虑添加INDEX提示

我在select后试了两种index提示：
/*+ index(klt klt_calc_separate_datadate)*/（复合索引）
/*+ index(klt klt_calc，klt_separate，klt_datadate)*/（单独索引）
查看执行计划，calc和separate的索引确实都有了，但最关键的datadate的索引都还是没有。
时间仍然是5分钟左右，属于正常波动范围，并没有比原先快[/quote]

如果你想使用datadate上的索引，那就只要指定这一条索引就够了，不要列那么多[/quote]

其他索引不是重点呀，关键是就算我只写datadate上的索引/*+ index(klt klt_datadate)*/，执行计划里仍然显示
klt by index rowid batched
klt_datadate full scan
时间也没有缩短

[minsic78]

引用 5 楼 a553942623 的回复:

[quote=引用 4 楼 minsic78 的回复:]
[quote=引用 3 楼 a553942623 的回复:]
[quote=引用 1 楼 minsic78 的回复:]
一定要写成between吗？不能写等于？是不是可以取一个范围，而这个范围是不可控的？
如果可以确定每次实际插入数据量并不多，那么可以考虑添加INDEX提示

我在select后试了两种index提示：
/*+ index(klt klt_calc_separate_datadate)*/（复合索引）
/*+ index(klt klt_calc，klt_separate，klt_datadate)*/（单独索引）
查看执行计划，calc和separate的索引确实都有了，但最关键的datadate的索引都还是没有。
时间仍然是5分钟左右，属于正常波动范围，并没有比原先快[/quote]

如果你想使用datadate上的索引，那就只要指定这一条索引就够了，不要列那么多[/quote]

其他索引不是重点呀，关键是就算我只写datadate上的索引/*+ index(klt klt_datadate)*/，执行计划里仍然显示
klt by index rowid batched
klt_datadate full scan
时间也没有缩短[/quote]

把这个SQL完整的文本和完整的执行计划贴一下

[minsic78]

一定要写成between吗？不能写等于？是不是可以取一个范围，而这个范围是不可控的？
如果可以确定每次实际插入数据量并不多，那么可以考虑添加INDEX提示

[凌空寻宇]

引用 1 楼 minsic78 的回复:

一定要写成between吗？不能写等于？是不是可以取一个范围，而这个范围是不可控的？
如果可以确定每次实际插入数据量并不多，那么可以考虑添加INDEX提示

是这样的，这个日期是一个范围，两个变量分别为开始日期和结束日期。我这里写相同日期是因为跑的时候如果超过一天，运行时间会直接大于10分钟然后断开连接。所以为了测试才写同一个日期，但实际上是一个范围，几天都有可能。

index提示我试一试

[超叔csdn]

查查索引状态，如果索引无效，就不会走索引。
如果索引状态正常，尝试重建索引试试。

[凌空寻宇]

解决了。最后是换了一种查询方式。把klt的datadate条件提前约束，而不是在inner join中，时间就能缩短为几秒了。
insert into kwdl
select ... from (select * from klt where datadate between to_date('2018-07-20','yyyy-mm-dd') and to_date('2018-07-20','yyyy-mm-dd'))
inner join mwl
on klt.loan_no = mwl.loan_no
and mwl.weightingend = to_date('2999,12,31','yyyy-mm-dd')
where klt.calc_flag = 'y'
and klt.separateness = 'n'
order by klt.loan_no,mwl.weighting_index