Use-index

[promem_da]

Hi,

I'm also using same type of query as mentioned above:

for each po USE-INDEX po-index
where po.cd-whs = 10
and po.estatus = 2
no-lock:

... procedure ...
end.

Find last po USE-INDEX po-index
where po.cd-whs = 10
and po.estatus = 2 no-lock no-error.

po-index is the Primary-Unique index on the table and hence i want to use that index while querying using for each or find last statement.

There are other index (incb-index) having the same fields cd-whs and estatus which is a not primary index.

Hence am forcing to use primary-unique index (po-index) to fetch the correct record from database using USE-INDEX.

But my question here is when i did XREF the "SEARCH" still shows the incb-index only, even though i specified "USE-INDEX po-index" to use it?

Does that mean USE-INDEX has no significance.

Can someone please throw some light on it..!

 

[jdpjamesp]

Let me get this straight... you have 2 indexes with the same fields, one is primary unique, the other isn't? Why? Or more to the point: How?

 

[tamhas]

I think you'ld better tell us what is in each index as a first step.

 

[TomBascom]

You seem to think that the "primary" attribute of an index means that you must use it.

"Primary" is nothing more than an arbitrary attribute that is used by the compiler as a tie-breaker when two indexes are otherwise equally useful for satisfying a WHERE clause. Other than that it has no meaning or usefulness. In many ways it is merely a way for the schema designer to override the next tie-breaker when everything else is equal -- alphabetical order. (The "primary" index is also the default index when no WHERE clause is specified or when performing a binary dump -- but if you think about it those are both cases of "everything else is equal").

Generally speaking -- USE-INDEX is anathema. The compiler is almost always smarter than you are. If you think you are smarter than the compiler you should document exactly why your choice is better and include lots of test data and and test results for the poor maintenance programmer who comes along later to refer to. IMHO you will only *very* rarely be able to actually justify the use of USE-INDEX.

Also, when specified in a FOR EACH it limits Progress to just that single index and forces v6 index selection rules, eliminating the potential use of multiple indexes.

It is pretty much always a bad idea to write USE-INDEX. Even if you have the misfortune of having been exposed to certain well known and widely distributed vendor applications that do so freely.

FIND LAST is also an abomination. Made worse by tossing USE-INDEX in there. The use of USE-INDEX in a FIND LAST reinforces the observation that you are, in fact, returning a non-unique record and that the order of records in that result set is important. So the LAST record is now magic. FIND has no way of specifying order -- there is no BY clause. USE-INDEX can be used to enforce an order but it is less than obvious what that order might be. A better approach:

define query q for customer.

open query q for each customer by discount.
get last q.

display customer.

Note: "discount" is not an indexed field and is non-unique -- illustrating the pitfalls of LAST (and FIRST) when used inappropriately. Ask yourself -- what makes this record any "better" than the next to last record in the set? If you can actually answer that then you have an attribute in the record that should be part of the index and which would eliminate the non-uniqueness.