Oracle indexes ....

Question

A colleague and I are new to Oracle and are analyzing indexes on a table. This is a legacy and indexes currently exist on the table

Mytable
* ID      (primary key)
* partId  (Id column in part)
* partNum (partNum column in part...partNum can have more than one partId)
* description (description of partNum...can be different for each partNum)
* dateReceived

IDX_PART_ID_PART_NUM(partId, PartNum)
IDX_PART_NUM(partNum)
IDX_DATE_RECEIVED(dateReceived)

It seems like we have redundancy in our indexes. Should we remove partNum from IDX_PART_ID_PART_NUM? Should we remove IDX_PART_NUM? As stated above, a partNum can have more than one id as each part can exist more than once in an object.

Whew....hope that makes sense

Basically, In Oracle, how does an Index work?

Answer 1

If you have queries that look up the partID and the partNum at the same time, then you want to maintain the index. Having both columns in the index means that the index is broken down first by partID, and then again for each partID it is broken down by partNum. Having the other index only on partNum will be useful for queries that only query on partNum and NOT on partID.

Here's a link to a good article: http://it.toolbox.com/blogs/confessions/post-index-how-oracle-works-10605

As a general rule, I'd avoid touching indexes on a legacy system. If it's an older system that's been in production for a while, those indexes were probably created by a DBA who did some analysis and planning to ensure they worked well and fit the usage of the data.

Answer 2

An index works by allowing the database to organize data around a set of column(s). I'll admit I'm not 100% sure on the technical implementation of this, but it creates a data structure using the values and mapping them to the object ID for each row in a table. (If Im' wrong on this, somebody correct me, though it shouldn't affect the value of my answer as a whole)

You try to create an index on anything you're going to be searching frequently, to speed it up. By having an index on partNum then you can find anything by partNum very quickly. the index on partId, partNum, though, would allow for very fast searches on the table where you have to match both columns.

Consider the following:

SELECT *
FROM MyTable
WHERE partId = 2
  AND partNum = 7

This would use IDX_PART_ID_PART_NUM to do the search, using both values to scan the index, rather than just IDX_PART_NUM. This would still be faster than if you had an index on both columns separately.

Essentially, it allows the values of both columns to be indexed together, so for every value of the one, it will know the allowed values of the other, and won't have to do 2 lookups for each query, when it can do one.

To answer the question, if you query both columns frequently, then keep the multipart index.

Answer 3

another approach is to run some sample queries against the tables and then examine the EXPLAIN PLAN results. You should see the usage pattern of the defined indexes.

as far as a recommendation, It seems both the indexes you reference could be useful independently. I would keep them both unless you notice some bad performance hit on inserts during batch loading or something.

Answer 4

All indexes are correct, but performance depends on number of records and query frecuency on each index column.

A query by an index is fast if you need less 20% of rows. A Query for 40% rows, full scan is fastest. A index needs Disk, memory, time on inserts / updates.

Many times a Index needs more space on disk and memory than the owner table.

Answer 5

Basically, In Oracle, how does an Index work?

Another comprehensive answer: http://Use-The-Index-Luke.com