473,320 Members | 1,865 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,320 software developers and data experts.

Primary Key and indexes

I am running DB2 8.1 Fp12.

I have a primary key consisting of 3 columns on a table. Is it
necessary for me to create another index on top of this primary
key(unique or not is another question)? I see access plans using
primary keys, is it better to have an index rather than access through
primary keys?

Any help appreciated.
Thanks.

May 2 '07 #1
7 2630
Asphalt Blazer wrote:
I am running DB2 8.1 Fp12.

I have a primary key consisting of 3 columns on a table. Is it
necessary for me to create another index on top of this primary
key(unique or not is another question)? I see access plans using
primary keys, is it better to have an index rather than access through
primary keys?
When you define a primary key on DB2 for LUW the system will try to find
a suitable index that it can use to enforce the constraint.
If that index does not exist then DB2 will create a unique index to
support the PK.

One reason to create an index in advance it to exploit INCLUDE columns.
another is to be able to control the index name (which shows up in the
query plans)

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
May 2 '07 #2
Thank You, Serge.

I have a query that is returning 10 columns from a table. When I check
the access plan I see only 2 columns having selectivity on 0.5 and
0.97, none of the other columns have any selectivity defined. So when
I create an index should it include all these columns (10 !!??) or
should I just create one on the columns with any kind of selectivity?
How do I aprroach this ?




On May 2, 10:27 am, Serge Rielau <srie...@ca.ibm.comwrote:
Asphalt Blazer wrote:
I am running DB2 8.1 Fp12.
I have a primary key consisting of 3 columns on a table. Is it
necessary for me to create another index on top of this primary
key(unique or not is another question)? I see access plans using
primary keys, is it better to have an index rather than access through
primary keys?


>
When you define a primary key on DB2 for LUW the system will try to find
a suitable index that it can use to enforce the constraint.
If that index does not exist then DB2 will create a unique index to
support the PK.

One reason to create an index in advance it to exploit INCLUDE columns.
another is to be able to control the index name (which shows up in the
query plans)

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

May 2 '07 #3
Asphalt Blazer wrote:
Thank You, Serge.

I have a query that is returning 10 columns from a table. When I check
the access plan I see only 2 columns having selectivity on 0.5 and
0.97, none of the other columns have any selectivity defined. So when
I create an index should it include all these columns (10 !!??) or
should I just create one on the columns with any kind of selectivity?
How do I aprroach this ?
Handwaving.. need to see the query and teh table defintion

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
May 2 '07 #4
Asphalt Blazer wrote:
Thank You, Serge.

I have a query that is returning 10 columns from a table. When I check
the access plan I see only 2 columns having selectivity on 0.5 and
0.97, none of the other columns have any selectivity defined. So when
I create an index should it include all these columns (10 !!??) or
should I just create one on the columns with any kind of selectivity?
How do I aprroach this ?
You might be interested in what db2advis has to say:

db2advis -d <DB-m I -i <infile>

Dont take its word for granted, but it is a great help from time to time.
/Lennart
May 2 '07 #5
Ian
Asphalt Blazer wrote:
Thank You, Serge.

I have a query that is returning 10 columns from a table. When I check
the access plan I see only 2 columns having selectivity on 0.5 and
0.97, none of the other columns have any selectivity defined. So when
I create an index should it include all these columns (10 !!??) or
should I just create one on the columns with any kind of selectivity?
How do I aprroach this ?
I'm guessing you probably only have 2 columns in your where clause.

Selectivity does not apply to columns that are returned, only to
expressions in the where clause.

i.e.

select col1, col2, col3, col4, col5, col6, col7, col8, col9, col10
from table
where col6 = 8 and col9 = 'a'

will only display selectivities for the expressions "col6 = 8" and "col9
= 'a'"
May 2 '07 #6
Right, but then here should the index be on the columns in select
clause or on the where clause? I have an index on the where clause
columns but I see another fetch to the table to return the values.

I'm guessing you probably only have 2 columns in your where clause.

Selectivity does not apply to columns that are returned, only to
expressions in the where clause.

i.e.

select col1, col2, col3, col4, col5, col6, col7, col8, col9, col10
from table
where col6 = 8 and col9 = 'a'

will only display selectivities for the expressions "col6 = 8" and "col9
= 'a'"

May 2 '07 #7
Asphalt Blazer wrote:
Right, but then here should the index be on the columns in select
clause or on the where clause? I have an index on the where clause
columns but I see another fetch to the table to return the values.
Correct. That is normal behavior.
You can "fix it" by adding all the other columns as INCLUDE columns to
the unique index when you create it.
HOWEVER: I doubt this is what you want. Adding all those columns will
make your index very wide and you are likely to loose more than you gain.
A narrow index typically get away without any or perhaps one physical
I/O. Including the table fetch that gives 2 I/O. Plenty good.
If you add all the other columns you get less entries per leaf and hence
your index may get deeper requiring more I/O on the index in addition to
wasting space.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
May 2 '07 #8

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

Similar topics

12
by: Tuhin Kumar | last post by:
Hi, Oracle give the error ORA-01418 when I try to do the following; Create unique index t1_pk on TABLE1(EntryId DESC) ; If the I try to add primary key Contraint using the above index t1_pk...
4
by: Peter Scott | last post by:
I created a table that has a column in that needs to contain a full Unix file path. Since 2048 was too long for a VARCHAR, I made it TEXT. I since populated the table. Now I want to make the...
3
by: Rodney King | last post by:
Hi, I am supporting an application that was converted from ACCESS to SQL Server 2000. My question focuses on two particuliar tables. The parent table has 14000 rows while child table has over...
4
by: serge | last post by:
I ran into a table that is used a lot. Well less than 100,000 records. Maybe not a lot of records but i believe this table is used often. The table has 26 fields, 9 indexes but no Primary Key at...
7
by: Philip | last post by:
Hey all, (Access 2000) I've been having a horror story with this design problem. My Database is Structured like This: AUTHORS, BOOKS, PAGES. Those are the Tables and each Item in each table...
1
by: GGerard | last post by:
Hello I'm trying to find the best way to set indexes and primary keys on MSAccess tables What are the advantages and disadvantages of indexes and primary keys? What fields should be indexed?...
8
by: Paul Hunter | last post by:
I am new to databases and thus to Access. I have a situation where I am trying to figure out how to key some tables I am working with. Consider that I have a database of my own records which are...
8
by: shumaker | last post by:
I'm wondering if adding an autonumber primary key will improve the performance of a multiuser access database on a network share. I have a website that lists many tips for improving performance of...
115
by: LurfysMa | last post by:
Most of the reference books recommend autonum primary keys, but the Access help says that any unique keys will work. What are the tradeoffs? I have several tables that have unique fields. Can...
1
by: mark_aok | last post by:
Hi all, I have a situation where I need to determine a specific table's primary key, and then output it. I have tried the Database Object, and the Record Object, but I've had no luck. ...
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...
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: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
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)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
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.