I support an application that has a SQL search in Oracle 10g. It is searching an activity table that stores all kinds of different types of activities and has grown to about 5 million records. I have done the basics in the last couple of years to keep it at a reasonable response time level but it is not doing well. I am not a DBA adn I am not a SQL expert. I use it sporadically developing packages that do certain functions for the application.
I do not know of better use of Oracle 10g for speeding up the database and I would like to get some advice as to what I should look at to improve the response time. I can look at different things online but I thought that I should ask the question here to avoid spending too much time reiventing the wheel. I am sure someone in the group will have done this type of development in the past and could give me some good hints.
Thank you very much