ITPub博客

首页 > Linux操作系统 > Linux操作系统 > oracle10g--11gR2分区表汇总十一

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

原创 Linux操作系统 作者:jack22220613 时间:2011-03-21 09:42:56 0 删除 编辑

1.3 基于虚拟列的分区

 

SQL>

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                                                                                             

                                                                                                                       

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

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

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

|   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>

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                                                                                            

                                                                                                                       

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

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

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

|   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博客 ” ,链接:http://blog.itpub.net/10037372/viewspace-690028/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2009-05-13

  • 博文量
    94
  • 访问量
    351530