Exadata does not remove indexes. On whether you, as DBA, should manually remove indexes, Arup Nanda has an excellent paper: http://www.proligence.com/pres/ioug14/2014_316_Nanda_ppr.pdf
Since Exadata does use indexes, query hints for indexes work as well.
Thanks for the excellent doc. Here in our environment, we are removing all the indexes of the database that are migrated to a exadata box.
One question that came to my mind here is as we are removing all the indexes, what will happen to a with hints ?(Assuming that it is a vendor code) Will exadata internally has any mechanism to handle this like disabling hints?
I'm not aware of any Exadata-specific internal mechanisms relating to disabling hints. In general, you should expect your hints to have the same effects as if the indexes were removed in a non-Exadata system. So is you have a /*+INDEX hint that references a specific index name that has been removed, the hint would be ignored, for example.
Can you tell us a bit more about the types of hints that you're using and what their intent is?
Normally I have seen the index hints(/* + index (col) and hints for parallel query processing.
You should be careful about removing ALL your indexes when migrating to Exadata. When and what to remove really depends on your workload and whether your current indexes are helping you. For instance, you should always retain indexes on Foreign Keys. If you don't, your Primary to Foreign key relationships will not have an index to use. If you are running a data warehouse-like workload, you can experiment with removing indexes that you "might" not need. Still, if you have PK/FK relationships in the database, you want to hang on to those associated indexes.
Making indexes INVISIBLE is a good way to test whether having them on Exadata helps or hurts. You have to remember that using this feature will make an index invisible for everyone so you still have to use some caution with INVISIBILity. You can also monitor your indexes to see which ones are being used and then evaluate which ones can be removed.
If you really just want hints to be ignored, there is the "_OPTIMIZER_IGNORE_HINTS" parameter, but its unsupported. Might help in your testing.
In general, it's pretty safe to leave your indexes on your tables when you move to Exadata and then remove them strategically.