Optimization of an SQL statement and turning Oracle pages

Optimization of an SQL statement:
Recently maintained a project. One of the SQL statements seriously affects the performance and needs to query two massive tables (icasuser has 3000 records and logon_info has nearly 0.4 million records .) the page is not displayed because it takes a long time.
The following test time is the time in the local database (the local oralce database has a small amount of data, and icasuser only has 3000 Records)
The first SQL statement, with a time of 5 S
Select count (*) from (
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 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 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-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)
)

I. Optimize the first union clause first:
-- 0.6 s
 
Select count (*) from (select
M. userid as userid, M. RSA as RSA, M. roleid as roleid,
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) Gg where usid = M. userid ))
Continue optimization: replace exists with in:
-- 0.04 s:
Select count (*) from (select
M. userid as userid, M. RSA as RSA, M. roleid as roleid,
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 userid in (select usid 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 )))
Ii. Rewrite the original SQL statement:
-- 0.8 s
Select count (*) from (
Select
M. userid as userid, M. RSA as RSA, M. roleid as roleid,
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 userid in (select usid 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)
) Or (sysdate-to_date (TRIM (M. edate), 'yyyy-MM-DD ')> 90)

Continue: change or to union:
-- 0.047 s

Select count (*) from (select
M. userid as userid, M. RSA as RSA, M. roleid as roleid,
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 userid in (select usid 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 ))
Union
Select
M. userid as userid, M. RSA as RSA, M. roleid as roleid,
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 (sysdate-to_date (TRIM (M. edate), 'yyyy-MM-DD ')> 90)

The optimized SQL statement is 5/0. 04 = 120 times faster than the original one.
Summary:
1: many materials say that exists is faster than in, but I find that in is faster. I don't know if it is true?
2: If you are pursuing speed, you can consider rewriting the or after where into union.

------

The system uses the paging class to compare the garbage. It first reads all the database data to a rowset, and then obtains 1 to 10, 11 to 20 in the rowset .... Record. The legend is still written by a master, dizzy. Oracle's paging SQL statement is written as follows. I will re-write a paging class when I have time.

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