1.3 基于虚拟列的分区

SQL>

SQL> create table tab_1(

2 time date,

3 id number,

4 city_id number,

5 value1 number,

6 value2 varchar2(10),

7 ymtime as (trunc(time,'hh24')))

8 partition by range(ymtime)

9 (

10 partition p_t_tab_0 values less than (to_date('2011-3-1 1:00:00','yyyy-mm-dd hh24:mi:ss')),

11 partition p_t_tab_1 values less than (to_date('2011-3-1 2:00:00','yyyy-mm-dd hh24:mi:ss')),

12 partition p_t_tab_2 values less than (to_date('2011-3-1 3:00:00','yyyy-mm-dd hh24:mi:ss'))

13 )

14 ;

Table created.

SQL> insert into tab_1 values(to_date('2011-3-1 0:00:01','yyyy-mm-dd hh24:mi:ss'),1,1,1,'a');

insert into tab_1 values(to_date('2011-3-1 0:00:01','yyyy-mm-dd hh24:mi:ss'),1,1,1,'a')

*

ERROR at line 1:

ORA-00947: not enough values

--在insert 时，需指定列，因为有一个虚拟列，而此列是不需要insert数据的，会自动算出

SQL> desc tab_1;

Name Null? Type

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

TIME DATE

ID NUMBER

CITY_ID NUMBER

VALUE1 NUMBER

VALUE2 VARCHAR2(10)

YMTIME DATE

SQL> insert into tab_1 values(to_date('2011-3-1 0:00:01','yyyy-mm-dd hh24:mi:ss'),1,1,1,'a',to_date('2011-3-1 0:00:00','yyyy-mm-dd hh24:mi:ss'));

insert into tab_1 values(to_date('2011-3-1 0:00:01','yyyy-mm-dd hh24:mi:ss'),1,1,1,'a',to_date('2011-3-1 0:00:00','yyyy-mm-dd hh24:mi:ss'))

*

ERROR at line 1:

ORA-54013: INSERT operation disallowed on virtual columns

SQL> insert into tab_1(time,id,city_id,value1,value2)

2 values((to_date('2011-3-1 0:00:01','yyyy-mm-dd hh24:mi:ss'),1,1,1,'a');

values((to_date('2011-3-1 0:00:01','yyyy-mm-dd hh24:mi:ss'),1,1,1,'a')

*

ERROR at line 2:

ORA-00907: missing right parenthesis

SQL> insert into tab_1(time,id,city_id,value1,value2)

2 values(to_date('2011-3-1 0:00:01','yyyy-mm-dd hh24:mi:ss'),1,1,1,'a');

1 row created.

--insert成功

SQL> select * from tab_1;

TIME ID CITY_ID VALUE1 VALUE2 YMTIME

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

01-MAR-11 1 1 1 a 01-MAR-11

SQL> insert into tab_1(time,id,city_id,value1,value2)

2 values(to_date('2011-3-1 1:00:01','yyyy-mm-dd hh24:mi:ss'),2,2,2,'b');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from tab_1 partition(p_t_tab_0);

TIME ID CITY_ID VALUE1 VALUE2 YMTIME

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

01-MAR-11 1 1 1 a 01-MAR-11

SQL> select * from tab_1 partition(p_t_tab_1);

TIME ID CITY_ID VALUE1 VALUE2 YMTIME

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

01-MAR-11 2 2 2 b 01-MAR-11

SQL> explain plan for select * from tab_1 where trunc(time,'hh24')=to_date('2011-3-1 1:00:00','yyyy-mm-dd hh24:mi:ss');

Explained.

SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT

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

Plan hash value: 2153153641

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

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

| 0 | SELECT STATEMENT | | 1 | 64 | 3 (0)| 00:00:01 | | |

| 1 | PARTITION RANGE SINGLE| | 1 | 64 | 3 (0)| 00:00:01 | 2 | 2 |

|* 2 | TABLE ACCESS FULL | TAB_1 | 1 | 64 | 3 (0)| 00:00:01 | 2 | 2 |

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

Predicate Information (identified by operation id):

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

2 - filter("TAB_1"."YMTIME"=TO_DATE(' 2011-03-01 01:00:00', 'syyyy-mm-dd

hh24:mi:ss'))

Note

-----

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

19 rows selected.

--从执行计划上看，当有虚拟列时，如果查询条件和虚拟列的定义规则一致，则sql会优化为利用虚拟列去过滤

SQL>

SQL> explain plan for select * from tab_1 where ymtime=to_date('2011-3-1 1:00:00','yyyy-mm-dd hh24:mi:ss');

Explained.

SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT

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

Plan hash value: 2153153641

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

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

| 0 | SELECT STATEMENT | | 1 | 64 | 3 (0)| 00:00:01 | | |

| 1 | PARTITION RANGE SINGLE| | 1 | 64 | 3 (0)| 00:00:01 | 2 | 2 |

|* 2 | TABLE ACCESS FULL | TAB_1 | 1 | 64 | 3 (0)| 00:00:01 | 2 | 2 |

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

Predicate Information (identified by operation id):

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

2 - filter("YMTIME"=TO_DATE(' 2011-03-01 01:00:00', 'syyyy-mm-dd hh24:mi:ss'))

Note

-----

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

18 rows selected.

ITPub博客

oracle10g--11gR2分区表汇总十一

1.3 基于虚拟列的分区

最新文章