11G以前Virtual columns的建立是通过建立函数索引的方式。而且在11G以前Virtual columns是"隐藏"的,11G以后可以定义以及显示在表中定义：
做一个测试,以scott的emp表为例子说明:
1. 10G下的测试:
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi
PL/SQL Release 10.2.0.3.0 - Production
CORE 10.2.0.3.0 Production
TNS for Linux: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production
SQL> create index if_ename on emp(lower(ename));
Index created.
SQL> create index if_ename1 on emp(empno,upper(ename));
Index created.
SQL> column column_name format a20
SQL> column data_type format a10
SQL> column data_default format a20
SQL> SELECT column_name, data_type, column_id, data_default, num_distinct FROM dba_tab_cols
WHERE table_name = 'EMP' AND wner = 'SCOTT' ORDER BY 3;
COLUMN_NAME DATA_TYPE COLUMN_ID DATA_DEFAULT NUM_DISTINCT
-------------------- ---------- ---------- -------------------- ------------
EMPNO NUMBER 1 14
ENAME VARCHAR2 2 14
JOB VARCHAR2 3 5
MGR NUMBER 4 6
HIREDATE DATE 5 13
SAL NUMBER 6 12
COMM NUMBER 7 4
DEPTNO NUMBER 8 3
SYS_NC00010$ VARCHAR2 UPPER("ENAME")
SYS_NC00009$ VARCHAR2 LOWER("ENAME")
10 rows selected.
select * from user_ind_expressions where table_name='EMP';
INDEX_NAME TABLE_NAME COLUMN_EXPRESSION COLUMN_POSITION
------------ ----------- ------------------- ---------------
IF_ENAME EMP LOWER("ENAME") 1
IF_ENAME1 EMP UPPER("ENAME") 2
可以发现建立两个虚拟列,SYS_NC00009$=>LOWER("ENAME"),SYS_NC00010$=>UPPER("ENAME").并且建立函数索引时没有统计NUM_DISTINCT等信息。
看看是否可以显示：
SQL> select SYS_NC00009$,SYS_NC00010$,ename from emp where empno= 7369;
SYS_NC0000 SYS_NC0001 ENAME
---------- ---------- ----------
smith SMITH SMITH
可以发现在10g下，这些列是可以显示的，而且一个现实的问题就是SYS_NC00009$这样的字段难以记忆，看看是否可以改名呢！
SQL> alter table emp rename column SYS_NC00009$ to lowerename;
Table altered.
发现居然可以，但是如果接着执行,执行出错退出,即使不访问其他字段情况也一样，也许这个是bug,或者oracle当时没有做好：
SQL> select * from emp ;
select * from emp
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
SQL> select sal from emp ;
select sal from emp
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
SQL> alter table emp rename column lowerename to SYS_NC00009$;
Table altered.
补充一点,如果要在SYS_NC00009$建立直方图，可以执行如下：
BEGIN
SYS.DBMS_STATS.GATHER_TABLE_STATS (
OwnName => 'SCOTT'
,TabName => 'EMP'
,Estimate_Percent => SYS.DBMS_STATS.AUTO_SAMPLE_SIZE
,Method_Opt => 'FOR COLUMNS SYS_NC00009$ SIZE 10 '
,Degree => 4
,Cascade => TRUE
,No_Invalidate => TRUE);
END;
/
SQL> SELECT column_name, data_type, column_id, data_default, num_distinct,histogram FROM dba_tab_cols
WHERE table_name = 'EMP' AND wner = 'SCOTT' ORDER BY 3;
COLUMN_NAME DATA_TYPE COLUMN_ID DATA_DEFAULT NUM_DISTINCT HISTOGRAM
-------------------- ---------- ---------- -------------------- ------------ ---------------
EMPNO NUMBER 1 14 NONE
ENAME VARCHAR2 2 14 NONE
JOB VARCHAR2 3 5 NONE
MGR NUMBER 4 6 NONE
HIREDATE DATE 5 13 NONE
SAL NUMBER 6 12 NONE
COMM NUMBER 7 4 NONE
DEPTNO NUMBER 8 3 NONE
SYS_NC00010$ VARCHAR2 UPPER("ENAME") NONE
SYS_NC00009$ VARCHAR2 LOWER("ENAME") 14 HEIGHT BALANCED
10 rows selected.
2.11G的测试：
SQL> alter table emp add (upperename as (upper(ename)));
Table altered.
SQL> create index if_ename on emp(lower(ename));
Index created.
SQL> column column_name format a20
SQL> column data_type format a10
SQL> column data_default format a20
SQL> SELECT column_name, data_type, column_id, data_default, num_distinct,histogram FROM dba_tab_cols
WHERE table_name = 'EMP' AND wner = 'SCOTT' ORDER BY 3;
COLUMN_NAME DATA_TYPE COLUMN_ID DATA_DEFAULT NUM_DISTINCT HISTOGRAM
-------------------- ---------- ---------- -------------------- ------------ ---------------
EMPNO NUMBER 1 14 NONE
ENAME VARCHAR2 2 14 NONE
JOB VARCHAR2 3 5 NONE
MGR NUMBER 4 6 NONE
HIREDATE DATE 5 13 NONE
SAL NUMBER 6 12 NONE
COMM NUMBER 7 4 NONE
DEPTNO NUMBER 8 3 NONE
UPPERENAME VARCHAR2 9 UPPER("ENAME") NONE
SYS_NC00010$ VARCHAR2 LOWER("ENAME") NONE
10 rows selected.
--注意upperename的column_id=9,而SYS_NC00010$的column_id=NULL.
执行select * from emp;就很容易明白,会显示upperename字段。
SQL> select upperename,SYS_NC00010$,ename from emp where empno= 7369;
UPPERENAME SYS_NC0001 ENAME
---------- ---------- ----------
SMITH smith SMITH
看看是否可以改名:
SQL> alter table emp rename column SYS_NC00010$ to lowerename;
Table altered.
SQL> select upperename,lowerename,ename from emp where empno= 7369;
UPPERENAME LOWERENAME ENAME
---------- ---------- ----------
SMITH smith SMITH
正常！