Oracle中的全局非分区索引 [英] Global nonpartitioned index in Oracle

问题描述

我正在阅读有关Oracle分区索引的文档.到处都可以找到本地分区索引和全局分区索引之间的区别，这对我来说很明显.

I am reading up documentation for oracle partitioned indexes. One can find everywhere the distinction between local partitioned index and global partitioned index and that is clear to me.

我很困惑全局"一词并不意味着该索引完全是分区的.

I am confused that word 'global' does not imply that the index is partitioned at all.

例如Thomas Kyte在专家Oracle数据库体系结构"中有几个示例，如下所示:

E.g. Thomas Kyte has several examples in 'Expert Oracle Database Architecture' that looks like this:

create index partitioned_idx_global
on partitioned(timestamp)
GLOBAL;
Index created.

并且可以找到类似的示例，其中关键字GLOBAL后面没有任何分区描述.

And one can find similar examples where the keyword GLOBAL is not followed with any partitioning description elsewhere.

GLOBAL之后没有分区子句，这似乎与

There is no partitioning clause after GLOBAL which seems to be in contrast to what is allowed in CREATE INDEX docs.

文档为非分区全局索引对我来说也没有太大意义.

The documentation for nonpartitioned global indexes does not make much sense to me either.

全局非分区索引的行为类似于本地非分区索引.

Global nonpartitioned indexes behave just like local nonpartitioned indexes.

本地非分区索引"到底是什么?它甚至不应该存在.

What the heck is 'local nonpartitioned index'? It should not even exist.

因此，最终我的问题是消除混乱.正则索引和全局非分区索引有什么区别?上面的书中引用的语法甚至是合法的"吗?

So ultimately my question is to clear the confusion. What is the difference between regular index and global nonpartitioned index? Is the syntax quoted from the book above even 'legal'?

推荐答案

本地非分区索引"到底是什么?

What the heck is 'local nonpartitioned index'?

没有诸如本地非分区索引"之类的内容.似乎可能是文档错误.我认为他们的意思是

There is no such as a "local nonpartitioned index". Seems likely that is a documentation bug. I think what they meant to say was

全局非分区索引的行为就像非分区表上的索引一样."

"Global nonpartitioned indexes behave just like indexes on nonpartitioned tables."

是的，汤姆·凯特(Tom Kyte)的书中引用的语法是合法的.

So yes, the syntax quoted in Tom Kyte's book is legal.

GLOBAL之后没有分区子句，这似乎与CREATE INDEX文档中允许的相反

There is no partitioning clause after GLOBAL which seems to be in contrast to what is allowed in CREATE INDEX docs

默认情况下，GLOBAL索引是未分区的.也就是说，表的所有分区都有一个索引.我们可能要执行此操作的主要原因是在整个表上强制执行唯一约束.实际上，很少需要这样做:分区通常仅限于数据仓库，因为DML的锁定程度更高，因此约束执行通常较为宽松.

By default GLOBAL indexes are nonpartitioned. That is, there is one index for all the partitions of the table. The main reason why we might want to do this is to enforce a unique constraint across the entire table. In practice it's rare to need to do this: Partitioning is usually restricted to data warehouses, where constraint enforcement is generally laxer, because DML is more locked down.

链接到的文档显示了如何创建GLOBAL分区索引.这是一种混合结构，使我们可以使用与用于分区数据的分区方案不同的分区方案来构建索引.老实说，我从未在与Partitioning合作过的任何网站上遇到过这种情况.但是，@ matthewmcpeak提出了一种可能有用的方案.因此，为了将来的搜索者的利益，我将他的评论包括在此答案中.

The documentation to which you linked shows how to create GLOBAL partitioned indexes. This is a hybrid construct which allows us to build indexes with a different partitioning scheme from that used to partition the data. To be honest I've never come across this in any site where I've worked with Partitioning. However, @matthewmcpeak suggested a scenario where it could be useful. So I am including his comment in this answer, for the benefit of future Seekers.

全局散列分区索引是一种减少序列值非常活跃的唯一索引上争用的好方法.通常，所有插入都在索引的右侧.如果许多并行会话中有许多插入，则可能会有很多争用-每个会话都会给其他会话带来更多工作(因为它们都需要应用大量的UNDO来撤消已提交的更改，以便获得一致的块读取).一个旧的解决方案是使用 REVERSE 索引.现在，全局散列分区索引更好-减少争用而不会增加物理I/O.该表本身不需要分区即可应用该技术.

A global hash partitioned index is a good way to reduce contention on very active, unique indexes of sequence values. Normally, all inserts are on the right side of the index. If there are many inserts from many parallel sessions, there can be a lot of contention -- with each session causing more work for the others (since they'll all need to apply a lot of UNDO to backout committed changes to get consistent block reads). An old solution to this was to use a REVERSE index. Now, a global hash partitioned index is better -- reduces contention without blowing up physical I/Os. The table itself does not need to be partitioned for the technique to apply.

这篇关于Oracle中的全局非分区索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！