Rownum in Select W/ Distributed Database

[EssJayKay]

I have the following query, which when executed on a single database returns values in teh corect order, most recent inserted to least recent inserted. When I try the same query on a database distributed over 2 machines and joined at a view layer, the returned rows are reversed, instead of LIFO, it returns FIFO.

select * from all_trades_tbl
where date = sysdate
and sym = input_sym
and rownum <= 21
order by seqno desc

Any Clues?

 

[sem]

The order of returned records does not depend on insert order. The fact that in most cases they're returned in the same order is nothing more than fortuity (the PROBABILITY is high enough because of internal architecture, but it's still only a probability).
Though, your query contains ORDER BY clause, so the order should be guaranteed. Can you provide your VIEW code (does it contain ORDER BY?) and seqno datatype (is it CHAR or NUMBER)? Regards, Dima

 

[EssJayKay]

Based on the order by clause they should, they single database returns as such:

SYM LINETIME PUBSEQNO
----------------- ---------- ----------
A 14194300 1650447
A 14193900 1649924
A 14193700 1649740
A 14192300 1648030
A 14190500 1645963
A 14184500 1643485
A 14183400 1642034
A 14182200 1640573
A 14181200 1639443
A 14180600 1638632
A 14180400 1638446


linetime being 14:19:43

the distributed returns:

A 9361000 49362
A 9360200 48424
A 9355800 48109
A 9354900 47267
A 9354600 46964
A 9351800 43906
A 9345900 42041
A 9344200 40667
A 9341800 38551
A 9335400 36725

 

[carp]

By any chance, are your databases running in different timezones?

 

[carp]

Disregard last post - just realized your time column has nothing to do with the sort!

Looking at your examples, it appears that the second list is also sorted in descending order, so how does this represent FIFO?

If pubseqno is a character field, then '4' would sort with a higher value than '1'. If this is the case, then change your order by clause to
ORDER BY to_number(pubseqno) DESC

 

[EssJayKay]

the data represents timestamped transactions. it is the same query, one done on the database table directly, one through the joined layer of the entire ditributed database. The data comes in based on linetime so linetime=9335400 is the 1st transaction and linetime=14194300 the last transaction received. basically the query is exactly the same, the difference is one is done on the table directly the other through a view layer which joins 2 databases on diferent machines, but all of these transactions reside on on machines database. when rownum is increased in the first instance more prior transaction are returned. when rownum is increased in the 2nd instance more future transactions are returned. although the output is sorted correctly in descending order, the transactions chosen by the sleect is different based on the fact of the view layer which joins the two tables. since the join adds no records from the 2nd database, the same data set should be retreived.

 

[EssJayKay]

ps - linetime is a NUMBER

 

[sem]

Could you provide the real query, as I really doubt that you may have a table with the column called DATE. Column names are also not the same in results and query. And fianlly, both sets are ordered by LINETIME and PUBSEQNO in reverse order, so what is the problem? Regards, Dima

 

[EssJayKay]

here is the query for the 1st response (Direct to Table)

select sym, linetime, pubseqno from all_trades_tbl
where mdb_date = '10-DEC-2002'
and sym = 'A'
and exid= 'N'
and reftype is null
and nvl(cextype,'0') != 'X'
and nvl(cextype,'0') != 'E'
and rownum <= 11
order by pubseqno desc;

---------------------------------------------
here is the query for the 2nd response (Through Join on the Table)

select sym, linetime, pubseqno from all_trades
where mdb_date = '10-DEC-2002'
and sym = 'A'
and exid= 'N'
and reftype is null
and nvl(cextype,'0') != 'X'
and nvl(cextype,'0') != 'E'
and rownum <= 11
order by pubseqno desc;

---------------------------------------------
The difference is they are the same data set and the same query hsould return the same results, but through the join, the table is retrieving rownums in the opposite direction. one data set is from the morning 1st data points being inserted and teh other being the final data points being insered (which is what i want). these tables can have millions of entries and i was using rownum to speed up the fetch. once we ahd to move to a larger database (distibuted) and use the join of two tables, the query fails.

 

[carp]

Try removing the rownum constraint (and rownum <= 11) and see if the results look correct. It may be that one data set that has more than 11 records is being retrieved first from one database. Since there are enough rows to satisfy your WHERE clause, Oracle stops at that point. If this is the case, then you may have to get all rows from both sets and THEN constrain on the rownms:

SELECT * FROM (query1
UNION ALL
query2)
WHERE rownum <= 11;

 

[EssJayKay]

i know it will work if i remove the rownum. data sets are exactly the same, just that rownum has different effect when using the join. is there any way around this?

 

[carp]

Well, maybe you could something like try:

SELECT * FROM
(select sym, linetime, pubseqno from all_trades_tbl
where mdb_date = '10-DEC-2002'
and sym = 'A'
and exid= 'N'
and reftype is null
and nvl(cextype,'0') != 'X'
and nvl(cextype,'0') != 'E'
union all
select sym, linetime, pubseqno from all_trades
where mdb_date = '10-DEC-2002'
and sym = 'A'
and exid= 'N'
and reftype is null
and nvl(cextype,'0') != 'X'
and nvl(cextype,'0') != 'E'
)
ORDER BY pubseqno
WHERE rownum <= 11;

?