QAD BI v3 - Tech Level 2 Certification - Part 4 > Performance Tuning > Table Performance – Indexes
  PPT
Table Performance – Indexes
 
database index is a data structure that improves the speed of data retrieval operations on a database table at the cost of slower writes and increased storage space. Indexes can be created using one or more columns of a database table, providing the basis for both rapid random lookups and efficient access of ordered records. The disk space required to store the index is typically less than that required by the table (since indices usually contain only the key-fields according to which the table is to be arranged, and exclude all the other details in the table), yielding the possibility to store indices in memory for a table whose data is too large to store in memory.
Wikipedia - http://en.wikipedia.org/wiki/Index_(database)
Table Performance – Indexes
 
The data is present in random order, but the logical ordering is specified by the index. The data rows may be randomly spread throughout the table. The non-clustered index tree contains the index keys in sorted order, with the leaf level of the index containing the pointer to the page and the row number in the data page.
Microsoft SQL Server creates non-clustered indexes by default when CREATE INDEX command is given. There can be more than one non-clustered index on a database table. There can be as many as 249 non-clustered indexes per table in SQL Server 2005 and 999 non-clustered indexes per table in SQL Server 2008. It also creates a clustered index on a primary key by default.
Table Performance – Indexes
 
Clustering alters the data block into a certain distinct order to match the index, resulting in the row data being stored in order. Therefore, only one clustered index can be created on a given database table. Clustered indices can greatly increase overall speed of retrieval, but usually only where the data is accessed sequentially in the same or reverse order of the clustered index, or when a range of items is selected.
Since the physical records are in this sort order on disk, the next row item in the sequence is immediately before or after the last one, and so fewer data block reads are required. The primary feature of a clustered index is therefore the ordering of the physical data rows in accordance with the index blocks that point to them. Some databases separate the data and index blocks into separate files, others put two completely different data blocks within the same physical file(s). Create an object where the physical order of rows is same as the index order of the rows and the bottom(leaf) level of clustered index contains the actual data rows.
They are known as “index organized tables” under Oracle database.
Table Performance – Indexes
 
In this example, the query references the first, second and fifth columns in the index. Will the index be used?
Table Performance – Indexes
Table Performance – Indexes
 
If we do an Execution plan on the query, it looks like the Index is working but a closer look is warranted.
Table Performance – Indexes
Table Performance – Indexes