INDEX

Question 1

What are the different types of Indexcies

Answer 1

1. Clustered Index
   The Oracle database calls this concept index-organized tables (IOT), other databases use the term clustered index
   cluster index”, where the rows are stored in the primary key order, physically on the disk
2. Non Clustered Index

Question 2

Cluster vs Non Clustered Index

Answer 2

1. Table can only have 1 clustered index usually on PK. Up to 999 non cluster index.
2. Clustered Index on Data Page Physical. Non clustered is a pointer to the data
3. NonClustered is faster because when we use a DML statement (Select, Insert, Delete) on clustered index could have performance issues if it has to UPDAT the INDEX every time the DML is executed.

CREATE [Cluster|NonCluster] INDEX index-name
ON (column [asc|desc])

Question 3

What is a
Cluster Index
Oracle database calls this concept index-organized tables (IOT)

Answer 3

cluster index - where the rows are stored in the primary key order, physically on the disk

Oracle defines the term “cluster” to mean that different row types can be stored inside the same table block, sort of a physical way to pre-join tables together, when used with rows for the primary-foreign key relationships. Hence, the term cluster index has a different meaning that with SQL Server and DB2 cluster indexes