We're continuing with important questions on Database for specialist officers (SBI & IBPS). Feel free to share your feedback and doubts in comments section.
Indexing and its different kinds?
Indexing is a technique by which specific data can be retrieved quickly. They can also be created to enforce uniqueness and to facilitate sorting. Its different kinds are as follows:
- Binary Search - In this the target value is compared to the middle element in an array. The half in which value is not found is discarded and middle element of remaining half is compared again until the value is successfully found.
- B Tree - In B-Tree, internal nodes can have variable no. of child nodes. Any insertion or deletion of data changes the no. of child nodes. Each internal node has a no. of keys. E.g. If there are 3 internal nodes with keys X & Y, left subtree will have values less than X, middle would have values between X & Y and right sub tree would have values greater than Y.
- Inverted Index - Unlike regular index as in books which maps pages to topics, Inverted index consists of a list of unique words that appear in a document and for each word, a list of documents in which it appears. It is used for full text searches like in search engines.
- Database Index - You can create an index on a field in table which will have field value and a pointer to record it is related to. These indexes need additional storage space.
- BitMap Index - Typically used in Oracle databases, it is used for columns having low distinct values. Eg. Column called SEX which will have only 2 values - Male & Female.
What is a Phantom Deadlock?
In distributed systems, these are the deadlocks that are falsely detected due to delay in propagating local information. They don't actually exist and lead to unnecessary aborts.
What is cold back up & hot back up (in Oracle)?
- Cold Backup - During cold backup, database is not running & not available to users. Hence, there is no change in the data files during back up and DB is in sync when restored.
- Hot Backup - Database remains open and available to users. Hence there may be changes and upon restore, changes in log files are re-applied to bring DB in sync.
Difference between an exclusive lock & a shared lock?
Exclusive lock - It prohibits other users from reading the locked resource.
Shared lock - It allows other users to read the locked resource without updating it.
What is market basket analysis?
Its a data mining technique which predicts the sets of products to be purchased together. It is based on conditional probability.
What are Save points in Oracle?
SAVE POINTS can be used to divide a transaction into smaller parts and we can rollback only these part without having to roll back the whole transactions. Maximum of 5 SAVE POINTS are allowed.
What are snap shots in Oracle DB?
These are read only copies of master data maintained at a remote site and are periodically refreshed to reflect changes made in the master data.
Gk fact of the day
The first internet search engine was called 'Archie' created in 1990.
