SQL technique: views and indexes
A view is simply any SELECT query that has been given a name and saved in the database. For this reason, a view is sometimes called a named query or a stored query. To create a view, you use the SQL syntax:
CREATE OR REPLACE VIEW <view_name> AS SELECT <any valid select query>;
DROP VIEW <view_name>;
A view name may be used in exactly the same way as a table name in any SELECT query. Once stored, the view can be used again and again, rather than re-writing the same query many times.
CREATE VIEW phone_view AS SELECT empFName, empLName, empPhone FROM Employees; GRANT SELECT ON phone_view TO public; CREATE VIEW job_view AS SELECT employeeID, empFName, empLName, jobTitle, managerID FROM Employees; GRANT SELECT, UPDATE ON job_view TO managers; CREATE VIEW pay_view AS SELECT employeeID, empFName, empLName, payRate FROM Employees; GRANT SELECT, UPDATE ON pay_view TO payroll;
Sometimes, the execution speed of a query is so important that a developer is willing to trade increased disk space use for faster response, by creating a materialized view. Unlike the view discussed above, a materialized view does create and store the result table in advance, filled with data. The scheme of this table is given by the SELECT clause of the view definition.
An index, as you would expect, is a data structure that the database uses to find records within a table more quickly. Indexes are built on one or more columns of a table; each index maintains a list of values within that field that are sorted in ascending or descending order. Rather than sorting records on the field or fields during query execution, the system can simply access the rows in order of the index.
Unique and non-unique indexes: When you create an index, you may allow the indexed columns to contain duplicate values; the index will still list all of the rows with duplicates. You may also specify that values in the indexed columns must be unique, just as they must be with a primary key. In fact, when you create a primary key constraint on a table, Oracle and most other systems will automatically create a unique index on the primary key columns, as well as not allowing null values in those columns. One good reason for you to create a unique index on non-primary key fields is to enforce the integrity of a candidate key, which otherwise might end up having (nonsense) duplicate values in different rows.
Queries versus insertion/update: It might seem as if you should create an index on every column or group of columns that will ever by used in an ORDER BY clause (for example: lastName, firstName). However, each index will have to be updated every time that a row is inserted or a value in that column is updated. Although index structures such as B or B+ trees allow this to happen very quickly, there still might be circumstances where too many indexes would detract from overall system performance. This and similar issues are often covered in more advanced courses.
Syntax: As you would expect by now, the SQL to create an index is:
CREATE INDEX <indexname> ON <tablename> (<column>, <column>...);
To enforce unique values, add the UNIQUE keyword:
CREATE UNIQUE INDEX <indexname> ON <tablename> (<column>, <column>...);
To specify sort order, add the keyword ASC or DESC after each column name, just as you would do in an ORDER BY clause.
To remove an index, simply enter:
DROP INDEX <indexname>;