澳门金沙vip索引rebuild与rebuild online区别

可以看到,分区37和100都被锁定了。锁定37分区是意料中的事,因为要从该表转移数据。那为什么要锁定第100个分区,也就是最后一个分区呢?我的理解是:新增加分区的位置101是由原分区表的分区数100确定的,如果在增加分区的过程中允许对原表最后一个分区100作DDL操作,如coalesce操作,则新加的101分区就不一定是从原来的分区37分配数据了,101分区本身应该是新的第100分区,这样就引起混乱了。到这里,你可能会说,按这理解,是不是其他的分区也应该锁定呢?其实不用,因为hash分区表是不支持drop
partition操作的,而只支持coalesce操作来实现类似的操作,但coalesce只能从最后一个分区开始收缩。了解了增加hash表分区过程中锁信息的实际指导意义是什么呢?继续上例中的讨论,由于分区37和最后一个分区100会被排他锁定,因此在添加分区过程中这两个分区是不能作DML操作的,因为DML操作需要在分区上申请共享锁(mode为3)。也就是操作这两个分区的应用会受到影响。Hash表增加分区不会像其他类型分区表,如range分区那样能够迅速完成,因为这里添加分区的过程中是要有IO操作的,要转移数据到新的分区。其实这还不是最主要的,由于Hash表是根据分区键Hash函数值来决定分区的,添加分区的主要时间其实是花在了计算hash值上。在上面的测试中,添加新分区操作的消耗时间是6分58秒,从下面的10046统计信息可以看到,其中6分钟都是花在了CPU操作上,相信主要是Hash运算引起的。


复制代码 代码如下:create table equity(id
number,trade_date date,……);

 


Pstop |

| 0 | ALTER INDEX STATEMENT | | 500K| 2441K| 400 (1)| 00:00:05 | | |
| 1 | INDEX BUILD NON UNIQUE| T_P_IND | | | | | | |
| 2 | SORT CREATE INDEX | | 500K| 2441K| | | | |
| 3 | PARTITION HASH ALL | | 500K| 2441K| 400 (1)| 00:00:05 | 1 | 4 |

Equity表可能会很大,对equity表的查询通常都是指定id,查询某一交易日期或者某段时期内的其他信息。这种情况下我们需要如何为equity表选择分区呢?单从表本身结构来看,似乎trade_date列很适合被选择用来作范围分区。但如果我们这样分区的话,前面需求中的查询:指定某一id,查询其某一范围内的交易信息,比如看1年内的K线,则这种查询常常需要跨分区。我们知道,对分区表作跨分区查询,很多时候其性能并不会太好,特别是这种查询很可能还要跨很多分区。你也可能会说,我们再在id,
trade_date列上建个索引不就行了,仔细想想是不是这样呢?这时候的equity表中的数据是按trade_date值来聚集的,同样trade_date值的数据常常在一个数据块中,这样前面需求中所描述的查询即使通过索引访问,最终读表时也常常是去读离散的数据块,即每一条记录需要对应读一个表数据块。如果建成Hash分区表,则数据按hash分区键聚集,就更适合需求中描述的查询,因为同样id的记录必定在同一分区,同时,同样
id值的记录落在同一数据块的几率也增大了,从而“一定程度上”减少了IO。上面对hash分区减少IO的描述加了引号,因为仅依靠Hash分区表试图实现大范围减少IO操作是不现实的,特别是当equity表中记录的股票数非常多时,同一股票发生在不同交易日的记录在物理上也很难聚集到相同数据块中。实际上,如果我们在Hash分区的基础上再对equity表采用IOT表的组织方式,则前面描述的查询性能就可大为提高。IOT表不在该文讨论的范围之内,这里就不作进一步讨论了。当我们决定使用Hash表之前,我们还需要确定我们的所选择的分区键值是连续分布的,或者接近连续分区,此外,分区的个数需要是2的整数幂,比如2,4,8…
这些要求是由Hash函数的特点决定的,这样我们分区表的各个分区所包含的数据量才会比较平均。

MAX(ID)

499989

–update 非分区列,发现临时表不记录信息
update t set NAME=’NAME2′ where id=499989;

SCOTT > select * from SYS_JOURNAL_91897;

C0 O PARTNO RID


