|
View previous topic :: View next topic
|
| Author |
Message |
shailesh_do
New User
Joined: 27 Jun 2006
Posts: 9
|
|
|
|
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 |
|
 |
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hello,
What did you tell the interviewer? What was the interviewer's reaction? |
|
| Back to top |
|
|
shailesh_do
New User
Joined: 27 Jun 2006
Posts: 9
|
|
|
|
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 |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hello,
One way to "trick the optimizer" is to specify an ORDER BY. . . |
|
| Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
| Quote: |
| as it should use only one index & not the all? |
does not deserve an answer. |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
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 |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
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 |
|
|
|
|
