473,236 Members | 1,784 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,236 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 2464
"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: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
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
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
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...

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.