create table inv as
select
round(dbms_random.value(1, 100000)) as id,
round(dbms_random.value(1, 100)) as bp_id,
sysdate+round(dbms_random.value(1, 1000)) as dateinvd,
round(dbms_random.value(1, 100000)) as amount
from dual
connect by level<=100000Table created.
create index inv on inv(dateinvd)Index created.
create or replace function myFunction
return number
is
begin
for i in (select * from dual)
loop
null;
end loop;
return 0;
end;Function created.
explain plan for select * from (
select row_number() over (order by null) as rnum, myFunction(), i.*
from inv i
order by dateinvd desc
) i
where rnum<=100Statement processed.
select * from table(dbms_xplan.display)| PLAN_TABLE_OUTPUT | Plan hash value: 219430819 | ------------------------------------------------------------------------------------ | | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | | ------------------------------------------------------------------------------------ | | 0 | SELECT STATEMENT | | 100 | 7400 | 121 (1)| 00:00:01 | | |* 1 | VIEW | | 100 | 7400 | 121 (1)| 00:00:01 | | |* 2 | WINDOW SORT PUSHED RANK | | 100K| 2050K| 121 (1)| 00:00:01 | | | 3 | TABLE ACCESS STORAGE FULL| INV | 100K| 2050K| 121 (1)| 00:00:01 | | ------------------------------------------------------------------------------------ | Predicate Information (identified by operation id): | --------------------------------------------------- | 1 - filter("RNUM"<=100) | 2 - filter(ROW_NUMBER() OVER ( ORDER BY NULL )<=100) |
|---|
explain plan for select * from (
select myFunction(), i.*
from inv i
order by dateinvd desc
) i
where rownum<=100Statement processed.
select * from table(dbms_xplan.display)| PLAN_TABLE_OUTPUT | Plan hash value: 708563453 | -------------------------------------------------------------------------------------------------------- | | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | | -------------------------------------------------------------------------------------------------------- | | 0 | SELECT STATEMENT | | 100 | 6100 | | 771 (1)| 00:00:01 | | |* 1 | COUNT STOPKEY | | | | | | | | | 2 | VIEW | | 100K| 5957K| | 771 (1)| 00:00:01 | | |* 3 | SORT ORDER BY STOPKEY | | 100K| 2050K| 3552K| 771 (1)| 00:00:01 | | | 4 | TABLE ACCESS STORAGE FULL FIRST ROWS| INV | 100K| 2050K| | 121 (1)| 00:00:01 | | -------------------------------------------------------------------------------------------------------- | Predicate Information (identified by operation id): | --------------------------------------------------- | 1 - filter(ROWNUM<=100) | 3 - filter(ROWNUM<=100) |
|---|