JDeveloper version : ALL
WebLogic Server version : n.a.
impact level : high
why : annoying to users and performance implications
I would like to post about an issue i am straggling with oracle support since jdeveloper 126.96.36.199.
It is SR 3-5035735611 and referenced Bug 9316480: LOV AUTOCOMPLETE USES LIKE OPER FOR NUMBER, CAUSING FULL TABLE SCAN
By default when you type a value in an input list of values the query is excecuted always with the LIKE operator.
FROM DEPARTMENTS Departments WHERE
(((Departments.DEPARTMENT_ID LIKE ( :vc_temp_1 || '%'))))
And even if the value you entered is valid the LOV popup will open showing all values starting with the value you entered.
so in the attached test cases if you type '10' in the departmentId the LOV will open showing 10, 100, 101 etc.
This is very annoying for users that do heads-down data entry. They make change requests for us to create custom made popups for LOVs.
Also if the field is a number (like the departmentId) the executed query force full table scan even though it is the primary key.
In Large List of values this causes performance issues.
I understand this functionality is designed for the auto-suggest behavior, yet there is no way to override it.
We just need the option to disable auto-suggest behavior (on design time) so that the query is executed with '=' operator and the LOV does not open when the value entered is valid.