I have this query:


select tibom010.*, tiitm001.dsca:mitm.dsca,
tiitm001.cuni:mitm.cuni, tiitm001.txta:mitm.txta,
tiitm001.eitm:mitm.eitm, tiitm001.umer:mitm.umer,
tiitm001.effe.a:mitm.effe.a,
tiitm001.opol:mitm.opol,
tiitm001.oltm:mitm.oltm, tiitm001.sftm:mitm.sftm, |#441.sn
tiitm001.cpha:mitm.cpha,
tiitm001_s.oltm:sitm.oltm, tiitm001_s.sftm:sitm.sftm, |#441.en
tiitm001_s.kitm:sitm.kitm, tiitm001_s.dsca:sitm.dsca,
tiitm001_s.cuni:sitm.cuni, tiitm001_s.txta:sitm.txta,
tiitm001_s.eitm:sitm.eitm, tiitm001_s.umer:sitm.umer,
tiitm001_s.effe.a:sitm.effe.a, tiitm001_s.opol:sitm.opol
from tibom010, tiitm001, tiitm001 tiitm001_s
where tibom010._index1 = {:mitm.topl} |#441.n
| where tibom010._index1 inrange {:mitm.topl} |#441.so
| and {:item.t} |#441.eo
and tibom010.mitm refers to tiitm001
and tibom010.sitm refers to tiitm001_s
and tiitm001.kitm = tckitm.manufacture


It does not always return the first position on the bill. Yet if I add order by tibom010._index1 it always works - as I would expect. Since this is the primary index why does the above query not always work? Someone once posted something about using the "order by", but I can not find it.

It should use the key u use in where condition, butit depends by the database driver.

are u sure it doesn't works simply adding "order by tibom010._index1 asc" before selectdo?!? (asc should be implicit)

Yes - my post indicates it always works with the order by clause and just like you I thought it was implicit from the where clause. We have been running this code for years and for some reason I could not get it to fail in test, but it does in production and in our finance test company. Could it be caused by fragmentation or something on the Oracle index? More curious than anything.

Are you saying it does not return the first position..or that it returns the set but out of order..?

If it's just out of order, why not put the order by on there? I don't want to just guess (but I will of course), but maybe if the query has several where clauses - really you are presenting several indexes to read by. Which does it pick to order by? Logically, in your mind, the leading where clause is the driving piece, but who knows what confuses the drivers.

Can you trace in oracle what the query is doing?

I used the "order by" to fix the problem - I never thought to see what order they actually returned in, I just knew I did not get position 10. It was just confusing that it worked in one company and not in another - it shattered my illusion on what queries return, thinking the where clause set the primary index and the return order. I must admit I did not do an Oracle trace.

I just re-ran the program - it returns position 30 thru 1020, and then positions 10 and 20. Makes me wonder if a repair of the indexes is needed.

Hi Mark,

i would like to know whether you found a solution for your problem.

We have the same effect after an update from oracle8 to oracle10. When we copy bill of material to a sales order position we don't get the items in the sequence from table tibom010(as it was before the update).
We have to use 'order by' in the copy routine to get the sequence we were used to.

Best regards

Nikos

The only solution I found was to use order by - I went through all of the code and added order by on the select statements. My best guess was that something changed the drivers to where it did not default to index 1 - so I added order by to get the records in the correct order.

Hi Mark,

thank you for your reply. Seems that we will do the same.

Nikos