What are the different types of indexes what are the benefits of each

Question

What are the different types of indexes  what are the benefits of each   I heard of covering and clustered indexes  are there more  Where would you use them

User · Answer

SQL Server 2008 has filtered indexes  similar to PostgreSQL s partial indexes  Both allow to include in index only rows matching specified criteria   The syntax is identical to PostgreSQL   create index i on Customers name  where is alive   cast 1 as bit

User · Answer

Unique cluster non-cluster column store Index with included  column  index on computed column filtered spatial xml full text

User · Answer

OdeToCode has a good article covering the basic differences  As it says in the article      Proper indexes are crucial for good   performance in large databases    Sometimes you can make up for a poorly   written query with a good index  but   it can be hard to make up for poor   indexing with even the best queries    Quite true  too    If you re just starting out with it  I d focus on clustered and composite indexes  since they ll probably be what you use the most

User · Answer

I ll add a couple of index types  BITMAP   - when you have very low number of different possible values  very fast and doesn t take up much space  PARTITIONED  - allows the index to be partitioned based on some property usually advantageous on very large database objects for storage or performance reasons   FUNCTION EXPRESSION indexes - used to pre-calculate some value based on the table and store it in the index  a very simple example might be an index based on lower   or a substring function

User · Answer

PostgreSQL allows partial indexes  where only rows that match a predicate are indexed  For instance  you might want to index the customer table for only those records which are active  This might look something like   create index i on customers  id  name  whatever  where is active is true    If your index many columns  and you have many inactive customers  this can be a big win in terms of space  the index will be stored in fewer disk pages  and thus performance  To hit the index you need to  at a minimum  specify the predicate   select name from customers where is active is true

User · Answer

Oracle has various combinations of b-tree  bitmap  partitioned and non-partitioned  reverse byte  bitmap join  and domain indexes   Here s a link to the 11gR1 documentation on the subject  http   download oracle com docs cd B28359 01 server 111 b28274 data acc htm PFGRF004

User · Answer

Different database systems have different names for the same type of index  so be careful with this   For example  what SQL Server and Sybase call  clustered index  is called in Oracle an  index-organised table

User · Answer

Conventional wisdom suggests that index choice should be based on cardinality   They ll say       For a low cardinality column like GENDER  use bitmap   For a high cardinality like LAST NAME  use b-tree    This is not the case with Oracle  where index choice should instead be based on the type of application  OLTP vs  OLAP    DML on tables with bitmap indexes can cause serious lock contention   On the other hand  the Oracle CBO can easily combine multiple bitmap indexes together  and bitmap indexes can be used to search for nulls   As a general rule      For an OLTP system with frequent DML and routine queries  use btree   For an OLAP system with infrequent DML and adhoc queries  use bitmap    I m not sure if this applies to other databases  comments are welcome   The following articles discuss the subject further    Bitmap Index vs  B-tree Index  Which and When   Understanding Bitmap Indexes

User · Answer

I suggest you search the blogs of Jason Massie  http   statisticsio com   and Brent Ozar  http   www brentozar com   for related info  They have some post about real-life scenario that deals with indexes

User · Answer

To view the types of indexes and its meaning visits  https   msdn microsoft com en-us library ms175049 aspx

User · Answer

Unique - Guarantees unique values for the column or set of columns  included in the index Covering - Includes all of the columns that are used in a particular query  or set of queries   allowing the database to use only the index and not actually have to look at the table data to retrieve the results Clustered - This is way in which the actual data is ordered on the disk  which means if a query uses the clustered index for looking up the values  it does not have to take the additional step of looking up the actual table row for any data not included in the index

[sql] What are the different types of indexes, what are the benefits of each?

Examples related to sql

Examples related to database

Examples related to database-design

Examples related to indexing