Oracle table join causes sorting disorder

Hot weather, weekend at home knocking at the code to find a strange SQL, when using the LEFT JOIN table to join the query, a to go to join B, before the order of a table row, suddenly confused, the first row of records ran to the end, before the descending order to become 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 joins Tmerinfo B on A. STOREID = B.taccountid
) A
Left joins Tbankmore C on A. CARD_BANK_TP = c. ID
WHERE
A. R <=20
) A
Left JOIN tpransaction tr on a.id = tr. Torderid (execute this sentence before you follow plattime in descending-to-ascending order).

It's been a long time without thinking about the solution, and suddenly it's like looking at this SQL execution plan, we know that table joins have 3 ways, nested inner loops, sort merge connections, Hash joins, according to our SQL case, because the ID of a table and the Torder of B table are connected, the primary key of a table must be sorted, The default is ascending, and the Oracle optimization engine is definitely using a sort merge connection algorithm that causes the problem to occur. The workaround is to set up a descending index for the Torderid field in the B table, so that the merging will not only improve the efficiency of SQL execution, but also avoid the problem just now!

Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.

Oracle table join causes sorting disorder