Indexing only null column values in Oracle

We have a table that can have tens or hundreds of millions of rows in it, and in this table there is a certain column. This column usually—typically—generally—has non-null hash values in it.

In unusual cases though, that column can be NULL. And the thing is, there are times when the interesting rows, the rows we want to gather together and do something with, are exactly the rows where that column is null.

Table scans to find the rows where the column is NULL are expensive, so naturally we want to index this column. But since non-null values in this column are a hash and we only ever care about the NULL values, we’d like to avoid the time and space overhead of indexing the non-null values. We’d like a partial index of only the rows where the column is NULL…

It got overwhelmed and stopped listening

We’re trying to set up to test the relative performance of XA transactions vs. local transactions on our technology stack.  We have a producer and a consumer .war running on separate servers; an Oracle server; and an ActiveMQ server.  We’re running a LoadTest from soapUI, barraging the producer with web service calls telling it to send a message to the queue — and then the consumer gets the message and writes a record to the database.

The problem was, after only a few minutes, the producer and consumer would just seem to hang — the producer would not accept any more messages from soapUI, and the consumer would not receive any more messages from the queue (even though there were plenty there for it to receive!)  From that point on, the producer would place about one message in the queue per minute, but the consumer would never pick up any more messages.  Huh!

A search turned up a suggestion to check the producer flow control setting.

We changed our conf/activemq.xml to add producerFlowControl=”false”:

<destinationPolicy>
<policyMap>
<policyEntries>
<policyEntry queue=">" producerFlowControl="false" ...

This time both producer and consumer kept going.