The efficiency problem of repeating field records in the query table ...
- Objective: Take out the fields of M_NO, SQ, P_ROLL, NPL in T_TR, and 5000 pieces of T_TR table record
- Method 1 - Time: 00: 00: 29.09select B.M_NO, B.SQ, B.P_ROLL, B.NPL, B.TR_DATE FROM T_TR B, (Select Count (*), M_NO, SQ, P_ROLL, NPL from t_trgroup by M_NO, SQ, P_ROLL, NPL HAVING Count (*)> 1) Awhere B.M_NO = a.m_no and b.sq = a.sq and b.p_roll = a.p_roll and b.npl = a. NPLORDER BY B.TR_DATE
- Method 2 - Time: n second, finally pay the fork, SELECT M_NO, SQ, P_ROLL, NPL, TR_DATEFROM T_TR Awhere A.RowID! = (SELECT MAX (RowID) from T_TR b where A.m_no = B.m_NO And A.SQ = B.SQ and a.p_roll = b.p_roll and a.npl = b.npl) - Method 3 - Time: 5 seconds, - Using Oracle's Analytical Functions Select M_No, SQ, PT, P_roll, p_yds, p_kgs, tr_date, dept from trwhere (M_NO, SQ, NVL (PT, '