performance of Indexes in Composite key
hi
i have table having composite primary key on four fields.
According to my knowledge oracle has indexed in the sequence of fields i have given like " Primary key(f1,f2,f3,f4) "
Now
first is:
if i give my where condition like: "where f1='some value' and
f2='some value' and f3='some value' and f4='some value'"
Note : in above where condition i have taken the same sequence for fields as it is in compsite primary key.
Second is:
if i give my where condition like: "where f3='some value' and
f2='some value' and f1='some value' and f4='some value'"
Note : in above where condition it have not taken the same sequence for fields as it is in composite primary key.
Now my First question is which query will give me the better performance weather index created by composite primary key will work?
Second Question is if i take single any field in where condition like :
"where f3='some value' "
then the index created by composite primary key will work or not
thanx
Avi
Re: performance of Indexes in Composite key
Quote:
Originally posted by avi kumar
Now my First question is which query will give me the better performance weather index created by composite primary key will work?
Both will result in the same execution plan, hence the same performance in both cases. Composit index will (or will not) be used in botrh cases if optimizer chooses so. Nothing is preventing the usage of composite index in both cases, though.
Quote:
Second Question is if i take single any field in where condition like :
"where f3='some value' "
then the index created by composite primary key will work or not
Generaly, in this case the composite index can not be used unless f3 is the leading column of the index.
However in 9i cost based optimizer might choose to use your composite index even if f3 is not the leading column of the index.