Let me first say thank you in advance for your help.
Here is my situation: I have been asked to improve the response time
for an application I did not write. This application accesses an
Oracle 8.0.5 database that is simply a collection of tables with no
relationships, no indexes (each table does have it's own PK), etc. I
looked at the code that is generating the SELECT statement that is
having problems and it is basically like this:
SELECT a.field1, a.field2, a.field3, b.field9 FROM a, b WHERE a.field1
= b.field1 AND a.field2 = b.field2 AND a.field2 = 'xyz' ORDER BY
a.field1, a.field3
In this case Table A has a PK that contains more fields than Table B's
PK, but all of Table B's PK fields are also in Table A. (I know the
database seign sucks -- but they want a quick fix not a database
re-design).
I have noticed that if I add the following to the WHERE clause in the
SQL statement the retrieval time is improved by a factor of 10!
b.field2 = 'xyz'
This solves the speed problem, but would require a code change and I
do not want to do that as we would have to re-test the entire app
before releasing this fix.
So, I am looking for a fix that could be done on the database level
without affecting the application adversly. I am not that experienced
in Oracle and would appreciate any help I can get on this one.
Thanks again in advance (and thanks for reading this far in my
long-winded post).
Kevin Chasse
kchasse_at_arinc.com
