In index1 6 fields are there. but I knew only 6th field from and to values.
I want to use the index1 how to use?

regards,
juvenile

Take 5 * 2 = 10 variables

store the minimum and maximum values with set.min() and set.max() functions. Use them to use index1 along with 6th field.

In index1 6 fields are there. but I knew only 6th field from and to values...I want to use the index1 how to use?...

Do I understand correctly that you know only the sixth most significant index position ? That is the most significant five index positions are not known.

If this is the case I am sure that you would get at least as good if not better results by not using this index. I think most versions oracle would actually ignore the index even though it was specified in Baan sql and passed as a hint to oracle. Unless oracle knew a good index it would do a full table scan.

I suspect that other smart RDBMS would behave in similar fashion.

If you do use the index you will have code something like this:
WHERE txxyz999._index1 inrange {:low1,:low2,:low3,:low4,:low5,:knowL}
and {:hi1, :hi2, :hi3, :hi4, :hi5, :knowH}

Please confirm requirement.

Terry

Hi

I think the idea of getting minimum and maximum of the starting five fields of the index and then putting it for selection is not good. Becuase since the first field value itself is not know then the very purpose of fetching data with that index is defeated. It is like you are scanning the whole table again. And may be it may take more time.

Better in where condition you directly match the field for example :

where tablefield.6 inrange :know.field.f and :know.field.t

This will work better than the first solution.

You can give a try to this.

Regards
Vinod

...I think the idea of getting minimum and maximum of the starting five fields of the index and then putting it for selection is not good. Becuase since the first field value itself is not know then the very purpose of fetching data with that index is defeated. It is like you are scanning the whole table again. And may be it may take more time...

V_Chandra

Are you saying anything substantially different to what I said. I thought I went into a fair bit of explaination as to the undesirability of using the low order index positions if the high order are not known.

In case I got the question wrong, I then actually answered the question that was put after having given the advice that it should not used and I also pointed out that if the code were passed to a database like Oracle it would probably be ignored anyway.

Terry

Terry

I am sorry, i think i have over sighted your solution, any ways it was to the solution that Rita has proposed. If i would have properly read the solution that you have given i would have not posted mine. I think i have just read the example what you have given.

Actually i also meant the same what you said.

Vinod