Tom Kelliher, CS 318
Apr. 12, 2002
Read 11.4--5.
PL/pgSQL lab for some, an embarrassment for others.
Indexed organizations.
Example: Consider a table indexed on its key. The access path for a
SELECT
on the key would utilize the index. The access path for
an aggregate computation on the entire table might just access the pages
of the table sequentially, eliminating the index overhead.
Contiguous storage. Fragmentation.
VARCHAR
field.
F to perform duplicate checking, 1 to write new record.
Otherwise, 2F. Why not F as in the book?
SELECT * FROM TRANSCRIPT T;F transfers.
SELECT * FROM TRANSCRIPT T ORDER BY T.StudId;F + N transfers.
SELECT AVG(T.Grade) FROM TRANSCRIPT T;F accesses.
SELECT T.Grade FROM TRANSCRIPT T WHERE T.StudId = '123456' AND T.CrsCode = 'CS318' AND T.Semester = 'S2002';transfers on average.
SELECT T.Grade FROM TRANSCRIPT T WHERE T.StudId > '100000' AND T.StudId < '200000';F transfers.
Disadvantage: pages of file are no longer contiguous.
Derivation?
Derivation?
If it's not sorted on the relevant key?
SELECT * FROM TRANSCRIPT T;Transfers?
SELECT * FROM TRANSCRIPT T ORDER BY T.StudId;Transfers?
SELECT AVG(T.Grade) FROM TRANSCRIPT T;Transfers?
SELECT T.Grade FROM TRANSCRIPT T WHERE T.StudId = '123456' AND T.CrsCode = 'CS318' AND T.Semester = 'S2002';Transfers?
SELECT T.Grade FROM TRANSCRIPT T WHERE T.StudId > '100000' AND T.StudId < '200000';Transfers?