The "suggested database indexes" on this page suggest BITMAP indexes on the STATUS and CONTENTTYPE columns of the CONTENT table:

http://confluence.atlassian.com/display/CONF29/Improving+Database+Performance

Oracle bitmap indexes can lock several thousand rows of the table when they're being updated. This has been a big problem since (a) most write activity on the CONTENT table are inserts or deletes, which inevitably update the index, and (b) writes to the CONTENT table are much more frequent since we added drafts and auto-save.

We need to revisit these recommendations, especially for high volume instances.