This Blog is trying to demonstrate that the outcome of this new feature is varied with "ORDER BY" clause.
Note: Tested in Oracle 12.1.0.2.0 on AIX, Solaris, Linux.
1. Build Test
drop table base_tab cascade constraints;
create table base_tab
(
id number(12) not null,
seq number(9)
);
create index base_tab#ix_1 on base_tab (seq, id);
create index base_tab#ix_2 on base_tab (id);
drop table date_tab cascade constraints;
create table date_tab
(
id number(12),
base_id number(12),
come_date date ,
constraint date_tab#pk primary key (base_id, come_date) enable validate
) organization index;
insert into base_tab (id, seq) values (1, 11111);
insert into base_tab (id, seq) values (2, 22222);
insert into base_tab (id, seq) values (3, 33333);
insert into base_tab (id, seq) values (4, 4444);
insert into date_tab (id, base_id, come_date) values (11, 1, to_date('2010-01-11', 'yyyy-mm-dd'));
insert into date_tab (id, base_id, come_date) values (33, 3, to_date('2030-03-13', 'yyyy-mm-dd'));
insert into date_tab (id, base_id, come_date) values (44, 4, to_date('2040-04-14', 'yyyy-mm-dd'));
commit;
2. Run Test
--*** test 1: returns 2 rows without order by *** --
with
function get_max_date_sqlwith (p_id in number) return date is
l_date date;
begin
select come_date into l_date from (
select come_date from date_tab where base_id = p_id
order by come_date desc
) where rownum = 1;
return l_date;
end;
select b.id
,(select get_max_date_sqlwith(b.id) from dual) cdate
from base_tab b
where rownum <= 2
-- order by b.id
/
ID CDATE
--- -----------
4 14-APR-2040
1 11-JAN-2010
--*** test 2: returns only 1 row with order by *** --
with
function get_max_date_sqlwith (p_id in number) return date is
l_date date;
begin
select come_date into l_date from (
select come_date from date_tab where base_id = p_id
order by come_date desc
) where rownum = 1;
return l_date;
end;
select b.id
,(select get_max_date_sqlwith(b.id) from dual) cdate
from base_tab b
where rownum <= 2
order by b.id
/
ID CDATE
--- -----------
1 11-JAN-2010
--*** test 3 with_plsql hint: returns 2 rows without order by *** --
select /*+ with_plsql */ * from
(with
function get_max_date_sqlwith (p_id in number) return date is
l_date date;
begin
select come_date into l_date from (
select come_date from date_tab t where t.base_id = p_id
order by come_date desc
) where rownum = 1;
return l_date;
end;
select b.id, get_max_date_sqlwith(b.id) cdate
from base_tab b) x
where rownum <= 2
-- order by x.id
/
ID CDATE
--- -----------
4 14-APR-2040
1 11-JAN-2010
--*** test 4 with_plsql hint: returns only 1 row with order by *** --
select /*+ with_plsql */ * from
(with
function get_max_date_sqlwith (p_id in number) return date is
l_date date;
begin
select come_date into l_date from (
select come_date from date_tab t where t.base_id = p_id
order by come_date desc
) where rownum = 1;
return l_date;
end;
select b.id, get_max_date_sqlwith(b.id) cdate
from base_tab b) x
where rownum <= 2
order by x.id
/
ID CDATE
--- -----------
1 11-JAN-2010
3. Standalone PL/SQL function
--*** test 5: returns 2 rows without order by *** --
create or replace function get_max_date (p_id in number) return date is
l_date date;
begin
select come_date into l_date from (
select come_date from date_tab where base_id = p_id
order by come_date desc
) where rownum = 1;
return l_date;
end;
/
select b.id
,(select get_max_date(b.id) from dual) cdate
from base_tab b
where rownum <= 2
-- order by b.id
/
ID CDATE
--- -----------
4 14-APR-2040
1 11-JAN-2010
--*** test 6: returns 2 rows with order by *** --
select b.id
,(select get_max_date(b.id) from dual) cdate
from base_tab b
where rownum <= 2
order by b.id
/
ID CDATE
--- -----------
1 11-JAN-2010
2
4. 12c UDF Pragma PL/SQL function
--*** test 7: returns 2 rows without order by *** --
create or replace function get_max_date_UDF (p_id in number) return date is
l_date date;
PRAGMA UDF;
begin
select come_date into l_date from (
select come_date from date_tab where base_id = p_id
order by come_date desc
) where rownum = 1;
return l_date;
end;
/
select b.id
,(select get_max_date_UDF(b.id) from dual) cdate
from base_tab b
where rownum <= 2
-- order by b.id
/
ID CDATE
--- -----------
4 14-APR-2040
1 11-JAN-2010
--*** test 8: returns 2 rows with order by *** --
select b.id
,(select get_max_date_UDF(b.id) from dual) cdate
from base_tab b
where rownum <= 2
order by b.id
/
ID CDATE
--- -----------
1 11-JAN-2010
2
