I am trying to use the "--+ordered" directive through the Informix JDBC driver. I keep getting a syntax error when I try and run it as I would through dbaccess. Inofrmix tech support has not been able to locate the documentation, but I have heard of other people using it.
Yes, optimizer hints will be specified as comments.
I think this has to do with the fact that you might write
an application which should run against different
versions of the Informix databaseserver.
Not all Informix versions support optimizer directives,
but because they are specified as a comment, your
application will also run against the older Informix
Normally you could use curly braces or hyphens
for comments (for example in 'dbaccess') but it seems
that thru JDBC you have to use the curly braces only.
I don't know if that is somewhere documented, I didn't
find it in the JDBC programmers manual.
I would not recommend the use of optimizer hints, at least
not if you are running on Informix.
Informix has a very clever optimizer that is much more
sophisticated than Oracle's optimizer. The use of optimizer
hints might prevent the selection of a better access path
in the future. You think the optimizer hint you use is perfect,
but it is only perfect for the current point in time,
your data changes, so another access path might be
better in the future.
Run regular 'update statistics', that should be enough.
This is difficult to analyze without more information.
The Schema of the involved tables (including indices),
the actual number of rows and used datapages for each table,
the number of unique values inside each index and the
complete 'sqexplain.out' file would be helpful.
But to be honest, I earn my money doing consulting, so such
an analysis would be to much effort for doing it for free.
Just a few hints that might help:
1) Let your DBA check the setting of OPTCOMPIND in
-> 0 is normally could for OLTP systems but might hurt
complex queries, 2 might be better
you might specify this in JDBC thru the environment
settings in the connect string
2) Think about using PDQ (Parallel Database Query). This
could be a big improvement for complex queries.
However your DBA might also increase DS_TOTAL_MEMORY
if you want to use PDQ.
3) Maybe an 'update statistics high' (on the columns included
in the indexes) might help