分区表维护~我的SQL不一定解决，主要分区原理 谢谢

saup007

有一张表，有会员等级，会员等级可能发生变化，如果我以等级划分区的话，怎么处理后续问题？
创建表：

1. -- Create table
   
2. create table T_PART_INTER
   
3. (
   
4.   ID          NUMBER,
   
5.   NAME        VARCHAR2(30),
   
6.   CREATE_DATE DATE
   
7. )
   
8. partition by range (CREATE_DATE)
   
9. (
   
10.   partition P1 values less than (TO_DATE(' 2009-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    
11.     tablespace USERS
    
12.     pctfree 10
    
13.     initrans 1
    
14.     maxtrans 255,
    
15.   partition P2 values less than (TO_DATE(' 2009-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    
16.     tablespace USERS
    
17.     pctfree 10
    
18.     initrans 1
    
19.     maxtrans 255,
    
20.   partition P3 values less than (TO_DATE(' 2009-07-30 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    
21.     tablespace USERS
    
22.     pctfree 10
    
23.     initrans 1
    
24.     maxtrans 255
    
25.     storage
    
26.     (
    
27.       initial 8M
    
28.       next 1M
    
29.       minextents 1
    
30.       maxextents unlimited
    
31.     ),
    
32.   partition P4 values less than (TO_DATE(' 2009-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    
33.     tablespace USERS
    
34.     pctfree 10
    
35.     initrans 1
    
36.     maxtrans 255
    
37.     storage
    
38.     (
    
39.       initial 8M
    
40.       next 1M
    
41.       minextents 1
    
42.       maxextents unlimited
    
43.     )
    
44. );

复制代码

1. 插入只有一条数据：
   
2. 
   
3. insert into t_part_inter  values(1,'TEST',to_date('20080101','yyyymmdd'));
   
4. 
   
5. SQL> select * from t_part_inter partition (p1);
   
6. 
   
7.         ID NAME                           CREATE_DATE
   
8. ---------- ------------------------------ -----------
   
9.          1 TEST                           2008/1/1
   
10. 
    
11. SQL> update  t_part_inter set create_date=to_date('20090101','yyyymmdd')  where id=1;
    
12. 
    
13. update  t_part_inter set create_date=to_date('20090101','yyyymmdd')  where id=1
    
14. 
    
15. ORA-14402: 更新分区关键字列将导致分区的更改
    
16. 
    
17. SQL> alter table t_part_inter enable row movement;
    
18. 
    
19. Table altered
    
20. 
    
21. SQL> update  t_part_inter set create_date=to_date('20090101','yyyymmdd')  where id=1;
    
22. 
    
23. 1 row updated
    
24. 
    
25. SQL> select * from t_part_inter partition (p1);
    
26. 
    
27.         ID NAME                           CREATE_DATE
    
28. ---------- ------------------------------ -----------
    
29. 
    
30. SQL> select * from t_part_inter partition (p2);
    
31. 
    
32.         ID NAME                           CREATE_DATE
    
33. ---------- ------------------------------ -----------
    
34.          1 TEST                           2009/1/1

复制代码

问题1：如果我拿等级，实验用的时间 分区，那以后维护怎么办? 行迁移的代价怎么估计。。

问题2：分区怎么规划 存储的？  分区之间怎么划分物理结构的

问题3：关于分区索引，如果这张表在统计系统上，按天分区，所有系统都同步到这张表，导致每一天的数据量还是很很大的，每天跑昨天的数据，结果到新的表中，现在job里的sql，有分区全扫描，我想加一个分区索引，但几天以前的分区除了单独查询，job统计并不需要，所以可以只创建某个分区以后的local索引吗？

dba_tables  字段ROW_MOVEMENT 为enable就可以了。
补充：问题1、问题2 是由一个优化SQL引起的，问题3是另一个问题

问题1、问题2 的来由SQL：
select
             t.product_id,
             l.min_level,
             count(*)
         from
             USER_ROLE_807 t, level_interval l
         where
             t.role_state        = 5                       and
             last_logout_time    >= trunc(sysdate)-27   and
             t.role_level between l.min_level and l.max_level and
             l.code_id = 807
         group by t.product_id , l.min_level


清算t.product_id  某个产品，用户在某个等级的区间内的人数统计。

如果生个产品等级区间相同：
0~5
5~10
可以 round(level/等级区间) ，这样统计划分0  1 2 3 ...等人数即可。

现在问题是，各个产品等级区间不同，可能另一产品
0~2
2~5
5~10
这才是杯具的点。Orz~~~
所以想把USER_ROLE_*** 中比较大的表按等级做分区。

[ 本帖最后由 saup007 于 2012-5-4 10:19 编辑 ]

liuxbgiant

可以使用时间进行区间分区，后期维护想必是统一的时间进行大量维护，可以考虑使用在线重定义功能。

saup007

如果按 字段A分区，但是字段A会变化的，行迁移可能很频繁，肿么办。。。

Maclean Liu(刘相兵

NEED ODM TEST ， Delay this post until test finished

Maclean Liu(刘相兵

ODM TEST:

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

SQL> -- Create table
SQL> create table T_PART_INTER
  2  (
  3    ID          NUMBER,
  4    NAME        VARCHAR2(30),
  5    CREATE_DATE DATE
  6  )
  7  partition by range (CREATE_DATE)
  8  (
  9    partition P1 values less than (TO_DATE(' 2009-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
10      tablespace USERS
11      pctfree 10
12      initrans 1
13      maxtrans 255,
14    partition P2 values less than (TO_DATE(' 2009-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
15      tablespace USERS
16      pctfree 10
17      initrans 1
18      maxtrans 255,
19    partition P3 values less than (TO_DATE(' 2009-07-30 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
20      tablespace USERS
21      pctfree 10
22      initrans 1
23      maxtrans 255
24      storage
25      (
26        initial 8M
27        next 1M
28        minextents 1
29        maxextents unlimited
30      ),
31    partition P4 values less than (TO_DATE(' 2009-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
32      tablespace USERS
33      pctfree 10
34      initrans 1
35      maxtrans 255
36      storage
37      (
38        initial 8M
39        next 1M
40        minextents 1
41        maxextents unlimited
42      )
43  );

Table created.


SQL> insert into t_part_inter  values(1,'TEST',to_date('20080101','yyyymmdd'));

1 row created.

SQL> commit;

Commit complete.

SQL> select rowid from t_part_inter;

ROWID
------------------
AAATy3AAEAAAmwlAAA

SQL>
SQL> select distinct sid from v$mystat;

       SID
----------
       125

SQL> exec dbms_stats.gather_table_stats('','T_PART_INTER');

PL/SQL procedure successfully completed.




SQL> set linesize 200 pagesize 1400
SQL>
SQL> set autotrace on;
SQL> update  t_part_inter set create_date=to_date('20080102','yyyymmdd')  where rowid='AAATy3AAEAAAmwlAAA';

1 row updated.


Execution Plan
----------------------------------------------------------
Plan hash value: 1812390831

------------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT            |              |     1 |    16 |     1   (0)| 00:00:01 |       |       |
|   1 |  UPDATE                     | T_PART_INTER |       |       |            |          |       |       |
|   2 |   TABLE ACCESS BY USER ROWID| T_PART_INTER |     1 |    16 |     1   (0)| 00:00:01 | ROWID | ROWID |
------------------------------------------------------------------------------------------------------------


Statistics
----------------------------------------------------------
          1  recursive calls
          2  db block gets
          0  consistent gets
          0  physical reads
        452  redo size
        833  bytes sent via SQL*Net to client
        861  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> commit;

Commit complete.

SQL> update  t_part_inter set create_date=to_date('20090102','yyyymmdd')  where rowid='AAATy3AAEAAAmwlAAA';

1 row updated.


Execution Plan
----------------------------------------------------------
Plan hash value: 1812390831

------------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT            |              |     1 |    16 |     1   (0)| 00:00:01 |       |       |
|   1 |  UPDATE                     | T_PART_INTER |       |       |            |          |       |       |
|   2 |   TABLE ACCESS BY USER ROWID| T_PART_INTER |     1 |    16 |     1   (0)| 00:00:01 | ROWID | ROWID |
------------------------------------------------------------------------------------------------------------


Statistics
----------------------------------------------------------
         32  recursive calls
         46  db block gets
          7  consistent gets
          3  physical reads
       3368  redo size
        834  bytes sent via SQL*Net to client
        861  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL>  select cpu_time,elapsed_time,BUFFER_GETS,sql_text from v$sql where sql_text like '%AAATy3AAEAAAmwlAAA%' and sql_text not like '%like%' and sql_text not like '%EXPLAIN%';

  CPU_TIME ELAPSED_TIME BUFFER_GETS
---------- ------------ -----------
SQL_TEXT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
      1000         1580           2
update  t_part_inter set create_date=to_date('20080102','yyyymmdd')  where rowid='AAATy3AAEAAAmwlAAA'

      2000         2688          53
update  t_part_inter set create_date=to_date('20090102','yyyymmdd')  where rowid='AAATy3AAEAAAmwlAAA'


                  
update partition key :

1. 没有造成  row movement 的 cpu time = 1000 BUFFER_GETS =2
2. 造成 row movement 的 cpu time=2000  BUFFER_GETS=53

比起普通的update 而言 造成row movement的update partition key 的性能消耗是 巨大的， 在OLTP 环境中应当避免 频繁的 更新 update partition key 造成row movement 。

saup007

谢谢回答偶的问题。

主要是想到解决问题的方法。。。只是学到刘大的皮毛，对最近很有帮助。非常感谢。

[ 本帖最后由 saup007 于 2012-5-5 01:26 编辑 ]