HomeНаука и техникаRelated VideosMore From: ORACLE-BASE.com

Efficient Function Calls From SQL (Part 5) : Pipelined Table Functions

114 ratings | 11280 views
The fifth part of a mini-series of videos showing how you can improve the performance of function calls from SQL. In this episode, we compare the performance of conventions table functions with pipelined table functions. For more information see: https://oracle-base.com/articles/misc/pipelined-table-functions https://oracle-base.com/articles/misc/efficient-function-calls-from-sql Website: https://oracle-base.com Blog: https://oracle-base.com/blog Twitter: https://twitter.com/oraclebase Cameo by Mike Dietrich : Blog: https://blogs.oracle.com/UPGRADE Twitter: https://twitter.com/MikeDietrichDE Cameo appearances are for fun, not an endorsement of the content of this video.
Html code for embedding videos on your blog
Text Comments (5)
NAND KUMAR SAHU (10 months ago)
Hi, I am working in 12c Enterprise Edition Release 12.1.0.2.0 . I have created below pipeline table function CREATE TYPE typ_pipe_row AS OBJECT(empid INTEGER,empname VARCHAR2(30)); CREATE TYPE typ_pipe_tab IS TABLE OF typ_pipe_row; CREATE OR REPLACE FUNCTION f_Ret_Pipe_Data RETURN Typ_Pipe_Tab PIPELINED AS l_Name VARCHAR2(32767); BEGIN FOR i IN 1 .. 5 LOOP l_Name := ' test'; Dbms_Lock.Sleep(.5); PIPE ROW(Typ_Pipe_Row('12345', l_Name)); END LOOP; RETURN; END; i try to select from function like this SELECT * FROM table(f_ret_pipe_data); Then what i noticed is ,it is returning whole 5 records in single go in 2.5 sec , not one by one. What could be the reason? can you explain? Thanks Nand Kumar
ORACLE-BASE.com (10 months ago)
Hi. If you look on the screen before I start querying there is some text that says, -- Set array size to 1 to stop SQL*Plus hiding the difference. SET ARRAYSIZE 1 The SQL*Plus tool has a default array size in excess of 1, so it hides what is going on behind the scenes. Cheers Tim...
Vishal R (2 years ago)
Learned something new , thanks mate .
F4sT (2 years ago)
here you got my subscription :)
ORACLE-BASE.com (2 years ago)
Welcome aboard. :)

Would you like to comment?

Join YouTube for a free account, or sign in if you are already a member.