APEX

Hello,
We have just released an Oracle APEX app to the users and ran into a performance issue in the production version that was not in development. The interactive report goes against a simple view that returns Data similar to:

NAME VALUE MIN MAX
BEL123 1 1 3
BEL245 3 3 4
AB222 2 1 3


The issue is that when a filter is used on the name column with a like operand (name LIKE 'BEL%' ). The query performance become very poor (average is 29 seconds to return according to the APEX stats). These are huge tables but the performance in the development area is quick (almost instantaneous). Here are the things I have looked into to try and narrow the problem down:

1) I have tried running the exact same query in SQL*Plus and the performance is quick in both production and development, so its not the indexes.
2) Creating brand new reports from scratch causes the same results in production and development. So its not anything specific that has been changed in the two versions (which are identical)
3) Putting the NAME Like 'BEL%' directly into the SQL clause of the report produces quick returns in production so the problem is not the APEX user not seeing the indexes for some reason. It also ensures that any data differences in the two databases are not the root cause of this.

So my only theory is that somehow the use of the Like filter does not allow the index on the NAME column to be used but only in production. Any ideas ... I am at a loss.

Edited by: user491396 on Jan 7, 2009 2:18 PM