Hi there,

In DB2, the index name is used to create and drop the index. Other than that, the nme is not used in queries or updates to the table. So, the question is how an index is used in a query?

Thaks in advance,

Steven

In a relational database, the physical access path is isolated from the user. DB2 choses the best access path (the fastest) by looking at the statistics (number of rows, number of unique values, etc), the indexes available, and chooses the best access path. For best results, make sure you run the runstats command on each table (and get the most detailed stats available). See the Command Reference manual.

It's still depended on human being to run the runstats command to build the statistics, how can you say "In a relational database, the physical access path is isolated from the user"? Once I have took a look to those statistics and it looked so ridiculous that it showed there was no data in the table, in fact it contained several millions. I think if DB2 offers a way for developer to specify how a query uses an index, like Sybase, it would save much more trouble than it chooses itself.

Steven

Then Sybase is not relational, to extent that the user must supply anything but the desired results. This may partly explain its rapidly declining market share.

It takes a lot more human intervention to figure out which is the optimal index to use than it does to set up a scheduled Runstats. Even if Runstats is never run (stats are -1) then DB2 uses default values that would assume that there are rows in the table.

If the table is small enough, usually less than 10 pages, it is normally faster to do a tablespace scan even if indexes exist. If you always want to encourage the index to be used anyway, alter the table to "volatile."

We build the indexes, certainly we know better which index should be used than the system.

Maybe you know better than Sybase, but you do NOT know better than DB2.

Really? I don't think so!

I don't know anything about sybase, but with oracle, one can supply any info to optimizer to direct its way to execute a sql. Without additional information, oracle will use its default method to determine a best path. Oracle is stupid some time, but who think Oracle is not clever in general?