300000 D 3 D/////AAEAACvN8AAw
–update 其它列,并不记录在此视图中

–update
索引列,发现还需要找到一个值,Update后还存在该表中,暂缓update操作测试
update t set id=id+400 where id=499989
*
ERROR at line 1:
ORA-14402: updating partition key column would cause a partition change

 

–由于insert 随机,因此插入四条记录进行测试
SCOTT > select * from SYS_JOURNAL_91897;

C0 O PARTNO RID


300000 D 3 D/////AAEAACvN8AAw
declare
v_id int;
begin
for v_id in 500001 .. 500004
loop
insert into t values(v_id,’test’||v_id);
end loop;
commit;
end;
/
SCOTT > select max(id) from t partition(SYS_P112);

收集统计信息后查询新的分区记录数:

1.0目的,本篇文档探讨索引rebuild 与 rebuild online的区别

[code]OVERALL TOTALS FOR ALL RECURSIVE STATEMENTScall count cpu
elapsed disk query current rows——- —— ——– ———-
———- ———- ———- ———-Parse 328 0.17 0.27 0 0 148
0Execute 1520 360.14 396.30 456820 11416202 26357 11565252Fetch 1767
5.42 21.18 21421 26540 0 2862——- —— ——– ———-
———- ———- ———- ———-total 3615 365.73 417.76
478241 11442742 26505 11568114

select * from table(dbms_xplan.display);

该测试案例中分区COT_IND01_P37中共有接近1千万条数据,耗时接近7分钟,假设分区数据达到了1亿条,则耗时应该在1个小时以上。如果我们的Hash分区数按Oracle的建议为2的整数幂,则我们在增加分区时是要增加原有分区一倍的新分区,比如原分区为128个,扩展的时候需要增加128个分区,乘以每次添加分区需要的时间,则为Hash表增加分区将是一个很恐怖的操作。总之,Hash分区有其优势,但也有严重的缺陷,比如这里描述的分区扩展问题。因此在项目设计之初,我们就需要慎重选择分区数。但是随着数据量的增加,我们又很难避免为分区表增加分区的操作,这种操作是很耗资源的操作,操作过程中由于锁的问题会影响对原有某些分区的操作。但如果我们因为畏惧前面存在的问题拖着不作分区扩展,则越是往后,随着数据量的增加,这种增加分区的操作越难以实施。

2.0猜测:已有的知识
2.1对索引rebuild重建会对表申请TM4级表锁,将会影响业务修改数据,而对索引进行rebuild
online则不影响业务修改数据,但是可能会失败。
2.2对索引rebuild online,对于一个大的分区表,rebuild
online速度非常慢,而rebuild速度对比很快
2.3对索引rebuild
online查询数据库V$lOCK视图,发现对多个对象存在TM2、某个对象TM4级锁,但是不知道是什么对象

复制代码 代码如下:alter table
nts_commodity_ts add partition COT_IND01_P101;Table altered.Elapsed:
00:06:58.52

select * from table(dbms_xplan.display);

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

常用的分区表所具有的优点:如提高数据可用行,减少管理负担,改善语句性能等优点,hash分区同样拥有。此外,由于Hash分区表是按分区键的hash计算结果来决定其分区的,而特定的分区键其hash值是固定的,也就是说Hash分区表的数据是按分区键值来聚集的,同样的分区键肯定在同一分区。比如,在证券行业,我们经常查询某一只股票的K线,假设表的结构如下:

3.0实验思路
1.从锁角度查询区别
2.从执行计划查询区别
3.从10046分析查询区别(没整明白,可忽略)
申明,本次操作版本11.2.0.4

