在做ebs11.0的二次开发时,碰到这个问题,
我做了一下测试,发现并没有影响
例:
select *
from inv.mtl_material_transactions t
where t.organization_id = 4
--and t.transaction_date < trunc(to_date('&f_date', 'yymmdd
-------使用索引是MTL_MATERIAL_TRANSACTIONS_N5,列是ORGANIZATION_ID, TRANSACTION_DATEselect *
from inv.mtl_material_transactions t
where t.organization_id = 4
and trunc(t.transaction_date)<to_date('&f_date', 'yymmdd') ---
-------使用索引是MTL_MATERIAL_TRANSACTIONS_N5,列是ORGANIZATION_ID, TRANSACTION_DATE select *
from inv.mtl_material_transactions t
where t.organization_id = 4
and t.transaction_date < trunc(to_date('&f_date', 'yymmdd'))
-------使用索引是MTL_MATERIAL_TRANSACTIONS_N5,列是ORGANIZATION_ID, TRANSACTION_DATE
请兄弟们帮忙分析一下
我做了一下测试,发现并没有影响
例:
select *
from inv.mtl_material_transactions t
where t.organization_id = 4
--and t.transaction_date < trunc(to_date('&f_date', 'yymmdd
-------使用索引是MTL_MATERIAL_TRANSACTIONS_N5,列是ORGANIZATION_ID, TRANSACTION_DATEselect *
from inv.mtl_material_transactions t
where t.organization_id = 4
and trunc(t.transaction_date)<to_date('&f_date', 'yymmdd') ---
-------使用索引是MTL_MATERIAL_TRANSACTIONS_N5,列是ORGANIZATION_ID, TRANSACTION_DATE select *
from inv.mtl_material_transactions t
where t.organization_id = 4
and t.transaction_date < trunc(to_date('&f_date', 'yymmdd'))
-------使用索引是MTL_MATERIAL_TRANSACTIONS_N5,列是ORGANIZATION_ID, TRANSACTION_DATE
请兄弟们帮忙分析一下
select *
from inv.mtl_material_transactions t
where t.organization_id = 4
and trunc(t.transaction_date)<to_date('&f_date', 'yymmdd') ---
-------使用索引是MTL_MATERIAL_TRANSACTIONS_N5,列是ORGANIZATION_ID, TRANSACTION_DATE
--不能在列本身上做运算,但这里是组合索引,查询条件里有organization_id,所以不管日期有没有TRUNC,都会走这个索引。
--如果条件里去掉organization_id ,用下面的SQL查询就是全表扫描了
---- where t.transaction_date < trunc(to_date('&f_date', 'yymmdd'))
--或者 where trunc(t.transaction_date)<to_date('&f_date', 'yymmdd')
你把索引里面的两个字段顺序换一下,然后再看下执行计划,就看出区别来了