[20111221]关于11G Virtual columns.txt

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

正常！

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/267265/viewspace-713677/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/267265/viewspace-713677/