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 JCL to Set Return code based on DB2 S... vinu78 DB2 17 Mon Mar 13, 2017 9:47 pm
No new posts SQL query not working in Cobol program. CuriousMainframer COBOL Programming 14 Wed Feb 22, 2017 5:56 pm
No new posts Omegamon for DB2 query ashek15 DB2 0 Wed Feb 15, 2017 11:25 am
No new posts Query on DB2 WLM stored procedures ashek15 DB2 8 Sun Feb 12, 2017 11:13 pm
No new posts GETMAIN/FREEMAIN query Suja.Sai CICS 9 Tue Jan 31, 2017 12:01 pm


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