Partition by clause in SQL

SQL Interview questions

Hibernate Interview Questions

SQL Interview Questions

NOSQL High Scalability

Choosing your next NoSQL database

Introduction to NoSQL by Martin Fowler

Data driven subscriptions in SS2K 2005


akadia – database design and software engineering

Good publications – for the “hands-on” guys.

A concise description of what is “Selectivity” and “cardinality”

cardinality = nbr of unique/distinct values in a column
selectivity = ratio of (nbr of distinct values/total nbr of records)…

Quiz 🙂
Which columns have the highest selectivity? Ideal selectivity=1
Ans: Primary keys

Bitmap indexes are used for OLAP databases = highly compressed indexes ( on low cardinality columns)
B-Tree indexes are used for OLTP databases = uncompressed. (recommended on high cardinality columns)

ROWNum and Order By in Oracle

Insightful article

LockModes in Hibernate

from “Hibernate in Action” –

Hibernate defines several lock modes:
■ LockMode.NONE—Don’t go to the database unless the object isn’t in either
■ LockMode.READ—Bypass both levels of the cache, and perform a version
check to verify that the object in memory is the same version that currently
exists in the database.
■ LockMode.UPDGRADE—Bypass both levels of the cache, do a version check
(if applicable), and obtain a database-level pessimistic upgrade lock, if
that is supported.
■ LockMode.UPDGRADE_NOWAIT—The same as UPGRADE, but use a SELECT…FOR
UPDATE NOWAIT on Oracle. This disables waiting for concurrent lock releases,
thus throwing a locking exception immediately if the lock can’t be obtained.
Understanding database transactions 167
■ LockMode.WRITE—Is obtained automatically when Hibernate has written to
a row in the current transaction (this is an internal mode; you can’t specify
it explicitly).
By default, load() and get() use LockMode.NONE. LockMode.READ is most useful with
Session.lock() and a detached object. For example:
Item item = … ;
Bid bid = new Bid();

Transaction tx = session.beginTransaction();
session.lock(item, LockMode.READ);
This code performs a version check on the detached Item instance to verify that
the database row wasn’t updated by another transaction since it was retrieved,
before saving the new Bid by cascade (assuming that the association from Item to
Bid has cascading enabled).
By specifying an explicit LockMode other than LockMode.NONE, you force Hibernate
to bypass both levels of the cache and go all the way to the database.