DBA_TAB_COLUMNS vs DBA_TAB_COLS

Not sure how many DBAs have noticed the difference between DBA_TAB_COLUMNS view and DBA_TAB_COLS view. Both views look pretty identical, if you are not paying too much attention. According to Oracle documentation, DBA_TAB_COLS describes the columns of all tables, views, and clusters in the database. This view differs from “DBA_TAB_COLUMNS” in that system-generated hidden columns and invisible columns, which are user-generated hidden columns, are not filtered out.

Let me dig a little deep to understand the differences better. First, how the views are built…

Until 11gR2, DBA_TAB_COLS view is a complex SQL joining multiple internal dictionary tables. The FROM clause is shown here…

The FROM clause of DBA_TAB_COLS view in 10gR2 is…

--10gR2 
SQL> set long 32000 
SQL> select text from dba_views 
     where view_name = 'DBA_TAB_COLS'; 
 
     from sys.col$ c, sys.obj$ o, sys.hist_head$ h, sys.user$ u, 
          sys.coltype$ ac, sys.obj$ ot, sys.user$ ut 

The FROM clause of DBA_TAB_COLS view in 11gR2 is…

--11gR2
SQL> select text from dba_views 
     where view_name = 'DBA_TAB_COLS';

     from sys.col$ c, sys."_CURRENT_EDITION_OBJ" o, sys.hist_head$ h, 
     sys.user$ u, sys.coltype$ ac, sys.obj$ ot, 
     sys."_BASE_USER" ut, sys.tab$ t

Because of the increased complexity by introducing new features like invisible columns, I think Oracle decided to create an intermediate view named DBA_TAB_COLS_V$ to make the SQL for DBA_TAB_COLS simpler in 12c. The view SQL for SYS.DBA_TAB_COLS_V$ is similar to the SQL used in DBA_TAB_COLS in 11gR2, but has more lines.

So, in 12c, the DBA_TAB_COLS view is a straight SELECT of columns from DBA_TAB_COLS_V$ without any WHERE clause.

In all versions the DBA_TAB_COLUMNS is a query on selected columns from DBA_TAB_COLS with a WHERE clause to filter the hidden columns. In 10g and 11g, the WHERE clause is where HIDDEN_COLUMN = ‘NO’ and in 12c the WHERE clause changed to where USER_GENERATED = ‘YES’.

Full SQL used for the DBA_TAB_COLUMNS view in 12c is below.

--12cR1
SQL> set long 32000
SQL> select text from dba_views where view_name = 'DBA_TAB_COLUMNS';

TEXT
----------------------------------------------------------------------
select OWNER, TABLE_NAME,
COLUMN_NAME, DATA_TYPE, DATA_TYPE_MOD, DATA_TYPE_OWNER,
DATA_LENGTH, DATA_PRECISION, DATA_SCALE, NULLABLE, COLUMN_ID,
DEFAULT_LENGTH, DATA_DEFAULT, NUM_DISTINCT, LOW_VALUE, HIGH_VALUE,
DENSITY, NUM_NULLS, NUM_BUCKETS, LAST_ANALYZED, SAMPLE_SIZE,
CHARACTER_SET_NAME, CHAR_COL_DECL_LENGTH,
GLOBAL_STATS, USER_STATS, AVG_COL_LEN, CHAR_LENGTH, CHAR_USED,
V80_FMT_IMAGE, DATA_UPGRADED, HISTOGRAM, DEFAULT_ON_NULL,
IDENTITY_COLUMN, SENSITIVE_COLUMN,
EVALUATION_EDITION, UNUSABLE_BEFORE, UNUSABLE_BEGINNING
from DBA_TAB_COLS
where USER_GENERATED = 'YES'

Which columns are available in DBA_TAB_COLS that are not in DBA_TAB_COLUMNS? Following query shows the columns that are additional in DBA_TAB_COLS in 12cR1.

SQL> select column_name from dba_tab_cols 
     where table_name = 'DBA_TAB_COLS'
     minus
     select column_name from dba_tab_cols 
     where table_name = 'DBA_TAB_COLUMNS';

COLUMN_NAME
-----------------------------
HIDDEN_COLUMN
INTERNAL_COLUMN_ID
QUALIFIED_COL_NAME
SEGMENT_COLUMN_ID
USER_GENERATED
VIRTUAL_COLUMN

USER_GENERATED column is new in 12c, all other columns are same in 11gR2/10gR2 as well.

When you mark a column in table as UNUSED (that is a candidate for drop), it becomes hidden in 11gR2. Is Hidden Column different from Invisible Column in 12c DBA_TAB_COLS?

Let me create a table with the following characteristics. Columns 1, 4 & 7 are regular columns with no speciality. x2 is invisible, x3 is virtual, x5 is invisible and virtual, x6 is identity.

