包括 Oracle 中的等效项

发布于 2024-10-19 08:35:36 字数 154 浏览 1 评论 0原文

在 SQL Server 中，您可以写

create index indx on T1 (A,B) INCLUDE (C,D,E)

Is there a way to do the same thing in Oracle?

原文

In SQL server you can write

create index indx on T1 (A,B) INCLUDE (C,D,E)

Is there a way to do the same thing in Oracle?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

放飞的风筝 2024-10-26 08:35:36

参考资料：
http://msdn.microsoft.com/en-us/library/ms190806.aspx
http://www.dba-oracle.com/t_garmany_easysql_btree_index.htm

这个答案在这里需要指出的是，SQL Server Included 列并不在键级别存储 INCLUDED 列，而仅在叶级别存储。如果包含 4 列，它们将作为数据存储在叶级别的块中。

将它们创建为复合索引的附加部分，从而将索引分为更多级别。

As 复合索引 (A,B,C)

  Level1   Level2   Leaf
           (Branch)
  A1
           B1
                    C1
           B2
                    C3
           B3
                    C6
                    C7
  A2

As 索引 (A) include (B,C)

  Level1    Leaf
  A1        B1,C1 | B2,C3 | B3,C6 | B3,C7
  A2        null,null

存储结构的差异（影响性能）是它们被引入为 INCLUDED 列的原因，否则就没有理由引入这个新功能。

Refs:
http://msdn.microsoft.com/en-us/library/ms190806.aspx
http://www.dba-oracle.com/t_garmany_easysql_btree_index.htm

This answer is here to point out that SQL Server Included columns do not store the INCLUDED columns at the key levels, only at the leaf level. If you include 4 columns, they get stored as data in a block on the leaf level.

Creating them as additional parts of a composite index breaks the index into more levels instead.

As composite index (A,B,C)

  Level1   Level2   Leaf
           (Branch)
  A1
           B1
                    C1
           B2
                    C3
           B3
                    C6
                    C7
  A2

As index (A) include (B,C)

  Level1    Leaf
  A1        B1,C1 | B2,C3 | B3,C6 | B3,C7
  A2        null,null

The difference in storage structure (which affects performance) is the reason why they are introduced as INCLUDED columns, otherwise there would be no reason to introduce this new feature.

回复收藏 0 原文

冰雪梦之恋 2024-10-26 08:35:36

只需将所有列放入索引即可：

在 T1 上创建索引 indx（A、B、C、D、E）

如果 Oracle 决定使用您的索引（例如，A 和 B 位于 WHERE 子句中），它将从索引中获取 C、D 和 E 的值。

如果列非常长，varchars Oracle 可能无法构建索引。这有时被称为“覆盖”索引，我已经多次使用或见过它。

回复收藏 0 原文

你穿错了嫁妆 2024-10-26 08:35:36

您可以创建索引组织表并仅存储带有键的特定列。请注意，您没有列出要包括的列，您只列出了最后一列，并且它包括截至该列的所有列。（这看起来很奇怪，通常列顺序在 SQL 中并不重要。）就我个人而言，我认为索引组织表很奇怪并且有太多限制，redcayuga 的想法可能更好。

create table t1
(
    a number,
    b number,
    c number,
    d number,
    e number,
    f number,
    g number,
    primary key (a, b, c)
)
organization index
including e
overflow;

You can create an index organized table and only store specific columns with the key. Note that you don't list the columns to include, you only list the last one and it includes all the columns up to that. (Which seems odd, usually column order doesn't matter in SQL.) Personally, I think index organized tables are weird and have too many limitations, redcayuga's idea is probably better.

create table t1
(
    a number,
    b number,
    c number,
    d number,
    e number,
    f number,
    g number,
    primary key (a, b, c)
)
organization index
including e
overflow;

回复收藏 0 原文

~没有更多了~