I 've got a huge table containing 25 millions records and still on-going grows, we have defined a number of indexes for it. We have many store procedures calling one of its index in which is composed of 7 columns.
Now we have to add two new columns in this table, also, they need to be indexed together with the other 7 columns.
we can create another index to contain these 9 columns, however, I am thinking what's the result if I just update the existing index(ind1) to append these two columns.
I want to know if this index would be still work in thoese quereies in which we only use the preceding 7 columns ?
I suggest you test that.
Oracle might decide not to use the index because there's too much data to be read, so it can better do a full table scan.
Only way to find out is test it in your test database.