This will be extension of previous two posts (link1
and link2),
in case if you have not gone through that, please read that before to proceed
here.
In case not interested in using hints index_desc, we can utilize a bit
of plsql here, to be index friendly.
rajesh@ORA11G> create or replace function foo(p_owner
varchar2)
2 return sys.odcivarchar2list
3 as
4 l_data sys.odcivarchar2list ;
5 begin
6 select
to_char(object_id,'fm000000')||object_name
7 bulk collect into l_data
8 from (
9 select object_id,object_name,
10 row_number() over(order by
object_id desc) rn
11 from t
12 where owner = p_owner
13 )
14 where rn <= 5 ;
15 return l_data;
16 end;
17 /
Function created.
A Table function to provide top five entries from table ‘T’ for a given
input.
rajesh@ORA11G> with datas(x) as
2 ( select min(owner) from t
3 union all
4 select ( select min(t2.owner) from t t2
5 where t2.owner > t1.x )
6 from datas t1
7 where x is not null )
8 select * from datas
9 where x is not null ;
X
------------------------------
APEX_030200
APPQOSSYS
. . . . .
. . . . .
. . . . .
WMSYS
XDB
31 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 946542369
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows
| Cost (%CPU)|
--------------------------------------------------------------------------------
| 0 | SELECT
STATEMENT | |
2 | 4 (0)|
|* 1 | VIEW | |
2 | 4 (0)|
| 2 | UNION ALL (RECURSIVE WITH) BREADTH
FIRST| | |
|
| 3 | SORT AGGREGATE | |
1 | |
| 4 | INDEX FULL SCAN
(MIN/MAX) | T_IDX
| 1 | 2
(0)|
| 5 | SORT AGGREGATE | |
1 | |
| 6 | FIRST ROW | |
1 | 2 (0)|
|* 7 | INDEX RANGE SCAN
(MIN/MAX) | T_IDX | 1 |
2 (0)|
|* 8 | RECURSIVE WITH PUMP | |
| |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 -
filter("X" IS NOT NULL)
7 -
access("T2"."OWNER">:B1)
8 -
filter("X" IS NOT NULL)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
35 consistent gets
0 physical reads
0 redo size
935 bytes sent via SQL*Net to client
499 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
33 sorts (memory)
0 sorts (disk)
31 rows processed
rajesh@ORA11G> set autotrace off
rajesh@ORA11G>
So given 31 distinct keys available in the owner column, invoking plsql
table function once for each owner will produced 31 recursive calls in total,
we can see that clearly from Autotrace statistics section below.
rajesh@ORA11G> set autotrace traceonly explain statistics
rajesh@ORA11G>
rajesh@ORA11G> with datas(x) as
2 ( select min(owner) from t
3 union all
4
select ( select min(t2.owner) from t t2
5 where t2.owner > t1.x )
6 from datas t1
7 where x is not null )
8 select /*+ cardinality(d2,5) */ d1.x ,
9 to_number(substr( column_value ,1,6)) as
object_id ,
10 substr(column_value,7) as object_name
11 from datas d1 , table(foo(d1.x)) d2
12 where d1.x is not null ;
148 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 93707171
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows
| Cost (%CPU)|
---------------------------------------------------------------------------------
| 0 | SELECT
STATEMENT | |
10 | 106 (1)|
| 1 | NESTED LOOPS | |
10 | 106 (1)|
|* 2 | VIEW | |
2 | 4 (0)|
| 3 | UNION ALL (RECURSIVE WITH) BREADTH
FIRST| | | |
| 4 | SORT AGGREGATE | |
1 | |
| 5 | INDEX FULL SCAN (MIN/MAX) | T_IDX | 1 |
2 (0)|
| 6 | SORT AGGREGATE | |
1 | |
| 7 | FIRST ROW | |
1 | 2 (0)|
|* 8 | INDEX RANGE SCAN (MIN/MAX) | T_IDX | 1 |
2 (0)|
|* 9 | RECURSIVE WITH PUMP | |
| |
| 10 | COLLECTION ITERATOR PICKLER FETCH | FOO
| 5 | 51
(0)|
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 -
filter("D1"."X" IS NOT NULL)
8 -
access("T2"."OWNER">:B1)
9 -
filter("X" IS NOT NULL)
Statistics
----------------------------------------------------------
31 recursive calls
0 db block gets
144 consistent gets
0 physical reads
0 redo size
4722 bytes sent via SQL*Net to client
499 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
33 sorts (memory)
0 sorts (disk)
148 rows processed
rajesh@ORA11G>
rajesh@ORA11G> set autotrace off
And Tkprof output shows that sql invoked from plsql table function
remains index friendly.
SELECT TO_CHAR(OBJECT_ID,'fm000000')||OBJECT_NAME
FROM
( SELECT
OBJECT_ID,OBJECT_NAME, ROW_NUMBER() OVER(ORDER BY OBJECT_ID DESC)
RN FROM T WHERE OWNER =
:B1 ) WHERE RN <= 5
call count cpu
elapsed disk query
current rows
------- ------ --------
---------- ---------- ---------- ----------
----------
Parse 1 0.00
0.00 0 0 0 0
Execute 31 0.01
0.00 0 0 0 0
Fetch 31 0.00
0.00 0 109 0
148
------- ------ --------
---------- ---------- ---------- ----------
----------
total 63 0.01
0.00 0 109 0 148
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 90
(recursive depth: 1)
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max)
Row Source Operation
---------- ---------- ----------
---------------------------------------------------
5 5 5
VIEW (cr=3 pr=0 pw=0 time=38 us
cost=65 size=85742 card=1994)
5 5 5
WINDOW NOSORT STOPKEY (cr=3 pr=0 pw=0 time=31 us cost=65 size=55832
card=1994)
6 6 6
TABLE ACCESS BY INDEX ROWID T (cr=3 pr=0 pw=0
time=16 us cost=65 size=55832 card=1994)
6 6 6
INDEX RANGE SCAN DESCENDING T_IDX (cr=2 pr=0 pw=0 time=17 us
cost=8 size=0 card=1994)(object id 93174)
Note-to-Self.
ReplyDeleteWith 12c in place, using SQL Pattern matching, the above SQL can be rewritten like this.
select owner,object_id,object_name from t
match_recognize(
partition by owner
order by object_id desc
measures
match_number() mno
all rows per match
pattern( b1 b2{4} )
define
b1 as object_id = max(object_id) )
where mno = 1;