大家好,
我有下面一个sql,index已经都优化好了,我想取出结果集的前20行,并且还是在order by以后再取出
select * from
(select distinct T0.NSID as ColAlias1, T0.ID as ColAlias2, T0.VERSION as ColAlias3, T0.NAME as ColAlias4,
T1.NAME as ColAlias5, T0.INITIALEFFECTIVEDATE as ColAlias6, T0.TERMINATIONDATE as ColAlias7, T0.STATUS as ColAlias8,
T2.NAME as ColAlias9, T3.NAME as ColAlias10, T4.NAME as ColAlias11, T1.SHORTNAME as ColAlias12, T5.IDCONTRATEXTERNEFORMATRIB as ColAlias13,
T5.IDCONTRATEXTERNE as ColAlias14, T5.NUMIFIP as ColAlias15, T6.IDENTIFIANTORCODEAGENCE as ColAlias16
from AWFCONTRACT T0,
AWFPERSON T1,
AWFPRODUCT T2,
AWFISSUINGCOMPANY T3,
AWFHIERARCHY T4,
AWFCUSTOMIZATIONCONTRACTDA356F T5,
AWFBROKER T6,
AWFBROKERASSOCIATIONONCONTRACT T7,
AWFCONTRACTBROKERDATA T8,
AWFCONTRACTBROKERDATA_BROK54F6 T9
where ((((T0.BROKERDATA_NSID = T8.NSID) and (T0.BROKERDATA_ID = T8.ID) and (-T0.BROKERDATA_VERSION = T8.VERSION)))
and T8.NSID=T9.LONSID and T8.ID=T9.LOID and (((T7.BROKER_NSID = T6.NSID) and (T7.BROKER_ID = T6.ID)))
and (T7.ENDDATE >= :1) and (((T0.DATAEXTENSION_NSID = T5.NSID) and (T0.DATAEXTENSION_ID = T5.ID) and (-T0.DATAEXTENSION_VERSION = T5.VERSION)))
and (T0.STATUS:9))and ((T0.SUBSCRIBER_NSID=T1.NsId) and (T0.SUBSCRIBER_ID=T1.Id)) and ((T0.OFFEREDENTITY_NSID=T2.NsId) and (T0.OFFEREDENTITY_ID=T2.Id))
and ((T0.ISSUINGCOMPANY_NSID=T3.NsId) and (T0.ISSUINGCOMPANY_ID=T3.Id)) and ((T0.DISTRIBUTIONUNIT_NSID=T4.NsId)
and (T0.DISTRIBUTIONUNIT_ID=T4.Id)) and T9.NSId=T7.NSID and T9.Id=T7.ID and T9.Version= - T7.VERSION and (T1.H_ISCURVERS = 1)
and (T2.H_ISCURVERS = 1) and (T3.H_ISCURVERS = 1) and (T4.H_ISCURVERS = 1) and (T0.H_ISCURVERS = 1) and (T5.H_ISCURVERS = 1) and (T6.H_ISCURVERS = 1)
and (T0.H_ISKILLED = 0) and (T5.H_ISKILLED = 0) and (T6.H_ISKILLED = 0) and (T7.H_ISKILLED = 0) and (T8.H_ISKILLED = 0) and ( (T0.H_CLID=218109553)
or (T0.H_CLID=218956153) or (T0.H_CLID=218956154) or (T0.H_CLID=218113103) or (T0.H_CLID=218497198)) and (T5.H_CLID=219086894) and (T6.H_CLID=219086880)
Order By T0.NAME)
where ROWNUM <= 20;
oracle的工作原理是不是想把结果集放到一个临时表中进行order by,然后再取出前20行呢???
能不能有一个方法,在T0.NAME上建立个index,然后直接通过index取出前20行呢???
今天查了很久了,还是不清楚order by的工作原理,目的就是想避开取出结果集,而直接通过index取出前20行。。。
谢谢指点
|