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

Technote (FAQ)


Question

Why is my SQL query slower after upgrading from Oracle 9.x to 10.x or above?

Cause

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.

Answer

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.

Rate this page:

(0 users)Average rating

Add comments

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 Itanium, HP-UX, Solaris, Windows

Reference #:

1580749

Modified date:

2013-05-03

Translate my page

Machine Translation

Content navigation