Partition by clause in SQL

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.

ROWNUM and Order by

select id from table where id =
(select id from
(select id from table where
columnx = ‘x’ ORDER BY id ASC)
where ROWNUM <= 1)
for update wait 10

Without this, Oracle first applies rownum before ordering thus yielding surprising results!

Optimistic versus Pessimistic Locking

Pessimistic Locking

Locking is a mechanism that prevents concurrent access to a particular item of data. When one transaction holds a lock on an item, no concurrent transaction can read and/or modify this item. A lock might be just a momentary lock, held while the item is being read, or it might be held until the completion of the transaction. A pessimistic lock is a lock that is acquired when an item of data is read and that is held until transaction completion.

In read-committed mode (our preferred transaction isolation level), the database never acquires pessimistic locks unless explicitly requested by the application. Usually, pessimistic locks aren’t the most scalable approach to concurrency. However, in certain special circumstances, they may be used to prevent database-level deadlocks,
which result in transaction failure. Some databases (Oracle and PostgreSQL, for example) provide the SQL SELECT…FOR UPDATE syntax to allow the use of explicit pessimistic locks.

Optimistic Locking

Optimistic Locking is implemented using “managed versioning” in Hibernate.

In our CaveatEmptor application, both the user who posted a comment and any system administrator can open an Edit Comment screen to delete or edit the text of a comment. Suppose two different administrators open the edit screen to view the same comment simultaneously. Both edit the comment text and submit their changes. At this point, we have three ways to handle the concurrent attempts to write to the database:

■ Last commit wins—Both updates succeed, and the second update overwrites
the changes of the first. No error message is shown.
■ First commit wins—The first modification is persisted, and the user submitting
the second change receives an error message. The user must restart the
business process by retrieving the updated comment. This option is often
called optimistic locking.
■ Merge conflicting updates—The first modification is persisted, and the second
modification may be applied selectively by the user.

Managed versioning relies on either a version number that is incremented or a timestamp that is updated to the current time, every time an object is modified. For Hibernate managed versioning, we must add a new property to our Comment class  and map it as a version number using the <version> tag.

Whenever Hibernate updates a comment, it uses the version column in the SQL WHERE clause:
update COMMENTS set COMMENT_TEXT=’New comment text’, VERSION=3
where COMMENT_ID=123 and VERSION=2

If another application transaction would have updated the same item since it was read by the current application transaction, the VERSION column would not contain the value 2, and the row would not be updated. Hibernate would check the row count returned by the JDBC driver—which in this case would be the number of rows updated, zero—and throw a StaleObjectStateException.

Managing Oracle Optimizer Statistics – Found the part on Dynamic Sampling very interesting.