Xrhrft

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 cluster index is automatically created as well.

I got these points, but my questions are:

1. Is Cluster 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 cluster index.


3. As said above, cluster 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 :

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

Is Cluster 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 can only have 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.

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 cluster index is automatically created as well.

You dug a couple of hours. You should have noticed that all the above facts are for SQL Server and not Oracle.

Tables (at least ordinary ones) in Oracle do not have a clustered index. There is a special kind of tables, called Index Organized Tables (IOT) that are of similar concept. Details in Oracle documentation (9i): Index Organized Tables:

What Are Index-Organized Tables?

An index-organized table - in contrast to an ordinary table - has its own way of structuring, storing, and indexing data. A comparison with an ordinary table may help to explain its uniqueness.

Index-Organized Tables Versus Ordinary Tables

A row in an ordinary table has a stable physical location. Once this location is established, the row never completely moves. Even if it is partially moved with the addition of new data, there is always a row piece at the original physical address--identified by the original physical rowid--from which the system can find the rest of the row. As long as the row exists, its physical rowid does not change. An index in an ordinary table stores both the column data and the rowid.

A row in an index-organized table does not have a stable physical location. It keeps data in sorted order, in the leaves of a B*-tree index built on the table's primary key. These rows can move around to preserve the sorted order. For example, an insertion can cause an existing row to move to a different slot, or even to a different block.

The leaves of the B*-tree index hold the primary key and the actual row data. Changes to the table data - for example, adding new rows, or updating or deleting existing rows - result only in updating the index.

See also the documenation about (11g): Index Organized Tables in the more recent Oracle 11g version.

CREATE CLUSTER employees_departments_cluster
(department_id NUMBER(4)) SIZE 512;

CREATE TABLE employees ( department_id number(4), ename varchar2(10), empno int )
CLUSTER employees_departments_cluster (department_id);

CREATE TABLE departments ( department_id number(4), dname varchar2(10), sales int )
CLUSTER employees_departments_cluster (department_id);

CREATE INDEX idx_emp_dept_cluster ON CLUSTER employees_departments_cluster;
insert into departments values (1,'d1',1000);
insert into employees values (1,'emp123',123);
insert into employees values (1,'emp124',124);
insert into employees values (1,'emp125',125);