5.3 Managing indexes and views

Indexes and views are powerful tools for optimizing database performance and simplifying data access. Indexes speed up data retrieval by creating efficient lookup structures, while views provide virtual tables that can simplify complex queries and enhance security.

Managing indexes and views involves creating, altering, and dropping these objects as needed. Understanding how to effectively use and maintain these structures is crucial for database administrators and developers to ensure optimal database performance and usability.

Creating and Dropping Indexes

Creating Indexes

Top images from around the web for Creating Indexes

• 

  PostgreSQL Indexes View original

  Is this image relevant?

• 

  SchemaCrawler - Free database schema discovery and comprehension tool View original

  Is this image relevant?

• 

  PostgreSQL Indexes View original

  Is this image relevant?

• 

  PostgreSQL Indexes View original

  Is this image relevant?

• 

  SchemaCrawler - Free database schema discovery and comprehension tool View original

  Is this image relevant?

1 of 3

Top images from around the web for Creating Indexes

• 

  PostgreSQL Indexes View original

  Is this image relevant?

• 

  SchemaCrawler - Free database schema discovery and comprehension tool View original

  Is this image relevant?

• 

  PostgreSQL Indexes View original

  Is this image relevant?

• 

  PostgreSQL Indexes View original

  Is this image relevant?

• 

  SchemaCrawler - Free database schema discovery and comprehension tool View original

  Is this image relevant?

1 of 3

• Use the

  [CREATE INDEX](https://www.fiveableKeyTerm:create_index)

  statement to create an index on one or more columns of a table
• Indexes improve query performance by allowing the database to quickly locate and retrieve specific rows based on the indexed columns
• Specify the name of the index, the table it belongs to, and the column(s) to be indexed
• Can create unique indexes that enforce uniqueness on the indexed columns by adding the

  UNIQUE

  keyword
• Can create composite indexes that span multiple columns by specifying multiple columns in the

  CREATE INDEX

  statement

Types of Indexes

• Clustered index determines the physical order of data in a table
  • Table can have only one clustered index because it dictates the storage order of rows
  • Primary key of a table is typically created as a clustered index
• Non-clustered index is a separate structure from the table data
  • Table can have multiple non-clustered indexes
  • Each non-clustered index contains the indexed columns and a pointer to the corresponding row in the table
  • Useful for frequently queried columns that are not part of the clustered index

Dropping Indexes

• Use the

  [DROP INDEX](https://www.fiveableKeyTerm:drop_index)

  statement to remove an existing index from a table
• Specify the name of the index to be dropped
• Dropping an index can be useful when an index is no longer needed or when you want to modify the structure of an index
• Dropping an index does not affect the data in the table itself

Managing Views

Creating Views

• Use the

  [CREATE VIEW](https://www.fiveableKeyTerm:create_view)

  statement to create a virtual table based on the result of a SELECT statement
• Views allow you to simplify complex queries, provide a layer of abstraction, and enforce security by limiting access to specific columns or rows
• Specify the name of the view and the SELECT statement that defines the view's contents
• Views can be queried and manipulated like regular tables
• Example:

  CREATE VIEW EmployeeSalary AS SELECT EmployeeID, FirstName, LastName, Salary FROM Employees

Altering Views

• Use the

  [ALTER VIEW](https://www.fiveableKeyTerm:alter_view)

  statement to modify the definition of an existing view
• Specify the name of the view and the new SELECT statement that defines the view's updated contents
• Altering a view allows you to change the columns, conditions, or underlying tables without dropping and recreating the view
• Example:

  ALTER VIEW EmployeeSalary AS SELECT EmployeeID, FirstName, LastName, Salary, Department FROM Employees

Dropping Views

• Use the

  [DROP VIEW](https://www.fiveableKeyTerm:drop_view)

  statement to remove an existing view from the database
• Specify the name of the view to be dropped
• Dropping a view does not affect the underlying tables or data
• Example:

  DROP VIEW EmployeeSalary

Materialized Views

• Materialized views are precomputed result sets stored in the database
• Unlike regular views, which are virtual and computed on-the-fly, materialized views store the result set physically on disk
• Useful for complex queries or aggregations that are expensive to compute in real-time
• Materialized views can be refreshed manually or automatically to keep the data up to date
• Example:

  CREATE [MATERIALIZED VIEW](https://www.fiveableKeyTerm:Materialized_View) SalesSummary AS SELECT ProductID, SUM(Quantity) AS TotalQuantity, SUM(Amount) AS TotalAmount FROM Sales GROUP BY ProductID