Optimize Oracle SELECT on large dataset

Question

I am new in Oracle (working on 11gR2). I have a table TABLE with something like ~10 millions records in it, and this pretty simple query :

SELECT t.col1, t.col2, t.col3, t.col4, t.col5, t.col6, t.col7, t.col8, t.col9, t.col10
FROM TABLE t
WHERE t.col1 = val1
AND t.col11 = val2
AND t.col12 = val3
AND t.col13 = val4

The query is currently taking about 30s/1min.

My question is: how can I improve performance ? After a lot of research, I am aware of the most classical ways to improve performance but I have some problems :

• Partitioning: can't really, the table is used in an other project and it would be too impactful. Plus it only delay the problem given the number of rows inserted in the table every day.

• Add an index: The thing is, the columns used in the WHERE clause are not the one returned by the query (except for one). Thus, I have not been able to find an appropriate index yet. As far as I know, setting an index on 12~13 columns does not make a lot of sense (or does it?).

• Materialized views: I must say I never used them, but I understood the maintenance cost is pretty high and my table is updated quite often.

I think the best way to do this would be to add an appropriate index, but I can't find the right columns on which it should be created.

Answer 1

Table with 10 millions records is quite little table. You just need to create an appropriate index. Which column select for index - depends on content of them. For example, if you have column that contains only "1" and "0", or "yes" and "no", you shouldn't index it. The more different values contains column - the more effect gives index. Also you can make index on two or three (and more) columns, or function-based index (in this case index contains results of your SQL function, not columns values). Also you can create more than one index on table.

And in any case, if your query selects more then 20 - 30% of all table records, index will not help.

Also you said that table is used by many people. In this case, you need to cooperate with them to avoid duplicating indexes.

Answer 2

An index makes sense provided that your query results in a small percentage of all rows. You would create one index on all four columns used in the WHERE clause.

If too many records match, then a full table scan will be done. You may be able to speed this up by having this done in parallel threads using the PARALLEL hint:

SELECT /*+parallel(t,4)*/
  t.col1, t.col2, t.col3, t.col4, t.col5, t.col6, t.col7, t.col8, t.col9, t.col10
FROM TABLE t
WHERE t.col1 = val1 AND t.col11 = val2 AND t.col12 = val3 AND t.col13 = val4;

Answer 3

Indexes on each of the columns referenced in the WHERE clause will help performance of a query against a table with a large number of rows, where you are seeking a small subset, even if the columns in the WHERE clause are not returned in the SELECT column list. The downside of course is that indexes impede insert/update performance. So when loading the table with large numbers of records, you might need to disable/drop the indexes prior to loading and then re-create/enable them again afterwards.