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
