tag:blogger.com,1999:blog-25740336.post4999359077234297170..comments2023-08-24T22:26:48.675+01:00Comments on The PeopleSoft DBA Blog: Who is using this index?David Kurtzhttp://www.blogger.com/profile/00468908370233805717noreply@blogger.comBlogger2125tag:blogger.com,1999:blog-25740336.post-84645319474559140162014-09-03T16:03:31.910+01:002014-09-03T16:03:31.910+01:00Index covering the foreign key lock-deadlock threa...Index covering the foreign key lock-deadlock threat might not be reported by your ASH script and then you might take a decision to get rid of them making your application subject to a new threat.<br />Index that are used by the CBO for its estimation during parse time (execution plan compilation) are not reported as being used by your script so that dropping them will lead to sub-optimal execution plan due to the wrong estimations that might be consequently done by the CBO.<br />Redundant indexes as well cannot been dropped without a careful attention.<br />I 100% agree with you about putting the index in an invisible stat for a week or so and observe the application reaction to this index absence (from the CBO point of view) before definitely dropping it or making it back to its visible status.<br />Best regards<br />Mohamed Houri<br />www.hourim.wordpress.com<br />Mohamed Hourihttps://www.blogger.com/profile/11687776847553675567noreply@blogger.comtag:blogger.com,1999:blog-25740336.post-25441852703528454622014-09-03T12:18:08.086+01:002014-09-03T12:18:08.086+01:00David, Excellent post.
In the general case I woul...David, Excellent post.<br /><br />In the general case I would also mention that if some index is potentially used for some constraint validation (even if only rarely) then making sure some other reasonable support for the constraint validation remains (such as a similar index with some extra columns or the like) to avoid potential extreme slowness and locks when the potentially rare event occurs. Of course in the special case that the application does not use live constraints this does not apply.Mark W. Farnhamhttps://www.blogger.com/profile/05161780579837403983noreply@blogger.com