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

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