Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2003
    Location
    Singapore
    Posts
    4

    Cool Unanswered: Primary Key Not Used

    Hi,
    Does anyone know why the the PK of table is not used (as shown in query plan) even though the JOIN is on the PK?

    Thanks for any advice!

  2. #2
    Join Date
    Nov 2003
    Posts
    5

    Re: Primary Key Not Used

    Originally posted by sun_zi_88
    Hi,
    Does anyone know why the the PK of table is not used (as shown in query plan) even though the JOIN is on the PK?

    Thanks for any advice!

    Is it using any other index instead?

    Reason could be covering indexes.

  3. #3
    Join Date
    Mar 2001
    Location
    Lexington, KY
    Posts
    606
    Show the query and the query plan.

    If you're joining on two tables like this:
    select *
    from t1
    inner join t2 on t1.id = t2.id

    then *one* of the tables is going to have to be scanned -- the optimizer should choose the smallest one.
    Thanks,

    Matt

  4. #4
    Join Date
    Oct 2003
    Location
    Singapore
    Posts
    4
    Why would one of the tables be scanned?

    The query in the previous situation is rather complex to simulate.

    Following is a similar case. I would be grateful if anyone can help.

    /*
    Query which uses an index.

    However, the key mentioned in the query plan is not correct.

    The index is:

    create nonclustered index idx_b
    on dbo.B (col_B1, col_B3, col_B2)
    on 'default'
    go

    */

    SELECT A.col_A1, A.col_A2, A.col_A4
    FROM A, B
    WHERE
    A.col_A1 = 'xxx'
    AND A.col_A2 = ISNULL(B.col_B3, '')
    AND A.col_A3 = 'yyy'
    AND B.col_B1 = 'yyy'
    AND B.col_B2 = '0'


    QUERY PLAN FOR STATEMENT 1 (at line 1).

    STEP 1
    The type of query is SELECT.

    FROM TABLE
    B
    Nested iteration.
    Index : idx_b
    Forward scan.
    Positioning by key.
    Index contains all needed columns. Base table will not be read.
    Keys are:
    col_B1 ASC
    Using I/O Size 16 Kbytes for index leaf pages.
    With LRU Buffer Replacement Strategy for index leaf pages.


    FROM TABLE
    A
    Nested iteration.
    Index : idx_a
    Forward scan.
    Positioning by key.
    Keys are:
    col_A2 ASC
    Using I/O Size 16 Kbytes for index leaf pages.
    With LRU Buffer Replacement Strategy for index leaf pages.
    Using I/O Size 2 Kbytes for data pages.
    With LRU Buffer Replacement Strategy for data pages.


    /*
    Query modified to return an additional column - idx_b of table B is not used.
    */
    SELECT A.col_A1, A.col_A2, A.col_A4
    FROM A a, B ap, B.s_agency_code
    WHERE
    A.col_A1 = 'xxx'
    AND A.col_A2 = ISNULL(B.col_B3, '')
    AND A.col_B1 = '011'
    AND B.col_B1 = '011'
    AND B.col_B2 = '0'


    QUERY PLAN FOR STATEMENT 1 (at line 1).

    STEP 1
    The type of query is SELECT.


    FROM TABLE
    B
    Nested iteration.
    Table Scan.
    Forward scan.
    Positioning at start of table.
    Using I/O Size 16 Kbytes for data pages.
    With LRU Buffer Replacement Strategy for data pages.


    FROM TABLE
    A
    Nested iteration.
    Index : idx_a
    Forward scan.
    Positioning by key.
    Keys are:
    col_A2 ASC
    Using I/O Size 16 Kbytes for index leaf pages.
    With LRU Buffer Replacement Strategy for index leaf pages.
    Using I/O Size 2 Kbytes for data pages.
    With LRU Buffer Replacement Strategy for data pages.

Posting Permissions

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