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
 
SQL - select data available in index

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

New User


Joined: 09 May 2016
Posts: 16
Location: India

PostPosted: Mon Jun 26, 2017 1:30 am    Post subject: SQL - select data available in index
Reply with quote

I read somewhere in my email trail with local
DBAs that if selected colums in a query are available
in indexed columns, the query is faster.
Any idea how much faster it makes a select query if all selected colums are available in indexed compinent vs some could not available in indexed components
Thanks
Nilesh
Back to top
View user's profile Send private message

Phrzby Phil

Active Member


Joined: 31 Oct 2006
Posts: 967
Location: Richmond, Virginia

PostPosted: Mon Jun 26, 2017 8:37 am    Post subject:
Reply with quote

Wouldn't you feel more comfortable with your understanding of this complex issue by relying on your own easily designed testing as opposed to someone you don't know telling you the answer?

You are a new user. I and I assume just about every other experienced programmer/DBA/etc. on this excellent forum encourage you to own your experience and education by investing your time, analytical thinking, and programming skills in gaining knowledge.

Then YOU will become the answer person at your job location (and can then guide your own novices on how to test their own questions!).
Back to top
View user's profile Send private message
RahulG31

Active User


Joined: 20 Dec 2014
Posts: 408
Location: USA

PostPosted: Mon Jun 26, 2017 8:26 pm    Post subject: Re: SQL - select data available in index
Reply with quote

Nileshkul wrote:
I read somewhere in my email trail with local
DBAs that if selected colums in a query are available
in indexed columns, the query is faster.
Any idea how much faster it makes a select query if all selected colums are available in indexed compinent vs some could not available in indexed components
Thanks
Nilesh


1. Do you know if the index is unique/non-unique?
2. How large is the table?
3. Why didn't you ask the same question to your DBA on the same email chain?

'how much faster' depends on point 1 and 2 above (and possibly some more). Suppose your table has only 5 rows then, what do you think would happen if you have an index on your select column? Your query will be slower and Not faster as it is a very small table and a complete table scan will be faster than Index search.

.
Back to top
View user's profile Send private message
Rohit Umarjikar

Senior Member


Joined: 21 Sep 2010
Posts: 1819
Location: NY,USA

PostPosted: Mon Jun 26, 2017 9:16 pm    Post subject:
Reply with quote

Quote:
I read somewhere in my email trail with local
DBAs that if selected columns in a query are available
in indexed columns, the query is faster.
Any idea how much faster it makes a select query if all selected columns are available in indexed component vs. some could not available in indexed components
I doubt your DBA said it true everything and even considerable.
If you have unused indexes then the performance will slow down because of too much corresponding sorts and this is implemented by us and saved a lot of $$ every year by taking out unused INDEXes. You would want the better indexes on the columns used as a pert of WHERE clause and you don't/never design indexes based on SELECTed columns in your query.
Moreover, if you have any performance issues then did you try EXPLAIN first? did you make sure all other performance factors checklist(which can be found on internet or IBM site) to make sure your SQL Concur the fact?
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 Extract record for change in combinat... Trinadh DFSORT/ICETOOL 6 Thu Nov 23, 2017 3:32 pm
No new posts Fetching data from JHS as per the cur... arunsoods All Other Mainframe Topics 4 Wed Nov 22, 2017 1:54 pm
No new posts Extract Record using range of Data scorp_rahul23 DFSORT/ICETOOL 3 Wed Nov 15, 2017 11:54 pm
No new posts Updating data from one file to other!!! Vignesh Sid SYNCSORT 1 Mon Nov 06, 2017 2:42 pm
This topic is locked: you cannot edit posts or make replies. Can a alphanumeric data be moved to a... nico_neoz COBOL Programming 10 Mon Oct 30, 2017 8:28 am

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