Learning is not a spectator sport

July 22, 2013

12c invisible columns

Filed under: Uncategorized — connormcdonald @ 9:27 pm

As several bloggers have already pointed out, column order changes when you start playing with invisible columns, for example

SQL> create table T ( c1 int, c2 int, c3 int );

Table created.

SQL> desc T
 Name                                      Null?    Type
 ----------------------------------------- -------- ---------------
 C1                                                 NUMBER(38)
 C2                                                 NUMBER(38)
 C3                                                 NUMBER(38)

SQL> alter table T modify c1 invisible;

Table altered.

SQL> alter table T modify c1 visible;

Table altered.

SQL> desc T
 Name                                      Null?    Type
 ----------------------------------------- -------- ---------------
 C2                                                 NUMBER(38)
 C3                                                 NUMBER(38)
 C1                                                 NUMBER(38)

So just for a laugh, here’s a little routine to put the column order back to what you want :-)

SQL> create or replace
  2  procedure fix_cols(p_tname varchar2, p_col_list varchar2) is
  3    l_col_list varchar2(1000) := p_col_list||',';
  4    type clist is table of varchar2(30)
  5      index by pls_integer;
  6   c clist;
  7
  8   this_col varchar2(30);
  9   l_id int;
 10  begin
 11    while instr(l_col_list,',') > 1 loop
 12      c(c.count+1) := substr(l_col_list,1,instr(l_col_list,',')-1);
 13      l_col_list := substr(l_col_list,instr(l_col_list,',')+1);
 14      dbms_output.put_line(c(c.count));
 15    end loop;
 16
 17    for i in 1 .. c.count loop
 18      loop
 19         select column_name
 20         into   this_col
 21         from   user_tab_columns
 22         where  table_name = p_tname
 23         and    column_id = i;
 24
 25         exit when this_col = c(i);
 26
 27         execute immediate 'alter table '||p_tname||' modify '||this_col||' invisible';
 28         execute immediate 'alter table '||p_tname||' modify '||this_col||' visible';
 29      end loop;
 30    end loop;
 31  end;
 32  /

Procedure created.

SQL> exec fix_cols('T','C1,C2,C3');

PL/SQL procedure successfully completed.

SQL> desc T
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 C1                                                 NUMBER(38)
 C2                                                 NUMBER(38)
 C3                                                 NUMBER(38)
 
 
About these ads

6 Comments »

  1. Can you please try fix_cols(‘T’,’C1,C2,C3′) and then fix_cols(‘T’,’C1,C3,C2′); ?
    I believe there’s a bug that will fail to reorder certain permutations.

    Otherwise, nice catch.

    Did you verify what happens with rows inside materialized view log?
    It used to have bit-coded column positions in change_vector$$ column. I wonder if just a single “invisible” breaks the data inside the log.

    Does that work with functional-based index generated columns?

    Comment by Vladimir Sitnikov (@VladimirSitniko) — July 23, 2013 @ 5:11 am

    • Seems to work as expected … did you have something else in mind ?

      SQL> exec fix_cols(‘T’,’C1,C2,C3′)

      PL/SQL procedure successfully completed.

      SQL> desc t
      Name Null? Type
      —————————————– ——– —————————-
      C1 NUMBER(38)
      C2 NUMBER(38)
      C3 NUMBER(38)

      SQL> exec fix_cols(‘T’,’C1,C3,C2′)

      PL/SQL procedure successfully completed.

      SQL> desc t
      Name Null? Type
      —————————————– ——– —————————-
      C1 NUMBER(38)
      C3 NUMBER(38)
      C2 NUMBER(38)

      SQL> exec fix_cols(‘T’,’C1,C2,C3′)

      PL/SQL procedure successfully completed.

      SQL> desc t
      Name Null? Type
      —————————————– ——– —————————-
      C1 NUMBER(38)
      C2 NUMBER(38)
      C3 NUMBER(38)

      Having said that, the procedure is hardly what you’d call robust :-)

      In terms of change vectors, I would imagine* that the internal column id would be used (in the same way handling of an ‘unused’ column would be)

      SQL> select column_name, COLUMN_ID, INTERNAL_COLUMN_ID
      2 from user_tab_cols
      3 where table_name = ‘T';

      COLUMN_NAME COLUMN_ID INTERNAL_COLUMN_ID
      —————————— ———- ——————
      C1 1 1
      C2 3 2
      C3 2 3

      * – of course, the term ‘imagine’ is not the same as ‘i am sure’ :-)

      Comment by Connor McDonald — July 24, 2013 @ 8:08 am

  2. […] Invisible columns and column order […]

    Pingback by Oracle Database 12c (12.1) Installation and New Features | DBLinks Consulting Ltée — August 20, 2013 @ 3:50 am

  3. […] Invisible columns and column order […]

    Pingback by Oracle Database 12c (12.1) Installation and New Features | DBLinks Consulting LTDDBLinks Consulting LTD — October 22, 2013 @ 1:56 am

  4. […] a neat way to now hide the existence columns within a table. It’s also a neat way to now easily change the default ordering of columns within a table […]

    Pingback by 12c Invisible Columns (The Invisible Man) | Richard Foote's Oracle Blog — November 19, 2013 @ 12:29 pm

  5. brand, your LinkedIn profile, how you come across, or what anyone thinks of you. The genuine you.
    Oakley Jupiter Squared http://www.ebuyaccessories.com

    Comment by Oakley Jupiter Squared — September 10, 2014 @ 9:54 am


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

The WordPress Classic Theme. Create a free website or blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 80 other followers

%d bloggers like this: