Create partitions on the 11g virtual column of Oracle

Create partitions on the 11g virtual column of Oracle

On Oracle 11g, partitions can be performed on virtual columns. This feature is useful. Let's perform a test:

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 ('20140901 ');
SQL> insert into test (bureau_code) values ('20140901 ');
SQL> insert into test (bureau_code) values ('20140901 ');
SQL> insert into test (bureau_code) values ('20140901 ');
SQL> insert into test (bureau_code) values ('20140901 ');
SQL> insert into test (bureau_code) values ('20140901 ');
SQL> insert into test (bureau_code) values ('20140901 ');
SQL> insert into test (bureau_code) values ('20140901 ');
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 );
Execution Plan
----------------------------------------------------------
Plan hash value: 213508695
Bytes ----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | Pstart | Pstop |
Bytes ----------------------------------------------------------------------------------------------
| 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 | -- it indicates that the partition is used.
Bytes ----------------------------------------------------------------------------------------------
Note
-----
-Dynamic sampling used for this statement (level = 2)
Statistics
----------------------------------------------------------
0 recursive cballs
0 db block gets
8 consistent gets
0 physical reads
0 redo size
434 bytes sent via SQL * Net to client
338 bytes encoded ed via SQL * Net from client
2 SQL * Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2 rows processed