What’s in a Name ? USER_TAB_COLS and USER_TAB_COLUMNS are different.

My son and I are quite similar in some ways ( although he would vehemently dispute this).
Like me, he works in IT, in his case as a Support Engineer.
Like me, he’s called Mike (well, my Mum likes the name…and I can spell it).
Unlike me – as he would be quick to point out – he still has all his own hair.
These similarities have been known to cause confusion – I’m often contacted by recruitment agents with enticing offers to work on…some newfangled stuff I know nothing about, whilst he’s constantly being offered “exciting” Database related opportunities.

Similar confusion can arise when you’re delving into the Oracle Data Dictionary…

Note – the examples that follow apply to 11gR2. Additionally, apart from the COLS synonym, what is true for USER_TAB_COLUMNS and USER_TAB_COLS also applies to their ALL_ and DBA_ equivalents.

When it comes to getting column meta-data out of the Data Dictionary, you’ve got several choices. To illustrate this, connect as HR and ….

select column_name, data_type
from cols
where table_name = 'EMPLOYEES'
order by column_id
/

select column_name, data_type
from user_tab_columns
where table_name = 'EMPLOYEES'
order by column_id
/

select column_name, data_type
from user_tab_cols
where table_name = 'EMPLOYEES'
order by column_id
/

In each case the results are identical :

COLUMN_NAME		       DATA_TYPE
------------------------------ --------------------
EMPLOYEE_ID		       NUMBER
FIRST_NAME		       VARCHAR2
LAST_NAME		       VARCHAR2
EMAIL			       VARCHAR2
PHONE_NUMBER		       VARCHAR2
HIRE_DATE		       DATE
JOB_ID			       VARCHAR2
SALARY			       NUMBER
COMMISSION_PCT		       NUMBER
MANAGER_ID		       NUMBER
DEPARTMENT_ID		       NUMBER

11 rows selected.

So, it would appear that COLS, USER_TAB_COLUMNS and USER_TAB_COLS are all synonyms for the same thing…

select synonym_name, table_owner, table_name
from all_synonyms
where synonym_name in ('COLS', 'USER_TAB_COLS', 'USER_TAB_COLUMNS')
order by table_name
/ 

SYNONYM_NAME		       TABLE_OWNER	    TABLE_NAME
------------------------------ -------------------- --------------------
USER_TAB_COLS		       SYS		    USER_TAB_COLS
USER_TAB_COLUMNS	       SYS		    USER_TAB_COLUMNS
COLS			       SYS		    USER_TAB_COLUMNS

…OK, so COLS is indeed a synonym for USER_TAB_COLUMNS. USER_TAB_COLS and USER_TAB_COLUMNS also appear to be identical…

select table_name, comments
from all_tab_comments
where table_name in ('USER_TAB_COLUMNS', 'USER_TAB_COLS')
/

TABLE_NAME	     COMMENTS
-------------------- --------------------------------------------------
USER_TAB_COLS	     Columns of user's tables, views and clusters
USER_TAB_COLUMNS     Columns of user's tables, views and clusters

There you go then. Must be the case…

Unused Columns

Lets create another table in the HR schema as a copy of EMPLOYEES….

create table non_sales_emps as
    select * 
    from employees
    where commission_pct is null
/

Table created.

As the name suggests, we’re not going to have any Sales Staff in this table, so we don’t really need the COMMISSION_PCT column…

SQL> alter table non_sales_emps
  2      set unused column commission_pct
  3  /

Table altered.

SQL> 

So, the table no longer contains the COMMISSION_PCT column…

select column_name, data_type
from user_tab_cols
where table_name = 'NON_SALES_EMPS'
order by column_id
/

COLUMN_NAME		       DATA_TYPE
------------------------------ --------------------
EMPLOYEE_ID		       NUMBER
FIRST_NAME		       VARCHAR2
LAST_NAME		       VARCHAR2
EMAIL			       VARCHAR2
PHONE_NUMBER		       VARCHAR2
HIRE_DATE		       DATE
JOB_ID			       VARCHAR2
SALARY			       NUMBER
MANAGER_ID		       NUMBER
DEPARTMENT_ID		       NUMBER
SYS_C00009_15061918:46:18$     NUMBER

11 rows selected.

Yes, the table now has 10 columns…and here are the details of all 11 of them.
Weren’t expecting that ? Well then you probably won’t be expecting this either….

select column_name, data_type
from user_tab_columns
where table_name = 'NON_SALES_EMPS'
order by column_id
/

