SQL's Data Definition Language (DDL) – View, Sequence, Index

1. SQL's Data Definition Language (DDL) – View, Sequence, Index

2. DDL: View, Index, Sequence, Synonym • Objects are defined using the CREATE statement • Some objects can be modified using the ALTER statement • Some objects can be modified using the ALTER statement • Some objects cannot be modified - they must be dropped and then re-created • Objects are removed using the DROP statement

3. View • A view is a virtual table – no data is actually maintained for a view • A view is defined based on existing tables by using a SELECT statement: CREATE VIEW viewname AS SELECT … • When a view is referenced the view's defining query is executed and data is retrieved from tables referenced by the defining query • The defining query for a view is stored in the data dictionary

4. View Example CREATE VIEW management AS SELECT Emp_no, ename FROM employees WHERE emp_no IN (SELECT mgr FROM employees WHERE mgr IS NOT NULL); • Using a view in a query causes the SELECT statement that defines the view to be executed and data values retrieved eg SELECT ename FROM management;

5. View Example 2 • Views are often used to simplify writing of complex queries which involve multiple tables or group functions Eg:CREATE VIEW deptsummary AS SELECT d.deptno, dept_name, sum(sal) Salaries FROM departments d, employees e WHERE d.dept_no = e.dept_no(+) GROUP BY d.dept_no, dept_name; SELECT * FROM deptsummary WHERE Salaries > 5000;

6. View Example 3 • Views are also used to permit access to only certain rows and/or columns in a table to some user(s) and not permit access to the remaining columns and/or rows Eg: CREATE VIEW emplist AS SELECT emp_no, ename, fname, job FROM employees WHERE LOWER(job)!='president' ORDER BY ename, fname; SELECT * FROM emplist;

7. Simple and Complex Views • A simple view is based upon a query that involves only a single table and uses no grouping; any other view is referred to a complex view • Data can be modified using INSERT, UPDATE and DELETE statements applied to a simple view but not to a complex view eg: UPDATE emplist SET job = 'Assistant' WHERE job = 'Clerk';

8. Modifying Data using a View • Data can be modified using INSERT, UPDATE and DELETE statements on a simple view but this is not normally done • A simple view defined using a WITH READ ONLY clause will not permit data to be modified eg CREATE VIEW emplist2 AS SELECT emp_no, ename, fname, job FROM employees WHERE LOWER(job)!='president' ORDER BY ename, fname WITH READ ONLY;

9. Change View • Change definition of a view by using the OR REPLACE clause in the CREATE VIEW statement eg CREATE OR REPLACE VIEW emplist AS SELECT emp_no, ename, fname, job FROM employees ORDER BY ename, fname;

10. Indexes • Indexes store the value of a column(s) being indexed as well as pointer(s) to the physical location of the row(s) having this value • Most are implemented using a B*Tree • Indexes are used for 2 purposes: improving performance of queries and enforcing uniqueness of column values • CREATE INDEX indexname ON tablename(col1name,col2name,…)

11. Indexing and Performance • Indexes are often created to improve performance of queries • However indexes slow performance of INSERT, UPDATE and DELETE operations because the index must be updated also • Be aware of trade-off between overhead involved and usefulness of index • DBMS decides when an index should be used to retrieve data – programmer cannot reference an index in a query

12. Indexes • Index can be used to enforce uniqueness on column(s) in a table • PRIMARY KEY and UNIQUE constraints are actually implemented by Oracle DBMS defining a UNIQUE index • CREATE UNIQUE INDEX indexname ON tablename(col1name,…) eg CREATE UNIQUE INDEX SINIndex ON STUDENT(SIN)

13. Composite Indexes • If defining an index on a composite field, the order of the columns is significant : list the most commonly queried column first so that the index can be used for this single column or for the combined columns eg CREATE INDEX SectionIndex ON SECTION (subjcode,sectcode) • SectionIndex can be used to search on subjcode alone or subjcode and sectcode combined – but is not used to search on sectcode alone

14. Common Reasons to Define an Index on Large Tables • Column(s) used frequently in joins – therefore index defined on most foreign key fields • Column(s) used frequently in clauses (WHERE, GROUP BY, ORDER BY) • Columns with a wide distribution of values so index entries correspond to a limited number of rows • Column with large occurrence of NULL values since NULL values are not indexed

15. Sequence • A sequence is an object which is used to generate sequential values which are often assigned to primary keys • A sequence is not physically linked with a table or column • It is the responsibility of the developer to only use a sequence with the primary key or column that it was defined for • CREATE SEQUENCE seqname INCREMENT BY n START WITH n

16. Sequence(ctd) • Eg CREATE SEQUENCE emp_no_seq START AT 1000 INCREMENT BY 1 • A sequence has two pseudo-columns: - NEXTVAL : gets the next sequential value of the sequence - CURRVAL : gets the current value of the sequence Eg INSERT INTO employees VALUES(emp_no_seq.nextval, …) Eg SELECT emp_no_seq.currval FROM dual

17. Synonym • A synonym allows you to associate an alternate name with an object such as a table or view • Eg CREATE SYNONYM emp FOR employees; SELECT * FROM emp;

18. Remove Object from Data Dictionary • Remove object from data dictionary DROP VIEW viewname DROP SEQUENCE sequencename DROP INDEX indexname DROP SYNONYM synonymname