Oracle multiple vs single column index

Question

Imagine I have a table with the following columns:

1. Column: A (numer(10)) (PK)

2. Column: B (numer(10))

3. Column: C (numer(10))

   CREATE TABLE schema_name.table_name (
   column_a number(10) primary_key,
   column_b number(10) ,
   column_c number(10)
   );
   

Column A is my PK.

Imagine my application now has a flow that queries by B and C. Something like:

SELECT * FROM SCHEMA.TABLE WHERE B=30 AND C=99

If I create an index only using the Column B, this will already improve my query right?

The strategy behind this query would benefit from the index on column B?

Q1 - If so, why should I create an index with those two columns?

Q2 - If I decided to create an index with B and C, If I query selecting only B, would this one be affected by the index?

Answer 1

In spite of this question being answered and one answer being accepted already, I'll just throw in some more information :-)

An index is an offer to the DBMS that it can use to access data quicker in some situations. Whether it actually uses the index is a decision made by the DBMS.

Oracle has a built-in optimizer that looks at the query and tries to find the best execution plan to get the results you are after.

Let's say that 90% of all rows have B = 30 AND C = 99. Why then should Oracle laboriously walk through the index only to have to access almost every row in the table at last? So, even with an index on both columns, Oracle may decide not to use the index at all and even perform the query faster because of the decision against the index.

Now to the questions:

If I create an index only using the Column B, this will already improve my query right?

It may. If Oracle thinks that B = 30 reduces the rows it will have to read from the table imensely, it will.

If so, why should I create an index with those two columns?

If the combination of B = 30 AND C = 99 limits the rows to read from the table further, it's a good idea to use this index instead.

If I decided to create an index with B and C, If I query selecting only B, would this one be affected by the index?

If the index is on (B, C), i.e. B first, then Oracle may find it useful, yes. In the extreme case that there are only the two columns in the table, that would even be a covering index (i.e. containing all columns accessed in the query) and the DBMS wouldn't have to read any table row, as all the information is already in the index itself. If the index is (C, B), i.e. C first, it is quite unlikely that the index would be used. In some edge-case situations, Oracle might do so, though.

Answer 2

The simple answers to your questions.

For this query:

SELECT *
FROM SCHEMA.TABLE
WHERE B = 30 AND C = 99;

The optimal index either (B, C) or (C, B). The order does matter because the two comparisons are =.

An index on either column can be used, but all the matching values will need to be scanned to compare to the second value.

If you have an index on (B, C), then this can be used for a query on WHERE B = 30. Oracle also implements a skip-scan optimization, so it is possible that the index could also be used for WHERE C = 99 -- but it probably would not be.

I think the documentation for MySQL has a good introduction to multi-column indexes. It doesn't cover the skip-scan but is otherwise quite applicable to Oracle.

Answer 3

Short answer: always check the real performance, not theoretical. It means, that my answer requires verification at real database.

Inside SQL (Oracle, Postgre, MsSql, etc.) the Primary Key is used for at least two purposes:

• Ordering of rows (e.g. if PK is incremented only then all values will be appended)
• Link to row. It means that if you have any extra index, it will contain whole PK to have ability to jump from additional index to other rows.

If I create an index only using the Column B, this will already improve my query right? The strategy behind this query would benefit from the index on column B?

It depends. If your table is too small, Oracle can do just full scan of it. For large table Oracle can (and will do in common scenario) use index for column B and next do range scan. In this case Oracle check all values with B=30. Therefore, if you can only one row with B=30 then you can achieve good performance. If you have millions of such rows, Oracle will need to do million of reads. Oracle can get this information via statistic.

Q1 - If so, why should I create an index with those two columns?

It is needed to direct access to row. In this case Oracle requires just few jumps to find your row. Moreover, you can apply unique modifier to help Oracle. Then it will know, that not more than single row will be returned.

However if your table has other columns, real execution plan will include access to PK (to retrieve other rows).

If I decided to create an index with B and C, If I query selecting only B, would this one be affected by the index?

Yes. Please check the details here. If index have several columns, than Oracle will sort them according to column ordering. E.g. if you create index with columns B, C then Oracle will able to use it to retrieve values like "B=30", e.g. when you restricted only B.

Answer 4

Well, it all depends.

If that table is tiny, you won't see any benefit regardless any indexes you might create - it is just too small and Oracle returns data immediately.

If the table is huge, then it depends on column's selectivity. There's no guarantee that Oracle will ever use that index. If optimizer decides (upon information it has - don't forget to regularly collect statistics!) that the index should not be used, then you created it in vain (though, you can choose to use a hint, but - unless you know what you're doing, don't do it).

How will you know what's going on? See the explain plan.

But, generally speaking, yes - indexes help.

---

Q1 - If so, why should I create an index with those two columns?

Which "two columns"? A? If it is a primary key column, Oracle automatically creates an index, you don't have to do that.

---

Q2 - If I decided to create an index with B and C, If I query selecting only B, would this one be affected by the index?

If you are talking about a composite index (containing both B and C columns, respectively), and if query uses B column, then yes - index will (OK, might be used). But, if query uses only column C, then this index will be completely useless.