1 / 18

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

SQL's Data Definition Language (DDL) – View, Sequence, Index. 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

silas
Download Presentation

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

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  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

More Related