|
View previous topic :: View next topic
|
| Author |
Message |
callkris_cit
New User
Joined: 22 Apr 2006
Posts: 44
|
|
|
|
I have written the SELECT query, based on STR_ID range.
When i running the query, with some range, the query using index.
| Code: |
SELECT ROW_EFF_DT
FROM TABLE2
WHERE STR_ID <= 1000 AND STR_ID >= 10000 |
But if i change the range its not using the index.
| Code: |
SELECT ROW_EFF_DT
FROM TABLE2
WHERE STR_ID <= 1000 AND STR_ID >= 10000 |
What could be the reason for this |
|
| Back to top |
|
 |
Anuj Dhawan
Superior Member
Joined: 22 Apr 2006
Posts: 6250
Location: Mumbai, India
|
|
|
|
Hi,
Both the queries look same to me, did I miss something here or.. |
|
| Back to top |
|
|
Anuj Dhawan
Superior Member
Joined: 22 Apr 2006
Posts: 6250
Location: Mumbai, India
|
|
|
|
Hi,
Please notice your post is been edited to add the code tags, learn to use BBcode - they make the post rather readable.
-Ad |
|
| Back to top |
|
|
Robert Sample
Global Moderator
Joined: 06 Jun 2008
Posts: 8696
Location: Dubuque, Iowa, USA
|
|
|
|
| Not only do they look the same to me, but also how many records are going to have STR_ID values <= 1000 and >= 10000 at the same time? |
|
| Back to top |
|
|
callkris_cit
New User
Joined: 22 Apr 2006
Posts: 44
|
|
|
|
I have written the SELECT query, based on STR_ID range.
When i am doing EXPLAIN, with some range, the query using index, when
Code:
SELECT ROW_EFF_DT
FROM TABLE2
WHERE STR_ID <= 1000 AND STR_ID >= 10000
( But there is no record have this range )
But if i change the range its not using the index.
Code:
SELECT ROW_EFF_DT
FROM TABLE2
WHERE STR_ID <= 20000 AND STR_ID >= 10001
( Lot of records present in this range )
What could be the reason for this |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hello,
Is there some reason you do not use the "Code" tag as requested?
| Quote: |
WHERE STR_ID <= 1000 AND STR_ID >= 10000
( But there is no record have this range ) |
Hopefully, this is not a surprise - there can never be a row that satisfies the condition 
| Quote: |
| WHERE STR_ID <= 20000 AND STR_ID >= 10001 |
What happens when you reverse these? |
|
| Back to top |
|
|
Anuj Dhawan
Superior Member
Joined: 22 Apr 2006
Posts: 6250
Location: Mumbai, India
|
|
|
|
Hi,
First query will show the ROW_EFF_DT for
| Code: |
| 1000 <=STR_ID <=10000 |
& second query will show the ROW_EFF_DT for
| Code: |
| 10001 <=STR_ID <=20000 |
, both the queries are "effective" on different range of STR_ID - of course there are very chances that they will show different results. |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hi Anuj,
| Quote: |
| First query will show the ROW_EFF_DT for |
I think not. . .
What value can satisfy this "WHERE STR_ID <= 1000 AND STR_ID >= 10000 "?
I can think of no number that is both less than 1 thousand and also greater than 10 thousand
My second point had nothing to do with the value(s) returned but rather how db2 would handle/process the query. |
|
| Back to top |
|
|
Anuj Dhawan
Superior Member
Joined: 22 Apr 2006
Posts: 6250
Location: Mumbai, India
|
|
|
|
oops ...please read "AND" in the query as "OR"..my post would appear meanigful.. 
tough crowd..ya.. 
-Ad |
|
| Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
| not near as tough as a pre-compiler, compiler, or OP system. |
|
| Back to top |
|
|
callkris_cit
New User
Joined: 22 Apr 2006
Posts: 44
|
|
|
|
WHERE STR_ID >= 10001 AND STR_ID <= 20000
also not using the Index.
WHERE STR_ID >= 1 AND STR_ID <= 10000
is using the index |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hello,
It probably has to do with the optimizer and population counting.
Suggest you work with your dba to learn how/why the query is being handled in the different situations. |
|
| Back to top |
|
|
callkris_cit
New User
Joined: 22 Apr 2006
Posts: 44
|
|
|
|
My DBA said, it may be Partition problem..
But i don't think so. Is there any possibility? |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hello,
| Quote: |
| My DBA said, it may be Partition problem.. |
This is not a term i'm familiar with - what does it mean in your environment? |
|
| Back to top |
|
|
Anuj Dhawan
Superior Member
Joined: 22 Apr 2006
Posts: 6250
Location: Mumbai, India
|
|
|
|
Hi Dick,
As tables grow in size, it may be easier to manage your data in chunks or by limited ranges. Those ranges are table-partitions.
In such a scenario, applications can continue to access data by specifying column and table names, and do not need to worry about which data partition(s) the data resides in. |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hi Anuj,
Yup, i'm quite with partitioned tables.
What i'm not familiar with a/the "partition problem" |
|
| Back to top |
|
|
|
|
