oracle如何创建虚拟列,Oracle 11g虚拟列上建分区

在Oracle 11g上，可以在虚拟列上做分区，这个特性还比较有用，下面来做一个测试：

SQL> select * from v$version;

BANNER

--------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

PL/SQL Release 11.2.0.1.0 - Production

CORE    11.2.0.1.0      Production

TNS for Linux: Version 11.2.0.1.0 - Production

NLSRTL Version 11.2.0.1.0 - Production

SQL> drop table test purge;

SQL> create table test

(bureau_code varchar2(20) not null,

province_code as (CAST(SUBSTR(bureau_code,0,2) AS VARCHAR2(2)))

)

partition by list (province_code)

(

partition p1 values ('01'),

partition p2 values ('02'),

partition p3 values ('03'),

partition p4 values ('04'),

partition p5 values ('05')

);

SQL> insert into test(bureau_code) values('0101');

SQL> insert into test(bureau_code) values('0102');

SQL> insert into test(bureau_code) values('0202');

SQL> insert into test(bureau_code) values('0202');

SQL> insert into test(bureau_code) values('0302');

SQL> insert into test(bureau_code) values('0302');

SQL> insert into test(bureau_code) values('0402');

SQL> insert into test(bureau_code) values('0502');

SQL> commit;

SQL> select * from test partition(p1);

BUREAU_CODE          PR

-------------------- --

0101                01

0102                01

SQL> set autotrace traceonly

SQL> select * from test partition(p1);

执行计划

----------------------------------------------------------

Plan hash value: 213508695

----------------------------------------------------------------------------------------------

| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time    | Pstart| Pstop |

----------------------------------------------------------------------------------------------

|  0 | SELECT STATEMENT      |      |    2 |    30 |    4  (0)| 00:00:01 |      |      |

|  1 |  PARTITION LIST SINGLE|      |    2 |    30 |    4  (0)| 00:00:01 |    1 |    1 |

|  2 |  TABLE ACCESS FULL  | TEST |    2 |    30 |    4  (0)| 00:00:01 |    1 |    1 |--证明是走了分区的

----------------------------------------------------------------------------------------------

Note

-----

- dynamic sampling used for this statement (level=2)

统计信息

----------------------------------------------------------

0  recursive calls

0  db block gets

8  consistent gets

0  physical reads

0  redo size

434  bytes sent via SQL*Net to client

338  bytes received via SQL*Net from client

2  SQL*Net roundtrips to/from client

0  sorts (memory)

0  sorts (disk)

2  rows processed