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
 

 

Will the query do indexed search or sequential search

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

New User


Joined: 24 Jan 2007
Posts: 89
Location: USA

PostPosted: Sun Aug 26, 2007 7:03 pm    Post subject: Will the query do indexed search or sequential search
Reply with quote

Hi,

I am having below query with the index developed on fields
COL1,COL2,COL3,COL4 and COL5.

The value of COL1=COL2=COL3=SPACES.

Select * from Tab1
where COL1>=' '
AND COL2>=' '
AND COL3>=' '
AND COL4>='1001'
AND COL5>='1002 '.

will the above query do indexed search or sequential search,

regards,
Nagendran.R
Back to top
View user's profile Send private message

Pons

New User


Joined: 25 May 2007
Posts: 61
Location: Coimbatore

PostPosted: Mon Aug 27, 2007 11:01 pm    Post subject:
Reply with quote

I hope it will use only the index search. Because there is no negative condition present in the query(NOT condition).
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


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

PostPosted: Mon Aug 27, 2007 11:18 pm    Post subject:
Reply with quote

what Pons said is true, they are all Stage 1 predicates, but to find out you should always use EXPLAIN, since the optimizer makes its decisions based on your DB and its statistics.

But, why are you using SELECT *? That is not good SQL. The optimizer might just decide to do a full table scan, since you are going to access everything anyway.

Learn to use Explain.
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


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

PostPosted: Tue Aug 28, 2007 12:00 am    Post subject:
Reply with quote

and, since you have no ORDER BY clause, there is no need to use the index to prohibit a sort.
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 Join in SQL Query vickey_dw DB2 2 Tue Jan 17, 2017 12:15 am
This topic is locked: you cannot edit posts or make replies. MQ setup query mohitsaini Java & MQSeries 4 Sun Jan 08, 2017 1:53 am
No new posts DB2 query Using Recursion, Converting... smilewithashu2 DB2 1 Tue Jan 03, 2017 12:50 pm
No new posts SQL query to run through list of valu... Ni3-db2 DB2 14 Wed Dec 14, 2016 9:52 am
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


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