Making a column invisible and then visible again, moves it at the end in the column order. That’s not always funny…

Let’s make a simple test-table with two columns and insert a row (I’m running 12.1.0.2):

SQL> create table mytable
  2    (
  3      id   number
  4    , id2 number
  5    , id3 number
  6    );

Table MYTABLE created.

SQL> insert
  2  into mytable 
  3  values (1, 2, 3);

1 row inserted.

SQL> select *
  2  from mytable;

        ID        ID2        ID3
---------- ---------- ----------
         1          2          3

SQL> select column_name
  2  , column_id
  3  from user_tab_columns
  4  where table_name = 'MYTABLE';

COLUMN_NAME   COLUMN_ID
------------ ----------
ID                    1
ID2                   2
ID3                   3

OK – so the columns are in expected order. Now make the first column invisible:

SQL> alter table mytable modify id invisible;

Table MYTABLE altered.

SQL> select *
  2  from mytable;

       ID2        ID3
---------- ----------
         2          3

The invisible column is still selectable if you specify it:

SQL> select id
  2  from mytable;

        ID
----------
         1

SQL> select column_name
  2  , column_id
  3  from user_tab_columns
  4  where table_name = 'MYTABLE';

COLUMN_NAME   COLUMN_ID
------------ ----------
ID3                   2
ID2                   1
ID

When we make the column visible again, it’s listed at the end of the column list:

SQL> alter table mytable modify id visible;

Table MYTABLE altered.

SQL> select *
  2  from mytable;

       ID2        ID3         ID
---------- ---------- ----------
         2          3          1

SQL> select column_name
  2  , column_id
  3  from user_tab_columns
  4  where table_name = 'MYTABLE';

COLUMN_NAME   COLUMN_ID
------------ ----------
ID3                   2
ID2                   1
ID                    3

SQL> drop table mytable purge;

Table MYTABLE dropped.

Notice that the columns “switched place” after making the id-column visible again. It’s not a physical change on the block level, but it’s a change in the dictionary regarding the order of column presentation.

The scary part: Putting a round into a chamber

Now – imagine that you have client code that does selects like this:

select * from mytable

And then reads the columns by positional notation. And what if all columns in the table are of the same type? Like in many-to-many relationship-tables?

The morale is:

• Yet another reason to avoid ‘select *’!
• And – be careful where you play with invisible columns!