Hi guys, I am trying to clean up a little bit a database, due to it has many doubled or unused indexes. The thing is that many of these doubled indexes are like:
The first one contains just one column (let's say it will be ID column)
The other one contains more than one column (multiplied index, e.g. ID, First_name,Last_name)
I am not very sure if I may drop any of them, but also I am not sure if there supposed to be any doubled indexes. My first thought was to drop the single index.
I've checked the Explain plan to see if any index is used while running query and.. it was not.
Sample query:
select * from table where ID between 2 and 111 and First_name in ('Jake','Julia','Tom','Katie') and Last_name in ('Smith','Brown','Kennedy')
(Of course for my real table I used much wider ranges.)
So - as far as I understood, my attempt to use the multiple index was correct (I kept an order of indexes). However, the CBO decided it is better not to use an index.
My questions are:
Should these type of indexes be dropped if Oracle engine decides it is better to not use them?
For completely unused indexes, should I ask application owner for approvement to drop these indexes? (I mean, if this is reasonable? Or maybe there might be any reasons to keep these indexes that I am not awared of?)
The unused indexes I checked with below query:
SELECT
v.index_name, v.table_name,
v.monitoring, v.used, u.owner,
start_monitoring, end_monitoring
FROM v$object_usage v, all_indexes u
WHERE v.index_name = u.index_name
and used= 'NO';