Oracle uses subqueries and left-join queries for the same result performance gap. __oracle

This SQL, execute, query all employees of the month's attendance record summary, display all the data takes 2.3 seconds


String sql = "Select Userid,orgid," + subquery, performance too slow, not applicable
"(select username from t_acl_userinfo where Userid=t.userid) Username," +
"(select count (state) from T_chk_manage where Userid=t.userid and state= '" +sqlappend+ ") state01," +
"(select count (state) from T_chk_manage where Userid=t.userid and state= '" +sqlappend+ ") state02," +
"(select count (state) from T_chk_manage where Userid=t.userid and state= '" +sqlappend+ ") state03," +
"(select count (state) from T_chk_manage where Userid=t.userid and state= ' +sqlappend+") state04, "+
"(select count (state) from T_chk_manage where Userid=t.userid and state= '" +sqlappend+ ") state05," +
"(select count (state) from T_chk_manage where Userid=t.userid and state= '" +sqlappend+ ") state06," +
"(select count (state) from T_chk_manage where Userid=t.userid and state= '" +sqlappend+ ") state07," +
"(select count (state) from T_chk_manage where Userid=t.userid and state= '" +sqlappend+ ") state08," +
"(select count (state) from T_chk_manage where Userid=t.userid and state= '" +sqlappend+ ") state09" +
"From T_chk_manage T Group by Userid,orgid have 1=1";


Using the left connection, first identify the basic data, and then connect, Oracle takes 0.2 seconds (speed up nearly 10 times times)


String sql = "Select s.*, userinfo.username as username, state1.state as state01, state2.state as STATE02, State3.stat E as state03, state4.state as STATE04, "+
"State5.state as State05, state6.state as State06, state7.state as State07, state8.state as State08, state9.state as S Tate09 "+
' From ' (select Userid,orgid from T_chk_manage t where 1=1 ' +datasearch+ ' GROUP by userid, OrgID have 1=1 "+search+") s " +
"Left join (select Username,userid from T_acl_userinfo) userinfo on userinfo.userid = S.userid" +
"Left Join" (select Userid,count (state) as state from t_chk_manage where State = ' The ' +datasearch+ ' GROUP by UserID) state 1 on state1.userid = S.userid "+
"Left Join" (select Userid,count (state) as state from t_chk_manage where state = ' +datasearch+ ' GROUP by UserID) state 2 on State2.userid = S.userid "+
"Left Join" (select Userid,count (state) as state from t_chk_manage where state = ' +datasearch+ ' GROUP by UserID) state 3 on State3.userid = S.userid "+
"Left Join" (select Userid,count (state) as state from t_chk_manage where state = ' +datasearch+ ' GROUP by UserID) state 4 on State4.userid = S.userid "+
"Left Join" (select Userid,count (state) as state from t_chk_manage where state = ' +datasearch+ ' GROUP by UserID) state 5 on State5.userid = S.userid "+
"Left Join" (select Userid,count (state) as state from t_chk_manage where state = ' +datasearch+ ' GROUP by UserID) state 6 on State6.userid = S.userid "+
"Left Join" (select Userid,count (state) as state from t_chk_manage where state = ' modified ' +datasearch+ ' GROUP by UserID) state 7 on State7.userid = S.userid "+
"Left Join" (select Userid,count (state) as state from t_chk_manage where State = ' The ' +datasearch+ ' GROUP by UserID) state 8 on State8.userid = S.userid "+
"Left Join" (select Userid,count (state) as state from t_chk_manage where state = ' +datasearch+ ' GROUP by UserID) state 9 on state9.userid = S.userid ";


This SQL query for the month staff attendance detail record, takes 0.4 seconds, (originally use subquery need 7 seconds, raise 20 times times)


Select Day0.*,username, "+dayappend+" from (select Userid,orgid,substr (t.create_time,0,7) from T_chk_manage t where substr (t.create_time,0,7) = ' "+daystart.substring (0, 7) +" ' "+
"GROUP by USERID,ORGID,SUBSTR (t.create_time,0,7) has 1=1" +search+ ") day0" +
"Left join (select Userid,username from T_acl_userinfo) Username on username.userid = Day0.userid" +
"" +sqlappend