469,315 Members | 1,486 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,315 developers. It's quick & easy.

QUESTION: Select <PK> from <TABLE> - index scan or table scan??

BD
Hi, all.

My background is more Oracle than db2. My skills at SQL tuning are
quite limited.

I'm running 8.2 on Windows.

I'm tasked with some SQL optimization, and am doing some explain plans
on various queries.

The cost of one particular part of a query struck me as a bit high, so
I did some digging.

I now have 2 tables, each of which has a single-column (non-composite)
clustered PK index on it.

For one of the tables, SELECT <PKFROM <TABLEresults in an index
scan.

For another, SELECT <PKFROM <TABLEresults in a full table scan.

I'm stumped as to why the optimizer would call for a full table scan
when I'm selecting only the value that is in the PK, and therefore the
PK index. Why would it not call for an index scan of the primary key
index?

I just ran stats, and did a reorg of the tables.

Can someone suggest why this might be happening?

Cheers and thanks,

BD
Feb 13 '08 #1
2 2294
"BD" <ro*********@gmail.comwrote in message
news:2a**********************************@h11g2000 prf.googlegroups.com...
Hi, all.

My background is more Oracle than db2. My skills at SQL tuning are
quite limited.

I'm running 8.2 on Windows.

I'm tasked with some SQL optimization, and am doing some explain plans
on various queries.

The cost of one particular part of a query struck me as a bit high, so
I did some digging.

I now have 2 tables, each of which has a single-column (non-composite)
clustered PK index on it.

For one of the tables, SELECT <PKFROM <TABLEresults in an index
scan.

For another, SELECT <PKFROM <TABLEresults in a full table scan.

I'm stumped as to why the optimizer would call for a full table scan
when I'm selecting only the value that is in the PK, and therefore the
PK index. Why would it not call for an index scan of the primary key
index?

I just ran stats, and did a reorg of the tables.

Can someone suggest why this might be happening?

Cheers and thanks,

BD
If the number of rows in the table is small enough, it actually might be
faster to scan the entire table for that one column than to scan the index.
Feb 14 '08 #2
BD
On Feb 13, 4:00*pm, "Mark A" <nob...@nowhere.comwrote:
"BD" <robert.d...@gmail.comwrote in message

news:2a**********************************@h11g2000 prf.googlegroups.com...


Hi, all.
My background is more Oracle than db2. My skills at SQL tuning are
quite limited.
I'm running 8.2 on Windows.
I'm tasked with some SQL optimization, and am doing some explain plans
on various queries.
The cost of one particular part of a query struck me as a bit high, so
I did some digging.
I now have 2 tables, each of which has a single-column (non-composite)
clustered PK index on it.
For one of the tables, SELECT <PKFROM <TABLEresults in an index
scan.
For another, SELECT <PKFROM <TABLEresults in a full table scan.
I'm stumped as to why the optimizer would call for a full table scan
when I'm selecting only the value that is in the PK, and therefore the
PK index. Why would it not call for an index scan of the primary key
index?
I just ran stats, and did a reorg of the tables.
Can someone suggest why this might be happening?
Cheers and thanks,
BD

If the number of rows in the table is small enough, it actually might be
faster to scan the entire table for that one column than to scan the index..- Hide quoted text -

- Show quoted text -
Yeah, a co-worker suggested that to me just now. I'll test that by
stripping all but a few rows out of a table that results in an index
scan on a PK select, and see if the behavior changes.

Thanks!
Feb 14 '08 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by Viktor Popov | last post: by
1 post views Thread by pitjpz | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by harlem98 | last post: by
reply views Thread by harlem98 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.