index order
-
hi i have created the index in order col1, col2, now if in my application or on query analyzer i write the script select * from table1 where col2 = 'value 2' and col1 = 'value 1' will the index be used or not as order is changed in my srcipt. i want to know about both sql server and oracle.
Regards Shajeel
-
hi i have created the index in order col1, col2, now if in my application or on query analyzer i write the script select * from table1 where col2 = 'value 2' and col1 = 'value 1' will the index be used or not as order is changed in my srcipt. i want to know about both sql server and oracle.
Regards Shajeel
This sounds suspiciously like homework to me, but I'll answer it anyway. If you have created two indexes, then they will most likely be used. Note that the usage depends entirely on the query execution plan that the DB server determines is the best usage. Suppose that there are only 2 possible values in col2, then the engine may determine that it needs to do a sequential scan because there is not enough differentiation in the data to allow it to perform an indexed scan. Similarly, if there are only a few rows in the table then it may perform a sequential scan because the engine has determined that the row count is too few to allow it to index search efficiently. As always, the best way to find out the answer is to try it out yourself.
Deja View - the feeling that you've seen this post before.
-
This sounds suspiciously like homework to me, but I'll answer it anyway. If you have created two indexes, then they will most likely be used. Note that the usage depends entirely on the query execution plan that the DB server determines is the best usage. Suppose that there are only 2 possible values in col2, then the engine may determine that it needs to do a sequential scan because there is not enough differentiation in the data to allow it to perform an indexed scan. Similarly, if there are only a few rows in the table then it may perform a sequential scan because the engine has determined that the row count is too few to allow it to index search efficiently. As always, the best way to find out the answer is to try it out yourself.
Deja View - the feeling that you've seen this post before.
i think i explained it wrong i have not created two indexes, i have created only one index with two columns like CREATE UNIQUE INDEX Index1 ON table1 (col1, col2) so in order col1 is first and col2 is second. in where i used where col2 = 'val 2' and col1 = 'val 1' i.e. filter in wrong order.
Regards Shajeel
-
i think i explained it wrong i have not created two indexes, i have created only one index with two columns like CREATE UNIQUE INDEX Index1 ON table1 (col1, col2) so in order col1 is first and col2 is second. in where i used where col2 = 'val 2' and col1 = 'val 1' i.e. filter in wrong order.
Regards Shajeel
Hi Shajeel It shouldn't matter which way round you have the where-clause, because SQL-Server (and Oracle) are able to recognise that the index is potentially applicable (because both RDBMS products use a cost-based optimiser). The main criteria as-to whether the index will be used will be how selective the index is (i.e. how many records does SQL-Server think will be returned from the query based on its statistics). Regards Andy