IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

SQL - select data available in index


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
Nileshkul

New User


Joined: 09 May 2016
Posts: 43
Location: India

PostPosted: Mon Jun 26, 2017 1:30 am
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

Senior Member


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

PostPosted: Mon Jun 26, 2017 8:37 am
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: 446
Location: USA

PostPosted: Mon Jun 26, 2017 8:26 pm
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

Global Moderator


Joined: 21 Sep 2010
Posts: 3049
Location: NYC,USA

PostPosted: Mon Jun 26, 2017 9:16 pm
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 View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts Data set Rec-Cnt and Byte-Cnt Testing & Performance 2
No new posts SCOPE PENDING option -check data DB2 2
No new posts Check data with Exception Table DB2 0
No new posts JCL EXEC PARM data in C Java & MQSeries 2
This topic is locked: you cannot edit posts or make replies. Automation need help in sorting the data DFSORT/ICETOOL 38
Search our Forums:

Back to Top