Example for situation when corresponding index does not exist

When a table does not have an index on a column that can serve as the fragment of the resultant index, the database server estimates the cost of building the index fragment for the column, 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 a fragmented table and index with the following SQL statements:

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);

Suppose you then create two more tables that are not fragmented, and you subsequently decide to attach them to the fragmented table.

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(b) IN db4; 

ALTER FRAGMENT ON TABLE tb1 
   ATTACH tb2 AS (a >= 10 and a<15) tb3 AS (a >= 15 and a<20);

The three CREATE INDEX statements automatically calculate distribution statistics for the leading column of each index in HIGH mode, as well as index statistics and table statistics in LOW mode.

The only time the UPDATE STATISTICS LOW FOR TABLE statement is required is after a CREATE INDEX statement in a situation in which the table has other preexisting indexes, as shown in this example:

CREATE TABLE tb1(col1 int, col2 int); 
CREATE INDEX index idx1 on tb1(col1); 
  (equivalent to update stats low on table tb1) 
LOAD from tb1.unl insert into tb1; (load some data) 
CREATE INDEX idx2 on tb1(col2);

The statement CREATE INDEX idx2 on tb1(col2) is NOT completely equivalent to UPDATE STATISTICS LOW FOR TABLE tb1, because the CREATE INDEX statement does not update index- level statistics for the preexisting index called idx1.

In the preceding example, table tb3 does not have an index on column a that can serve as the fragment of the resultant index idx1. 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 for all fragments on the resultant table. The database server chooses the index build with the least cost.