Thanks
That made sense which was rownum doing as well. The sqlplus extra fetch
confused even more the matter though because it made the rownum looked like
non-working :-)
Thanks again
On Mon, Jul 18, 2016 at 9:28 PM, Nicholas Krasnov <nicholaskrasnov@xxxxxxxxx
wrote:
View merging is the reason why the function gets executed each time an
object field is being referenced. So, as another workaround use
/*+no_merge */ hint.
Here the snippet of trace filed made with the 10053 trace event enabled
1) Without /*+no_merge */ hint
Registered qb: SEL$F5BB74E1 0x147924b8 (VIEW MERGE SEL$1; SEL$2)
query block SEL$1 transformed to SEL$F5BB74E1 (#0)
SELECT SYS_OP_ATG("NK"."F1"(),1,2,2) "S.I1"
, SYS_OP_ATG("NK"."F1"(),2,3,2) "S.I2" --- we end up
calling the function as many times as many references to the object's
FROM "SYS"."DUAL" "DUAL" --- fields we
make in the select list
2) With /*+no_merge*/ hint
final query after transfomation with /*+no_merge*/ hint
SELECT "X"."S"."I1" "S.I1"
, "X"."S"."I2" "S.I2"
FROM (SELECT /*+ NO_MERGE */ "NK"."F1"() "S"
FROM "SYS"."DUAL" "DUAL") "X"
The function is being called only once no matter how many times we
reference object's fields in the select list.
On Mon, Jul 18, 2016 at 3:54 PM, Ls Cheng <exriscer@xxxxxxxxx> wrote:
Hi
One of my developer wrote a function to return an object type, composed
of 2 attributes, he then print the attribute in a query.
It looks like this (example code):
CREATE OR REPLACE TYPE ot AS OBJECT
(
o1 number,
o2 number
);
/
CREATE OR REPLACE FUNCTION get_ot
RETURN ot
IS
PRAGMA AUTONOMOUS_TRANSACTION;
l_ot ot;
BEGIN
l_ot := ot(NULL, NULL);
SELECT 1, 2
INTO l_ot.o1, l_ot.o2
FROM dual;
INSERT into t1 values (systimestamp);
commit;
RETURN l_ot;
END get_ot;
/
SQL> select
2 x.z.o1,
3 x.z.o2
4 from (select get_ot z from dual)x;
Z.O1 Z.O2
---------- ----------
1 2
It turns out that the function is execute twice, once per attribute. I
wonder if there is any other more efficient way to accomplish this?
This is going to return a row only so I dont think we need to dig into
plsqsl table functions.
Thanks
--
Best regards,
Nicholas Krasnov
