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

Mark 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

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