SQL> create table hidden_test (
     x1 number,
     x2 number invisible,
     x3 number generated always as (x1/2) virtual,
     x4 number,
     x5 number invisible generated always as (x1/4) virtual,
     x6 number generated always as identity,
     x7 number );

Table created.

Query the column properties of the table just created.

SQL> col column_name format a5
SQL> select column_name, user_generated, virtual_column, hidden_column, 
            identity_column, column_id, segment_column_id, internal_column_id
     from dba_tab_cols
     where table_name = 'HIDDEN_TEST';

COLUM USE VIR HID IDE  COLUMN_ID SEGMENT_COLUMN_ID INTERNAL_COLUMN_ID
----- --- --- --- --- ---------- ----------------- ------------------
X7    YES NO  NO  NO           5                 5                  7
X6    YES NO  NO  YES          4                 4                  6
X5    YES YES YES NO                                                5
X4    YES NO  NO  NO           3                 3                  4
X3    YES YES NO  NO           2                                    3
X2    YES NO  YES NO                             2                  2
X1    YES NO  NO  NO           1                 1                  1

7 rows selected.

Notice all columns have USER_GENERATED as YES, which means all of these columns will be visible in DBA_TAB_COLUMNS. Remeber, this is the filter condition for DBA_TAB_COLUMNS, so for all subsequent queries on DBA_TAB_COLS, if USER_GENERATED is NO, those columns will not be visible in DBA_TAB_COLUMNS. [[I would have liked to see VIRTUAL_COLUMN (HIDDEN_COLUMN too) as part of DBA_TAB_COLUMNS, they would have added some useful information to DBA_TAB_COLUMNS.]] Virtual column information is included in DBA_TAB_COLUMNS, but does not identify if the column is VIRTUAL or not. The invisible columns are marked as HIDDEN_COLUMN=YES. Now, mark x4 for drop and x2 visible.

SQL> alter table hidden_test set unused (x4);

Table altered.

SQL> alter table hidden_test modify x2 visible;

Table altered.

SQL>

Query the properties again…

SQL> select column_name, user_generated, virtual_column, hidden_column, 
            identity_column, column_id, segment_column_id, internal_column_id
     from dba_tab_cols
     where table_name = 'HIDDEN_TEST';

COLUMN_NAM USE VIR HID IDE  COLUMN_ID SEGMENT_COLUMN_ID INTERNAL_COLUMN_ID
---------- --- --- --- --- ---------- ----------------- ------------------
X7         YES NO  NO  NO           4                 5                  7
X6         YES NO  NO  YES          3                 4                  6
X5         YES YES YES NO                                                5
SYS_C00004 NO  NO  YES NO                             3                  4
_14060322:
52:06$

X3         YES YES NO  NO           2                                    3
X2         YES NO  NO  NO           5                 2                  2
X1         YES NO  NO  NO           1                 1                  1

7 rows selected.

The x4 column name marked for drop got renamed, and got a system generated name. The rename is to facilitate adding a column with same name to the table. The column x4 also got property changes – USER_GENERATED became NO and HIDDEN_COLUMN changed to YES. Also, the COLUMN_ID is released, thus the column will not be visilbe in “SELECT *” and “DESCRIBE”. The UNUSED column still maintains the same INTERNAL_COLUMN_ID.

When x2 column is made VISIBLE, it got a new COLUMN_ID assigned (the highest available, thus the column becomes the last column in “SELECT *” and “DESCRIBE”). Its hidden status changed to NO.

What happens to the ID columns, when the UNUSED column is dropped.

SQL> alter table hidden_test drop unused columns;

Table altered.

SQL> select column_name, user_generated, virtual_column, hidden_column, 
            identity_column, column_id, segment_column_id, internal_column_id
     from dba_tab_cols
     where table_name = 'HIDDEN_TEST';

COLUMN_NAM USE VIR HID IDE  COLUMN_ID SEGMENT_COLUMN_ID INTERNAL_COLUMN_ID
---------- --- --- --- --- ---------- ----------------- ------------------
X7         YES NO  NO  NO           4                 4                  6
X6         YES NO  NO  YES          3                 3                  5
X5         YES YES YES NO                                                4
X3         YES YES NO  NO           2                                    3
X2         YES NO  NO  NO           5                 2                  2
X1         YES NO  NO  NO           1                 1                  1

The COLUMN_ID got reordered after the unused column is dropped (X4 was dropped, hence X6 and X7 got new column ids – X5 never had a column id assigned as it is INVISIBLE). The internal column id, which includes id for INVISIBLE columns also got adjusted. After the column is dropped, the INTERNAL_COLUMN_ID also got adjusted.

Well, I started with just difference between DBA_TAB_COLS and DBA_TAB_COLUMNS, but got into investigating column ids… 🙂

[amazon text=Amazon&template=carousel&asin=111864395X]