Menu
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?
260
The plus sign is Oracle syntax for an outer join. There isn't a minus operator for joins. An outer join means return all rows from one table. Also return the rows from the outer joined where there's a match on the join key. If there's no matching row, return null.
An index is a database structure that provides quick lookup of data in a column or columns of a table. For example, a Flights table in a travelDB database has three indexes: An index on the orig_airport column (called OrigIndex) An index on the dest_airport column (called DestIndex)
You can create an index on multiple columns in a table. If you want to create an index on the EMPLOYEE_ID and DEPARTMENT_ID columns in the employees table, for example, you can do so, and the result is called a composite or concatenated index.
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.
111
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
