Queries,DatabaseDesign,ConstraintEnforcement.pptVIP

14 15 Original had “dense” vs. “sparse” – perhaps look in Ullman? Work-around for last point, e.g. in SQL server: create views on table, index view. Comments on column storage, key-value. Examples of Clustered Indexes B+ tree index on E.age can be used to get qualifying tuples. How selective is the condition? Is the index clustered? Consider the GROUP BY query. If many tuples have E.age 10, using E.age index and sorting the retrieved tuples may be costly. Clustered E.dno index may be better! Equality queries and duplicates: Clustering on E.hobby helps! SELECT E.dno FROM Emp E WHERE E.age40 SELECT E.dno, COUNT (*) FROM Emp E WHERE E.age10 GROUP BY E.dno SELECT E.dno FROM Emp E WHERE E.hobby=Stamps Index-Only Plans A number of queries can be answered without retrieving any tuples from one or more of the relations involved if a suitable index is available. SELECT D.mgr FROM Dept D, Emp E WHERE D.dno=E.dno SELECT D.mgr, E.eid FROM Dept D, Emp E WHERE D.dno=E.dno SELECT E.dno, COUNT(*) FROM Emp E GROUP BY E.dno SELECT E.dno, MIN(E.sal) FROM Emp E GROUP BY E.dno SELECT AVG(E.sal) FROM Emp E WHERE E.age=25 AND E.sal BETWEEN 3000 AND 5000 E.dno E.dno,E.eid Tree index! E.dno E.dno,E.sal Tree index! E. age,E.sal or E.sal, E.age Tree! Summary Many alternative file organizations exist, each appropriate in some situation. If selection queries are frequent, sorting the file or building an index is important. Hash-based indexes only good for equality search. Sorted files and tree-based indexes best for range search; also good for equality search. (Files rarely kept sorted in practice; B+ tree index is better.) Index is a collection of data entries plus a way to quickly find entries with given key values. Summary (Contd.) Data entries can be actual data records, key, rid pairs, or key, rid-list pairs. Choice orthogonal to indexing technique used to locate data entries with a given key value. Can have several indexes on a given file of data reco