Oracle Index Redundancy

Question

My colleague is new to Oracle and we analyze indexes on a table. This legacy and indexes currently exist in 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 looks like we have redundancy in our indices. Should I remove partNum from IDX_PART_ID_PART_NUM? Should I remove IDX_PART_NUM? As indicated above, partNum can have more than one identifier, since each part can exist more than once in an object.

Basically, in Oracle, how does an index work?

Answer 1

, partID partNum , . , partID, partID partNum. partNum , partNum NOT partID.

: http://it.toolbox.com/blogs/confessions/post-index-how-oracle-works-10605

, . , , , , , , , .

Answer 2

, , PLAY EXPLAIN PLAN. .

. , , , . , , - .

Answer 3

, (). , 100% , , . ( Im ' , - , )

, , . partNum, - partNum . partId, partNum, , , .

:

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

IDX_PART_ID_PART_NUM, , IDX_PART_NUM. , .

, , 2 , .

, , .

Answer 4

, .

Querying an index is fast if you need less than 20% of the rows. Query 40% of the rows; full scan is fastest. Index requires disk, memory, insertion / update time.

In many cases, the index requires more disk and memory space than the owner table.

Answer 5

Basically, in Oracle, how does an index work?

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