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. 8 4933
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
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
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.
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
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
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
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
I would agree peacefully.
Peace This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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",
|
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...
|
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...
|
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...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
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: 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...
|
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...
|
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)...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
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....
|
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
|
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...
| |