Optimization of a SQL statement and Oracle Tour

Optimization of a SQL statement: Recently maintained a project, one of the SQL seriously affects performance, requires two massive Table queries (ICASUSER has 3,000 records, and logon_info has nearly 400,000 records.) Due to more time consumption, the page is basically not displayed. The following test time is the time in the local database (small local Oralce database data size, ICASUSER only 3000 records) The first SQL, with time 5Sselect Count (*) from (Select Icasuser.Userid As Userid, Icasuser.rs RSA, icasuser.roleid AS roleid, icasuser.fname as fname, icasuser.lname as lname, icasuser.centerid as centerid, icasuser.status as status, icasuser.priv as priv, icasuser.password as password, icasuser.edate as edate, icasuser. coachid as coachid, icasuser.analystid as analystid, icasuser.trainerid as trainerid, icasuser.title as title, icasuser.action as action FROM icasuser, logon_info WHERE SYSDATE - logon_info.login_time> 90 AND icasuser.userid = logon_info.sbcuid UNION SELECT icasuser .userid AS userid, icasuser.rsa AS rsa, icasuser.roleid AS roleid, icasuser.fname as fname, icasuser.lname as lname, icasuser.centerid as centerid, icasuser.status as status, icasuser.priv as priv, icasuser.password AS PA ssword, icasuser.edate as edate, icasuser.coachid as coachid, icasuser.analystid as analystid, icasuser.trainerid as trainerid, icasuser.title as title, icasuser.action as action FROM icasuser, logon_info WHERE (SYSDATE - TO_DATE (TRIM (icasuser .edate), 'YYYY-MM-DD')> 90 and ICASUSER.USERID NOT IN (SELECT LOGON_INFO.SBCUID from logon_info group by logon_info.sbcuid))

One: First optimize the first UNION clause: - Time 0.6s Select Count (*) from (SELECT M.USERID AS USERID, M.RSA AS RSA, M.ROLEID AS ROLLEID, M.FNAME AS FNAME, M. Lname As Lname, M.Centerid As CenterID, M.Status AS Status, M.Priv As Priv, M.Password As Password, M.edate As Edate, M.coachid As Coachid, M.Analystid As Analystid, M.Trainerid AS Trainerid, M.Title As Title, M.Action As Action from ICASUSER M Where EXISTS (SELECT 'X' from (Select T.userid AS USID from ICASUSER T, LOGON_INFO D Where t.userid = D.SBCUID GROUP BY T.USERID Having max (d.login_time)

Sysdate-90)) 2: Rewind the original SQL statement: - Time 0.8Sselect Count (*) from (SELECT M.USERID AS Userid, M.RSA AS RSA, M.ROLEID AS ROLLEID, M.FNAME AS FNAME, M .lname as lname, M.Centerid AS CenterId, M.Status AS Status, M.Priv As Priv, M.Password As Password, M.edate As EDATE, M.COACHID AS COACHID, M. InternationalStid As AnalystId, M.Trainerid As Trainerid, M.Title As Title, M.ASID AS (SELECT T.USERID AS USID from ICASUSER T, LOGON_INFO D Where t.userid = D.SBCUID GROUP BY T.USERID Having max (d.login_time) 90) Continue: Change OR to Union: - Time 0.047 s

Select Count (*) from (SELECT M.USERID AS Userid, M.RSA AS RSA, M.ROLEID AS ROLLEID, M.FNAME AS FNAME, M.LNAME AS LNAME, M.Centerid As CenterId, M.Status AS Status, M.Priv As Pasword, M.edate As EDATE, M.COACHID AS COACHID, M.AALYSTID AS AnalystId, M.TRAINERID As Trainerid, M.TITLE AS TITLE, M.ACTION As Action from ICASUSER M WHERE Userid in (SELECT T.USERID AS USID from ICASUSER T, Logon_Info D where t.userid = D.SBCUID Group by T.Userid Having Max (D.Login_Time) 90) Optimized SQL is higher than the original fast 5 / 0.04 = 120 times summary: 1: Many materials say that EXISTS is fast than IN, but I found that IN is much more. I don't know if I'm pursuit, I can consider rewriting the WHERE OR to Union --------

The system is compared to the page class, which is to read all the data data into a ROWSET and take 1 to 10, 11 to 20 in the rowset. . . . recording. Legend is still a master written, dizzy. Oracle flipping SQL Writing is as follows. I have time I rewrite a page pages.

Select * from (SELECT ROWNUM NUMROW, FSQL. * from (SELECT T.USERID, T.edate from ICASUSER T ORDER BY T.USERID) FSQL) Where Numrow> 100 And Numrow <200

http://www-128.ibm.com/developerWorks/websphere/techjournal/0306_wosnick/wosnick.html#main