|
Question: Can Java objects be indexed?
Yes, within reason. Figure 1 shows the IndexDemo.java source code
for a simple Java class containing two fields: string1 and integer1.
Figure 1: IndexDemo.java
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
INSTALL JAVA NEW FROM FILE
'c:\\amisc\\ASAJava\\IndexDemo.class';
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)
|
|
