Why is my SQL query slower after upgrading from Oracle 9.x to 10.x or above?
Oracle 10g started using the cost-based optimizer rather than the rules-based optimizer used on 9.x. This could cause queries to behave differently.
Starting from 4.1.1 FP3, IS software added the 'ORDER BY' clause for a key condition to the SQL SELECT statement to guarantee the query result would be in sorted order. This sorted order result matches the behavior on IS with Oracle 9.x.
The 'ORDER BY' clause can affect query performance if the database is not properly tuned. Therefore, IS software provides a trigger file to turn off the 'ORDER BY' clause in the SQL SELECT statement to improve performance. If the trigger file exists, the 'ORDER BY' clause will not be used.
Trigger File to disable "order by"