How to force full table scan

Desmodromic

unread,

Aug 19, 2011, 8:10:45 AM8/19/11

to

How can I force an SQL query to perform a full table scan rather than
use an index? I also need to obtain the number of rows in the table,
so all I really want to do is:

select count(1)
from table;

but by scanning the table and not an index.

I have tried a few things so far such as including "where col1 = col1
+ 0" and "select count(1) from (select * from table)".

However, the DB2 9.5 FP7 optimizer seems to be too smart for me and
always uses an index.

Any idea how I can force a full table scan and get the row count?

Rhino

unread,

Aug 20, 2011, 8:01:24 PM8/20/11

to

"Desmodromic" <davi...@yahoo.com.au> wrote in message
news:9ddaea7c-f327-4497...@t30g2000prm.googlegroups.com...

Are you concerned that the actual number of rows in the table differs from
the number of entries in the (presumably unique) index? Otherwise, I can't
imagine why you'd want to do a table scan rather than an index scan. After
all, most people want their answer faster, not slower and the index scan is
going to be faster in all but a few trivial cases....

Still, if you really must have a table scan you could delete the index; then
you can't possibly do an index scan since DB2 can't use an index that
doesn't exist.

--
Rhino

Frederik Engelen

unread,

Aug 21, 2011, 3:18:51 PM8/21/11

to

On 21 aug, 02:01, "Rhino" <no_offline_contact_ple...@example.com>
wrote:
> "Desmodromic" <davies...@yahoo.com.au> wrote in message

Count through an index is not always 100% accurate if updates are
occurring on the index keys (looks like a delete/insert for the
index). that might be a reason.

Are optimization profiles an option? That should help:

--
Frederik Engelen

Rhino

unread,

Aug 21, 2011, 4:26:38 PM8/21/11

to

"Frederik Engelen" <engelen...@gmail.com> wrote in message
news:8ea99a98-f1bc-4150...@t29g2000vby.googlegroups.com...

Would a count(*) query be any more accurate though? It seems to me that if
an accurate count is essential, updates (meaning updates, deletes AND
inserts) be stopped for the duration of the counting. At that point, I would
like to think that using or not using the index would be irrelevant to
getting the desired result.

I wonder if Desmodromic is indeed worried about differences between the
count given by a unique index and the table itself? I dimly remember a case
I saw roughly 20 years ago where an index had four fewer (or more?) entries
than there were rows in the table. It wasn't my database so I don't know
much about what had happened or how it was resolved; I only remember that it
happened.

Desmodromic

unread,

Aug 21, 2011, 9:17:44 PM8/21/11

to

On Aug 22, 4:26 am, "Rhino" <no_offline_contact_ple...@example.com>
wrote:
> "Frederik Engelen" <engelenfrede...@gmail.com> wrote in message

Thanks for your responses. Firstly, I found a way to force a full
table scan by adding a filter on a non-indexed INTEGER NOT NULL column
such as WHERE COL1 >= 0. Since there are no NULL or negative values in
COL1 this will give me the row count of the table.

The reason why I want to force a full table scan and also get the row
count is for benchmarking and so I can calculate the number of rows
scanned per second. I have a number of tables with the same structure,
similar volumes of data but different compression methods. What I have
discovered is that for those tables compressed using REORG the average
rows scanned per second is 400,000. For those tables not compressed it
is 109,000 rows scanned per second. However, for those tables
compressed using TABLE GROWTH the scan rate is a pitiful 2,400 rows
per second.

Now I have another question - how can this terrible scan performance
for tables compressed by TABLE GROWTH be explained?

Frederik Engelen

unread,

Aug 22, 2011, 6:44:05 AM8/22/11

to

> for tables compressed by TABLE GROWTH be explained?- Hide quoted text -
>
> - Show quoted text -

What do you mean by TABLE GROWTH? Automatic Dictionary Creation?

--
Frederik Engelen

Yonghang Wang

unread,

Aug 22, 2011, 9:58:42 AM8/22/11

to

before goes into the complex of optimization profile, try,

select * from ur_table where some_predication_always_false_but_not_just_1=0

the predication includes no column in a index.

Desmodromic

unread,

Aug 22, 2011, 10:03:43 PM8/22/11

to

On Aug 22, 6:44 pm, Frederik Engelen <engelenfrede...@gmail.com>
wrote:

Yes, I mean Automatic Dictionary Creation. The table is created with
compression on and then data is inserted over time. I got "TABLE
GROWTH" from the DICT_BUILDER column of the
SYSIBMADM.ADMINTABCOMPRESSINFO view.

Manoj Sutar

unread,

Aug 29, 2011, 3:14:00 PM8/29/11

to

If you are on Unix/Linux,

db2 "select * from tablename" | tail
It will read all data from table forcing a tablescan and show last 10
lines of result.

Cheers...
Manoj