Index and stuff Flashcards
Why use Views from JDBC?
Why use Views from JDBC
• Security
– Separate permissions on VIEW (e.g. read-only, potentially
enforced with trigger)
• Hide data
– Tables may contain sensitive data that not everyone needs
access to (e.g. credit card numbers)
• Hide complexity
– Queries may be complicated with joins, unions, subqueries, etc.
This complexity can be hidden with a view
• Support legacy code
– Refactoring a table may break a lot of code. A view can preserve
the original look of a table while the latter changes in the
background.
How costly is this operation (naive solution)?
Explain Index.
Explain the structure of Disk and Main Memory.
Some (over-simplified) typical costs of disk
accessing for database operations on a
relation stored over n blocks are:
Some (over-simplified) typical costs of disk
accessing for database operations on a
relation stored over n blocks:
– Query the full relation: n (disk operations)
– Query with the help of index: k, where k is the
number of blocks pointed to (1 for key).
– Access index: 1
– Insert new value: 2 (one read, one write)
– Update index: 2 (one read, one write)
SELECT *
FROM Lectures
WHERE course = ’TDA357’
AND period = 3;
How to solve this faster?
Assume Lectures is stored in n disk blocks. With no
index to help the lookup, we must look at all rows,
which means looking in all n disk blocks for a total
cost of n.
With an index, we find that there are 2 rows with the
correct values for the course and period attributes.
These are stored in two different blocks, so the total
cost is 3 (2 blocks + reading index).
With no Index?
With Index?
How to Create Index in DMBS?
CREATE INDEX
• Most DBMS support the statement
CREATE INDEX index name
ON table (attributes);
– Example:
CREATE INDEX courseIndex ON Courses (code);
– Statement not in the SQL standard, but most
DBMS support it anyway.
– Primary keys are given indexes implicitly (by
the SQL standard).
– In PostgreSQL, use \di to list indexes
Important properties of index.
Important properties
• Indexes are separate data stored by itself.
§ Can be created
üon newly created relations
üon existing relations
- will take a long time on large relations.
§ Can be dropped without deleting any table data.
• SQL statements do not have to be
changed
– a DBMS automatically uses any indexes.
Why don’t we have indexes on all (combinations of) attributes for faster lookups?
– Indexes require disk space.
– Modifications of tables are more expensive.
• Need to update both table and index.
– Not always useful
• The table is very small.
• We don’t perform lookups over it (Note: lookups ≠ queries).
– Using an index costs extra disk block accesses.
Summary – indexes
• Indexes make certain lookups and joins more
efficient.
– Disk block access matters.
– Multi-attribute indexes
• CREATE INDEX
• Usage analysis
– What are the expected operations?
– How much do they cost?
Σ(cost of operation)x(proportion of operations of that kind)
What are natural key?
Natural keys
• A natural key is a key consisting of
attributes in the domain model.
• In some cases, no suitable natural key
exists.
– No suitably unique natural candidate key.
– Natural candidate key ”too large”.
– Natural candidate key ”not stable”.
–…
