INDEX Flashcards
1
Q
What are the different types of Indexcies
A
- 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 - Non Clustered Index
2
Q
Cluster vs Non Clustered Index
A
- Table can only have 1 clustered index usually on PK. Up to 999 non cluster index.
- Clustered Index on Data Page Physical. Non clustered is a pointer to the data
- 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])
3
Q
What is a
Cluster Index
Oracle database calls this concept index-organized tables (IOT)
A
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