- 浏览: 85962 次
- 性别:
- 来自: 北京
最新评论
Question 1:
Hi,
I am partitioning few huge tables,
regarding The feature of Enable Row movement i need some clarifications :
First Question
Does partitioning column should not be ever updated or what ? if normal updates with in that partition is it an extra overhead or have some performance impact..
Second doubt is :-
If update causes row movement from 1 partition to other will it have performance impact.
These question has been raised by one of my senior DBA since when i started partitioning on DATE column (range ) and the application was updating the date column and in some cases it falls out of partition and error came so , I enabled row movement it is resolved, but he says it's an extra over head..
Usually in our environment once at month end there are chances that this partitioning column might get updated and the new value might fall in new partition..
Do you people say that partitioning column should never be updated or what ?
Some experience tips on this please.
Thanks.
Question 2:
Hello,
Flashback table requires that enable row movement is activated for the
table. By default, this is disabled. Are there any disadvantages by enabling
this for all tables (except that the ROWIDs might change) ? Couldn't find
much about this in the docs or with Google.
Oracle 10.2.0.3.
Matthias
Answer 1:
I would say that 99% of partitioned tables would use a non-updateable column.
Factors to consider:
- Overhead of moving between partitions
- Overhead of Oracle checking to make sure that a row doesn't move partitions.
- Effect of empty space left in locally partitioned indexes
- Effect of empty space left in table partitions.
The effect will depend on how many partitions are in the table, the number of LOCALLY partitioned indexes, the number of rows your move, and the proportion of rows in a partition that move.
If you want to make your partition key updateable, then why don't you benchmark it using your table, and time the results:
- Update 100K rows - a column that is not the partition key
- Update 100K rows on the partition column, but not so that the rows will move partitions
- Update 100K rows on the partition column so that they move partition.
Answer 2:
The only disadvantage is logical. ROWID used to be fixed value and I've
seen some examples where ROWID has been used as a key to retrieve data,
much like it's used in the EXCEPTIONS table. With row movement enabled,
that stops making sense. Another situation in which row movement comes
into play is partitioning. Row movement must be enabled if you need rows
to move between partitions.
Answer 3:
To my knowledge for all practical purposes there is no performance
impact to enabling row movement. This is not to say that an activity
that actually caused mass movement of rows will not have a noticable
impact on your system but generally speaking this is probably not an
issue.
HTH -- Mark D Powell --
Hi,
I am partitioning few huge tables,
regarding The feature of Enable Row movement i need some clarifications :
First Question
Does partitioning column should not be ever updated or what ? if normal updates with in that partition is it an extra overhead or have some performance impact..
Second doubt is :-
If update causes row movement from 1 partition to other will it have performance impact.
These question has been raised by one of my senior DBA since when i started partitioning on DATE column (range ) and the application was updating the date column and in some cases it falls out of partition and error came so , I enabled row movement it is resolved, but he says it's an extra over head..
Usually in our environment once at month end there are chances that this partitioning column might get updated and the new value might fall in new partition..
Do you people say that partitioning column should never be updated or what ?
Some experience tips on this please.
Thanks.
Question 2:
Hello,
Flashback table requires that enable row movement is activated for the
table. By default, this is disabled. Are there any disadvantages by enabling
this for all tables (except that the ROWIDs might change) ? Couldn't find
much about this in the docs or with Google.
Oracle 10.2.0.3.
Matthias
Answer 1:
I would say that 99% of partitioned tables would use a non-updateable column.
Factors to consider:
- Overhead of moving between partitions
- Overhead of Oracle checking to make sure that a row doesn't move partitions.
- Effect of empty space left in locally partitioned indexes
- Effect of empty space left in table partitions.
The effect will depend on how many partitions are in the table, the number of LOCALLY partitioned indexes, the number of rows your move, and the proportion of rows in a partition that move.
If you want to make your partition key updateable, then why don't you benchmark it using your table, and time the results:
- Update 100K rows - a column that is not the partition key
- Update 100K rows on the partition column, but not so that the rows will move partitions
- Update 100K rows on the partition column so that they move partition.
Answer 2:
The only disadvantage is logical. ROWID used to be fixed value and I've
seen some examples where ROWID has been used as a key to retrieve data,
much like it's used in the EXCEPTIONS table. With row movement enabled,
that stops making sense. Another situation in which row movement comes
into play is partitioning. Row movement must be enabled if you need rows
to move between partitions.
Answer 3:
To my knowledge for all practical purposes there is no performance
impact to enabling row movement. This is not to say that an activity
that actually caused mass movement of rows will not have a noticable
impact on your system but generally speaking this is probably not an
issue.
HTH -- Mark D Powell --
发表评论
-
dbms_output can not put the zero
2011-08-25 09:29 759DECLARE V_INTA NUMBER ... -
what is the difference between object_id and data_object_id?
2011-08-24 09:17 959The object_id is the primary k ... -
oracle EXECUTE IMMEDIATE ora-00911
2011-08-14 10:15 1519I get an error when I try to ex ... -
Will the valid status of index impact dml operation?
2011-08-05 10:34 851DROP TABLE tab01; SELECT * FRO ... -
where can i find the job number of those jobs defined in dba_scheduler_jobs?
2011-08-01 10:41 855Question: Hello, could anybody ... -
Listener HPUX Error: 242: No route to host
2011-05-17 14:55 989现象: 引用LSNRCTL> status Conne ... -
一进程阻塞问题解决
2011-05-12 16:38 4126同事反映,删除一条数据总是没有反应,请求协助解决. 问题非常 ... -
open database with ORA-00704 and ORA-39700
2011-05-06 16:13 29241,Error 1)alter.log Fri May ... -
oracle text index create and use
2011-05-06 13:41 1925一、Install Text Index 1,The ste ... -
offline datafile and offline tablespace
2011-05-04 11:43 25341)offline datafile OFFLINE Spe ... -
oracle three type of block size
2011-04-28 17:35 776Tools: 引用[oracle@node oracle]$ ... -
bbed一(安装)
2011-04-26 14:54 1519bbed ----------------------- bl ... -
Strategies for RAC inter-instance parallelized queries
2011-04-25 14:14 1141I recently had to sit down and ... -
Row Movement in Oracle
2011-04-23 22:23 2001One of the relatively newer fea ... -
ORA-14402 updating partition key column
2011-04-23 19:48 6390做DBA几年来,经常遇到项目到了维护期总是修改表的结构,原因很 ... -
ORACLE DSI 介绍
2011-04-19 18:33 878DSI是Data Server Internals的缩写,是O ... -
Oracle / Buffer cache
2011-04-19 17:18 798引用8.7 Tuning the Operating Syst ...
相关推荐
Introduction to partitioning technologies
Row Movement 579 Table Partitioning Schemes Wrap-Up 581 ■CONTENTS ix Partitioning Indexes 582 Local Indexes vs Global Indexes 583 Local Indexes 584 Global Indexes 590 Partitioning and Performance, ...
Partitioning Trust in Network Testbeds
Ensuring Privacy and Security for LBS through Trajectory PartitioningEnsuring Privacy and Security for LBS through Trajectory Partitioning
秦岭油松林林冠生长变化与降雨分配间的关联性研究,陈书军,CHRIS B.Zou,林冠截留量的增加,实质会导致用于生态系统蒸腾和非生态系统可用水量的减少。总体而言,在植物生长变化与降雨截留间的动态联系机
Approximate Hypergraph Partitioning and Applications∗Eldar Fischer† Arie Matsliah‡ Asaf Shapira§AbstractSzemerédi’s regularity lemma is a corner-stone result in extremal combinatorics....
Set Partitioning in Hierarchical Trees 英文文档,多级树集合分裂(SPIHT)算法
set partitioning in heirarchial tree
A Comprehensive Study of Main-Memory Partitioning and its Application to Large-Scale Comparison- and Radix-SortOrestis Polychroniou Columbia Universityorestis@cs.columbia.eduKenneth A. Ross∗ Columbia...
subtree partitioning and pure hashing are two common techniques used for metadata management. Neither of these techniques can support large systems with high concurrent accesses due to the conflicting...
This is taught in ECE241 class. It's useful for both computer science major and double E major. Partitioning is a important method to reduce state in electronic problems.
The joint development of the upcoming High Efficiency Video Coding (HEVC) standard by ITU-T ...Experts Group and ISO/IEC Moving Picture Experts Group marks a new step in video compression capability.
Amir Said和William Pearlman 写的SPIHT的源代码
Partitioning can provide tremendous benefits to a wide variety of applications by improving manageability, performance, and availability.
and the material is in minimum energy state Simulated Annealing Algorithm • Components: Solution space (e.g., slicing floorplans) Cost function (e.g., the area of a floorplan) o Determines how...
oracle partitioning document
Next, you will learn about data partitioning and consistent hashing in Cassandra through examples and also see high availability features and replication in Cassandra. Finally, you'll learn about ...
Next, you will learn about data partitioning and consistent hashing in Cassandra through examples and also see high availability features and replication in Cassandra. Finally, you'll learn about ...
Video Partitioning by Temporal Slice Coherency 图像检测,边缘检测 论文,侵权删
Oracle Database VLDB and Partitioning Guide 11g Release 2 (11.2)-310