Sql, Storage, Indexing, Design, Tuning

Front 1

Block

Back 1

The amount of data read or written in one I/O operation.

Front 2

Page

Back 2

Same as a block

Front 3

Blocking factor

Back 3

The number of physical records per block.

Front 4

Inputs of physical design phase

Back 4

Logical (implementation) model
Documentation/Definitions
DBMS Characteristics
Response time requirements
Security, Backup, Recovery, Retention, Integrity requirements

Front 5

Outputs of physical design phase

Back 5

Produces a Description of the Implementation of the Database on Secondary Storage.

Describes the storage structures and access methods used to achieve efficient access to the data.

Front 6

Primary & Secondary index differences

Back 6

Primary: An ordered, fixed-length file that stores the primary key and a pointer.
Each record in the index file corresponds to each block in the ordered data file.
Sparse / nondense index

Secondary:
A secondary index is an ordered file that stores the nonordering field of a data file and a pointer.
If the indexing field is a secondary or candidate key, then the index is dense.
If the indexing field is a nonkey field, then the index is sparse.
Introduces the idea of adding a level of indirection

Front 7

Clustered vs. Unclustered indexes

Back 7

If order of data records is the same as, or `close to’, order of data entries, then called clustered index.

A file can be clustered on at most one search key.
Cost of retrieving data records through index varies greatly based on whether index is clustered or not!

Front 8

Describe hash based indexes

Back 8

Hash function calculates the address of the page on which the record is stored.
The field that’s used in the hash function is called the hash field.
Called a hash key if the hash field is also a key field.
Good for equality searches
Not good for range searches

Front 9

Describe B-tree Indexes

Back 9

If the root is not a leaf node, it must have at least two children.
For a tree of order n, each node (other than root/leaf nodes) must have between n/2 and n pointers and children.
… (other rules)
Tree must always be balanced
Every path from the root to a leaf must have same length.  This is what matters.
This means that it always takes about the same time to access any record.
Leaf pages contain data entries, and are chained (prev & next)
Non-leaf pages contain index entries and direct searches:

Front 10

Tradeoffs in deciding which indexes to create

Back 10

Indexes can make queries go faster, updates slower but require disk space, too.

Front 11

Index selection guidelines

Back 11

Attributes in WHERE clause are candidates for index keys.
Exact match condition suggests hash index.
Range query suggests tree index.
Clustering is especially useful for range queries; can also help on equality queries if there are many duplicates.
Multi-attribute search keys should be considered when a WHERE clause contains several conditions.
Order of attributes is important for range queries.
Such indexes can sometimes enable index-only strategies for important queries.
For index-only strategies, clustering is not important!
Try to choose indexes that benefit as many queries as possible. Since only one index can be clustered per relation, choose it based on important queries that would benefit the most from clustering.

Front 12

Rules of thumb for indexes

Back 12

Index primary key fields
DBMS may do this automatically
Composite keys require composite indexes
Index foreign key fields
Index other fields frequently used in:
WHERE clauses
GROUP BY clauses
ORDER BY clauses
Consider composite indexes when fields are used together in conditions

Front 13

Advantages/Disadvantages of Clustered Indexes

Back 13

Advantages:
Efficient storage; faster searches than heap
Faster inserts/deletes than sorted file

Disadvantages:
Only one clustering factor per file

Front 14

Advantages/Disadvantages of Unclustered tree indexes

Back 14

Advantages:
Fast searches, fast inserts/deletes

Disadvantages:
Slow scans and range searches with many matches

Front 15

Advantages/Disadvantages of Hashed indexes

Back 15

Advantages:
Same as unclustered, but faster on equality searches

Disadvantages:
Does not support range searches

Front 16

Four steps of transaction analysis

Back 16

1. Create xact/table cross-reference and/or usage map.
2. Determine which tables are most frequently accessed by xacts
3. Analyze usage data for selected xacts that involve the most frequently-used tables.
4. Design to improve performance of key transactions (indexes)

Front 17

Which transactions should be analyzed?

Back 17

May not be able to analyze all xacts
Focus on “important” transactions
80/20 rule
80% of data access comes from 20% of xacts
Also consider high-priority xacts

Front 18

What information is contained in a cross reference table

Back 18

Transactions and relations and the operations performed for each transaction

Front 19

What information should you consider when performing a detailed analysis of a transaction

Back 19

Tables and attributes used
Type of access (select, update)
Avoid indexes for update xacts
Attributes used in conditional statements
Range vs. equality (cluster vs. hash index)
Attributes used in joins
Attributes used in order/group by
Expected frequency of query
Performance goals:
High freq/priority queries are priority candidates for indexes.

Front 20

What is denormalization, why would it be considered

Back 20

Introduce additional redundancy
Improves performance
May lead to anomolies

Front 21

What is a view? Why are views used?

Back 21

View
Virtual table based on base tables in the DB
Only the definition really exists in the DB
Provide a limited “view” of the DB
“Pre-do” joins
Limit tables
Limit columns
Way to implement logical independence
Used for security

Front 22

Name and briefly describe four design goals for distributed DBMS'

Back 22

Local transparency
users don’t need to know where data is stored
Replication transparency
user can treat data as if it is stored at only one site, even if it is replicated at several sites
Failure transparency
a transaction is committed or not committed
commit protocol
Concurrency transparency
it appears to the user as their transaction is the only activity on the system