本人水平有限，帮忙优化一下sql，谢谢

如下sql：
select *
from (select rownum rn, t.*
from (select usr_ent_id,
trr_id,
trr_name,
trr_gender,
trr_birthday,
trr_company,
trr_education,
trr_id_no,
trr_main_teach,
trr_mobile,
trr_email,
trr_itm_total_count
from teacherResume --讲师表(大约500数据)
left join reguser --用户表 (大约35万数据)
on trr_ent_id = usr_ent_id
WHERE trr_ent_id in
(select distinct gpm_ent_id_member
from groupmember --用户与用户组及用户组与用户组关系表(大约100万数据)
where gpm_type = 'USR_PARENT_USG'--用户与用户组
and sysdate between gpm_start_timestamp and
gpm_end_timestamp
and gpm_ent_id_group in
(select grp.gpm_ent_id_member
from (select gpm_ent_id_member,
gpm_ent_id_group
from groupmember
where gpm_type = 'USG_PARENT_USG'--用户组与用户组
and sysdate between
gpm_start_timestamp and
gpm_end_timestamp) grp
start with grp.gpm_ent_id_member in
(select tce_ent_id
from tcTrainingCenterTargetEntity --公司与用户组表
where tce_tcr_id = 9)
connect by prior grp.gpm_ent_id_member =
grp.gpm_ent_id_group))
and trr_status is null
AND trr_type = 'INNER'
ORDER BY trr_comment_avg_score desc) t
where rownum <= 10)
where rn > 0

执行计划如下：



说明： 1、每个公司下面有1000个左右用户组
2、用户组有3.6万个左右
3、一个公司可能对应多个用户组
在线等大虾们