SQL Testing
Introduction To RDMS
DataBase
DBMS Vs RDBMS
SQL Tutorials
SQL DataTypes
SQL Operators
SQL Statements
DDL (Data Definition Language)
SQL-Create Table
SQL-Alter Table
SQL-Drop Table
SQL-Truncate Table
DML (Data Manipulation Language)
SQL-Select Statement
SQL-Insert Statement
SQL-Update Statement
SQL-Delete Statement
SQL Constraints
SQL-Not Null Constraint
SQL-Unique Constraint
SQL-Primary Key Constraint
SQL-Foreign Key Constraint
SQL-Check Constraint
SQL Special Operators
SQL-IN Operator
SQL-LIKE Operator
SQL-BETWEEN Operator
SQL Sorting (Order By)
SQL Group By
SQL Aggregate Function
SQL Having
SQL Joins
SQL-Cross Join
SQL-Inner Join
SQL-Left Outer Join
SQL-RIGHT Outer Join
SQL-Self Join
SQL SubQueries
ISQL-Independent Sub Queries
SQL-Correlated Sub Queries
SQL Views
SQL Index
DataBase
DBMS Vs RDBMS
SQL Tutorials
SQL DataTypes
SQL Operators
SQL Statements
DDL (Data Definition Language)
SQL-Create Table
SQL-Alter Table
SQL-Drop Table
SQL-Truncate Table
DML (Data Manipulation Language)
SQL-Select Statement
SQL-Insert Statement
SQL-Update Statement
SQL-Delete Statement
SQL Constraints
SQL-Not Null Constraint
SQL-Unique Constraint
SQL-Primary Key Constraint
SQL-Foreign Key Constraint
SQL-Check Constraint
SQL Special Operators
SQL-IN Operator
SQL-LIKE Operator
SQL-BETWEEN Operator
SQL Sorting (Order By)
SQL Group By
SQL Aggregate Function
SQL Having
SQL Joins
SQL-Cross Join
SQL-Inner Join
SQL-Left Outer Join
SQL-RIGHT Outer Join
SQL-Self Join
SQL SubQueries
ISQL-Independent Sub Queries
SQL-Correlated Sub Queries
SQL Views
SQL Index
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](https://tutorialshut.com/wp-content/uploads/2021/03/Sql-index.jpg)
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
Recommended Articles:
SQL Testing
Introduction To RDMS
DataBase
DBMS Vs RDBMS
SQL Tutorials
SQL DataTypes
SQL Operators
SQL Statements
DDL (Data Definition Language)
SQL-Create Table
SQL-Alter Table
SQL-Drop Table
SQL-Truncate Table
DML (Data Manipulation Language)
SQL-Select Statement
SQL-Insert Statement
SQL-Update Statement
SQL-Delete Statement
SQL Constraints
SQL-Not Null Constraint
SQL-Unique Constraint
SQL-Primary Key Constraint
SQL-Foreign Key Constraint
SQL-Check Constraint
SQL Special Operators
SQL-IN Operator
SQL-LIKE Operator
SQL-BETWEEN Operator
SQL Sorting (Order By)
SQL Group By
SQL Aggregate Function
SQL Having
SQL Joins
SQL-Cross Join
SQL-Inner Join
SQL-Left Outer Join
SQL-RIGHT Outer Join
SQL-Self Join
SQL SubQueries
ISQL-Independent Sub Queries
SQL-Correlated Sub Queries
SQL Views
SQL Index
DataBase
DBMS Vs RDBMS
SQL Tutorials
SQL DataTypes
SQL Operators
SQL Statements
DDL (Data Definition Language)
SQL-Create Table
SQL-Alter Table
SQL-Drop Table
SQL-Truncate Table
DML (Data Manipulation Language)
SQL-Select Statement
SQL-Insert Statement
SQL-Update Statement
SQL-Delete Statement
SQL Constraints
SQL-Not Null Constraint
SQL-Unique Constraint
SQL-Primary Key Constraint
SQL-Foreign Key Constraint
SQL-Check Constraint
SQL Special Operators
SQL-IN Operator
SQL-LIKE Operator
SQL-BETWEEN Operator
SQL Sorting (Order By)
SQL Group By
SQL Aggregate Function
SQL Having
SQL Joins
SQL-Cross Join
SQL-Inner Join
SQL-Left Outer Join
SQL-RIGHT Outer Join
SQL-Self Join
SQL SubQueries
ISQL-Independent Sub Queries
SQL-Correlated Sub Queries
SQL Views
SQL Index