I have a table in SQL Server that needs its normal pk index to be replaced by a clustered index on 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 index-ordered tables, but my guess is these indexes are only constructed on the primary key.
Is there any way I can get a similar behavior to SQLServer's clustered index in Oracle?
Index organized tables are the concept of oracle which is near to clustered indexes in sql server. I found a discussion about the topic in oracle forum and one on asktom.
My question is: why do you want to adapt the behavior? What is the benefit you whant to obtain?
A clustered index in sql server is mostly the primary key index. Rowdata is stored into the index node. The conecpt on oracle to store row data into the index is an index organized table. On oracle the iot is used to avoid a second lookup for row data into the table after index lookup.
The purpose of clustered index on sql server is to store the rowdata. A table can have only one clustered index. This index will hold the rowdata. Any other index is a non clustered index.
IMHO the concept of clustered index is bound to sql server data storage and there is no need to rebuild this behavior in oracle. Oracle has other concepts to store data.
Answer: A regular index on oracle is all to solve your problem.