Master_Class_Special_Edition_-_Database_Indexing_C_Secure_文档.docx

mailto:stuart.hutchison@bcu.ac.ukstuart.hutchison@bcu.ac.ukMaster Class (Special Edition)Database Indexing (C)In this third and final part of the indexing material we look at some of the more advanced and specialized aspects of indexing strategy – starting with index suppression.Index SuppressionAll the discussion up to now has been about how and when to create different types of indexes so as to speed up DBMS queries. Well now we look at something different – those situations when you already have existing indexes in place and yet your queries still do not utilize those indexes. In other words, under what circumstances will the DBMS simply ignore your hard-earned indexes! This is known as index suppression.There are several reasons why the DBMS will completely ignore your indexes when extracting data for a query. If no index is used the DBMS has no option but to execute a FULL TABLE SCAN and search sequentially through the whole table looking for the required data. We cover the major reasons for index suppression here.REASON 1: No WHERE clause usedLook at the following sample query:SELECT ID, Name, AddressFROM StudentsWHERE Age 25;The SELECT clause tells the DBMS which columns are to be used to provide the final data and the FROM clause specifies which table these columns reside in – in this case the results from the query will be the id, name and address of some students - but which students? That is the job of the WHERE clause – to target (filter out) only specified students – in this case only those students aged over 25 years.So it is the WHERE clause that acts as a row-level filter seeking out specific rows in the table and rejecting others. If there is no WHERE clause in the query – as in:SELECT ID, Name, AddressFROM Students;Then the DBMS has nothing to use as a row-level filter and so it will simply extract the ID, Name and Address sequentially for every row in the Students table – using a FULL TABLE SCAN. No index will be used – even if one already existe