19

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?

OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
Michael
  • 205
  • 1
  • 3
  • 5
  • 3
    Might help to explain in more detail what it means to do that in SQL Server. Someone who knows how to do the same thing in Oracle might not know SQL Server at all. – Dave Costa Feb 24 '11 at 18:08
  • 1
    "Specifies the non-key columns to be added to the leaf level of the nonclustered index." See second answer here: http://stackoverflow.com/questions/5108064/join-takes-too-long – Michael Feb 24 '11 at 18:11
  • To my knowledge, Oracle only supports indexes, composite indexes, and function based indexes (which most DBAs IME loath). – OMG Ponies Feb 24 '11 at 18:17
  • 10g enterprise. Yeah I think you're right, I'll probably need do move C,D,E with A and B. – Michael Feb 24 '11 at 18:17

3 Answers3

12

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.

RichardTheKiwi
  • 105,798
  • 26
  • 196
  • 262
  • Thank you for this explanation, sometimes it's hard to just use whatever is accepted as the answer without knowing behind the scenes. – alegria Oct 01 '20 at 10:01
9

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.

redcayuga
  • 1,241
  • 6
  • 4
  • See my answer for an explanation of SQL Server included columns in an index – RichardTheKiwi Feb 24 '11 at 19:01
  • Technically, any index in Oracle is a unique index (with the ROWID used if the index isn't declared as unique). As such, it can't really index A,B,ROWID in the branch and have A,B,C,D,E,ROWID in the leaf block as the ordering would be inconsistent. – Gary Myers Feb 25 '11 at 06:54
  • This fails if the requirement is a unique index on (a,b) - including the C,D,E in an index definition then allows duplicate a,b records. – Andrew Apr 11 '12 at 12:59
  • 1
    @Andrew, true, but you can then define a Unique Key constraint on (A, B) and Oracle will use the (A, B, C, D, E) index to enforce it, even if it is not a unique index. – redcayuga Aug 02 '12 at 15:45
4

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;
Jon Heller
  • 34,999
  • 6
  • 74
  • 132
  • 1
    IOTs make a lot of sense for small to medium lookup up tables where almost all access is by primary key. That's the only purpose I've used them for. Richard's link includes examples of partitioned IOTs which implies rather large tables. Probably works well in some applications and poorly in others - like anything else. – redcayuga Feb 24 '11 at 20:32
  • Doesn't that mean you need to manually maintain this index table when your source table is modified? – dsum Apr 29 '11 at 16:08
  • @dsum: What kind of modification are you talking about? If you're talking about DDL, then as far as I know Oracle will make the necessary changes as if there is only one object. – Jon Heller May 02 '11 at 00:33
  • @jonearles: Sorry, I don't have much experience on Oracle. I was confused of your "create table" because I didn't know what is an index organized table (IOT). After I read more on IOT, I can see the advantages using IOT. – dsum May 03 '11 at 17:29
  • @jonearles: My first thought on Oracle IOT is that it doesn't seems to be as flexible as SQLServer covered index. I think you can only have one IOT since it is a table implementation, while you can have as many covered indexes as you need in SQLServer, please correct me if I am wrong. – dsum May 03 '11 at 17:42
  • 2
    IOT in Oracle is basically `primary key clustered` in Sql Server. – George Polevoy Apr 11 '17 at 18:30