473,324 Members | 2,400 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,324 software developers and data experts.

INCLUDE in index

Have a table T1 with a uniqe index defined on columns COL1, COL2 with
INCLUDE of COL3. So, what will the access path be for the following
query:

SELECT COL1, COL2 FROM T1 WHERE COL3 = 'ABC'

Will it be an 'index only' scan or will DB2 have to go to the
datapages to fetch records.

TIA
Raquel.
Nov 12 '05 #1
8 4933
AK
Raquel,

DB2 optimizer will be considering scanning the whole index on (COL1,
COL2, COL3)
among other alternatives. Other alternatives could be better, for
example,
If there is an index on COL3 only, and the condition COL3='ABC' is
very selective, then it might make more sence use it

it always depends
Nov 12 '05 #2
It also depends on whether you have done RUNSTATS or not.
Without any stats DB2 will probably do a table scan.

Raquel wrote:
Have a table T1 with a uniqe index defined on columns COL1, COL2 with
INCLUDE of COL3. So, what will the access path be for the following
query:

SELECT COL1, COL2 FROM T1 WHERE COL3 = 'ABC'

Will it be an 'index only' scan or will DB2 have to go to the
datapages to fetch records.

TIA
Raquel.


--
Anton Versteeg
IBM Certified DB2 Specialist
IBM Netherlands
Nov 12 '05 #3
AK
Serge,
I would say that depends on how many records meet the criteria WHERE
COL3 = 'ABC', doesn't it?
If, for example, there is a UNIQUE conshtraint on COL3, then the index
on COL3 should be used.
Nov 12 '05 #4
Keep in mind that Col3 is the last column only.
c1 and c2 ar ealready unique. This means a full index scan would happen.
The race is between full index scan and table scan.
It depends on how many other columns are in the table.
If there are so many more rows per page on the index that it makes up
for hopping round on disk then yes.

Cheers
Serge

PS: I'm just talking gut-feeling here. I have no clue what teh algorthm
of teh Optimizer will do.

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

Nov 12 '05 #5
I would opt for a table-scan here.
I doubt there is a strong incentive for the index.

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

Nov 12 '05 #6
AK
Serge,

if there is any index with first column COL3, there is a third player:
access the table via that another index. This third player is
especially atractive if it is a unique index on COOL3 only, which
means (depth of index on COL3) index pages and just 1 data page will
be read.

Here is a real life story:

http://searchdatabase.techtarget.com...939445,00.html
Nov 12 '05 #7
We agree violently. Stated that col3 may have a unique constarint.
A unique constarint is implemented via an index on col3.
Which woul dbring us back to my first comment.
If theer were an index on col3 (as the lead column)....
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

Nov 12 '05 #8
AK
I would agree peacefully.

Peace
Nov 12 '05 #9

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

11
by: Yannick Turgeon | last post by:
Oups! I did a typing error in my last post. Fixed. ----------- Hello all, We are currently changing our web server and, in the process, updating PHP version from 4.3.0 to 4.3.5. The problem...
5
by: mdawg414 | last post by:
Hey guys, please bear with me cause I am a major newbie at php. I am building a website and have it so that the content in the middle of the page changes but the banner on top and the panels on the...
2
by: Robizzle | last post by:
I'm working on a very simple script that logs the ip address, time of hit, and os/browser information and currently it works every time. The problem is getting the script included into my html...
11
by: MBS | last post by:
I am playing around with some PHP code. I want to put in an include() function so I can include existing HTML code and output it to the browser. Lo and behold PHP does not support relative paths...
11
by: McKirahan | last post by:
I am looking for feedback on an approach to using PHP. Below is a stripped down version of a Home page: "index.php". The content of the site is displayed in the middle of the page and is...
3
by: =?Utf-8?B?QnJhbmRvbg==?= | last post by:
Hi, I have an aspx page that has the "include" code in it which includes another page that displays information. I also have an upload page that allows users to upload a simple html document...
1
by: Proximus | last post by:
hello, at first, sorry on my bad english. i have a problem. on index.php: <? $include=array( "link"=>"link.html",
13
by: Heikki | last post by:
first. sory about bad english. my index.php has link-menu in a right side, that opens something.php next to the menu. something.php has a link link-menu at the top of the page that should...
3
by: runway27 | last post by:
i am doing seo for a website and this website uses a lot of php for which i need suggestions. this is how the website is set up. in the index.php file there is a flash banner at the top of the...
25
by: Mark | last post by:
so, i'm making a website. let's say i have header.php, footer.php and content.php. now in index.php I simply want to include the 3 pages. easy enough to do. but let's say the user navigates to...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
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...
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: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
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: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
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...

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.