Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2006
    Location
    Czech Republic
    Posts
    82

    Unanswered: How to use just part of composed index?

    Can I use just part of composite index?

    There is composed index on column code, state:

    idx_code_status nonclustered located on default code, state

    Condition like this :

    select ... where code = 5 and state = 0

    uses this index idx_code_state...

    Even if I use query with condition just for first part of index (column code) sybase uses idx_code_state...

    e.g. select ..... where code = 5


    But if there is only condition on column state:

    e.g. select ..... where state = 0

    Sybase don´t use this index. Is it normal behaviour?
    Is any way how sybase will use this index or I will have to create another index just for column state?

  2. #2
    Join Date
    Jan 2003
    Location
    Geneva, Switzerland
    Posts
    353
    This is normal behavior.

    Remember that the index is a modified binary tree, and an index on a composite key will store the elements in the tree in the order of the two keys together.

    If you have a query that only uses the second element of the key then there is no way to know which rows qualify using the index without traversing the entire index.

    Michael

  3. #3
    Join Date
    Sep 2006
    Posts
    15
    Yes thats the rule of thumb. As Michael said you can't get away without using the first key.
    But there's always a solution.

    You can try this:

    If you don't care about the value of the code...

    select ... where code between 0 and 999999
    and state = 0

    The above code will use the index

  4. #4
    Join Date
    Jan 2003
    Location
    Geneva, Switzerland
    Posts
    353
    Quote Originally Posted by EddieA
    If you don't care about the value of the code...

    select ... where code between 0 and 999999
    and state = 0

    The above code will use the index
    That code MAY use the index, but is quite likely to do a table scan because the entire index still has to be checked to find the matching rows...

    Michael

  5. #5
    Join Date
    Oct 2006
    Location
    ddfdf
    Posts
    1
    Import Oracle table data into MS Access thru Visual Basic 6 code.

  6. #6
    Join Date
    Sep 2006
    Posts
    15
    Quote Originally Posted by mpeppler
    That code MAY use the index, but is quite likely to do a table scan because the entire index still has to be checked to find the matching rows...

    Michael
    Is it table scan or index scan.

  7. #7
    Join Date
    Jan 2003
    Location
    Geneva, Switzerland
    Posts
    353
    Right - and index scan is of course somewhat better than a table scan.

    However, for a large table it will still be pretty slow compared with an index access with a proper key.

    Michael

Posting Permissions

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