{"id":225,"date":"2018-12-11T15:57:46","date_gmt":"2018-12-11T14:57:46","guid":{"rendered":"http:\/\/anjo.pt\/wp\/keyword-oracle\/?p=225"},"modified":"2018-12-12T07:50:54","modified_gmt":"2018-12-12T06:50:54","slug":"index-columns-query","status":"publish","type":"post","link":"https:\/\/anjo.pt\/keyword-oracle\/2018\/12\/11\/index-columns-query\/","title":{"rendered":"Index columns query"},"content":{"rendered":"<p>Query to show all indexes, its characteristics and respective columns from a table.<br \/>\nNote: It does not filter on owner.<\/p>\n<pre class=\"brush: sql; title: ; notranslate\" title=\"\">\r\nselect c.index_name,\r\n       c.table_name||'('||listagg(column_name,',') within group (order by column_position)||')' as columns,\r\n       decode(status,'VALID',null,status||' ')\r\n       ||decode(visibility,'VISIBLE',null,visibility||' ')\r\n       ||decode(segment_created,'YES',null,'EMPTY ')\r\n       ||decode(uniqueness,'NONUNIQUE',null,uniqueness||' ')\r\n       ||funcidx_status as STATUS\r\n from dba_ind_columns c, dba_indexes i\r\nwhere i.table_name=c.table_name and i.index_name=c.index_name\r\n  and c.table_name='&amp;TABLE_NAME'\r\ngroup by c.table_name,c.index_name,i.status,visibility, segment_created,uniqueness,funcidx_status\r\norder by c.table_name,c.index_name;\r\n<\/pre>\n<p>Example of output:<\/p>\n<pre class=\"brush: plain; title: ; notranslate\" title=\"\">\r\nINDEX_NAME                     COLUMNS                                 STATUS  \r\n------------------------------ --------------------------------------- ----------\r\nIDX_LIB_VERSION_PROFILE_ID     LIB_VERSION(PROFILE_ID)                       \r\nINX_LIB_VERSION_SRC            LIB_VERSION(DATA_RESOURCE,SOURCE)             \r\nLIB_VERSION_NAME_IDX           LIB_VERSION(VERSION_NAME)                     \r\nLIB_VERSION_PK                 LIB_VERSION(ID)                         UNIQUE\r\nLIB_VERSION_UK1                LIB_VERSION(PROFILE_ID,VERSION_NAME)    UNIQUE\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"<p>Query to show all indexes, its characteristics and respective columns from a table. Note: It does not filter on owner. select c.index_name, c.table_name||'(&#8216;||listagg(column_name,&#8217;,&#8217;) within group (order by column_position)||&#8217;)&#8217; as columns, decode(status,&#8217;VALID&#8217;,null,status||&#8217; &#8216;) ||decode(visibility,&#8217;VISIBLE&#8217;,null,visibility||&#8217; &#8216;) ||decode(segment_created,&#8217;YES&#8217;,null,&#8217;EMPTY &#8216;) ||decode(uniqueness,&#8217;NONUNIQUE&#8217;,null,uniqueness||&#8217; &#8216;) ||funcidx_status as STATUS from dba_ind_columns c, dba_indexes i where i.table_name=c.table_name and i.index_name=c.index_name and c.table_name=&#8217;&amp;TABLE_NAME&#8217; group by c.table_name,c.index_name,i.status,visibility, [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[6,2,7],"tags":[],"class_list":{"0":"post-225","1":"post","2":"type-post","3":"status-publish","4":"format-standard","6":"category-oracle","7":"category-oracle-11g","8":"category-oracle-12c","9":"czr-hentry"},"_links":{"self":[{"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/posts\/225","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/comments?post=225"}],"version-history":[{"count":9,"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/posts\/225\/revisions"}],"predecessor-version":[{"id":247,"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/posts\/225\/revisions\/247"}],"wp:attachment":[{"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/media?parent=225"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/categories?post=225"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/tags?post=225"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}