Oracle Query Table Repeat the efficiency problem when recording the field record ...

xiaoxiao2021-03-06  92

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, ''), P_ROLL) IN (SELECT M_NO, SQ, NVL (PT, ''), p_roll from SELECT M_NO, SQ, PT, P_ROLL, ROW_NUMBER () OVER (Partition By M_NO, SQ, PT, P_ROLL ORDER BY M_NO, SQ, PT, P_ROLL) RN from Tr ORDER BY M_NO, SQ, PT, P_ROLL) T where rn> 1) And tr_date> to_date ('2004-04-01', 'YYYY-MM-DD') Summary: Reasonable Analysis Function Using Oracle can greatly improve the efficiency of SQL.

转载请注明原文地址:https://www.9cbs.com/read-123066.html

New Post(0)