Foxhound is the better* Database Monitor for SQL Anywhere.
*better: More thorough, more relevant, more effective.
...more Alerts, more All Clears, more details, more control in your hands.

[Home] [Back to Tip 82] [Forward to Tip 84] [Archives]
Breck Carter
Last modified: November 30, 1998
mail to:

Tip 83: Java In The Database (3)
Object Indexes

Question: Can Java objects be indexed?

Yes, within reason. Figure 1 shows the source code for a simple Java class containing two fields: string1 and integer1.

Figure 1:
public class IndexDemo {
  public String string1 ;
  public java.lang.Integer integer1 ;

  IndexDemo () {                  // Constructor 1
    string1  = " ";
    integer1 = new java.lang.Integer ( 0 );

  IndexDemo ( String inString1,   // Constructor 2
              int inInteger1
             ) {
    string1  = inString1;
    integer1 = new java.lang.Integer ( inInteger1 );

  public String toString() {
    return string1 + ": " + integer1.toString();

  public int compareTo( IndexDemo anotherIndexDemo ) {

    // Compare first on the basis of counter
    // and then on the basis of toString()

    java.lang.Integer lVal = integer1;
    java.lang.Integer rVal = anotherIndexDemo.integer1;

    if ( lVal.intValue() > rVal.intValue() ) {
      return 1;
    else if (lVal.intValue() < rVal.intValue() ) {
      return -1;
    else {
      return toString().compareTo( anotherIndexDemo.toString() );

Figure 2 shows a table java_index_demo with a primary key plus three other columns that each have an index defined:

  • The JIndexDemo column is a Java object, and the index ix_JIndexDemo relies on the toString and compareTo methods to support index operations on a multi-field Java object as a whole.

  • The compute_integer1 column is a SQL INTEGER which uses the new COMPUTE column constraint to define it as a read-only computed column based on a reference to the integer1 field within JIndexDemo. This computed column is used to create the ix_compute_integer1 index.

  • The ordinary_integer column is an ordinary SQL INTEGER used to create the ix_ordinary_integer index.

Figure 2: Create Object Indexes

CREATE TABLE java_index_demo
   ( pkey              INTEGER NOT NULL,
     JIndexDemo        IndexDemo NOT NULL,
     compute_integer1  INTEGER COMPUTE ( JIndexDemo >> integer1 ) 
                       NOT NULL,
     ordinary_integer  INTEGER NOT NULL,
     PRIMARY KEY ( pkey ) );

CREATE INDEX ix_JIndexDemo       
          ON java_index_demo ( JIndexDemo );
CREATE INDEX ix_compute_integer1       
          ON java_index_demo ( compute_integer1 );
CREATE INDEX ix_ordinary_integer       
          ON java_index_demo ( ordinary_integer );

Computed fields are necessary because indexes must be defined on columns, not expressions. In other words, it's OK to create an index on JIndexDemo or compute_integer1 but not on JIndexDemo >> integer1; the following statement is invalid:

   CREATE INDEX ix_integer1       
             ON java_index_demo ( JIndexDemo >> integer1 );

Figure 4 shows four SELECTs with the resulting PLANs which show how the various indexes are used.

  • The first SELECT uses one of the IndexDemo constructors to specify an object instance to compare with JIndexDemo, and the PLAN proves that ix_JIndexDemo is used.

  • The second SELECT specifies compute_integer1 in the WHERE clause, and not surprisingly the PLAN shows that ix_compute_integer1 is used.

  • The third SELECT refers to JIndexDemo.integer1 in the WHERE clause, and this time the PLAN is surprising: the same index ix_compute_integer1 is used, showing that the optimizer has done its job well.

  • The fourth SELECT simply shows that ix_ordinary_integer is used for a query involving the ordinary_integer column.

Figure 3: Using Object Indexes
select * 
  from java_index_demo 
 where JIndexDemo = NEW IndexDemo ( 'A333', 333 );
 // PLAN> java_index_demo (ix_JIndexDemo)

select * 
  from java_index_demo 
 where compute_integer1 = 333;
 // PLAN> java_index_demo (ix_compute_integer1)

select * 
  from java_index_demo 
 where JIndexDemo.integer1 = 333;
 // PLAN> java_index_demo (ix_compute_integer1)

select * 
  from java_index_demo 
 where ordinary_integer = 333;
 // PLAN> java_index_demo (ix_ordinary_integer)

[Home] [Back to Tip 82] [Forward to Tip 84] [Archives] [mail to:]