复制代码 代码如下:select
TABLE_NAME,PARTITION_POSITION,PARTITION_NAME,NUM_ROWS from
user_tab_partitions where table_name=\’COMMODITY\’ order by
PARTITION_POSITION;TABLE_NAME PARTITION_POSITION PARTITION_NAME
NUM_ROWS————– —————— ———————-
———-COMMODITY 1 COT_IND01_P1 4405650COMMODITY 2 COT_IND01_P2
5046650COMMODITY 3 COT_IND01_P3 5107550……COMMODITY 36 COT_IND01_P36
5718800COMMODITY 37 COT_IND01_P37 9905200COMMODITY 38 COT_IND01_P38
10118400COMMODITY 39 COT_IND01_P39 10404950COMMODITY 40
COT_IND01_P40 9730850COMMODITY 41 COT_IND01_P41 9457300COMMODITY 42
COT_IND01_P42 9717950COMMODITY 43 COT_IND01_P43 9643900COMMODITY 44
COT_IND01_P44 11138000COMMODITY 45 COT_IND01_P45 9381300COMMODITY 46
COT_IND01_P46 10101150COMMODITY 47 COT_IND01_P47 8809950COMMODITY 48
COT_IND01_P48 10611050COMMODITY 49 COT_IND01_P49 10010600COMMODITY
50 COT_IND01_P50 8252600COMMODITY 51 COT_IND01_P51 9709900COMMODITY
52 COT_IND01_P52 8983200COMMODITY 53 COT_IND01_P53 9012750COMMODITY
54 COT_IND01_P54 9310650COMMODITY 55 COT_IND01_P55 8966450COMMODITY
56 COT_IND01_P56 8832650COMMODITY 57 COT_IND01_P57 9470600COMMODITY
58 COT_IND01_P58 8932450COMMODITY 59 COT_IND01_P59 9994850COMMODITY
60 COT_IND01_P60 9617450COMMODITY 61 COT_IND01_P61 10278850COMMODITY
62 COT_IND01_P62 9277600COMMODITY 63 COT_澳门金沙vip,IND01_P63 8136300COMMODITY
64 COT_IND01_P64 10064600COMMODITY 65 COT_IND01_P65
3710900……COMMODITY 99 COT_IND01_P99 5273800COMMODITY 100
COT_IND01_P100 5293350100 rows selected.

| 3 | INDEX FAST FULL SCAN| T_P_IND | | | | |

explain plan for
alter index scott.t_p_ind rebuild online;

Hash分区表的扩展:

select * from table(dbms_xplan.display);

Hash分区是通过对分区键运用Hash算法从而决定数据的分区归属。使用Hash分区有什么优点呢?

select * from table(dbms_xplan.display);

COMMODITY COT_IND01_P100 4004126COMMODITY COT_IND01_P37 4004063

SCOTT > select a.TABLE_NAME,a.PARTITION_NAME,bytes/1024/1024 from
user_tab_partitions a,dba_segments b where
a.partition_name=b.PARTITION_NAME and a.table_name=’T’;
TABLE_NAME PARTITION_NAME BYTES/1024/1024

复制代码 代码如下:select
TABLE_NAME,PARTITION_POSITION,PARTITION_NAME,NUM_ROWS from
user_tab_partitions where table_name=\’COMMODITY\’ and
partition_name in
(\’COT_IOT_IND01_P37\’,\’COT_IOT_IND01_P101\’);TABLE_NAME
PARTITION_POSITION PARTITION_NAME NUM_ROWS——————
—————— ——————— ———-COMMODITY 37
COT__IND01_P37 4905200COMMODITY 101 COT_IND01_P101 5107550

Pstop |

| 0 | ALTER INDEX STATEMENT | | 124K| 609K| 102 (1)| 00:00:02 | | |
| 1 | INDEX BUILD NON UNIQUE | T_P_IND | | | | | | |
| 2 | SORT CREATE INDEX | | 124K| 609K| | | | |
| 3 | PARTITION HASH SINGLE| | | | | | 4 | 4 |

查询各个分区的数据分布,我们可以看到,从分区37 ~
64的28个分区的记录数大概是其他分区的两倍。由于100不是2的整数幂,所以Oracle的hash函数是无法保证数据是平均分布的。我们为该表添加一个新的分区COT_IND01_P101:

| 4 | TABLE ACCESS FULL | T | 124K| 609K| 102 (1)| 00:00:02 | 4 | 4 |

可以发现,对于分区表,rebuild
online索引,进行的是全表扫描,随后分区扫描,
全表扫描->单分区扫描->

5.1/ 5.2对比,可以理解实际操作,rebuild online为什么一个多小时,rebuild
几分钟,全表扫描,数据量大差异越大

5.3rebuild 普通堆表、Global索引
explain plan for
alter index scott.t_ind rebuild;