OS : OL/RHEL/Unices
DB versions : 11.2 and above
Global indexes can go INVALID when you do maintenance activities like DROP or TRUNCATE table partitions. And then you need to rebuild it and it can take a long time if the table is huge. Because of this, people usually prefer creating local indexes.
If global index is a maintenance nightmare, and should be avoided, I am curious on what are circumstances that force DBAs to create global indexes ? i.e. When does creating a global index become inevitable ?
Is it because global indexes offer better performance than local indexes? Any other reasons ?