Database index by Reema Gajjar

Hi All.. I am sharing small presentation about database indexes. Hope it will useful for all of you... :)

1. www.company.com
Database Indexes
Presented By :
Reema Gajjar

2. www.company.com
What Is Indexes ??
Indexes are Database Object that you can create to
improve the performance of query.
Indexes can be created explicitly or automatically.
It is used to retrieve data faster.
You can create indexes on one or more columns of a table.
You can create more than one index on one table.
User can't see indexes.
Company
LOGO

3. www.company.com
Index Of Book
If you have a book and want to find any topic??
You can just go to the index at the back of the book, which
will tell you the exact pages where you can find that topic.
book index contains a page number, same way
database index contains a pointer to the row.
That row containing the value that you are searching for in
your select SQL statement.
Company
LOGO

4. www.company.com
What exactly is inside a database index?
index stores the values for a specific column in a table.
ex. emp_name
But, It will use pointer for fetching values of other
columns of the same table. ex. emp_age, emp_salary
A pointer is a reference to a place in memory where the
row data is stored on disk.
Without that pointer all you would have is a single value,
which would be meaningless
emp_age emp_salaryemp_name ROWID
Indexed Column Pointer To The Row

5. www.company.com
Example of B-tree Index

Company
LOGO

6. www.company.com
CREATE INDEX statement
CREATE INDEX index_name
ON table_name (column_name);
Example :

Company
LOGO

7. www.company.com
Creating Index On Multiple Column
CREATE INDEX index_name
ON table_name (column_name1, column_name2);
Example :
Company
LOGO

8. www.company.com
RENAME An Index
ALTER INDEX index_name
RENAME TO new_index_name;
Example:

Company
LOGO

9. www.company.com
DROP An Index
DROP INDEX index_name;
Example:

Company
LOGO

10. www.company.com
Create Index Using Create Table
State
CREATE TABLE table_name
(
column_name datatype <PRIMARY KEY>/<UNIQUE>
USING INDEX (CREATE INDEX index_name on TABLE
(COLUMN_NAME))
);
Example:

Company
LOGO

11. www.company.com
When To Use Index??
The column contains a wide range of values.

One or more COLUMNS are frequently used together in a
WHERE clause or in JOIN condition.
Insert or load data into a table before creating indexes.
Company
LOGO

12. www.company.com
When To Not Use Index??
If columns are not often used as a condition in the query.
The table is updated frequently.
Company
LOGO

13. www.company.com
Advantages Of Index
Their use in queries usually results in much better
performance.
They make it possible to quickly retrieve (fetch) data.
Company
LOGO

14. www.company.com
Disadvantage Of Index
Having more indexes on a table doesn't produce faster
queries.
It take up a space.
DML operations that is committed, Indexes must be
UPDATED.
Slower INSERT, DELETE or UPDATE operations.
Company
LOGO

15. www.company.com
Thank You...
Company
LOGO