IBM Support

FileNet Image Services: Slower SQL query on Image Services with Oracle 10.x or 11.x

Technote (FAQ)


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"

    UNIX: /fnsw/local/trigger/DISABLE_KEY_ORDER_BY
    Windows: \fnsw_loc\trigger\DISABLE_KEY_ORDER_BY
NOTE - Dropping the 'ORDER BY' clause also means that a sorted result is not guaranteed.

Document information

More support for: FileNet Image Services
Image Services

Software version: 4.1.1, 4.1.2, 4.2

Operating system(s): AIX, HP-UX, Solaris, Windows

Reference #: 1580749

Modified date: 03 May 2013

Translate this page: