16

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.

Improve this question
0

4 Answers 4

Reset to default
28

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.

Improve this answer
0
14

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.

Improve this answer
1
  • @a_horse_with_no_name I wasn't sure if it is exactly the same. Looking at the documentation, I notice a small difference regarding secondary indexes: "Logical rowid in ROWID pseudocolumn allows building secondary indexes." In SQL Server, the PK columns are used for that, not any ROWID. Commented Mar 8, 2017 at 15:56
-2

There is no possibility for now to create a clustered index, but it it possible to add some features to achieve nearly same result: (B-Tree & Bitmap INDX) 1. STEP - create cluster for one or more table - reference e.g. PK - common column 2. STEP - add some tables to the cluster 3. STEP - create CLUSTERED INDEX within a CLUSTER - see last sql stmt (INDEX_TYPE=CLUSTERED CLUSTERING_FACTOR=1)

enter image description here

Improve this answer
-7

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);

select INDEX_NAME, INDEX_TYPE, TABLE_NAME, TABLE_TYPE, CLUSTERING_FACTOR from DBA_INDEXES WHERE INDEX_NAME=UPPER('idx_emp_dept_cluster') ;

INDEX_NAME, INDEX_TYPE, TABLE_NAME, TABLE_TYPE, CLUSTERING_FACTOR IDX_EMP_DEPT_CLUSTER,CLUSTER,EMPLOYEES_DEPARTMENTS_CLUSTER,CLUSTER,1

Improve this answer
2
  • 3
    It would be more useful to future readers if you added some explanation as to how these statements answer the original question. Commented Jan 21, 2019 at 1:59
  • 1
    This does not add to what the existing answers have said. Commented Jan 21, 2019 at 4:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.