Oracle table joins cause sorting disorder __oracle

Weather hot, weekend at home knocking code found a strange SQL, when using the LEFT JOIN table to join the left-hand query, a-B, before a-table row a good order, suddenly confused, the first line of records ran to the last, the descending order into ascending. Simulate SQL as follows

SELECT
A. *
From
(
SELECT
A. *
From
(
SELECT
A. *,
RowNum R
From
(
SELECT
*
From
Tporder A
WHERE
To_char (A. Plattime, ' yyyy-mm-dd ') >= ' 2015-08-09 '
and To_char (A. Plattime, ' yyyy-mm-dd ') <= ' 2015-08-09 '
and A. TYPE in (0, 1, 23, 24, 25, 30)
and A. Busi_type = 1
ORDER BY
Plattime DESC
) A
Left JOIN Tmerinfo B on A. STOREID = B.taccountid
) A
Left JOIN Tbankmore C on A. CARD_BANK_TP = c. ID
WHERE
A. R <=20
) A
Left JOIN tpransaction tr on a.id = tr. Torderid (before executing this phrase, follow Plattime descending--> Ascending).

Toss for a long time did not think of the solution, suddenly like to look at this SQL implementation plan, we know that table is connected in 3 ways, nested within loops, sort merge joins, hash connections, according to our SQL case, because of the ID of table A and the torder of table B to do the connection, a table's primary key is definitely sorted, The default is ascending, and the Oracle optimization engine must be using an algorithm to sort merge joins, causing problems to occur. The workaround is to set up a descending index for the Torderid field in table B so that when merging, you can not only improve the efficiency of SQL execution, but also avoid the problem that you just made.