Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2002
    Posts
    29

    Question Unanswered: Index & Primary Key??

    I have two tables. T1 has 1m rows and T2 has 2.7m rows. T1 & T2 are in different servers. T1 has clustered index on 3 fields C1+C2+C3. C1 & C2 ar chars and C3 is int. T2 has Primary key nonclusted on 3 fields C4+C5+C6. C4 & C5 are chars and C6 is int.

    If I run a query against T1 by giving the values either C2 or C3, query plan shows that it does not use the index. I remember that unless we give first part of combination index, in this case C1, it will not use the Index at all and does the table scan.

    But when I run the second query against T2 by giving values either C5 or C6, the query plan shows that it uses the Primary key index.

    Now I am confused. Can any one explain? Does this index and Primary key makes difference? or clusterd index and non clusterd PK makes the difference?

    I am using ASE 12.0.0.2v

    Thanks.

  2. #2
    Join Date
    Sep 2003
    Posts
    17

    Re: Index & Primary Key??

    Hi,

    I could think of one reason for this behaviour:

    Does the index on T2 (C4, C5, C6) cover the query, which means selects only the columns used in the index? Then I could imagine that the index gets used, even if C4 is missing (covering nonmatching index scans).

    Regards,
    Ulrike

    Originally posted by nmr
    I have two tables. T1 has 1m rows and T2 has 2.7m rows. T1 & T2 are in different servers. T1 has clustered index on 3 fields C1+C2+C3. C1 & C2 ar chars and C3 is int. T2 has Primary key nonclusted on 3 fields C4+C5+C6. C4 & C5 are chars and C6 is int.

    If I run a query against T1 by giving the values either C2 or C3, query plan shows that it does not use the index. I remember that unless we give first part of combination index, in this case C1, it will not use the Index at all and does the table scan.

    But when I run the second query against T2 by giving values either C5 or C6, the query plan shows that it uses the Primary key index.

    Now I am confused. Can any one explain? Does this index and Primary key makes difference? or clusterd index and non clusterd PK makes the difference?

    I am using ASE 12.0.0.2v

    Thanks.

  3. #3
    Join Date
    Sep 2003
    Location
    Switzerland
    Posts
    443

    Re: Index & Primary Key??

    Originally posted by nmr
    I have two tables. T1 has 1m rows and T2 has 2.7m rows. T1 & T2 are in different servers. T1 has clustered index on 3 fields C1+C2+C3. C1 & C2 ar chars and C3 is int. T2 has Primary key nonclusted on 3 fields C4+C5+C6. C4 & C5 are chars and C6 is int.

    If I run a query against T1 by giving the values either C2 or C3, query plan shows that it does not use the index. I remember that unless we give first part of combination index, in this case C1, it will not use the Index at all and does the table scan.

    But when I run the second query against T2 by giving values either C5 or C6, the query plan shows that it uses the Primary key index.

    Now I am confused. Can any one explain? Does this index and Primary key makes difference? or clusterd index and non clusterd PK makes the difference?

    I am using ASE 12.0.0.2v

    Thanks.
    Hi,

    I think this is because the 2nd query is a covered query.

    A covered query is a query in which all the data can be retrieved from a nonclustered index without having to read the data pages.

    Covered queries are also possible with clustered indexes on DOL tables since their structure is similar to that of nonclustered indexes.

    Hope this helps.

    Thanks,
    Vishi.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •