Index tuning

1
Index tuning

• Performance Tuning

2
Overview
3
Index

• An index is a data structure that supports
  efficient access to data

Set ofRecords
Matchingrecords
Conditiononattributevalue
index
(search key)
4
Index Implementations in some major DBMS

• SQL Server
• B-Tree data structure
• Clustered indexes are sparse
• Indexes maintained as updates/insertions/deletes
  are performed
• DB2
• B-Tree data structure, spatial extender for
  R-tree
• Clustered indexes are dense
• Explicit command for index reorganization

• Oracle
• B-tree, hash, bitmap, spatial extender for
  R-Tree
• No clustered index until 10g
• Index organized table (unique/clustered)
• Clusters used when creating tables.
• MySQL
• B-Tree, R-Tree (geometry and pairs of integers)
• Indexes maintained as updates/insertions/deletes
  are performed

5
Types of Queries

1. Point QuerySELECT balanceFROM accountsWHERE
   number 1023
2. Multipoint QuerySELECT balanceFROM
   accountsWHERE branchnum 100

1. Range QuerySELECT numberFROM accountsWHERE
   balance gt 10000
2. Prefix Match QuerySELECT FROM employeesWHERE
   name Jensen and firstname Carl
   and age lt 30

6
Types of Queries

1. Extremal QuerySELECT FROM accountsWHERE
   balance max(select balance from accounts)
2. Ordering QuerySELECT FROM accountsORDER BY
   balance

1. Grouping QuerySELECT branchnum,
   avg(balance)FROM accountsGROUP BY branchnum
2. Join QuerySELECT distinct branch.adresseFROM
   accounts, branchWHERE accounts.branchnum
   branch.numberand accounts.balance gt 10000

7
Benefits of Clustered Index

• Benefits of a clustered index
• A sparse clustered index stores fewer pointers
  than a dense index.
• This might save up to one level in the B-tree
  index.
• A clustered index is good for multipoint queries
• White pages in a paper telephone book
• A clustered index based on a B-Tree supports
  range, prefix, extremal and ordering queries
  well.
• A clustered index (on attribute X) can reduce
  lock contention
• Retrieval of records or update operations using
  an equality, a prefix match or a range condition
  based on X will access and lock only a few
  consecutive pages of data

8
Advantage of Clustered Index

• Multipoint query that returns 100 records out of
  1000000.
• Cold buffer
• Clustered index is twice as fast as non-clustered
  index and orders of magnitude faster than a scan.

8
9
Disvantage of Clustered Index

• Cost of a clustered index
• Cost of overflow pages
• Due to insertions
• Due to updates (e.g., replace a NULL value by a
  long string)

10
Index Face Lifts

• Index is created with fillfactor 100.
• Insertions cause page splits and extra I/O for
  each query
• Maintenance consists in dropping and recreating
  the index
• With maintenance performance is constant while
  performance degrades significantly if no
  maintenance is performed.

10
11
Index Face Lifts

• Index is created with pctfree 0
• Insertions cause records to be appended at the
  end of the table
• Each query thus traverses the index structure and
  scans the tail of the table.
• Performances degrade slowly when no maintenance
  is performed.

12
Index Face lifts

• In Oracle, clustered index are approximated by an
  index defined on a clustered table
• No automatic physical reorganization
• Index defined with pctfree 0
• Overflow pages cause performance degradation

13
Clustered Index

• Because there is only one clustered index per
  table, it might be a good idea to replicate a
  table in order to use a clustered index on two
  different attributes
• Yellow and white pages in a paper telephone book
• Which is feasible for Low insertion/update rate

14
Non-Clustered Index

• Benefits of non-clustered indexes
• A non-clustered index can eliminate the need to
  access the underlying table through covering.
• It might be worth creating several indexes to
  increase the likelihood that the optimizer can
  find a covering index

• A non-clustered index is good if each query
  retrieves significantly fewer records than there
  are pages in the table.
• Point queries
• Multipoint queries
• number of distinct key values gt c number of
  records per page
• Where c is the number of pages can be prefetched
  in each disk read

15
Example

• Non-clustering index on attribute A, which has 20
  different values, each equality query will
  retrieve approximately 1/20 records
• If each page contains 80 record, then nearly
  every page will have almost every distinct values
  of A
• If each page contains 2 record, a query will
  touch only every tenth page on the average

16
Scan Can Sometimes Win

• IBM DB2 v7.1 on Windows 2000
• Range Query
• If a query retrieves 10 of the records or more,
  scanning is often better than using a
  non-clustering non-covering index. Crossover gt
  10 when records are large or table is fragmented
  on disk scan cost increases.

17
Summary

• Use a hash index for point queries only. Use a
  B-tree if multipoint queries or range queries are
  used
• Use clustering
• if your queries need all or most of the fields of
  each records returned (compared to index-only
  scan)
• if multipoint or range queries are asked
• Use a dense index to cover critical queries
• Dont use an index if the time lost when
  inserting and updating overwhelms the time saved
  when querying