Indexing Oracle IOT on non-pk fields
I have a table in SQL Server that requires a normal pk index to replace a clustered index with two fields. These other fields are not part of the primary key.
I need to do something similar in Oracle. As far as I know, this can be done using indexed tables, but I think these indexes are only built on the primary key.
Is there a way to get similar behavior with a clustered SQLServer index in Oracle?
source to share
Indexed tables is an oracle concept that sits next to clustered indexes in sql server. I found a discussion about this topic in the oracle forum and one on asktom .
My question is, why do you want to adapt the behavior? What benefit do you want to get?
Clustered index in sql server is mainly primary key index. Rowdata is stored in the index node. The oracle spell for storing row data in an index is an index-organized table. In oracle iot is used to avoid a second lookup of the row data in the table after the index lookup.
The purpose of a clustered index on sql server is to store rowdata. A table can only have one clustered index. This index will contain rowdata. Any other index is a nonclustered index.
IMHO the concept of a clustered index is tied to the sql server datastore and there is no need to rebuild this behavior in oracle. Oracle has other storage concepts.
Answer: Regular oracle index is everything to solve your problem.
source to share