How to create Clustered and non clustered index in Oracle

clustered-indexoracle

I dug couple of hours regarding my question and didn't get satisfactory answer. Still I have doubt. I have found the following about Clustered Index:

  1. Data is stored in the order of the clustered index.
  2. Only one clustered index per table.
  3. When a primary key is created a clustered index is automatically created as well.

I got these points, but my questions are:

  1. Does Clustered index exist in Oracle database? since I read in some blogs "Oracle does not have a concept of a clustered index."
  2. If yes, please let me know the sql statement to create a clustered index.
  3. As said above, clustered index automatically gets created when primary key is defined on a column of a table, how can I check the index type if it is created or not ?

Please find my table architecture :

enter image description here

Let me know if anything else is required to get answers for these questions.

Best Answer

Does Clustered index exist in Oracle database? since I read in some blogs

Yes there is.

It is called "index organized table" (IOT) - which in my opinion is the better name as it makes it absolutely clear that the index and the table are the same physical thing (which is the reason why we can have only one clustered index in SQL Server)

If yes, please let me know the SQL statement to create a cluster index.

There is no such thing as create clustered index in Oracle.

To create an index organized table, you use the create table statement with the organization index option.

In Oracle you usually use IOTs for very narrow tables. Very often for tables that only consist of the primary key columns (e.g. m:n mapping tables), e.g.

create table assignment
(
   person_id  integer not null, 
   job_id     integer not null, 
   primary key (person_id, job_id)
)
organization index;

You can create IOTs with more column, in that case the you need to define the non-pk columns as "included" columns. E.g. if the assignment table should have additional columns, like start and end date that are not part of the primary key:

create table assignment
(
   person_id   integer not null, 
   job_id      integer not null, 
   start_date  date, 
   end_date    date,
   primary key (person_id, job_id)
)
organization index
including start_date
overflow storage (initial 4k);

See the manual for more details and examples: https://docs.oracle.com/database/121/SQLRF/statements_7002.htm#i2153235

Somewhat unrelated, but maybe interesting anyway:

An interesting blog post that questions SQL Server's (and MySQL's) behaviour of using a clustered index as the default when creating a table:

Unreasonable Defaults: Primary Key as Clustering Key

Coming from an Oracle background I wholeheartedly agree with that.

Related Question