DML性能：带索引的未分配表和带索引的分区表 [英] DML performance : Unpartioned Table with Index vs Partitioned table with Index
问题描述
嗨
Hi
我对分区的看法是，它有利于大大量数据进/出主表，限制表因锁定而导致的停机时间。 除此之外，它没有具体用途。它还可以促进偶尔的DBA维护活动，如
压缩等。 但是，它在日常DML操作方面几乎没有用，特别是针对Indexing提供的影响。
My take on Partitioning is, it facilitates large chunks of data into/out of a main table, limiting table's downtime, due to locking. Other than that, its of no specific use. It may also facilitate occasional DBA maintenance activities like compression etc. But, its of little use in terms of day to day DML operations, especially gauged against the influence offered by Indexing.
例如，如果在日期对表进行分区，则在删除整个特定日期行时，分区功能会发光。 但是，如果在给定日期内，只删除某些特定行，那么，它实际上不是Partition
策略，但索引机制是闪耀的。  在选择性删除的情况下， 在具有Index的分区表和具有Index的Unpartitioned表上，性能相同。  这种理解是否正确？
For example, if a table is partitioned on date, the partition feature shines when the entire particular dated rows are to be deleted. However, if within a given date, only certain specific rows are to be deleted, then, its actually not Partition policy, but the indexing mechanism is what shines. In cases of selective deletes, the performance is same both on a Partitioned table with Index and an Unpartitioned table with Index. Is this understanding correct?
如何通过分区功能在特定方案中影响DML操作的其他变体？
How are other variants of DML operations get influenced in specific scenarios by partition feature?
非常感谢
推荐答案
从性能的角度来看，表分区可以使用DDL SWITCH操作而不是传统的DML来促进大量插入/清除/存档。性能的另一个用例是扫描可以限制在特定分区（而不是整个表的
），因为即使没有有用的索引也可以消除分区。
From a performance perspective, table partitioning can facilitate mass insert/purge/archive using a DDL SWITCH operation instead of traditional DML as you mentioned. Another use case for performance is that scans can be limited to specific partitions (instead of the entire table) due to partition elimination even without a useful index.
That being said. partitioning is mostly a manageability feature. Partitioned or not, query and index tuning is most often the way to improve performance.
这篇关于DML性能：带索引的未分配表和带索引的分区表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
