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
 
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: 6970
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: 6970
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 reg query on DYNALLOC feature raghuraman123 SYNCSORT 12 Wed Jan 10, 2018 2:42 pm
No new posts Utility for search operation Gopalakrishna551 TSO/ISPF 11 Wed Jan 03, 2018 11:07 am
No new posts TBSARG - Deleting the search criteria... ISPFHerc TSO/ISPF 6 Thu Dec 14, 2017 8:18 am
No new posts ISSUE IN copying Sequential file reco... thesumitk JCL & VSAM 2 Wed Dec 13, 2017 3:07 pm
No new posts query to fetch record which has only ... maxsubrat DB2 12 Mon Dec 11, 2017 5:03 pm

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