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

DB2 UDB 9.5 FP2a is not using the best index

Hi,

We are using DB2 UDB 9.5 FP2a.
And the following SQL is not using the right index.

SELECT a1.col1, a0.col2
FROM TABLE1 a0 INNER JOIN TABLE2 a1 ON a0.KEY1
= a1.KEY1
WHERE ((a0.number_01 = '00001985014082') AND (a0.number_02 = '001'))

There are indexes on number_01 and number_02 columns.

We have done RUNSTATS on the tables.
The TABLE1 has - 38 million records
number_01 has - 35 million distinct values
number_02 has - 11 million distinct values

Still it is using index on "number_02"

Any help will be appreciated.

Thanks
Arabinda
Mar 13 '10 #1
1 2239
Could be that distribution stats are changing the optimizer's estimate.
Apr 27 '10 #2

Sign in to post your reply or Sign up for a free account.

Similar topics

2
by: Phillip | last post by:
Select member from NameList Inner join Members on (Left(Namelist.NameID,5) = Members.ID OR (left(namelist.SSN,9) = Members.ssn OR (Left(namelist.CustID,9) + '*01' = Members.CustID) where...
2
by: sridharg.rao | last post by:
Hi, I have a table t1 with columns a,b,c,d,e,f,g,h,i,j,k,l I have created a clustered index on a,b,d,e which forms the primary key. I have created a covering index on all the columns of t1....
1
by: Adam Teasdale Hartshorne | last post by:
Using the code below I get a very stange result. I assume that I must be doing something wrong, but I can't figure out what it is. getTriangleEdges(t,t1e) ; for (int i = 0 ; i <...
9
by: Guy | last post by:
I have extended the datetimepicker control to incorporate a ReadOnly property. I have used the new keyword to implement my own version of the value property, so that if readonly == true then it...
15
by: Nathan | last post by:
I have an aspx page with a data grid, some textboxes, and an update button. This page also has one html input element with type=file (not inside the data grid and runat=server). The update...
5
by: Mr Newbie | last post by:
OK, I have a fairly simple setup where I have an Index of records which are displayed using a datagrid on a page. When you click the link associated with a Row (ArticleID) , this ID is passed to a...
3
by: Andreas | last post by:
Hi! I'm currently developing a DLL that makes use of C++ and .net (mixed) using Visual Studio 2003. Now, as I wanted to move to the new Visual Studio 2005, I converted this project into the...
21
by: John Salerno | last post by:
If I want to make a list of four items, e.g. L = , and then figure out if a certain element precedes another element, what would be the best way to do that? Looking at the built-in list...
10
by: Trammel | last post by:
Hi, Im just about to start developing a new website for personal / friend use that may be accessed by other people all over the web. I was sitting thinking through designs for the pages, etc when...
4
by: crazy_jutt | last post by:
hi all, i heard that db2 ignores indexes when using any column function on the column which has index on it. but i have seen db2 using indexes even when using column function. what is the...
1
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: 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
by: ryjfgjl | last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...

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.