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… Continue reading

How to put a delay in an Oracle sproc, for testing

To simulate your Oracle stored procedure taking 15 minutes to execute, put this in your sproc:

dbms_lock.sleep(15 * 60);

But first you’ll need to give your user EXECUTE permissions on the dbms_lock module:

C:\>sqlplus sys@clayoracle3 as sysdba
SQL> GRANT EXECUTE ON dbms_lock TO my_user;

Grant succeeded.

If you’re trying this out outside a sproc, put an exec on the beginning, thus:

exec dbms_lock.sleep(15 * 60);

Note: dbms_lock.sleep() is known to be unreliable past about a 10 minute nap.

How to spit out a result set returned by an Oracle sproc

When a sproc returns a result set, in Oracle it takes a little more work to view the contents of that result set. But it can be done.

Simple example

As an example, say you want to call the spir_get_fooids_by_bazidkey sproc which returns a result set of foo ids, and you want to see what foo ids it returned. Here’s how that would look:
Continue reading

The SEVERE but ignorable error

There’s a certain error-and-stack-trace we get every time we bring up an instance of JBoss (or is it every time we deploy one of our war projects?), to wit:

09:15:37,945 ERROR [STDERR] Oct 16, 2008 9:15:37 AM oracle.jdbc.driver.OracleDriver registerMBeans
SEVERE: Error while registering Oracle JDBC Diagnosability MBean. Invalid character '
' in value part of property
        at oracle.jdbc.driver.OracleDriver.registerMBeans(
        at oracle.jdbc.driver.OracleDriver$
        at Method)
        at oracle.jdbc.driver.OracleDriver.(
        at oracle.jdbc.pool.OracleDataSource.(
        at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
        at sun.reflect.NativeConstructorAccessorImpl.newInstance(
        at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(
        at java.lang.reflect.Constructor.newInstance(
        at java.lang.Class.newInstance0(
        at java.lang.Class.newInstance(
        at org.jboss.resource.adapter.jdbc.xa.XAManagedConnectionFactory.getXADataSource(
        at org.jboss.resource.adapter.jdbc.xa.XAManagedConnectionFactory.createManagedConnection(
        at org.jboss.resource.connectionmanager.InternalManagedConnectionPool.createConnectionEventListener(
        at org.jboss.resource.connectionmanager.InternalManagedConnectionPool.getConnection(
        at org.jboss.resource.connectionmanager.JBossManagedConnectionPool$BasePool.getConnection(
        at org.jboss.resource.connectionmanager.BaseConnectionManager2.getManagedConnection(
        at org.jboss.resource.connectionmanager.TxConnectionManager.getManagedConnection(
        at org.jboss.resource.connectionmanager.BaseConnectionManager2.allocateConnection(
        at org.jboss.resource.connectionmanager.BaseConnectionManager2$ConnectionManagerProxy.allocateConnection(
        at org.jboss.resource.adapter.jdbc.WrapperDataSource.getConnection(
        at org.springframework.orm.hibernate3.LocalDataSourceConnectionProvider.getConnection(
        at org.hibernate.cfg.SettingsFactory.buildSettings(
        at org.hibernate.cfg.Configuration.buildSettings(
        at org.hibernate.cfg.Configuration.buildSessionFactory(
        at org.hibernate.cfg.AnnotationConfiguration.buildSessionFactory(
        at org.springframework.orm.hibernate3.LocalSessionFactoryBean.newSessionFactory(
        at org.springframework.orm.hibernate3.LocalSessionFactoryBean.buildSessionFactory(
        at org.springframework.orm.hibernate3.AbstractSessionFactoryBean.afterPropertiesSet(
        at Method)

We’re used to this, and it doesn’t seem to cause any harm, so we ignore it, but wish it weren’t there.

A post to the SQLJ/JDBC forum at Oracle Technology Network Discussion Forums brings up this same issue, and the reply says that a patch to ojdbc5.jar has been released, dealing with this issue.  My results show we’re not using the patched version:

C:\path\to\jboss-4.2.2.GA\server\default\lib>java -jar ojdbc5-
Oracle JDBC 3.0 compiled with JDK5

(If we had the patch, the output would be

Oracle JDBC 3.0 compiled with JDK5


The drivers are available from Oracle.  Perhaps soon we’ll upgrade to them…

Oracle Treajure hunt

I wanted to find the Javadoc for Oracle’s JDBC datasource classes, to see what XA interfaces they implement (…to form a more focused question to ask on the Bitronix Transaction Manager forum).  I’d looked for the Javadoc before there and I thought I’d found some, though it was hard to find.  I decided to search again and this time, bookmark it.

(Usually one would just post the solution, but I’m cataloging what my process was too.  Maybe writing down what I did will allow us to analyze my process and improve on it next time.)


After some, Google, and searches didn’t seem to turn up anything (well, I did find something on someone else’s personal site, but I wanted my bookmark to point to Oracle) I decided to see if I could find what I was looking for by browsing Oracle’s site.  From the main page there were Products and Services, Industries, Support, Partners, Communities, and About across the top; Database, Middleware, Applications, and Industries in the middle, and Featured Downloads, Events and News, and Resources down the right hand side.

I picked Support, but didn’t see Java stuff there, so I went back and picked Communities.  The Oracle Technology Network looked promising (it turns out I could have gotten to this directly from the main page using a link under Resources there), and from there I went to Java under Technologies on the left hand sidebar, and then to JDBC under Focus Areas.

At this point, though, I couldn’t see a Javadoc link.  Dead end.

XA Marks the Spot

I paused and thought about how I would know what I was looking for.  I remembered that Javadoc tends to have a standard look to it, with standard links.  I had the idea to go to some other Javadoc I had up and use the link text from those standard links to use as part of my search:

OracleXADataSource Overview Package Class Use Tree Deprecated Index Help

This time, came through for me, finding the Oracle JDBC Javadoc!