CODEWITHSUNDEEP
oracle-databaseindexing
kara
kara

Reputation: 3455

How does a multi-column index work in oracle?

I'm building a table to manage some articles:

Table

| Company | Store | Sku | ..OtherColumns.. | 
|       1 |     1 | 123 | ..               | 
|       1 |     2 | 345 | ..               | 
|       3 |     1 | 123 | ..               |

Scenario

Most time company, store and sku will be used to SELECT rows:

SELECT * FROM stock s WHERE s.company = 1 AND s.store = 1 AND s.sku = 123;

..but sometimes the company will not be available when accessing the table.

SELECT * FROM stock s WHERE s.store = 1 AND s.sku = 123;

..Sometimes all articles will be selected for a store.

SELECT * FROM stock s WHERE s.company = 1 AND s.store = 1;

The Question

How to properly index the table?

I could add three indexes - one for each select, but i think oracle should be smart eneugh to re-use other indexes.

Would an Index "Store, Sku, Company" be used if the WHERE-condition has no company?

Would an Index "Company, Store, Sku" be used if the WHERE-condition has no company?

Upvotes: 5

Views: 6691

Answers (2)

nimdil
nimdil

Reputation: 1381

first - do you need index at all? Indexes are not for free. If your table is small enoguh, perhaps you don't need index at all.

Second - what is data structure? You have store column in every scenario - I can imagine situation in which filtering data on store dissects source data to enough degree to be good enough for you.

However if you want to have maximum reasonable performance benefit you need two:

(store, sku, company)

(store, company)

or

(store, company, sku)

(store, sku)

Would an Index "Store, Sku, Company" be used if the WHERE-condition has no company?

Yes

Would an Index "Company, Store, Sku" be used if the WHERE-condition has no company?

Probably not, but I can imagine scenarios in which it might happen (not for the index seek operation which is really primary purpose of indices)

You dissect data in order of columns. So you group data by first element and order them by first columns sorting order, then within these group you group the same way by second element etc. So when you don't use first element of index in filtering, the DB would have to access all "subgroups" anyway.

I recommend reading about indexes in general. Start with https://en.wikipedia.org/wiki/B-tree and try to draw how it behaves on paper or write simple program to manage simplified version. Then read on indexes in database - any db would be good enough.

Upvotes: 0

Connor McDonald
Connor McDonald

Reputation: 11591

You can think of the index key as conceptually being the 'concatenation' of the all of the columns, and generally you need to have a leading element of that key in order to get benefit from the index. So for an index on (company,store,sku) then

WHERE s.company = 1 AND s.store = 1 AND s.sku = 123;

can potentially benefit from the index

WHERE s.store = 1 AND s.sku = 123;

is unlikely to benefit (but see footnote below)

WHERE s.company = 1 AND s.store = 1;

can potentially benefit from the index.

In all cases, I say "potentially" etc, because it is a costing decision by the optimizer. For example, if I only have (say) 2 companies and 2 stores then a query on company and store, whilst it could use the index is perhaps better suited to not to do so, because the volume of information to be queried is still a large percentage of the size of the table.

In your example, it might be the case that an index on (store,sku,company) would be "good enough" to satisfy all three, but that depends on the distribution of data. But you're thinking the right way, ie, get as much value from as few indexes as possible.

Footnote: There is a thing called a "skip scan" where we can get value from an index even if you do not specify the leading column(s), but you will typically only see that if the number of distinct values in those leading columns is low.

Upvotes: 9

Related Questions