What would be the most efficient indexes to make to speed up these 4 queries

indexindex-tuningoracle

I have a few queries that I want to speed up, but the columns they filter on are all different, but related, and I'm having trouble figuring out the best way to make indexes for them.

So the first query is something like this

1: select * from TABLE where A=? AND B=? and C=?

Then, the next three queries are all on the same table, but they each use one of the possible combinations of 2 elements from A, B and C.

2: select * from TABLE where A=? AND B=?

3: select * from TABLE where A=? and C=?

4: select * from TABLE where B=? and C=?

So I figure if I make an index on (A,B,C) that will speed up query 1 and 2, but I don't think it will do anything for query 3 or 4. So then would I need two more indexes on (A, C) and (B, C), or is there a more efficient way to do this?

I'm using Oracle 12c btw.

Best Answer

Without more information the answer is "it depends" as what is optimal will vary depending on the distribution properties of A, B, and C (are any of them unique per row, that is are any of them your primary key or the subject of a unique index? or do they contain many duplicate values and if so how are those values clumped?), their types & sizes, and so on. Also, how wide is the table: does it have many more columns in addition to these three and/or columns containing a lot of data (such as long strings)?

General answers where possible:

if I make an index on (A,B,C) that will speed up query 1 and 2

Correct.

but I don't think it will do anything for query 3 or 4.

It could help query 3. The query planner it like to be able to use that index because it starts with A and that is one of the columns filtered by in query 3. It may chose not to depending on the selectivity of A and properties of the data int he other columns, the table more generally, and in more complex queries the other objects.

If the table is quite wide, that is it has noticeably more data per row than that held in these three columns, then this index may also help query 4: in the absence of any other useful indexes for this query the query planner may chose to scan the index on A,B,C to find the matching rows instead of scanning the whole table which could mean reading many more pages.

indexes on (A, C) and (B, C), or is there a more efficient way to do this?

If your goal is query speed only then those two indexes may be optimal for these queries. An index on just C would probably (again, depending on the data in C and the other columns) be useful for both queries 3 and 4 as the planner can index-seek using the where clause on C then partial scan to find the rows matching the clause on B or A. This, depending on the sizes of your columns, will probably save disk space & working memory and speed up inserts and updates, but is likely to be less optimal for selects then the two wider indexes.

Getting more advanced: your choice of clustering settings could be significant (see http://use-the-index-luke.com/sql/clustering/index-organized-clustered-index), particularly as you are using SELECT *, though Oracle is a little less flexible than SQL server (where any index could be the clustering key) on this matter.

To try it out for yourself, throw some realistic data into a copy of the table, and use EXPLAIN PLAN (see https://docs.oracle.com/cd/B19306_01/server.102/b14211/ex_plan.htm) to see what indexes it chooses to use for your queries and other performance counters to see what wall-clock effect this has.

Related Question