大分区表数据切换的解决方案

1、问题描述

大表mv_auc上有hash分区,3个索引; 多用户不定时查询;如何使新旧数据快速交换,并且不影响查询性能? 

 

2、问题分析

可以将每天的数据导入到一个唯一命名的表，比如T20090708，然后通过建立一个视图来屏蔽底层表的变化（通过存储过程的动态sql来实现视图定义的动态变化），当每天凌晨我们把数据导入表中之后，即执行vw_shift存储过程来改变vw_test视图的定义，这样就会使得查询业务平稳切换，完全不会影响到业务，并且查询性能也不会受到影响。

 

3、方案验证

实验方案的设计：创建两张表，一张为大表t20090708，共3670016行；一张为小表t20090709，共896行。之所以将第一张表设计得如此之大，目的就是为了使查询操作的时间尽量长，这样可以保证在这个查询操作执行的过程中我们有充裕的时间进行视图定义的切换，同时这样的设计也可以验证该方案完全不会影响业务操作。

实验旨在验证所提出的大分区表数据切换的可操作性，通过set timing on为命令提示符前加上时间线索，所显示的时间与命令的执行时间基本一致，三个会话交错执行，从时间上可以一目了然地看出它们的执行轨迹。

 

会话1：

22:46:43 SQL> select count(*) from t20090708;

 

  COUNT(*)

----------

   3670016

 

22:47:27 SQL> select count(*) from t20090709;

 

  COUNT(*)

----------

       896

 

22:47:42 SQL> create or replace view vw_test as select * from t20090708;

 

视图已创建。

 

22:48:00 SQL> create or replace procedure vw_shift as

22:48:10   2  curr_date varchar2(10);

22:48:10   3  vw_str varchar2(100);

22:48:10   4  begin

22:48:10   5    select to_char(sysdate,'yyyymmdd') into curr_date from dual;

22:48:10   6    vw_str := ' create or replace view vw_test as select * from t'||

curr_date;

22:48:10   7    execute immediate vw_str;

22:48:10   8  end;

22:48:13   9  /

 

过程已创建。

 

22:51:12 SQL> exec vw_shift

 

PL/SQL 过程已成功完成。

 

会话2：

22:50:04 SQL> set autot on

22:50:09 SQL> alter system flush shared_pool;

 

系统已更改。

 

22:50:34 SQL> select count(*) from vw_test;

 

  COUNT(*)

----------

   3670016

 

执行计划

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

Plan hash value: 1428396537

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

| Id  | Operation          | Name      | Rows  | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT   |           |     1 |     5   (0)| 00:00:01 |

|   1 |  SORT AGGREGATE    |           |     1 |            |          |

|   2 |   TABLE ACCESS FULL| T20090709 |   896 |     5   (0)| 00:00:01 |

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

 

Note

-----

   - dynamic sampling used for this statement

 

这里要注意的是，会话2在查询vw_test视图的时候视图定义还没有修改，所以上面查询的是基表t20090708，结果为3670016。不过，执行计划中所显示的基表为t20090709，这是由于Oracle在开始执行查询操作的时候会首先将视图名称转化为视图的定义，然后才执行查询操作，实际上这时候的查询已经与视图毫无关系了，在查询操作执行期间，我们可以改变视图的定义，从而使得视图指向另一个基表t20090709。而执行计划的生成是在查询操作结束之后进行的，此时视图的定义已经发生变化，因此就出现了真正查询的表与执行计划中显示的表名不相同的情况

 

会话3：

22:52:07 SQL> set autot on

22:52:39 SQL> select count(*) from vw_test;

 

  COUNT(*)

----------

       896

 

执行计划

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

Plan hash value: 1428396537

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

| Id  | Operation          | Name      | Rows  | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT   |           |     1 |     5   (0)| 00:00:01 |

|   1 |  SORT AGGREGATE    |           |     1 |            |          |

|   2 |   TABLE ACCESS FULL| T20090709 |   896 |     5   (0)| 00:00:01 |

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

 

Note

-----

   - dynamic sampling used for this statement

 

上面的查询操作是在视图定义改变之后开始执行的，因此，所查询的基表是t20090709，结果为896。同样，执行计划中显示的基表仍然为t20090709。

 

接下来验证一下分区表的执行计划是否会受到影响：

SQL> create table t2009(

  2  id number,

  3  EMPNO NUMBER(4),

  4  ENAME                                              VARCHAR2(10),

  5  JOB                                                VARCHAR2(9),

  6  MGR                                                NUMBER(4),

  7  HIREDATE                                           DATE,

  8  SAL                                                NUMBER(7,2),

  9  COMM                                               NUMBER(7,2),

 10  DEPTNO                                             NUMBER(2)

 11  )

 12  PARTITION BY HASH(id)

 13  (PARTITION p1 ,

 14   PARTITION p2,

 15   PARTITION p3,

 16   PARTITION p4);

 

表已创建。

 

SQL> insert into t2009 select * from t20090709;

 

已创建896行。

 

SQL> commit;

 

提交完成。

 

SQL> create or replace view vw_test as select * from t2009;

 

视图已创建。

 

SQL> create index idx_empno on t2009(empno);

 

索引已创建。

 

SQL> select * from vw_test where empno=8888;

 

 

执行计划

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

Plan hash value: 1069650138

 

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

 

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

 

| Id  | Operation                          | Name      | Rows  | Bytes | Cost (%

 

CPU)| Time     | Pstart| Pstop |

 

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

 

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

 

|   0 | SELECT STATEMENT                   |           |     1 |    43 |     2

 (0)| 00:00:01 |       |       |

 

|   1 |  TABLE ACCESS BY GLOBAL INDEX ROWID| T2009     |     1 |    43 |     2

 (0)| 00:00:01 | ROWID | ROWID |

 

|*  2 |   INDEX RANGE SCAN                 | IDX_EMPNO |     1 |       |     1

 (0)| 00:00:01 |       |       |

 

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

 

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

 

 

Predicate Information (identified by operation id):

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

 

   2 - access("EMPNO"=8888)

 

 

统计信息

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

        223  recursive calls

          0  db block gets

         42  consistent gets

          0  physical reads

          0  redo size

        854  bytes sent via SQL*Net to client

        385  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          2  sorts (memory)

          0  sorts (disk)

          1  rows processed

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/15203236/viewspace-610709/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/15203236/viewspace-610709/