Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups 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: 7905
Location: Bellevue, IA

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: 6966
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
This topic is locked: you cannot edit posts or make replies. Db2 SQL query to convert rows into co... subratarec DB2 5 Thu Nov 17, 2016 4:51 pm
No new posts Problem in writing Output file vickey_dw COBOL Programming 5 Mon Nov 14, 2016 11:14 pm
No new posts Two Selects in a single select query Rohit Umarjikar DB2 1 Fri Nov 04, 2016 8:46 pm
No new posts Problem in Running Query via JCL vickey_dw DB2 3 Tue Oct 18, 2016 11:11 pm
No new posts DB2 Streaming Batch Processing Problem Manshadi DB2 4 Sat Sep 24, 2016 12:14 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us