PARTITION表SPLIT操作的内部过程和注意事项

显示全部楼层 · 2005-10-30 17:05:33

PARTIOTION表对于处理巨量数据来讲是个非常重要的方法
我对于系统中的大表和超大型表，主要的处理方式是根据公司前台应用的操作特性，对于一定周期后，只进行查询的数据，进行分区压缩，只是保留近期内可能还涉及DML操作的表在“最近”(我的分区名称：recent)中，用普通的存储方式进行保存。
而对于PARTITION的表来讲，之后的管理工作还是比较多的。
其中涉及到的工作中比较多的就是SPLIT(分裂)和DROP PARTITION（11g中应该就不会这样痛苦了）。
通过我对SPLIT过程的分析，发现SPLIT内部操作的基本过程：
1.发出命令后，ORACLE在我制定的新分区所在表空间中建立一个TEMPERORARY类型的表，其实这个表就是未来的新分区。将符合条件的数据导入。
2.在完成第一步后，ORACLE在原被分区的分区所在表空间上，又建立了一个TEMPRORARY类型的临时表，这时ORACLE开始将不属于新分区的数据在导入到这张临时表中。如果使用了UPDATE INDEXES的话，ORACLE最后还要维护本地索引。
3.完成后，改变表名（更改数据字典）
知道了这样的过程，我们在做SPLIT操作时，需要注意：
在被分区的分区所在表空间上，需要有足够的剩余表空间。当然，如果使用UPDATE INDEXES的话，索引所在的表空间也需要必要的剩余表空间。
如果说有LOCAL INDEX，在系统资源允许的情况下，带上 update indexes子句是不错的选择。
另外，这个分裂过程也解除了我的一个忧虑，被分裂的分类数据是否要重组？现在看是没有必要的，因为ORACLE已经为你重组了这个分区中的数据了。
-------
ORACLE版本：10.2.0.1
OS：HP-UX B.11.23 U ia64
欢迎指正

千问 · 2005-10-30 17:05:33

原帖由 yue_01 于 2007-11-20 14:27 发表
被分裂的分类数据是否要重组？现在看是没有必要的，因为ORACLE已经为你重组了这个分区中的数据了。
Can you elaborate that part? Thanks.
Yong Huang

千问 · 2005-10-30 17:05:33

Hello,Yong Huang
When spliting a partition table ,I found a temporary table was created in the target tablespace and increasing it's size continually. When the temporary table stoped increasingsize, I found a new temporary table was created in the tablespace where the partition of the table was splited and increasing it's size too.

I found the temporay tables was disappeared when the oracle prompted the "split" command was executed over. and I computed the sum size of two partitons, discovered the sum is smaller than one paratition which was splitd (Maybe you can compute a large different if the paration which was splited was often executed DMLs ). This process very likeyou execute a command insert /*+apped*/into xxx select * from xxx_resource. you will discover the target table's size is increasing during this proces.

千问 · 2005-10-30 17:05:33

下面一段话说明分开的两个新的分区已经重组了：
“Oracle implements a SPLIT PARTITION operation by creating two new partitions and redistributing the rows from the partition being split into the two new partitions. This is an expensive operation because it is necessary to scan all the rows of the partition being split and then insert them one-by-one into the new partitions.”

千问 · 2005-10-30 17:05:33

原帖由 yue_01 于 2007-12-19 00:54 发表
Hello,Yong Huang
When spliting a partition table ,I found a temporary table was created in the target tablespace and increasing it's size ...
Thanks Larry. I rewrote your words as follows so others can read better. Let me know if I misunderstood any part of it, especially the sentence "you'll get a larger difference".
Larry yue_01 says:
When splitting a partitioned table, I found that a global temporary table was created in the target tablespace and increasing in size continually. When the temporary table stopped increasing its size, I found a new temporary table was created in the tablespace where the partition of the table was split and this new temporary table was increasing its size too.
I found the temporay tables disappeared when the "split" command finished. I computed the sum of the sizes of two partitions, and discovered the sum is smaller than the original partition which was split (Maybe you'll get a larger difference if the split partition had frequent DMLs running against it). This process is very much like insert /*+ apped* / into xxx select * from xxx_resource. You will discover the target table's size is increasing during this process.

千问 · 2005-10-30 17:05:33

原帖由 f0310 于 2007-12-19 14:15 发表
下面一段话说明分开的两个新的分区已经重组了：
“Oracle implements a SPLIT PARTITION operation by creating two new partitions and redistributing the rows from the partition being split into the two new partitions. This is an expensive operation because it is necessary to scan all the rows of the partition being split and then insert them one-by-one into the new partitions.”
If 重组 or reorganization or reorg means re-storing the rows sorted by some order, obviously no. Splitting a partition will not do that. If saying this reorg is like alter table move or move partition or CTAS, yes. That's exactly what it does. Empty holes in the middle of a segment will be eliminated.
We need Larry Yue's clarification on "被分裂的分类数据是否要重组". In what sense does Larry think this reorg is not needed or not done by Oracle?
Yong Huang

千问 · 2005-10-30 17:05:33

Hello
First of all ,I want to say many thanks !
Thank you for writing back this.
Thank you for rewrting my post. -

And I really found that there is a error characeter in the statement "被分裂的分类数据是否要重组" .In fact , I want to express that "被分裂的分区数据是否要重组".
"重组" what I wrote is meanings that empty holes in the middle of a segment will be eliminated,in my opinion.In dba's job, the space management is very much important.Sometimes, We need use like 'move' command or 'exp/imp' tools or etc. to dispose some tables space problem.But, it isnot needed when we split a partition table.
Glad to discuss anything with you.

千问 · 2005-10-30 17:05:33

Didn't you see "two new partitions"? Who told you only "alter table move" can reorg tables? If you copy the data from a table to a newly created table, isn't a reorg?

千问 · 2005-10-30 17:05:33

不太明白。了解下。

千问 · 2005-10-30 17:05:33

我用下面这个命令将MAXVALUES(2千万行记录)分区分割为两个data_tmp01和data_tmp02，完成后发现分割出来的两个新分区，大小和原来的一样，相当于多占用用了一倍的空间。用select查询两个新分区，分别都是1千万行记录。
为什么新分区的大小和原来的会一样，不是应该是原来的一半才对吗？
ALTER TABLE T_BILL_EXPEND_bakSPLIT PARTITION
MAXVALUES at (TO_DATE('2007-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
INTO ( PARTITION data_tmp01 tablespace DATA_MAXVAL, PARTITION data_tmp02 tablespace DATA_MAXVAL) UPDATE INDEXES;