INDEX Flashcards

1
Q

What are the different types of Indexcies

A
  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
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
2
Q

Cluster vs Non Clustered Index

A
  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])

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly