增量列是否使列的b树索引不平衡？ [英] does incremented column makes the b-tree index on the column unbalanced?

问题描述

我一直在想两个问题。在互联网上找不到任何资源这方面。 dbms如何处理呢？还是他们？特别是Oracle。

在问题之前，这里有一个例子：说我有一个主表MASTER和从表SLAVE。
主表具有作为主键的ID列，并且由Oracle.Slave表创建的索引具有指向主表和SLAVE_NO的外键MASTER_ID。这两个一起是从表的主键，它再次被索引。

  ** MASTER ** | ** SLAVE ** 
（P）ID< ------> （P）（F）MASTER_ID 
（P）SLAVE_NO 
  

p>

1-如果MASTER_ID是自动递增的列，并且没有记录被删除，这不会得到表的索引不平衡吗？ Oracle是否定期重建索引？据我所知，Oracle在构建时仅平衡索引分支。 Oracle是否自动重新构建索引？

2-假设Oracle不会自动重建，除了定期定期重建索引的作业之外，它会更明智订单SLAVE表的主键列反向？我的意思是代替MASTER_ID，SLAVE_NO排序为SLAVE_NO，MASTER_IDi，它会帮助从表的b树索引更平衡吗？ （每个主表可能没有确切数量的从属记录，但仍然，比颠倒顺序好）

任何人都知道吗？或者意见？

解决方案

如果 MASTER_ID 是自动增量列，并且没有记录被删除，这不会使表的索引不平衡？

Oracle 的索引从不不平衡：索引中的每个叶子与任何其他叶子处于相同的深度。

split引入了一个新的层次：叶页面不会成为新页面的父页面，就像它在非自平衡树上一样。

并且新记录（加上可能来自旧页面的一些记录）转到新页面。

如果父页面空间不足（不能接受指向新创建的叶子页面的指针） ，它也会被拆分，等等。

这些分裂可以传播到根页面，其分裂是增加索引深度的唯一的东西

索引页面还被组织为双向链接列表，每个列表在其自己的级别。如果树不平衡，这是不可能的。

如果 master_id 是自动递增的， （例如 90/10 拆分），这使得最密集的索引成为可能。

如果订单 SLAVE 表的主键列反转会更明智吗？

不会，因为上述原因。

如果您加入 slave $ c> master 经常，您可以考虑创建两个表的 CLUSTER ，由 master_id 。这意味着两个表中的记录共享相同的 master_id ，转到相同或附近的数据页面，这使得它们之间的联接非常快。

当引擎从 master 中找到一个记录时，使用索引或其他内容，这也意味着它已经找到了 slave 与 master 加入。反之亦然，找到一个 slave 也意味着找到 master 。

I have been thinking about two questions. Couldn't find any resources on the internet about this. How do dbms handle it ? Or do they ? Especially Oracle.

Before the questions, here is an example: Say I have a master table "MASTER" and slave table "SLAVE". Master table has an "ID" column which is the primary key and index is created by Oracle.Slave table has the foreign key "MASTER_ID" which refers to master table and "SLAVE_NO". These two together is the primary key of slave table, which is again indexed.

 **MASTER**  |  **SLAVE**
     (P) ID <------> (P)(F) MASTER_ID 
                     (P) SLAVE_NO

Now the questions;

1- If MASTER_ID is an autoincremented column, and no record is ever deleted, doesn't this get the table's index unbalanced ? Does Oracle rebuilds indexes periodically ? As far as i know Oracle only balances index branches at build time. Does Oracle re-builds indexes Automatically ever ? Say if the level goes up to some high levels ?

2- Assuming Oracle does not rebuild automatically, apart from scheduling a job that rebuilds index periodically, would it be wiser to order SLAVE table's primary key columns reverse ? I mean instead of "MASTER_ID", "SLAVE_NO" ordering it as "SLAVE_NO", "MASTER_ID"i, would it help the slave table's b-tree index be more balanced ? (Well each master table might not have exact number of slave records, but still, seems better than reverse order)

Anyone know anything about that ? Or opinions ?

解决方案

If MASTER_ID is an autoincremented column, and no record is ever deleted, doesn't this get the table's index unbalanced ?

Oracle's indexes are never "unbalanced": every leaf in the index is at the same depth as any other leaf.

No page split introduces a new level by itself: a leaf page does not become a parent for new pages like it would be on a non-self-balancing tree.

Instead, a sibling for the split page is made and the new record (plus possibly some of the records from the old page) go to the new page. A pointer to the new page is added to the parent.

If the parent page is out of space too (can't accept the pointer to the newly created leaf page), it gets split as well, and so on.

These splits can propagate up to the root page, whose split is the only thing which increases the index depth (and does it for all pages at once).

Index pages are additionally organized into double-linked lists, each list on its own level. This would be impossible if the tree were unbalanced.

If master_id is auto-incremented this means that all splits occur at the end (such called 90/10 splits) which makes the most dense index possible.

Would it be wiser to order SLAVE table's primary key columns reverse?

No, it would not, for the reasons above.

If you join slave to master often, you may consider creating a CLUSTER of the two tables, indexed by master_id. This means that the records from both tables, sharing the same master_id, go to the same or nearby data pages which makes a join between them very fast.

When the engine found a record from master, with an index or whatever, this also means it already has found the records from slave to be joined with that master. And vice versa, locating a slave also means locating its master.

这篇关于增量列是否使列的b树索引不平衡？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！