At a recent customer site (the Oracle version was 18c i.e. 22.214.171.124) we noticed that if Remedy was being run in Case Insensitive mode certain use cases would not return expected rows. Case in point is the "Site" drop down menu in the CTM:People form that returned the expected 72 rows (for this client) when Remedy was in Case Sensitive mode but not when in Case Insensitive mode.
One of the SQL statements associated with the population of the field is shown below (there were other SQLs where a value was entered for the Company field or the Region field or the Site Groupg field - all of them failed to return rows)
SELECT DISTINCT T538.C260000001 FROM T538 WHERE ((((T538.C1000000001 = ' ') OR (' ' = ' ')) AND ((T538.C200000012 = ' ') OR (' ' = ' '))
AND ((T538.C200000007 = ' ') OR (' ' = ' ')) AND (T538.C1000000073 = 0) AND (T538.C1000000081 = 1) AND (T538.C7 = 1)))
AND ( ROWNUM <= 20101 ) ORDER BY T538.C260000001 ASC
We eventually realized that we may be encountering Oracle Bug 27416997 (check Metalink Doc ID 2390584.1).
The Bug has been resolved in Oracle 19.1. For the customer we set one of the parameters mentioned in the Doc (shown below) and the use case started working instantaneously.
alter system set "_optimizer_generate_transitive_pred"=FALSE scope=both