CODEWITHSUNDEEP
sqloracle-database
a1ex07
a1ex07

Reputation: 37354

Oracle equivalent to SQL Server included columns to index

Does Oracle let me include columns to index (like SQL Server INCLUDE clause in CREATE INDEX) ?

Thanks.

Upvotes: 9

Views: 3142

Answers (2)

Esko Piirainen
Esko Piirainen

Reputation: 1379

CREATE TABLE test (
    a       VARCHAR2(400 char) NOT NULL PRIMARY KEY,
    b       NUMBER(33) NOT NULL
);

CREATE TABLE test_2 (
    a       VARCHAR2(400 char) NOT NULL,
    b       NUMBER(33) NOT NULL
);
CREATE INDEX ix_test_2 ON test_2(a, b);

explain plan for
select a,b from test where a in ('a', 'b', 'c');

--|   0 | SELECT STATEMENT(50)
--|   1 |  INLIST ITERATOR
--|   2 |   TABLE ACCESS BY INDEX ROWID
--|*  3 |    INDEX UNIQUE SCAN         

explain plan for
select a,b from test_2 where a in ('a', 'b', 'c');

--|   0 | SELECT STATEMENT
--|   1 |  INLIST ITERATOR       
--|*  2 |   INDEX RANGE SCAN

So, to me it looks like that in Oracle12c at least, including column b in the index prevents table access -> faster.

Upvotes: 1

Justin Cave
Justin Cave

Reputation: 231661

No. An index in Oracle either includes the column in the index itself or it doesn't.

Depending on the problem you are trying to solve, however, an index-organized table may be the appropriate analogue in Oracle.

Upvotes: 6

Related Questions