COLUMN_NAME		       DATA_TYPE
------------------------------ --------------------
EMPLOYEE_ID		       NUMBER
FIRST_NAME		       VARCHAR2
LAST_NAME		       VARCHAR2
EMAIL			       VARCHAR2
PHONE_NUMBER		       VARCHAR2
HIRE_DATE		       DATE
JOB_ID			       VARCHAR2
SALARY			       NUMBER
MANAGER_ID		       NUMBER
DEPARTMENT_ID		       NUMBER

10 rows selected.

The extra column has magically disappeared again. Just what is going on ?

Delving a bit deeper into this particular rabbit-hole…

select column_name, data_type
from all_tab_columns
where owner = 'SYS'
and table_name = 'USER_TAB_COLS'
minus
select column_name, data_type
from all_tab_columns
where owner = 'SYS'
and table_name = 'USER_TAB_COLUMNS'
/

COLUMN_NAME		       DATA_TYPE
------------------------------ ------------------------------
HIDDEN_COLUMN		       VARCHAR2
INTERNAL_COLUMN_ID	       NUMBER
QUALIFIED_COL_NAME	       VARCHAR2
SEGMENT_COLUMN_ID	       NUMBER
VIRTUAL_COLUMN		       VARCHAR2

SQL> 

From this we can see that USER_TAB_COLS contains five additional columns over those available in USER_TAB_COLUMNS.

select column_name, comments
from all_col_comments
where owner = 'SYS'
and table_name = 'USER_TAB_COLS'
and column_name in ( 'HIDDEN_COLUMN', 'INTERNAL_COLUMN_ID', 
    'QUALIFIED_COL_NAME', 'SEGMENT_COLUMN_ID', 'VIRTUAL_COLUMN')
/

COLUMN_NAME		  COMMENTS
------------------------- --------------------------------------------------
HIDDEN_COLUMN		  Is this a hidden column?
VIRTUAL_COLUMN		  Is this a virtual column?
SEGMENT_COLUMN_ID	  Sequence number of the column in the segment
INTERNAL_COLUMN_ID	  Internal sequence number of the column
QUALIFIED_COL_NAME	  Qualified column name

Furthermore, if we examine the source code for the USER_TAB_COLUMNS view, the reason for it’s similarity with USER_TAB_COLS becomes apparent :

select 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
  from USER_TAB_COLS
 where HIDDEN_COLUMN = 'NO'

So, just when does it become useful to use USER_TAB_COLS rather than USER_TAB_COLUMNS ?

In 11g, you’d set a column to be unused on a large table in order for the “drop” to happen quickly.
Once you have set it to unused, the only thing you can do with it is drop it altogether to reclaim the space it’s using.
To find out which tables have unused columns, you can use…

select table_name, count
from user_unused_col_tabs
/

TABLE_NAME			    COUNT
------------------------------ ----------
NON_SALES_EMPS				1

…and if you want to drop an unused column, you don’t need to specify it’s name in the command…

alter table non_sales_emps
    drop unused columns
/

Table altered.

Where USER_TAB_COLS does come in handy is when you’re looking for …

Virtual Columns

For example, we could add a column to our NON_SALES_EMPS table to calculate the number of completed years service for each employee.
First, we need a deterministic function to return the number of full years between a given date and today :

create or replace function years_elapsed_fn( i_date in date)
    return number deterministic
as
begin
    return floor( months_between( trunc(sysdate), i_date) / 12);
end;
/

Now we add a virtual column to the table which calls this function :

alter table non_sales_emps
    add years_service generated always as
        (years_elapsed_fn(hire_date))
/

Whilst there’s no way to tell which columns are virtual in USER_TAB_COLUMNS, there is in USER_TAB_COLS :

select column_name, data_type, virtual_column
from user_tab_cols
where table_name = 'NON_SALES_EMPS'
/

COLUMN_NAME		  DATA_TYPE			 VIR
------------------------- ------------------------------ ---
EMPLOYEE_ID		  NUMBER			 NO
FIRST_NAME		  VARCHAR2			 NO
LAST_NAME		  VARCHAR2			 NO
EMAIL			  VARCHAR2			 NO
PHONE_NUMBER		  VARCHAR2			 NO
HIRE_DATE		  DATE				 NO
JOB_ID			  VARCHAR2			 NO
SALARY			  NUMBER			 NO
MANAGER_ID		  NUMBER			 NO
DEPARTMENT_ID		  NUMBER			 NO
YEARS_SERVICE		  NUMBER			 YES

11 rows selected.

SQL> 

The reasons for having two such similar ( and similarly named) dictionary views seem to have been lost in the mists of time.
Whatever the rationale, it’s worth knowing the difference next time you need to go poking around the column meta-data in your database.