var sidebar_align = 'right';
var content_container_margin = parseInt('290px');
var sidebar_width = parseInt('270px');
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?
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.
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
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...
Originally Posted by EddieA
Import Oracle table data into MS Access thru Visual Basic 6 code.
Is it table scan or index scan.
Originally Posted by mpeppler
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.