Sequence & Index

Sequence :

• To generate sequences numbers to a column sequence is used.
• Can be used for any column of table.
• Column data type should be number.

 Example ;

SQL> Create or replace sequence no_seq start with 1 increment by 1 ;

To use this sequence in insert statement :
Insert into emp values (no_seq.next,'john',100);

Index :

• To speed up sql statement execution on a table . 
• it points directly to the location of the rows containing the values.

Types :

• Unique
• Non-Unique
• B-Tree
• Composite
• Function based 
• Cluster

Unique :

• It guarantee that no two rows of a table have duplicate values in the columns that define the index.
• Automatically created when primary key or unique key constraint is created.

Example :
SQL> create unique index stude_idx on student(sno);

Non-Unique :

• it doesn't  impose the above restrictions on the column values.

Example :
SQL> create index stude_idx on student(sno);

B-Tree :

• Default type of index in oracle.
• Created when column having more distinct or unique values . eg. student no , employee no etc.

SQL> create index std_idx on student(sno);

Bitmap Index : 

• Created when the columns have low cardinality values or distinct values.
• gender ,country etc.

SQL> create bitmap index stude_idx on student(gender);

Composite Index :

• also called concatenated index.
• Created on multiple columns

Example :
create bitmap index student_idx on student(sno,sname);

Function based Index :

• When we use any functions within the where clause we define function based index.