Repetitive Key Overflow
Storage structures other than heap that have a high degree of duplication in the key values are likely to have overflow because duplicate keys are stored in overflow pages. Keys with a high degree of duplication are not recommended. This applies to secondary index keys as well as primary keys.
Repetitive key overflow occurs, for example, if the emp table is keyed on sex, resulting in two primary pages for the values “M” and “F.” The remainder of the pages are overflow pages to these two primary pages.
Consider if the following query is run:
select * from student
where student.sex = 'F'
and student.name = 'Baker';
The key is used to find the first primary page. The search goes down the entire overflow chain for “F” looking for all names Baker. Every page is checked. Because this query looks restrictive, the locking system probably chooses to page level lock. The query locks 10 pages and eventually escalates to a table level lock. Wait for the table level lock if other users are updating. Finally, the search finishes scanning the overflow chain and returns the row.
Retrieval performance with a duplicate key is still better than for a heap table because only half the table is scanned.
However, update performance suffers. If a user wants to append a new female student, the locking system starts by exclusively locking pages in the “F” overflow chain. If another 10 pages need to be locked eventually, the locking system attempts to escalate to an exclusive table level lock. If only one user is updating the table, the lock is easily obtained. If multiple users are trying to update the table at the same time, deadlock is likely.
User1 and User2 both exclusively hold 10 pages in the table. User1 wants to escalate to an exclusive table level lock so the query can continue, but User1 cannot proceed until User2 drops the exclusive page level locks User2 holds. User2 also wants to obtain an exclusive table level lock, but cannot proceed until User1 releases the locks. This is deadlock, which can seriously degrade update performance. For more information, see the chapter “Understanding the Locking System.”