{"id":118,"date":"2017-08-14T01:05:08","date_gmt":"2017-08-14T01:05:08","guid":{"rendered":"http:\/\/www.mrmarkyoung.com\/oracle\/?p=118"},"modified":"2017-08-14T01:05:43","modified_gmt":"2017-08-14T01:05:43","slug":"unused-indexes-with-constraints","status":"publish","type":"post","link":"http:\/\/www.mrmarkyoung.com\/oracle\/2017\/08\/14\/unused-indexes-with-constraints\/","title":{"rendered":"Unused Indexes &#8211; With constraints"},"content":{"rendered":"<pre class=\"lang:default decode:true \" >SELECT i.owner,\r\n         a.index_name,\r\n         a.table_name,\r\n         c.constraint_name,\r\n         a.monitoring,\r\n         a.used,\r\n         a.start_monitoring,\r\n         ROUND (SUM (b.bytes) \/ 1024 \/ 1024 \/ 1024) GB,\r\n         ROUND (SUM (b.bytes) \/ 1024 \/ 1024) MB,\r\n         i.tablespace_name\r\n    FROM all_object_usage a,\r\n         dba_segments b,\r\n         dba_indexes i,\r\n         dba_constraints c\r\n   WHERE     a.index_name = b.segment_name\r\n         AND a.used = 'NO'\r\n         AND b.segment_name = i.index_name\r\n         AND a.table_name = c.table_name(+)\r\nGROUP BY i.owner,\r\n         a.index_name,\r\n         a.table_name,\r\n         c.constraint_name,\r\n         a.monitoring,\r\n         a.used,\r\n         a.start_monitoring,\r\n         i.tablespace_name\r\nORDER BY ROUND (SUM (b.bytes) \/ 1024 \/ 1024 \/ 1024) DESC\r\n \r\nand\r\n \r\nSELECT i.tablespace_name, round(SUM (b.bytes) \/ 1024 \/ 1024 \/ 1024) GB\r\n  FROM all_object_usage a, dba_segments b, dba_indexes i\r\n WHERE     a.index_name = b.segment_name\r\n       AND a.used = 'NO'\r\n       AND b.segment_name = i.index_name\r\n       group by i.tablespace_name;\r\n \r\nor\r\n \r\nSELECT i.tablespace_name, round(SUM (b.bytes) \/ 1024 \/ 1024 \/ 1024) GB\r\n  FROM all_object_usage a, dba_segments b, dba_indexes i\r\n WHERE     a.index_name = b.segment_name\r\n       AND a.used = 'NO'\r\n       AND b.segment_name = i.index_name\r\n       group by i.tablespace_name;<\/pre>\n","protected":false},"excerpt":{"rendered":"<p>SELECT i.owner, a.index_name, a.table_name, c.constraint_name, a.monitoring, a.used, a.start_monitoring, ROUND (SUM (b.bytes) \/ 1024 \/ 1024 \/ 1024) GB, ROUND (SUM (b.bytes) \/ 1024 \/ 1024) MB, i.tablespace_name FROM all_object_usage a, dba_segments b, dba_indexes i, dba_constraints c WHERE a.index_name = b.segment_name AND a.used = &#8216;NO&#8217; AND b.segment_name = i.index_name AND a.table_name = c.table_name(+) GROUP BY i.owner, &#8230; <a title=\"Unused Indexes &#8211; With constraints\" class=\"read-more\" href=\"http:\/\/www.mrmarkyoung.com\/oracle\/2017\/08\/14\/unused-indexes-with-constraints\/\" aria-label=\"Read more about Unused Indexes &#8211; With constraints\">Read more<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[4],"tags":[27,26],"class_list":["post-118","post","type-post","status-publish","format-standard","hentry","category-oracle-database","tag-constraints","tag-indexes"],"_links":{"self":[{"href":"http:\/\/www.mrmarkyoung.com\/oracle\/wp-json\/wp\/v2\/posts\/118","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/www.mrmarkyoung.com\/oracle\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.mrmarkyoung.com\/oracle\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.mrmarkyoung.com\/oracle\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/www.mrmarkyoung.com\/oracle\/wp-json\/wp\/v2\/comments?post=118"}],"version-history":[{"count":1,"href":"http:\/\/www.mrmarkyoung.com\/oracle\/wp-json\/wp\/v2\/posts\/118\/revisions"}],"predecessor-version":[{"id":119,"href":"http:\/\/www.mrmarkyoung.com\/oracle\/wp-json\/wp\/v2\/posts\/118\/revisions\/119"}],"wp:attachment":[{"href":"http:\/\/www.mrmarkyoung.com\/oracle\/wp-json\/wp\/v2\/media?parent=118"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.mrmarkyoung.com\/oracle\/wp-json\/wp\/v2\/categories?post=118"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.mrmarkyoung.com\/oracle\/wp-json\/wp\/v2\/tags?post=118"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}