Invisible Columns in the Oracle Database

What are Invisible Columns?

Invisible columns are exactly what they sound like: columns that exist in your table but don’t show up in a standard SELECT * statement. They’re there, working hard, but you won’t see them unless you ask.

This feature was added back in Oracle Database 12c as a way to add or update a tables structure without causing problems for existing queries or apps built on those tables.

Why Use Invisible Columns?  

You might ask: Why would I need an invisible column?  

With invisible columns, you can:

1. Add new columns safely

   Add the column without altering the results of existing queries.

   
   

2. Support gradual migrations 

   Make changes behind the scenes and only expose the column when you’re ready.

   
   

3. Reduce risk 

   Avoid unintended side effects like breaking applications unknowingly due to the table structure.

   
   

4. Security Through Obscurity

   While not a true security feature, invisible columns can hide sensitive data from standard select * statements.  

Invisible Columns in Action

Let’s see a demo using a BOOKS table for a library system. We’ll look at how an invisible columns works.

CREATE TABLE books (
    book_id NUMBER PRIMARY KEY,
    title VARCHAR2(200),
    author VARCHAR2(100),
    isbn VARCHAR2(13) INVISIBLE,
    publication_year NUMBER
);

Note: An ISBN (International Standard Book Number) is a unique identifier for books, used worldwide by publishers, bookstores, and libraries to distinguish books and their specific editions.

Let's add some sample books:

INSERT INTO books (book_id, title, author, isbn, publication_year)
VALUES (1, 'The Great Gatsby', 'F. Scott Fitzgerald', '9780743273565', 1925),
       (2, '1984', 'George Orwell', '9780451524935', 1949);

Now, let's see how the invisible column works:

select * from books;

desc books;

select book_id, title, author, isbn, publication_year 
from books;

Notice both select * and desc don't show the invisible column

You can also change the column visibility and add new columns as needed

alter table books modify isbn visible;

alter table books add page_count number invisible;

select column_id, column_name, hidden_column
from user_tab_cols
where table_name = 'BOOKS'
order by column_id;

We can find the column status from the user_tab_cols table.

Notice the column ID. The invisible column doesn't have an order associated with it. If we update the column and make it visible, it'll take the next available column ID.

alter table books modify page_count visible;

select column_id, column_name, hidden_column
from user_tab_cols
where table_name = 'BOOKS'
order by column_id; 

Invisible columns are fully functional columns that can be used in various ways:

-- Create indexes on invisible columns
CREATE INDEX idx_books_isbn ON books(isbn);

-- If we had another table called book prices, we could use them in joins
SELECT b.title, bp.price 
FROM books b 
JOIN book_prices bp ON b.isbn = bp.isbn;

Considerations and limitations

• Invisible columns still consume storage space

• They can impact performance if used in queries

• They're not a replacement for proper security measures

• SELECT * won't show them, but they're still accessible to anyone who knows they exist

• Making columns invisible and visible will change the column order IDs

Recap

Invisible columns are a nice feature that can help manage app changes in a safe way. They give an easy way for when you need to modify table structures without impacting existing applications.

-Killian