Database Performance Optimization Techniques

Study Notes

Database Performance Tuning

Database performance is a major user demand after application startup.
Two common ways to improve database performance are indexing and clustering.

Indexing

Indexing is a primary method to reduce disk I/O and improve database performance.
An index is used automatically by Oracle when the indexed column is in a query.
Indexes quickly find specific records using the index, reducing disk I/O.
Without an index, Oracle has to read the entire table to find a specific record.
Indexes are useful only for frequently accessed key columns.
Unnecessary indexes can slow down the system and database performance.
Indexes need to be updated with every operation, adding storage and processing burdens.

Creating an Index

An index is created using the CREATE INDEX command.
The syntax is: CREATE [UNIQUE] INDEX [schema.]indexname ON tablename (column1, column2 …);
Schema specifies the name of the schema that contains the index.
Indexname specifies the name of the index to be created.
Tablename specifies the name of the table on which the index is created.
Column1, column2 … specify the names of columns from tables on which the index is to be created.
UNIQUE specifies that the value of the column (or combination of columns) in the table to be indexed must be unique.

Dropping an Index

An index can be dropped when it is no more required.
The syntax is: DROP INDEX [schema.]indexname

Clustering

Clustering is another method of improving database performance.
Clustering stores data together from different tables that are often accessed together.
Clustering boosts performance for tables often used in join queries.
This reduces data retrieval time because joined rows are stored together.

Description

Learn about indexing and clustering techniques to improve database performance. Understand how indexing reduces disk I/O and enhances query performance. Explore the differences between indexing and clustering methods.