If I built a btree index on columns (one, two, three, four, five) will it be used for a query with where block:
(1) ... WHERE one = 1 AND two = 2 AND three = 3 AND four = 4 -- no five
or
(2) ... WHERE two = 2 AND one = 1 -- wrong sequence, no five
or
(3) ... WHERE one = 1 AND three = 3 AND two = 2 AND five = 5 AND four = 4 -- wrong sequence
Yes in all your cases, because databases don't generally worry about the order of statements in your WHERE clause. However, it's not likely to be used
WHERE two = 2 AND three = 3 -- no one
Because one is the first component of your index, but doesn't appear in your where clause
Since 'A AND B' is logically equivalent to 'B AND A', the statements that you give are logically equivalent to statements in which the clauses are listed in sequential order. Since the database will be able to do this simple rewriting, it will be able to use the index where it can use the index for the sequentially listed clauses.
It will probably be possible to create statements which are logically equivalent to sequentially ordered statements, but which the database is unable to convert. This is because producing logical equivalents can be a non-trivial task.