By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
444,078 Members | 1,846 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 444,078 IT Pros & Developers. It's quick & easy.

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

P: n/a
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
Share this Question
Share on Google+
2 Replies


P: n/a
"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

P: n/a
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.