Hi,
I am facing a problem with using the indexes created on my database through a hibernate Query by Example.
Here are the details-
TABLE- PERSON
=============
Name Address City Country

The index is on Name column. Now when i use the Example object and query this table the hibernate generated query always reads- where CITY=... and NAME = ...
Because of this ordering of the where clause, the index is not used.

Is there any way by which i can control the order in which the column names appear in the where clause?

I'm surprised that a query analyser will be so silly as to not use an otherwise useful index, simply because of the order of the columns in the where clause. What database are you using which does this?

David

I am using Oracle 9i
I dont have a great deal of experience on databases, but my understanding was as follows-
1. For an index to be used, that column has to be the first one in the where clause. (Am I wrong?)
2. For an index to be used, the column should not be referenced as LOWER() or LIKE

Please let me know your thoughts.

I have no detailed knowledge, but I would have said that you are wrong on (1), and that (2) is a good rule of thumb, but it is often possible to use an index with LIKE if the LIKE clause is anchored to the beginning, e.g. WHERE name LIKE 'A%' can often use an index on name.

Similarly if you want to do WHERE LOWER(username) = ? then you can create an index on the function LOWER(username).

David

Yes, I tried indexing on LOWER(name) but that doesn't show any improvement according to the Oracle SQL Analyzer. Information on the internet showed varying opinions about using LOWER in indexes. So I wasn't really sure that using LOWER would help