Question on having on index!!!!

[see_one]

Hi,

I was looking up on index on a table that I have and reading in a book about index and ran this query to check the index on my shipment table which is main table used where I work. By the way, I am using Oracle 8i database. Here it is:

select table_name, index_name, column_name, column_position  from dba_ind_columns 
where table_name = 'SHIPMENT'
AND TABLE_OWNER = 'ORACLE'
order by column_name, index_name, column_position

TABLE_NAME                INDEX_NAME                     COLUMN_NAME               COLUMN_POSITION
------------------------- ------------------------------ ------------------------- ---------------
SHIPMENT                  PK_SHIPMENT_REG_NUM            REG_NUM                                 1
SHIPMENT                  SHIPMENT_ACCT_NUM              ACCT_NUM                                1
SHIPMENT                  SHIPMENT_ACT_DELIVERY_DATE     ACT_DELIVERY_DATE                       1
SHIPMENT                  SHIPMENT_ACT_LOAD_DATE         ACT_LOAD_DATE                           1
SHIPMENT                  SHIPMENT_ACT_REGISTER_DATE     ACT_REGISTER_DATE                       1
SHIPMENT                  SHIPMENT_EST_ACT_DEL_DATE      EST_DELIVERY_DATE                       1
SHIPMENT                  SHIPMENT_EST_ACT_DEL_DATE      ACT_DELIVERY_DATE                       2
SHIPMENT                  SHIPMENT_EST_ACT_LOAD_DATE     EST_LOAD_DATE                           1
SHIPMENT                  SHIPMENT_EST_ACT_LOAD_DATE     ACT_LOAD_DATE                           2
SHIPMENT                  SHIPMENT_FINAL_DEL_DATE        FINAL_DEL_DATE                          1
SHIPMENT                  SHIPMENT_HAULER_1              HAULER_1                                1
SHIPMENT                  SHIPMENT_SCAC                  SYS_NC00068$                            1

12 rows selected. 

What I read in the book briefly, that if you have two index on the same column on the same table, it can result slow down since Oracle has two choose from those two index or somthing like that.
In my example, I have two indexes on ACT_DELIVERY_DATE AND ACT_LOAD_DATE, which in my opinion is not good for the table. Can someone with a little bit of knowledge on index can reinforce on this? Thanks.

[slimdave]

try reposting the result of ...

select table_name, index_name, column_name, column_position from dba_ind_columns
where table_name = 'SHIPMENT'
AND TABLE_OWNER = 'ORACLE'
order by index_name, column_position

... and surround it with the code tags. [ code ] at the beginning and [ /code ] at the end (take out the spaces separating the brackets from the rest)

[DaPi]

ACT_DELIVERY_DATE and ACT_LOAD_DATE are indexed alone and in SECOND position in the composite indexes - so you have no redundancy. (If they had been in first position in the composite indexes, then the index on theses fields alone would have been superfluous.) You will probably find a script on this site for identifying redundant indexes.

Of course, it's possible that some of the indexes are never actually used - but that is a different and MUCH HARDER question.

The overhead in deciding which index to use is unlikely to be noticible. The real overhead from excessive indexes is the extra time required for maintaining the indexes when changes are made to the table, and the space occupied.

[DaPi]

I got this from somewhere on this site - appologies for not acknowledging the author.

[see_one]

Thanks for your reply and answering my question. After running the query as per your request I can also see the difference better. Thanks for the script too. This is a great forum.