Next, we repeat the test for the pipelined table function.ĭBMS_OUTPUT.put_line('Pipelined table function : ' || (get_stat('session pga memory') - l_start)) L_start := get_stat('session pga memory') ĭBMS_OUTPUT.put_line('Regular table function : ' || Checking the PGA memory allocation before and after the test allows us to see how much memory was allocated as a result of the test. It will allow us to compare the memory used by regular and pipelined table functions.ĬREATE OR REPLACE FUNCTION get_stat (p_stat IN VARCHAR2) RETURN NUMBER ASįirst we test the regular table function by creating a new connection and querying a large collection. The following function returns the current value for a specified statistic. The NO_DATA_NEEDED can also be used to perform cleanup operations. If you plan to use an OTHERS exception handler, you must include a specific trap for the NO_DATA_NEEDED exception. #Calculate pi from polymorphism table codeIf you include an OTHERS exception handler, this will capture the NO_DATA_NEEDED exception and potentially run some error handling code it shouldn't. The function below returns 10 rows, but the query against it only ask for the first 5 rows, so the function stops processing by raising the NO_DATA_NEEDED exception. This doesn't need to be explicitly handled provided you do not include an OTHERS exception handler. When this happens, the pipelined table function execution stops, raising the NO_DATA_NEEDED exception. NO_DATA_NEEDED ExceptionĪ pipelined table function may create more data than is needed by the process querying it. Once you start working with large warehousing ETL operations the performance improvements can be massive, allowing data loads from external tables via table functions directly into the warehouse tables, rather than loading via a staging area. If you are using 12.2 or above you can use the pipelined table function without the TABLE operator. PIPE ROW(t_tf_row(i, 'Description for ' || i)) Notice the empty RETURN call, since there is no collection to return from the function.ĬREATE OR REPLACE FUNCTION get_tab_ptf (p_rows IN NUMBER) RETURN t_tf_tab PIPELINED AS Pipelined table functions include the PIPELINED clause and use the PIPE ROW call to push rows out of the function as soon as they are created, rather than building up a table collection. Pipelining negates the need to build huge collections by piping rows out of the function as they are created, saving memory and allowing subsequent processing to start before all the rows are generated. If you are using 12.2 or above you can use the table function without the TABLE operator. Notice the above output is in reverse order because the query includes a descending order by clause. L_tab(l_tab.last) := t_tf_row(i, 'Description for ' || i) Create the types to support the table function.ĬREATE TYPE t_tf_tab IS TABLE OF t_tf_row ĬREATE OR REPLACE FUNCTION get_tab_tf (p_rows IN NUMBER) RETURN t_tf_tab AS Regular table functions require named row and table types to be created as database objects. These potential bottlenecks make regular table functions unsuitable for large Extraction Transformation Load (ETL) operations. Since collections are held in memory, this can be a problem as large collections can waste a lot of memory and take a long time to return the first row. Regular table functions require collections to be fully populated before they are returned. They can be queried like a regular table by using the TABLE operator in the FROM clause. Table functions are used to return PL/SQL collections that mimic tables.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |