Multi-column Index Using Only First Column

Question

If I have a table with a multi-column index (OrderId, OrderType) and my where clause is using the first column of the index, is the index useful?

Is the index slightly less efficient than if I had an index(OrderId), perhaps where there's a very large number of rows?

My understanding is the multicolumn index is useful when using where criteria on the first column, even if I'm not returning the second column in select or using in where. However, my experience with Oracle is less than other DB engines, and I've been told the multi-column index is not used unless I'm referencing both columns in my query. Additionally, a plan analyzer is recommending creating the single column index on OrderId, when there's already a index(OrderId, OrderType). So it feels like creating a single column index(OrderId) is redundant.

Not looking to analyze my query. Just trying to establish whether I'm misunderstanding something fundamental with multicolumn indexes in Oracle.

Asked another way:

If I already have index (OrderId, OrderType), would there ever be a situation where it would be beneficial to also create a index (OrderId)?

select OrderId, Amount
from Orders
where OrderDate > i_startSearchDate and OrderDate < i_endSearchDate
and OrderId in (
   select OrderFk from Items where ItemType = 1
);

Justin Cave Justin Cave · Accepted Answer · 2019-07-01T20:19:38

Oracle certainly can use an index on OrderID, OrderType for this query. Whether or not the optimizer expects that it would be more efficient to use that index or to do a table scan (or to use some other index) will depend on things like the number of OrderID values that are expected to be returned in the IN list and what fraction of the rows in the table that represents. The fact that scanning the index requires more I/O when there are additional columns will also be a factor that the optimizer considers but that shouldn't be a huge factor. If the optimizer is correct that the composite index will be slower than a table scan and a table scan will be slower than an index on just OrderID, it is likely that all three of those access plans would take very similar amounts of time.

Multi-column Index Using Only First Column

1 Answers