When re-ordering columns is warranted

Nearly 10 years ago(!!!) I blogged about how you can toggle columns to invisible and back again as a means of re-ordering the columns in the table. Of course, this is purely a logical re-ordering of the columns, and the post was mainly meant as a bit of a laugh, but I see references to it popping up from time to time as a “good technique”. Sigh

As I even put in the post itself “It really should not be a thing you ever need to do anyway!”.

However, here is a case where re-shuffling the columns is indeed warranted, even to the extent that we want to unload and reload the data in order to physically put the columns in the nominated order. We recently had a customer who was using Index Organised Tables (IOT), and in order to keep the index nice and dense, they separated out some unstructured JSON data into the overflow segment. Here’s a simplified example of what the table looked like:

SQL> create table t (
  2    object_id,
  3    owner,
  4    object_name,
  5    my_json,
  6    constraint t_pk primary key ( object_id ))
  7  organization index
  8  including object_name
  9  overflow tablespace users
 10  as
 11  select object_id, owner, object_name, '{"my":"large","json":"data"}'
 12  from dba_objects
 13  where object_id is not null;

Table created.

“Including object_name” means that all columns before/including that column are stored in the index B-tree, and those after it are placed into and overflow segment. You can see both segments created by using the object number of the table to find the overflow segment for the IOT.

select segment_name, bytes
  2  from   user_segments
  3  where  segment_name in ('T_PK',
  4    ( select 'SYS_IOT_OVER_'||object_id
  5      from   user_objects
  6      where  object_name = 'T' ));

SEGMENT_NAME                        BYTES
------------------------------ ----------
SYS_IOT_OVER_4876657              4194304
T_PK                              6291456

Assuming the JSON was accessed rarely (relative to the other columns), by separating it out to the overflow segment you get a lot more “bang for your buck” with the index because more rows will fit into each leaf block, making the B-tree portion of the table smaller and quicker to scan.

But what happens (as it often does) when applications evolve and you need to add more columns to this table? Because any new column goes “after” the JSON column, they too will added to the overflow segment. Here I’ll add an employee name and back-populate the data.

SQL> alter table t add emp_name varchar2(30);

Table altered.

SQL> update t set emp_name = 'Lawrence Joseph Ellison';

84120 rows updated.

SQL> commit;

Commit complete.

We can see that the index segment has not changed, but the overflow segment has grown to accommodate the new data. If my new application has a heavy demand for the new EMP_NAME column, then a lot of the benefit of using the IOT disappears, because every access is going to be heading into the overflow segment to get the new column data.

SQL> select segment_name, bytes
  2  from   user_segments
  3  where  segment_name in ('T_PK',
  4    ( select 'SYS_IOT_OVER_'||object_id
  5      from   user_objects
  6      where  object_name = 'T' ));

SEGMENT_NAME                        BYTES
------------------------------ ----------
SYS_IOT_OVER_4876657              6291456
T_PK                              6291456

Luckily, there is an easy way to genuinely re-shuffle the columns in an IOT. We can use the column mapping functionality in DBMS_REDEFINITION to re-order the physical placement of the columns. First I’ll need a table in the desired target column order.

SQL> create table t1 (
  2    owner varchar2(100),
  3    object_name varchar2(100),
  4    object_id number,
  5    emp_name varchar2(30),    -- notice new order before json
  6    my_json varchar2(1000),
  7  constraint t1_pk primary key ( object_id ))
  8  organization index
  9  including emp_name
 10  overflow tablespace users;

Table created.

And then I simply use column mapping parameter to nominate the new column order in the table.

SQL> exec dbms_redefinition.can_redef_table(user, 't');

PL/SQL procedure successfully completed.

SQL> exec dbms_redefinition.start_redef_table(user, 't', 't1', col_mapping   => 'owner,object_name,object_id,emp_name,my_json');

PL/SQL procedure successfully completed.

SQL> exec dbms_redefinition.finish_redef_table(user, 't', 't1');

PL/SQL procedure successfully completed.

A little cleanup and the job is done.

SQL> drop table t1 purge;

Table dropped.

SQL> alter table T rename constraint T1_PK to T_PK;

Table altered.

SQL> alter index T1_PK rename to T_PK;

Index altered.

Now when I examine the segment sizes, you can see that the index segment has grown to accommodate the new EMP_NAME column data, and the overflow segment has returned to its initial size (containing the JSON data).

SQL> select segment_name, bytes
  2  from   user_segments
  3  where  segment_name in ('T_PK',
  4    ( select 'SYS_IOT_OVER_'||object_id
  5      from   user_objects
  6      where  object_name = 'T' ));

SEGMENT_NAME                        BYTES
------------------------------ ----------
SYS_IOT_OVER_4876670              4194304
T_PK                              8388608

Just to reiterate – if you are regularly re-shuffling the columns using the invisible/visible trick, then please have a rethink. There is very rarely a need to do so. But if you genuinely need to reorganise the ordering in the physical storage of your data, then DBMS_REDEFINITION is your friend.

pic credit