SELECT *
FROM (SELECT sq.*, ROWNUM AS rn
FROM ( SELECT /*+ index(am) first_rows*/am.id,
am.customerid,
am.customername,
am.sex,
am.age,
TO_CHAR (am.birthday, 'YYYY-MM-DD') birthday,
am.nativeplace,
am.nation,
am.province proCode,
am.city citCode,
am.district disCode,
am.address,
am.telephone,
am.email,
TO_CHAR (am.register_date, 'YYYY-MM-DD HH24:MI:SS')
register_date,
am.mobilephone,
am.associatcode,
am.relation,
am.organizecode,
am.remarks,
am.integral,
am.idcard,
am.logincount,
am.salt,
am.member_type,
am.status,
am.org_id,
mor.org_code orgCode,
mor.org_name orgName,
me.RANK expertRank,
me.reference_no expertReferenceNo,
me.doctor_no expertDoctorNo,
me.product_line expertProductLine,
me.recommend expertRecommend,
me.flag expertFlag,
me.intro expertIntro,
am.backcontact,
am.backcontact_phone
FROM member am
LEFT JOIN member_expert me ON am.id = me.member_id
LEFT JOIN organize mor ON am.org_id = mor.org_id
ORDER BY am.register_date DESC,id desc
) sq where rownum<=100) row_limit
WHERE row_limit.rn >=1
执行计划:
Plan hash value: 451937818
---------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 100 | 351K| | 11M (1)| 38:18:49 |
|* 1 | VIEW | | 100 | 351K| | 11M (1)| 38:18:49 |
|* 2 | COUNT STOPKEY | | | | | | |
| 3 | VIEW | | 14M| 48G| | 11M (1)| 38:18:49 |
|* 4 | SORT ORDER BY STOPKEY | | 14M| 4536M| 4735M| 11M (1)| 38:18:49 |
| 5 | NESTED LOOPS OUTER | | 14M| 4536M| | 10M (1)| 34:55:29 |
| 6 | NESTED LOOPS OUTER | | 14M| 3620M| | 524K (1)| 01:44:49 |
| 7 | TABLE ACCESS BY INDEX ROWID| MEMBER | 14M| 2441M| | 523K (1)| 01:44:43 |
| 8 | INDEX FULL SCAN | IDX_MEMBER_RD | 14M| | | 137K (1)| 00:27:34 |
| 9 | TABLE ACCESS BY INDEX ROWID| MEMBER_EXPERT | 1 | 85 | | 1 (0)| 00:00:01 |
|* 10 | INDEX RANGE SCAN | IDX_MEMBER_EXPERT_ID | 1 | | | 0 (0)| 00:00:01 |
| 11 | TABLE ACCESS BY INDEX ROWID | ORGANIZE | 1 | 66 | | 1 (0)| 00:00:01 |
|* 12 | INDEX RANGE SCAN | IDX_ORGANIZE_ORGID | 1 | | | 0 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ROW_LIMIT"."RN">=1)
2 - filter(ROWNUM<=100)
4 - filter(ROWNUM<=100)
10 - access("AM"."ID"="ME"."MEMBER_ID"(+))
12 - access("AM"."ORG_ID"="MOR"."ORG_ID"(+))
强制走了索引,执行计划更难看,有没有办法把执行计划中的SORT ORDER BY 去掉?