Sql Index with Example
Sql Index is used to speed up the retrieval of rows.
- PRIMARY KEY or UNIQUE constraint are automatically considered as Indexes
- It is a sorted list of values that appear in the table, and each entry in the index helps you find the rows where that value appears.
- The main Benefit of creating a index is to retrieve the rows quickly.
- In SQL, you can create more than one index for a table.
- Indexes are created on table columns and also created by Database Administrator.
SQL Index Example:
If want to go on some topic or page in a book , it will take time to find the page manually. it is easy to find that page if indexing is done in the beginning of the book with page numbers and topics.
indexes are created in two ways –
-
-
- Automatically
- Manually
-
a) Automatically-
whenever we are creating a primary key or unique key in a table column then oracle server internally automatically creates BTree index on those columns.
b) Manually
We can also creates index explicitly using below syntax
Syntax
create index indexname on tablename(colunname);
Delete a Index
delete index for column is, DROP INDEX index_name ON table_name;
By default primary key is act as index but when it comes to multi value access queries instead of searching the whole DB it will only read that page where index is marked