INCLUDE equivalent 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 Oracl开发者_开发知识库e?

---

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.

---

Simply put all the columns in the index:

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

If Oracle decides to use your index (e.g., A and B are in the WHERE clause) it will take the values of C, D, and E from the index.

If the columns are very long varchars Oracle may not be able to build the index. This is sometimes called a "covered" index and I've used or seen it on more than a few occasions.

---

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;