Performance of SELECT query- Oracle/JDBC

Question

I have a existing query in the system which is a simple select query as follows:

SELECT <COLUMN_X>, <COLUMN_Y>, <COLUMN_Z> FROM TABLE <WHATEVER>

Over time, <WHATEVER> is growing in terms of records. Is there any way possible to improve the performance here? The developer is using Statement interface. I believe PreparedStatement won't help here since the query is executed only once.

Is there any thing else that can be done? One of the columns is a primary key and others are VARCHAR (if the information helps)

Answer 1

Does you query have any predicates? Or are you always returning all of the rows from the table?

If you are always returning all the rows, a covering index on column_x, column_y, column_z would allow Oracle to merely scan the index rather than doing a table scan. The query will still slow down over time but the index should grow more slowly than the table.

If you are returning a subset of rows, there are potentially other indexes that would be more advantageous from a performance perspective.

Answer 2

Are there any optimization you can do outside of the SQL query tunning? If yes here are some suggestion:

• Try putting the table in memory (like the MEMORY storage engine in MySQL) or any other optimization in the DB
• Cache the ResultSet in java. query again only when the table content changes. If the table only has inserts and no updates or delete (wishful thinking), then you can use SELECT COUNT(*) FROM table. If the rows returned are different than the previous time then fire your original query and update cache only if needed.