Thank you for your kind answer, I partially understood now. I still have one question : why the index on primary key is actually used ( or at least that's my understanding of explain plan output), while a regular index isn't ? Or indexes aren't used in both cases ? NB, resultsets are exactly same size for both queries, so I assume same amount of data is analysed.
All best, Dan If you try to fail, and succeed, which have you done? ----- Original Message ----- From: "Becker, Holger" <[EMAIL PROTECTED]> To: "'Dan Todor'" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Monday, July 14, 2003 20:06 PM Subject: RE: More questions on query optimization > Dan Todor wrote: > > > I remembered having some troubles some years ago with NOT > > NULL, table scans, > > and MS SQL server 6.5, so I made a little test. I created a > > table 'TEST', > > with columns NAME (varchar, primary key)and AGE ( int, > > indexed to index I1), > > and put some values in. Now, if I do "explain select * from test where > > name='max'" , result is OK, > > > > EQUAL CONDITION FOR KEY COLUMN > > (USED KEY COLUMN) > > RESULT IS COPIED , COSTVALUE IS > > > > However, when I try "explain select * from test where > > age=15", result is : > > > > TABLE SCAN > > RESULT IS NOT COPIED , COSTVALUE IS > > > > The only possible explanation for this I've found in > > documentation ( topic > > Optimizer-> Search strategy-> Sequential search ) is : > > <begin quote>If the possible non-sequential search strategies > > would be more > > costly than the sequential search, the table is processed > > sequentially. <end > > quote> > > > > Does that mean that, even if an index exists and the search criteria > > qualifies for using the index, it can be ignored ? How is > > this decision > > taken ? When the index isn't ignored ? > > The SAP DB optimizer estimates the pages which have to be scanned > on every affected index by the given command. > > Is the amount of pages greater then a specific value the index > is not used by the optimizer for this command. > > These specific value or I should say values are the database parameters > OPTIM_FETCH_RESLT for fetch strategies and OPTIM_BUILD_RESULT for build strategies. > > Usally both parameters have a value of 15 which means if the rate of scanned index pages > to total index pages is greater then 15% the index is not used. > > Best regards, > Holger > SAP Labs Berlin > _______________________________________________ sapdb.general mailing list [EMAIL PROTECTED] http://listserv.sap.com/mailman/listinfo/sapdb.general
