Databases Flashcards
What’s a column store for? When is it good?
Datawarehousing. When reading only specific columns.
What are the big O values of Hash lookup?
All O(1) except for inorder traversal which requires a sort.
How are indexes implemented?
Hash the key, result goes to a bucket in memory.
What are the isolation levels of DB transactions?
Read uncommitted - no isolation
Read Committed - commits on read if data is uncommitted
repeatable read - trx holds locks on rows
Serializable 0 concurrent trx execute serially
What’s better about a B+ tree than a B-?
All nodes on one child level,
in order traversal faster
What’s the big O of B-tree operations
Space = O(N), insert, search, delete = O(logn), traversal = O(n)
Describe non-clustered index
Data scructure separate from table. B-tree to organized.
What do isolation levels help? What are their cons?
Pro - data won’t change through the course of your trx. Cons - locking slow things down and cause deadlocks
What is pessimistic concurrency control?
Data is locked when read to eliminate chance of rollback
What does ACId stand for?
Atomic, Consistent, Isolation, Durable.
When is optimistic concurrency control good?
When data is not changed very often (historical records)
What’s truncate vs delete?
truncate gas an autocommit, frees up memory. Delete does not.
What is an aggregation pipeline?
For NoSQl, similar to SQL but aggregates all your data?
What is the mechanism to aggregate all your NoSQL data with SQL queryish type queries?
Aggregation pipeline.
