473,323 Members | 1,547 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,323 software developers and data experts.

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 2467
"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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

10
by: BuddhaBuddy | last post by:
Platform is DB2/NT 7.2.9 The table was created like this: CREATE TABLE MYTEST ( MYTESTOID bigint not null primary key, FK_OTHEROID bigint not null references other, FK_ANOTHEROID bigint not...
3
by: Viktor Popov | last post by:
Hi, I would like to ask you do you know how to return a resultset and int value from Stored Procedure. If we have a table Teachers ========= ID INT PK NAME VARCHAR(25) ADDR VARCHAR(75)
10
by: Dia | last post by:
At the company I work job applicants are required to do a little test. The human resource manager recently had a candidate who claimed one of the questions was ambiguous. Dependent upon the...
26
by: Jimmy | last post by:
ill have a database with 1 table and 3 fields: ID FIRSTNAME LASTNAME (the ID field will be the auto incrementing index) there might be 10 records in the DB, there might be 10,000. i...
4
by: Tomas | last post by:
I'm creating MS Access database and I need to have in query an average of 3 last records. How to do it? Maybe here is some function or sql expresion? Thanks
4
by: sialater | last post by:
Hello, I realise there are a lot of topics related to this problem but many of what I have found has run cold or unresolved. What I have is an addressbook clone where there are groups which have...
1
by: tensi4u | last post by:
Hi all, I've tried to get primary and unique constraint information via syscat.tabconst and syscat.keycoluse tables as one value for each constraint. For example, let's there is table called...
1
by: pitjpz | last post by:
We have moved our Database to another server. The server it was on used SQL 4 and the new one its on now uses SQL5 the only problem we can find is that when you attempt to delete a record from...
6
by: CatchSandeepVaid | last post by:
Consider this scenatio : I have one-to-one relation between Product and ProductBasic BUT at database level i have one-to-many between PRODUCT and PRODUCTBASIC tables as history is mainted in the...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.