Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
Index problem in SQL Query

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
callkris_cit

New User


Joined: 22 Apr 2006
Posts: 44

PostPosted: Wed Oct 29, 2008 10:40 pm    Post subject: Index problem in SQL Query
Reply with quote

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
View user's profile Send private message

Anuj Dhawan

Senior Member


Joined: 22 Apr 2006
Posts: 6258
Location: Mumbai, India

PostPosted: Thu Oct 30, 2008 12:06 am    Post subject:
Reply with quote

Hi,

Both the queries look same to me, did I miss something here or..
Back to top
View user's profile Send private message
Anuj Dhawan

Senior Member


Joined: 22 Apr 2006
Posts: 6258
Location: Mumbai, India

PostPosted: Thu Oct 30, 2008 12:07 am    Post subject:
Reply with quote

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
View user's profile Send private message
Robert Sample

Global Moderator


Joined: 06 Jun 2008
Posts: 8165
Location: East Dubuque, Illinois, USA

PostPosted: Thu Oct 30, 2008 12:08 am    Post subject:
Reply with quote

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
View user's profile Send private message
callkris_cit

New User


Joined: 22 Apr 2006
Posts: 44

PostPosted: Thu Oct 30, 2008 11:16 pm    Post subject:
Reply with quote

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
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Fri Oct 31, 2008 12:13 am    Post subject:
Reply with quote

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 icon_confused.gif

Quote:
WHERE STR_ID <= 20000 AND STR_ID >= 10001
What happens when you reverse these?
Back to top
View user's profile Send private message
Anuj Dhawan

Senior Member


Joined: 22 Apr 2006
Posts: 6258
Location: Mumbai, India

PostPosted: Fri Oct 31, 2008 7:19 am    Post subject:
Reply with quote

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
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Fri Oct 31, 2008 7:36 am    Post subject:
Reply with quote

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 icon_confused.gif

My second point had nothing to do with the value(s) returned but rather how db2 would handle/process the query.
Back to top
View user's profile Send private message
Anuj Dhawan

Senior Member


Joined: 22 Apr 2006
Posts: 6258
Location: Mumbai, India

PostPosted: Fri Oct 31, 2008 8:06 am    Post subject:
Reply with quote

oops ...please read "AND" in the query as "OR"..my post would appear meanigful.. icon_redface.gif

tough crowd..ya.. icon_smile.gif

-Ad
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6968
Location: porcelain throne

PostPosted: Fri Oct 31, 2008 2:45 pm    Post subject:
Reply with quote

not near as tough as a pre-compiler, compiler, or OP system.
Back to top
View user's profile Send private message
callkris_cit

New User


Joined: 22 Apr 2006
Posts: 44

PostPosted: Fri Oct 31, 2008 11:29 pm    Post subject: Reply to: Index problem in SQL Query
Reply with quote

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
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Sat Nov 01, 2008 12:49 am    Post subject:
Reply with quote

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
View user's profile Send private message
callkris_cit

New User


Joined: 22 Apr 2006
Posts: 44

PostPosted: Sat Nov 01, 2008 1:07 am    Post subject: Reply to: Index problem in SQL Query
Reply with quote

My DBA said, it may be Partition problem..

But i don't think so. Is there any possibility?
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Sat Nov 01, 2008 1:42 am    Post subject:
Reply with quote

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
View user's profile Send private message
Anuj Dhawan

Senior Member


Joined: 22 Apr 2006
Posts: 6258
Location: Mumbai, India

PostPosted: Sat Nov 01, 2008 3:40 am    Post subject:
Reply with quote

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
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Sat Nov 01, 2008 4:21 am    Post subject:
Reply with quote

Hi Anuj,

Yup, i'm quite with partitioned tables.

What i'm not familiar with a/the "partition problem" icon_confused.gif
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts HEX value search in a DB2 query maxsubrat DB2 2 Wed Oct 04, 2017 3:04 pm
No new posts LMINIT problem - not finding DDNAME Danielle.Filteau CLIST & REXX 7 Tue Sep 19, 2017 9:57 pm
No new posts Create procedure issues -628 when add... chandraBE DB2 1 Mon Sep 18, 2017 12:16 pm
No new posts Confusion b/w index and subscript Deepak kumar25 Mainframe Interview Questions 7 Thu Aug 31, 2017 6:50 am
No new posts Can we limit length in concatenation ... balaji81_k DB2 7 Tue Aug 22, 2017 2:50 am

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us