关于EBS Form 的LOV长值列表 查询效率异常问题处理

最近用户经常反馈和任务单相关的查询界面非常慢。并且都是很精确的查询。
正常来说，选择性好的查询（直接输入任务单的唯一编号了）应该速度很快的。
但是，为何这个查询慢（超过30秒）？不合理的现象，而且还是标准功能：

跟踪了这个查询对应的SQL，发现是这样子的：

  SELECT wip_entity_name, wip_entity_id
    FROM wip_entities
   WHERE (UPPER(WIP_ENTITY_NAME) LIKE :1
      AND (WIP_ENTITY_NAME LIKE :2
        OR WIP_ENTITY_NAME LIKE :3
        OR WIP_ENTITY_NAME LIKE :4
        OR WIP_ENTITY_NAME LIKE :5))
     AND (organization_id = :6)
ORDER BY wip_entity_name

然后看绑定变量，是这样子的：

Binds*
=================================================
| Name | Position | Type         | Value        |
=================================================
| :1   |        1 | VARCHAR2(32) | SL170221390% |
| :2   |        2 | VARCHAR2(32) | sl%          |
| :3   |        3 | VARCHAR2(32) | sL%          |
| :4   |        4 | VARCHAR2(32) | Sl%          |
| :5   |        5 | VARCHAR2(32) | SL%          |
| :6   |       *6 | NUMBER       | 189          |
=================================================

再看执行计划：

从SQL以及对应绑定变量，还有执行计划，可以看出：
这个准确的查询，被Oracle自动添加了一个Upper函数，从而无法用到选择性最好的关键字过滤，导致查询缓慢。而且会随着数据量的增加，这个查询会越来越慢的。

真的很奇怪，查询的时候，明明我输入了SL170221390，在LOV过滤的时候应该直接用：
WIP_ENTITY_NAME LIKE :1
就是：WIP_ENTITY_NAME LIKE ‘SL170221390%’就可以查询到结果了啊，这样子也是最有效率的。为什么偏偏要加一个UPPER函数呢？
经过不断的测试，加上本人对EBS系统的处理逻辑的理解，我确认了应该是这个原因导致：
原来是Oracle的LOV长值列表的自动处理逻辑导致：长值列表 的关键字查询 不区分大小写！
也就是说，LOV：Let the user filter records before dislaying them(Fliter Before Display)设置为Yes的查询，在做Fliter的时候，关键字自动做了不区分大小写的效果！
正是因为Oracle要达到这个效果，所以才自动套一个转大写的函数来做查询。
举个简单的例子：
在Lov里面，查询WORK_REQUEST_FNAME段：erp%
如果不区分大小写，则LOV查询的时候，自动转换为：
AND (UPPER(WORK_REQUEST_FNAME) LIKE ‘ERP%’
AND (WORK_REQUEST_FNAME LIKE ‘er%’
OR WORK_REQUEST_FNAME LIKE ‘eR%’
OR WORK_REQUEST_FNAME LIKE ‘Er%’
OR WORK_REQUEST_FNAME LIKE ‘ER%’))
这样子，所有包括ERP%关键字(不区分大小写)的数据都会搜索出来。

对不区分大小写的查询的处理探索：

--根据结果，猜测其处理的规律：
首先系统看查询的关键字是否有包括字母。
如果完全没包括字母，就是查询的关键字是纯数字，则默认是最有效率的查询：EMPLOYEE_NUMBER LIKE :1
只要是包括字母，则默认当前字段是自动添加函数：UPPER(EMPLOYEE_NUMBER) LIKE :1 （注意这里的:1是自动大写了，例如2144wsB92 会自动变为：2144WSB92%）
然后，自动添加前2个字符的各种不同的组合来做查询！
例如：
wsb214492：4种可能性 ws  WS  Ws wS
w214492：2种可能性 W2 w2
214492www：1种可能性 21

不得不说，这个效果还是很不错的。因为很多时候用户查询的时候确实可能大小写混用，导致查询没结果。
不过这个处理逻辑的副作用就是用不到该段的索引！最郁闷的是，目前没发现哪里可以设定！（如果哪个兄台知道这个的设定的麻烦留言一下，十分感谢！）

所以，针对这个问题，目前的解决办法只好是：在长值列表的主要搜索字段添加一个UPPER的函数索引。这样子就算LOV自动帮我们加一个UPPER函数，也可以用到对应段的索引了。

CREATE UNIQUE INDEX WIP.WIP_ENTITIES_U3 ON WIP.WIP_ENTITIES
(UPPER(WIP_ENTITY_NAME), ORGANIZATION_ID)
NOLOGGING
TABLESPACE APPS_TS_TX_IDX;

添加之后的查询执行计划是：

SQL_ID  dxcuk5cb89qk5, child number 0
-------------------------------------
select wip_entity_name, wip_entity_id from wip_entities where ( 
UPPER(WIP_ENTITY_NAME) LIKE :1 AND (WIP_ENTITY_NAME LIKE :2 OR 
WIP_ENTITY_NAME LIKE :3 OR WIP_ENTITY_NAME LIKE :4 OR WIP_ENTITY_NAME 
LIKE :5)) AND ( organization_id = :6 ) order by wip_entity_name

Plan hash value: 269543263

-------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name            | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                 |        |       |    13 (100)|          |
|   1 |  SORT ORDER BY               |                 |      1 |   145 |    13   (8)| 00:00:01 |
|*  2 |   TABLE ACCESS BY INDEX ROWID| WIP_ENTITIES    |      1 |   145 |    12   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | WIP_ENTITIES_U3 |     20 |       |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------

至此，问题得到解决！
其实我觉得最好的解决办法应该是有一个这样子的设定：LOV过滤不区分大小写。默认为Yes，如果设置为No，则就是区分大小写的查询处理，没必要添加什么UPPER函数！

备注：
Form表单的栏位有一个属性：Case Insensitive Query，默认为No(Flase)，其实就是这个意思。如果设置为Yes，则您按F11做栏位的查询的时候，会自动添加不区分大小写的查询逻辑。有兴趣的兄台可以试试！