Can someone help me in understanding below ?
why window sort pushed rank fetching records faster over sort order by stop key?Also omem/1mem/used-mem?
SELECT /*+ GATHER_PLAN_STATISTICS */ id
FROM (SELECT id, RANK () OVER (ORDER BY id) mtp_rank
FROM mtp_trs3
WHERE marker = 'J|SECTEST|78852671A865C93FB4A84AFFFF548354')
WHERE mtp_rank <= 101
select * from table(dbms_xplan.display_cursor(sql_id=>'a6qry2h3symph',FORMAT=>'ALLSTATS LAST'));
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 101 |00:00:00.18 | 46331 | 46321 | | | |
|* 1 | VIEW | | 1 | 11073 | 101 |00:00:00.18 | 46331 | 46321 | | | |
|* 2 | WINDOW SORT PUSHED RANK| | 1 | 11073 | 101 |00:00:00.18 | 46331 | 46321 | 13312 | 13312 |12288 (0)|
|* 3 | TABLE ACCESS FULL | MTP_TRS3 | 1 | 11073 | 11073 |00:00:00.01 | 46331 | 46321 | | | |
-----------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("MTP_RANK"<=101)
2 - filter(RANK() OVER ( ORDER BY "ID")<=101)
3 - filter("MARKER"='J|SECTEST|78852671A865C93FB4A84AFFFF548354')
SELECT /*+ GATHER_PLAN_STATISTICS */ id
FROM (SELECT id, ROWNUM
FROM mtp_trs3
WHERE marker = 'J|SECTEST|78852671A865C93FB4A84AFFFF548354' order by id)
WHERE ROWNUM <= 101
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 101 |00:00:00.33 | 46339 | 46315 | | | |
|* 1 | COUNT STOPKEY | | 1 | | 101 |00:00:00.33 | 46339 | 46315 | | | |
| 2 | VIEW | | 1 | 11073 | 101 |00:00:00.33 | 46339 | 46315 | | | |
|* 3 | SORT ORDER BY STOPKEY| | 1 | 11073 | 101 |00:00:00.33 | 46339 | 46315 | 6144 | 6144 | 6144 (0)|
| 4 | COUNT | | 1 | | 11073 |00:00:00.01 | 46339 | 46315 | | | |
|* 5 | TABLE ACCESS FULL | MTP_TRS3 | 1 | 11073 | 11073 |00:00:00.01 | 46339 | 46315 | | | |
----------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<=101)
3 - filter(ROWNUM<=101)
5 - filter("MARKER"='J|SECTEST|78852671A865C93FB4A84AFFFF548354')