Oracle 11g虛擬列上建分區

來源:互聯網
上載者:User

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

相關文章

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.