I'm having performance issues with my current Oracle setup. Queries are taking half a second and I can't figure out how to speed it up further.
I've upgraded my RDS instance from a t2.medium to m4.large, created an index for the two columns I use in the where clause for all my queries, and limited my query to return a single row.
My tables (there are 5 in total - the schemas for which isn't my own design) have ~700 columns and as little as 7 rows and as many as 160,000 rows.
No matter which table I query (directly from SQL Developer or my application) it takes about half a second. If I run all my queries together it still takes about half a second.
All my queries are basically the same for each of the 5 tables.
SELECT /*+ PARALLEL(auto) */ * FROM (SELECT VALUE_I_WANT FROM TABLE_I_CARE_ABOUT WHERE (CLAUSE_ONE = 'X' AND CLAUSE_TWO = 'Y')) WHERE ROWNUM=1;
As I mentioned I have indexes on all these tables for columns CLAUSE_ONE and CLAUSE_2.
What else can I do to try and bring this query down to a much smaller fraction of a second?
