Indexes in Databases || Why Index needed in Database
Indexes in Databases
to improve the performance of any query
index is a database object
creation of indexes Method:
Automatically
PRIMAY KEY, Unique constraint to any coulumn
MANUALLY
Creation of Index:
CREATE [UNIQUE][BITMAP] INDEX indx_name
on Table_name(column_name);
When create index:
WIDE RANGE OF VALUE IN A COLUMN
May contains large number of NULL Value.
frequently access WHERE Clause or Join
less than 2% of the rows in the table
When Not create index:
i. column not use in condition than no need to create index
ii. more than 4% of data,
iii. when table is updated very frequiently
Advantages:
Improve Performance
Disadvantages:
Extra store space
Remove the index:
Drop index index_name;
