2

if we have a table T1 with columns A,B,C,D,E and an index ( A,B,C) built for it

if we a SQL query joining on columns A,B or A,B,C or A, this index can still be used, but if the query is joining on B or C or B,C the index is totally useless

I know indexes are often implemented BTree , I want to know how is the implementation detail related with this ?

4

This is not necessarily the case. Oracle, for example, has an access path known as an "index skip scan". See http://docs.oracle.com/cd/B10501_01/server.920/a96533/optimops.htm#51553

Basically, if there are few distinct values in column A, and the query restricts on column B (and optionally, column C), the executor will substitute each of the distinct values of column A in turn and probe the index for the supplied value of column B (and optionally, column C).

Postgresql can do something similar, but it is still labelled as an "index scan", see Working of indexes in PostgreSQL

  • 1
    There is a Connect item that one can vote for implementing this in SQL-Server: MS Connect: Implement Index Skip Scan – ypercubeᵀᴹ Jan 15 '14 at 10:11
  • I think this states that my assertion is not necessarily true. but without considering this, how does DB apply INDEX ABC on column AB join ? is that by comparing only the AB part of the index key ? – zinking Jan 16 '14 at 8:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.