Optimize SQL, return fewer rows, NL is much faster than hash

SQL as follows

Select   as Sortno    from Engineering_type T    Left Join engineering_type tt      on =  where=0and   =0Order   by Sortno, T.sort

SQL is simple, equivalent to self-connected, return rows 12 rows, very small, but run around 5s to produce results

Take a look at the execution plan

As you can see, the table and table go a hash join, our general rule when the return row number is large, more than million lines, usually use a hash join, in the return of the number of rows is rarely used NL connection

Modify SQL

Select /*+ USE_NL (T,TT) leading (t)*/T.id, T.value, Tt.sort asSortno fromEngineering_type T Left JoinEngineering_type TT onT.parentid=tt.idwhereT.delete_flag= 0    andT.hasson= 0 Order  bySortno, T.sort

The implementation plan is as follows

The execution plan changed, and the hash became NL.

The execution speed of SQL is significantly improved, and the return implementation is 0.03 seconds or so, seconds kill.

When SQL chooses how to connect, the first thing to do is to look at the number of rows returned by the driver table and the driver table, which is the most basic judgment.

Optimize SQL, return fewer rows, NL is much faster than hash