Example for situation when index on table is not usable

When the index on a table is not usable, the database server estimates the cost of building the index fragment, compares this cost to rebuilding the entire index for all fragments on the resultant table, and chooses the index build with the least cost.

Suppose you create tables and indexes as in the previous section, but the index on the third table specifies a dbspace that the first table also uses. The following SQL statements show this scenario: 
CREATE TABLE tb1(a int, b int) 
   FRAGMENT BY EXPRESSION
      (a >=0 AND a < 5) IN db1,
      (a >=5 AND a <10) IN db2;
CREATE INDEX idx1 ON tb1(a);
CREATE TABLE tb2 (a int, b int, check (a >=10 and a<15)) 
   IN db3; 
CREATE INDEX idx2 ON tb2(a) 
   IN db3;

CREATE TABLE tb3 (a int, b int, check (a >= 15 and a<20)) 
   IN db4;
CREATE INDEX idx3 ON tb3(a) 
   IN db2 ;

This example creates the index idx3 on table tb3 in the dbspace db2. As a result, index idx3 is not usable because index idx1 already has a fragment in the dbspace db2, and the fragmentation strategy does not allow more than one fragment to be specified in a given dbspace.

Again, the database server estimates the cost of building the index fragment for column a on the consumed table tb3 and compares this cost to rebuilding the entire index idx1 for all fragments on the resultant table. Then the database server chooses the index build with the least cost.