-
Basic Ques. On Using Index
Hai,
Iam having Com.Index on StockMaster Table as
ItemId,Batchno,Length,Width,LoadFactor,Twist,Prodtype
I want to know whether My Query should be in same order ???
Thanks in advance.
-
I assume you're using thhe cost based optimiser these days.
No, the columns do not need to appear in your WHERE clause in the same order as the columns do in your index for the index to be used. I've certainly found this to be true in 8.1.7.4 at any rate. However, why take my word for it? Do some explain plans for your query, varying the order in which the columns appear in your WHERE clause.
-
Orader matters ?
I have a different understanding. It is the query fields/coulmns that matters rather than the order in which these fields are referenced in the where clause, I guess.
In Sathy's example:
SQL> select (whatever fields)
from StockMaster
where ItemId = 5000 and
Batchno = 45 and
Length = 20 ;
is same as as the following one.
SQL> select (whatever fields)
from StockMaster
where Length = 20 and
Batchno = 45 and
ItemId = 5000 ;
Both the above queries make use of the index, because the first columns of the index are refrenced in the where clause. Order does not matter.
If the 'ItemId' is not in the where clause and rest of the index columns are refrenced in the where clause, in order, even then the index will not be utilized for querying.
What you say guys ?
-Hidayath
-
Re: Orader matters ?
Originally posted by Hidayath
If the 'ItemId' is not in the where clause and rest of the index columns are refrenced in the where clause, in order, even then the index will not be utilized for querying.
Depends ... later versions of Oracle can use the index ... see documentation for "index skip scan"
-
Originally posted by hacketta1
I assume you're using thhe cost based optimiser these days.
No, the columns do not need to appear in your WHERE clause in the same order as the columns do in your index for the index to be used. I've certainly found this to be true in 8.1.7.4 at any rate. However, why take my word for it? Do some explain plans for your query, varying the order in which the columns appear in your WHERE clause.
Can u explain abt explain plans ..How to do it..
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|