Views and Indexes · Database Management System · Learn CSIT

Views and Indexes

CSC-265 · Semester IV · Database Management System

intermediate30 minAvailable

SQLin-browser engine · runs locallyviews

Ctrl Enter to run17 lines

Write SQL, then press Run. Result tables, affected-row counts and error messages appear here.

Lab brief

Objective

Create views to simplify access and indexes to accelerate queries, then use EXPLAIN to observe how the DBMS changes its execution plan.

Theory & concepts

A view is a named query that behaves like a table but stores no data. It hides joins and restricts visible columns. An index is an access structure that lets the DBMS find rows without scanning the whole table - the query optimizer's EXPLAIN plan shows the difference between a scan and an index lookup.

Rendering diagram…

Views simplify access; indexes speed up lookups

CREATE VIEW GradeReport AS
SELECT STUDENT.Name, COURSE.Title, ENROLLS.Grade
FROM STUDENT JOIN ENROLLS USING (Sid)
     JOIN COURSE USING (Cno);

CREATE INDEX idx_enrolls_cno ON ENROLLS (Cno);
EXPLAIN SELECT * FROM GradeReport WHERE Title = 'DBMS';

Prerequisites

Lesson: Views, indexes and additional constraints

Instructions

1Run the EXPLAIN for a join query on an unindexed foreign key and note the access method.
2Create the view GradeReport and query it like a table.
3Create an index on the join column and re-run EXPLAIN to compare plans.
4Insert new rows through the view and verify they land in the base tables.
5Test which view changes are allowed and which are rejected.
6Answer the closing questions on index trade-offs.

Tasks

Build the view
Create GradeReport over the three-way join and query it.
Updatable or not
Determine which views accept INSERT/UPDATE and explain why.
Index acceleration
Compare EXPLAIN output before and after creating the index.
Explain the plan
Interpret scan vs index lookup in the EXPLAIN output.

Self-check

The view returns correct joined data.
EXPLAIN output shows the index changing the access path.
The student explains why some views are read-only.
The student states the trade-off of adding an index.

Environment

SQL consoleEXPLAIN viewerview inspectorindex manager