{"id":73089,"date":"2016-12-12T16:27:09","date_gmt":"2016-12-12T16:27:09","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/uncategorized\/invisible-columns-in-oracle-database-12c\/"},"modified":"2021-07-14T13:06:59","modified_gmt":"2021-07-14T13:06:59","slug":"invisible-columns-in-oracle-database-12c","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/oracle-databases\/invisible-columns-in-oracle-database-12c\/","title":{"rendered":"Invisible Columns In Oracle Database 12C"},"content":{"rendered":"<p>In Oracle Database 12c, you can set a column in table as <strong>invisible either during CREATE TABLE or modifying existing table via ALTER TABLE command<\/strong>. By default, table columns are always visible. When you make it invisible, the COL# column in COL$ dictionary is updated to 0, and so is not included in the \u201cSELECT *\u201d or \u201cINSERT INTO VALUES\u201d statements unless specifically selected \u2013 it can be reverted back to visible by using ALTER TABLE command.<\/p>\n<p>When you change an <strong>invisible<\/strong> column in Oracle 12c database to <strong>visible<\/strong>, the COL# assigned will be the highest available, so the column becomes the last column in the table (not storage, only display). So, if you accidentally make a column invisible and correct this by changing it to visible, the column order changes. Therefore, if the application uses \u201cSELECT *\u201d or \u201cINSERT\u201d without column names, they might break!<\/p>\n<p>Once a table&#8217;s column is set to invisible, the following key statements will not work for the invisible column:<\/p>\n<ul>\n<li>SELECT * FROM in SQL Statement<\/li>\n<li>DESCRIBE statement<\/li>\n<li>%ROWTYPE in PL\/SQL variable declaration<\/li>\n<\/ul>\n<p>Invisible columns will still available for indexing and such indexes can be used for cost-based optimizer purposes. Don&#8217;t confuse an invisible index with an index on invisible column, as they are entirely different concepts.<\/p>\n<h2>Invisible Column Business Justification?<\/h2>\n<p>Why would you make a column invisible? There are not many reasons why you would suddenly make a column invisible, but one situation that comes to mind is where you might want to test the waters before dropping the column from table \u2013 to figure out if something breaks or someone yells. Oracle provides an option to mark a column as UNUSED before you DROP, and do ALTER TABLE \u2026 DROP UNUSED COLUMNS at a later time. Once you mark a column as UNUSED, there is no going back to undo the action. So, marking it INVISIBLE before drop is a good idea. Another use could be that you have a running application used by many teams \u2013 before you collaborate with everyone on a table change, you could test the changes in the table by creating the new column as invisible, do your basic tests, then talk to the other teams and make the column visible to all.<\/p>\n<h2>Invisible Column Behaviour:<\/h2>\n<p>Let me show you the behaviour of INVISIBLE column and UNUSED column in the data dictionary. Create a table with the following characteristics:<\/p>\n<ul>\n<li>Columns 1, 4 &amp; 7 are regular columns with no specialty<\/li>\n<li>x2 is invisible<\/li>\n<li>x3 is virtual<\/li>\n<li>x5 is invisible and virtual<\/li>\n<li>x6 is identity<\/li>\n<\/ul>\n<p><a href=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/oracle\/2016\/12\/word-image.png\" target=\"_blank\"><img decoding=\"async\" style=\"max-width:670px\"  class=\"wp-image-6993\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/oracle\/2016\/12\/word-image.png\" srcset=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/oracle\/2016\/12\/word-image.png 1014w, https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/oracle\/2016\/12\/word-image-300x151.png 300w, https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/oracle\/2016\/12\/word-image-768x386.png 768w\" sizes=\"(max-width: 1014px) 100vw, 1014px\" \/><\/a><\/p>\n<p>Query the column properties of the table you just created:<\/p>\n<p><a href=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/oracle\/2016\/12\/word-image-1.png\" target=\"_blank\"><img decoding=\"async\" style=\"max-width:670px\"  class=\"wp-image-6994\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/oracle\/2016\/12\/word-image-1.png\" srcset=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/oracle\/2016\/12\/word-image-1.png 1512w, https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/oracle\/2016\/12\/word-image-1-300x166.png 300w, https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/oracle\/2016\/12\/word-image-1-768x425.png 768w, https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/oracle\/2016\/12\/word-image-1-1024x566.png 1024w\" sizes=\"(max-width: 1512px) 100vw, 1512px\" \/><\/a><\/p>\n<p>Notice that all columns have USER_GENERATED as YES, and that the invisible columns are marked as HIDDEN_COLUMN=YES. Now, mark x4 for drop and x2 visible.<\/p>\n<p><a href=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/oracle\/2016\/12\/word-image-2.png\" target=\"_blank\"><img decoding=\"async\" style=\"max-width:670px\"  class=\"wp-image-6995\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/oracle\/2016\/12\/word-image-2.png\" srcset=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/oracle\/2016\/12\/word-image-2.png 1026w, https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/oracle\/2016\/12\/word-image-2-300x169.png 300w, https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/oracle\/2016\/12\/word-image-2-768x433.png 768w, https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/oracle\/2016\/12\/word-image-2-1024x577.png 1024w\" sizes=\"(max-width: 1026px) 100vw, 1026px\" \/><\/a><\/p>\n<p>Query the properties again\u2026<\/p>\n<p><a href=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/oracle\/2016\/12\/word-image-3.png\" target=\"_blank\"><img decoding=\"async\" style=\"max-width:670px\"  class=\"wp-image-6996\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/oracle\/2016\/12\/word-image-3.png\" srcset=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/oracle\/2016\/12\/word-image-3.png 1478w, https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/oracle\/2016\/12\/word-image-3-300x215.png 300w, https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/oracle\/2016\/12\/word-image-3-768x550.png 768w, https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/oracle\/2016\/12\/word-image-3-1024x733.png 1024w\" sizes=\"(max-width: 1478px) 100vw, 1478px\" \/><\/a><\/p>\n<p>The x4 column name marked to be dropped was renamed, and was given a <strong>system-generated name<\/strong>. The rename is to facilitate adding a column with the same name to the table. The column x4 also got property changes \u2013 USER_GENERATED became NO and HIDDEN_COLUMN changed to YES. Also, the COLUMN_ID is released, so the column will not be visible in \u201cSELECT *\u201d and \u201cDESCRIBE\u201d. The UNUSED column still maintains the same INTERNAL_COLUMN_ID.<\/p>\n<p>When x2 column is made VISIBLE, it got a new COLUMN_ID assigned (the highest available, thus the column becomes the last column in \u201cSELECT *\u201d and \u201cDESCRIBE\u201d). Its hidden status changed to NO.<\/p>\n<h2>Impact of dropping UNUSED Columns:<\/h2>\n<p>What happens to the ID columns, when the UNUSED column is dropped?<\/p>\n<p><a href=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/oracle\/2016\/12\/word-image-4.png\" target=\"_blank\"><img decoding=\"async\" style=\"max-width:670px\"  class=\"wp-image-6997\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/oracle\/2016\/12\/word-image-4.png\" srcset=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/oracle\/2016\/12\/word-image-4.png 1512w, https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/oracle\/2016\/12\/word-image-4-300x178.png 300w, https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/oracle\/2016\/12\/word-image-4-768x455.png 768w, https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/oracle\/2016\/12\/word-image-4-1024x607.png 1024w\" sizes=\"(max-width: 1512px) 100vw, 1512px\" \/><\/a><\/p>\n<p>The COLUMN_ID was reordered after the unused column is dropped (X4 was dropped, hence X6 and X7 got new column ids \u2013 X5 never had a column id assigned as it is INVISIBLE). The internal column id, which includes id for INVISIBLE columns was also adjusted. After the column was dropped, the INTERNAL_COLUMN_ID was also adjusted.<\/p>\n<p>Tip: By default SQL*Plus DESCRIBE will not show the invisible columns in a table. If you want to see the invisible columns in DESCRIBE, use SET COLINVISIBLE ON.<\/p>\n<p><a href=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/oracle\/2016\/12\/word-image-5.png\" target=\"_blank\"><img decoding=\"async\" style=\"max-width:670px\"  class=\"wp-image-6998\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/oracle\/2016\/12\/word-image-5.png\" srcset=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/oracle\/2016\/12\/word-image-5.png 1398w, https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/oracle\/2016\/12\/word-image-5-300x132.png 300w, https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/oracle\/2016\/12\/word-image-5-768x337.png 768w, https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/oracle\/2016\/12\/word-image-5-1024x450.png 1024w, https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/oracle\/2016\/12\/word-image-5-195x85.png 195w\" sizes=\"(max-width: 1398px) 100vw, 1398px\" \/><\/a><\/p>\n<h2>Query table with invisible Column:<\/h2>\n<p>When we SELECT * FROM without reference columns from a table with invisible columns, only the visible columns are returned as per the below screenshot.<\/p>\n<p><a href=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/oracle\/2016\/12\/desktop-screen20shot2020.png\" target=\"_blank\"><img decoding=\"async\" style=\"max-width:670px\"  class=\"wp-image-6999\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/oracle\/2016\/12\/desktop-screen20shot2020.png\" srcset=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/oracle\/2016\/12\/desktop-screen20shot2020.png 752w, https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/oracle\/2016\/12\/desktop-screen20shot2020-217x300.png 217w, https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/oracle\/2016\/12\/desktop-screen20shot2020-742x1024.png 742w\" sizes=\"(max-width: 752px) 100vw, 752px\" \/><\/a><\/p>\n<p>However, we can still query invisible column(s) by explicitly mentioning them in the SELECT statement as per the below screenshot:<\/p>\n<p><a href=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/oracle\/2016\/12\/desktop-screen20shot2020-1.png\" target=\"_blank\"><img decoding=\"async\" style=\"max-width:670px\"  class=\"wp-image-7000\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/oracle\/2016\/12\/desktop-screen20shot2020-1.png\" srcset=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/oracle\/2016\/12\/desktop-screen20shot2020-1.png 702w, https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/oracle\/2016\/12\/desktop-screen20shot2020-1-171x300.png 171w, https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/oracle\/2016\/12\/desktop-screen20shot2020-1-583x1024.png 583w\" sizes=\"(max-width: 702px) 100vw, 702px\" \/><\/a><\/p>\n<h2>Invisible Column Statistics:<\/h2>\n<p>Oracle 12c also maintains statistics on invisible columns as with generic visible columns.<\/p>\n<p><a href=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/oracle\/2016\/12\/word-image-6.png\" target=\"_blank\"><img decoding=\"async\" style=\"max-width:670px\"  class=\"wp-image-7001\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/oracle\/2016\/12\/word-image-6.png\" srcset=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/oracle\/2016\/12\/word-image-6.png 1190w, https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/oracle\/2016\/12\/word-image-6-300x266.png 300w, https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/oracle\/2016\/12\/word-image-6-768x682.png 768w, https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/oracle\/2016\/12\/word-image-6-1024x909.png 1024w\" sizes=\"(max-width: 1190px) 100vw, 1190px\" \/><\/a><\/p>\n<p><strong>Indexing Invisible Columns:<\/strong><\/p>\n<p>Indexing on invisible columns is similar to indexing on a generic visible column.<\/p>\n<p><a href=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/oracle\/2016\/12\/desktop-screen20shot2020-2.png\" target=\"_blank\"><img decoding=\"async\" style=\"max-width:670px\"  class=\"wp-image-7002\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/oracle\/2016\/12\/desktop-screen20shot2020-2.png\" srcset=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/oracle\/2016\/12\/desktop-screen20shot2020-2.png 1112w, https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/oracle\/2016\/12\/desktop-screen20shot2020-2-300x182.png 300w, https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/oracle\/2016\/12\/desktop-screen20shot2020-2-768x465.png 768w, https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/oracle\/2016\/12\/desktop-screen20shot2020-2-1024x621.png 1024w\" sizes=\"(max-width: 1112px) 100vw, 1112px\" \/><\/a><\/p>\n<p>Let&#8217;s check if Oracle is able to use the INDEX created above, named \u201cHIDDENT_COL_TEST_INDX\u201d.<\/p>\n<p><a href=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/oracle\/2016\/12\/word-image-7.png\" target=\"_blank\"><img decoding=\"async\" style=\"max-width:670px\"  class=\"wp-image-7003\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/oracle\/2016\/12\/word-image-7.png\" srcset=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/oracle\/2016\/12\/word-image-7.png 1634w, https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/oracle\/2016\/12\/word-image-7-300x112.png 300w, https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/oracle\/2016\/12\/word-image-7-768x288.png 768w, https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/oracle\/2016\/12\/word-image-7-1024x384.png 1024w\" sizes=\"(max-width: 1634px) 100vw, 1634px\" \/><\/a><\/p>\n<p>We can see from the above screenshot that Oracle can utilise the index defined on an invisible column.<\/p>\n<h2>Conclusion:<\/h2>\n<p>We have explored the new Oracle 12c feature of defining invisible columns and derived the following conclusions::<\/p>\n<ul>\n<li>Invisible columns in Oracle 12c provide the flexibility of adding columns to existing tables without the fear of breaking an application.<\/li>\n<li>Making a column invisible is a neat way to hide the existing columns within a table.<\/li>\n<li>Invisible columns are not returned while using SELECT * FROM statement unless explicitly referred to in the SELECT statement.<\/li>\n<li>Invisible columns are indexable, and can be used as part of the cost optimiser.<\/li>\n<li>Invisible columns don&#8217;t get a column ID but are tracked by the Internal Column ID.<\/li>\n<\/ul>\n<p>In a nutshell, an invisible column inherits all the features of a normal visible column with the exception of not being visible unless it is explicitly referenced.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>In Oracle Database 12c, you can set a column in table as invisible either during CREATE TABLE or modifying existing table via ALTER TABLE command. By default, table columns are always visible. When you make it invisible, the COL# column in COL$ dictionary is updated to 0, and so is not included in the \u201cSELECT *\u201d or \u201cINSERT INTO VALUES\u201d&hellip;<\/p>\n","protected":false},"author":243321,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143533],"tags":[48347,48415],"coauthors":[],"class_list":["post-73089","post","type-post","status-publish","format-standard","hentry","category-oracle-databases","tag-12c","tag-hidden-columns"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/73089","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/243321"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=73089"}],"version-history":[{"count":1,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/73089\/revisions"}],"predecessor-version":[{"id":91592,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/73089\/revisions\/91592"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=73089"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=73089"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=73089"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=73089"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}