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
 

 

Limiting use of index in a SQL evenif there are 4-5 indexes

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> Mainframe Interview Questions
View previous topic :: :: View next topic  
Author Message
shailesh_do

New User


Joined: 27 Jun 2006
Posts: 9

PostPosted: Sun Nov 01, 2009 10:14 am    Post subject: Limiting use of index in a SQL evenif there are 4-5 indexes
Reply with quote

Dear All,

There was an question was in interview as;

Say there are 4-5 indexes on a table (say table A)& COBOL-DB2 pgm has sql query on the same table A. Can we limit the use of index when the query is performed in pgm as it should use only one index & not the all?

Hope I have tried to put question more clearly.
(P.S. -Aapologies if this is a repeat question but I couldn't find answer to this when used search facility)
Back to top
View user's profile Send private message

dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Mon Nov 02, 2009 3:04 am    Post subject:
Reply with quote

Hello,

What did you tell the interviewer? What was the interviewer's reaction?
Back to top
View user's profile Send private message
shailesh_do

New User


Joined: 27 Jun 2006
Posts: 9

PostPosted: Mon Nov 02, 2009 8:51 pm    Post subject:
Reply with quote

Hi,

Actually I couldn't think of any answer to this one & when at the end of interview asked to the interviewer for this question he didn't tell me the answer.
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Mon Nov 02, 2009 9:25 pm    Post subject:
Reply with quote

Hello,

One way to "trick the optimizer" is to specify an ORDER BY. . .
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


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

PostPosted: Mon Nov 02, 2009 9:38 pm    Post subject:
Reply with quote

Quote:
as it should use only one index & not the all?


does not deserve an answer.
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Mon Nov 02, 2009 10:03 pm    Post subject:
Reply with quote

Hi Dick,

Quote:
does not deserve an answer.
It is surely amazing where/how some of these "interview" questions originate. . .

I suppose that when you're new and looking for work, you have to put up with such. . .
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


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

PostPosted: Mon Nov 02, 2009 11:03 pm    Post subject:
Reply with quote

A legitimate question of how coded sql can exclude indexes deserves an answer:

Craig Mullins, in the Db2 Developer's Guide (5th) comments:

Quote:
DB2 might be able to use an index-only access path that is unavailable for SELECT *.

Quote:
Why would the optimizer determine that an index should not be used? Aren't indexes designed to make querying tables more efficient? The optimizer decides that an index should not be used for one of two reasons. The first reason is when the table being accessed has only a small number of rows. Using an index to query a small table can degrade performance because additional I/O is required. For example, consider a tablespace consisting of one page. Accessing this page without the index would require a single I/O. But if you use an index, at least one additional I/O is required to read the index page. Even more I/O may be required if index root pages, index non-leaf pages, and additional index leaf pages must be accessed.

The second reason for not using an index is that, for larger tables, the organization of the index could require additional I/O to satisfy the query. Factors affecting this are the full and first key cardinality of the index and the cluster ratio of the index.

Quote:
append OR 0 = 1 to the predicate.

Quote:
code redundant predicates

Quote:
modifying DB2 Catalog statistics

Quote:
Using Optimization Hints (OPTHINT) to Force an Access Path


there is more discussion on all topics,
but the TS wanted a list,
not information about each item.
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 -> Mainframe Interview Questions All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Order BY AND Index Nileshkul DB2 2 Sat Dec 31, 2016 6:33 pm
No new posts Several errors during building altern... Andi1982 JCL & VSAM 11 Wed Jul 06, 2016 7:39 pm
No new posts Drop building Alternate Index for a p... bhavin.mehta JCL & VSAM 6 Mon Jul 04, 2016 3:47 pm
No new posts VSAM Alternate Index in CICS jacobdng CICS 2 Fri May 06, 2016 1:41 pm
No new posts Rebuilding IMS secondary index withou... Hooman24 IMS DB/DC 6 Tue Mar 29, 2016 12:22 pm


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