how to do index scan

[xixi]

hi, we are using db2 udb v8.1 on windows, if i have a query use where
clause where key > value, the explain sql gives me access path is
table scan, but if i do key = value, it uses index scan, so how can i
force db2 to use index scan when key > value to makes the query run
faster?

[Ian]

xixi wrote:

hi, we are using db2 udb v8.1 on windows, if i have a query use where
clause where key > value, the explain sql gives me access path is
table scan, but if i do key = value, it uses index scan, so how can i
force db2 to use index scan when key > value to makes the query run
faster?

Unless your stats are wrong (or if you haven't collected distribution
stats), the DB2 Optimizer is usually pretty smart about what is better.

If your predicate key > value matches 90% of the table, it's not likely
that DB2 will perform an index scan AND then scan the table, unless you
are looking only for columns that exist in the index.

-----= Posted via Newsfeeds.Com, Uncensored Usenet News =-----
http://www.newsfeeds.com - The #1 Newsgroup Service in the World!
-----== Over 100,000 Newsgroups - 19 Different Servers! =-----

[Mark A]

"xixi" <da****@yahoo.com> wrote in message
news:c0**************************@posting.google.c om...

hi, we are using db2 udb v8.1 on windows, if i have a query use where
clause where key > value, the explain sql gives me access path is
table scan, but if i do key = value, it uses index scan, so how can i
force db2 to use index scan when key > value to makes the query run
faster?

Make sure you have run runstats with distribution on key columns and index
all.

DB2 will use an index "IF" it can avoid reading some 4K data pages (or what
ever the page size is) in the table. But if there is at least 1 qualifying
row on each data page, it usually will not be any fast to use an index
because DB2 does I/O one page at a time. If the row size is 100 bytes, there
are 40 rows on each data page.

If you cluster the table on the index in question, that will likely cause
the index to be used for a > predicate. Or you can just reorg on that index
and then do runstats.

[AK]

da****@yahoo.com (xixi) wrote in message news:<c0**************************@posting.google. com>...

hi, we are using db2 udb v8.1 on windows, if i have a query use where
clause where key > value, the explain sql gives me access path is
table scan, but if i do key = value, it uses index scan, so how can i
force db2 to use index scan when key > value to makes the query run
faster?

if you really wish to _force_, try OPTIMIZE FOR FIRST clause, or use SELECTIVITY
Use db2batch to see if the query runs faster. Please post the results

[Stefan Kraemer]

Xixi, once you´ve checked the other possibilities, and all is ok
including your index (your key is the first column within the index,
isn´t it?), you may set the table to volatile.
This forces the optimizer to use an index rather than doing a table scan.
Stefan

xixi schrieb:

hi, we are using db2 udb v8.1 on windows, if i have a query use where
clause where key > value, the explain sql gives me access path is
table scan, but if i do key = value, it uses index scan, so how can i
force db2 to use index scan when key > value to makes the query run
faster?