Section 3.5. Table Function Examples
3.5. Table Function ExamplesThis section contains a number of additional examples that demonstrate some handy applications of using table functions to handle such tasks as performing extra tracing, establishing time limits, and using timed data refreshes. Each example takes advantage of the fact that table functions allow code to be written with a SELECT statement. 3.5.1. TracingMost PL/SQL tracing tools (SQL Trace , DBMS_TRACE, etc.) require you to run an operation and then look elsewhere for the trace output. Even Oracle's DBMS_OUTPUT package (the simplest debug tool of all) requires separate output when using a development tool like Toad or PL/SQL Developer . Table functions allow debug information to be included within query results. When combined with autonomous transactions, they can even provide tracing for DML operations. Consider the following function. /* File on web: tracer.sql */ CREATE OR REPLACE FUNCTION tracer RETURN debug_t AS PRAGMA AUTONOMOUS_TRANSACTION; v_debug debug_t := debug_t( ); BEGIN v_trace.EXTEND; v_trace(v_debug.LAST) := 'Started Insert At ' || TO_CHAR(SYSDATE,'HH24:MI:SS'); INSERT INTO a_table VALUES(1); COMMIT; v_trace.EXTEND; v_trace(v_debug.LAST) := 'Completed Insert At ' || TO_CHAR(SYSDATE,'HH24:MI:SS'); RETURN(v_trace); END; Without the AUTONOMOUS TRANSACTION clause , I would get the error ORA-14551: cannot perform a DML operation inside a query when executing the query. With this clause in place, I can run the function using a SELECT statement. SQL> SELECT * 2 FROM a_table; no rows selected SQL> SELECT * 2 FROM TABLE(debug); COLUMN_VALUE ---------------------------- Started Insert At 22:04:28 Completed Insert At 22:04:28 SQL> SELECT * 2 FROM a_table; COL1 ---------- 1 3.5.2. Establishing Time LimitsOne very useful application of table functions is to use them to establish time limits for returning records from queries. This is great if you want to test an application using a subset of queried records without having to wait for the whole list. The following function pipes records back from a query for the number of seconds passed in. Once the number of seconds is reached, a value of negative 1 is piped out and the function is exited. /* File on web: time_limit.sql */ CREATE OR REPLACE FUNCTION get_a_table ( p_limit NUMBER ) RETURN rowset_t PIPELINED AS CURSOR curs_get_a IS SELECT * FROM a_table; v_start DATE; BEGIN v_start := SYSDATE; FOR v_a_rec IN curs_get_a LOOP PIPE ROW(rowset_o(v_a_rec.col1)); IF SYSDATE - v_start >= ( p_limit * 0.000011574 ) THEN PIPE ROW(rowset_o(-1)); EXIT; END IF; END LOOP; END; Here's an example of selecting from a table with 1,000 records in it. SQL> SELECT * 2 FROM TABLE(get_a_table(1)); COL1 ---------- 661 662 663 664 -1 5 rows selected. If the query takes more than p_limit seconds to return, the function will exceed its time limit. 3.5.3. Enabling Nested CursorsTable functions can also be used to help queries perform better by adding application knowledge to a query. The classic example is the multiple "OR EXISTS" type of query shown in this example. SELECT * FROM main_table mt WHERE col1 = 1 AND ( EXISTS ( SELECT 1 FROM or_table_one WHERE col11 = mt.col1 ) OR EXISTS ( SELECT 1 FROM or_table_two WHERE col21 = mt.col1 ) OR EXISTS ( SELECT 1 FROM or_table_three WHERE col31 = mt.col1 ) ); It will return a record if a corresponding record is found in any of three other tables. The AUTOTRACE output for the query shows that the Oracle optimizer looked at each table involved in order to get the single resultant record. Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=1 Card=1 Bytes=4) 1 0 FILTER 2 1 TABLE ACCESS (BY INDEX ROWID) OF 'MAIN_TABLE' (TABLE) (Cost=1 Card=1 Bytes=4) 3 2 INDEX (UNIQUE SCAN) OF 'SYS_C003477' (INDEX (UNIQUE)) (Cost=0 Card=1) 4 1 INDEX (UNIQUE SCAN) OF 'SYS_C003479' (INDEX (UNIQUE)) (Cost=0 Card=1 Bytes=2) 5 1 INDEX (UNIQUE SCAN) OF 'SYS_C003481' (INDEX (UNIQUE)) (Cost=1 Card=1 Bytes=3) 6 1 INDEX (UNIQUE SCAN) OF 'SYS_C003483' (INDEX (UNIQUE)) (Cost=1 Card=1 Bytes=3) Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 13 consistent gets 0 physical reads 0 redo size 446 bytes sent via SQL*Net to client 511 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed The Statistics section shows the amount of work Oracle had to do to provide the result set, including 13 consistent gets. But what if I know that there is a 90% chance of a match in the OR_TABLE_ONE table and only a 10% chance of a match in the other tables? I'd want to look in OR_TABLE_ONE and only go to the others if I didn't find a record there. One solution is to use nested cursors within a table function so the whole operation can still be done as a query. CREATE OR REPLACE FUNCTION nested RETURN number_t AS -- get a main table record CURSOR curs_get_mt IS SELECT mt.col1, CURSOR ( SELECT 1 FROM or_table_one WHERE col11 = mt.col1 ), CURSOR ( SELECT 1 FROM or_table_two WHERE col21 = mt.col1 ), CURSOR ( SELECT 1 FROM or_table_three WHERE col31 = mt.col1 ) FROM main_table mt WHERE col1 = 1; v_col1 NUMBER; cursor_one SYS_REFCURSOR; cursor_two SYS_REFCURSOR; cursor_three SYS_REFCURSOR; v_dummy NUMBER; v_ret_val number_t := number_t( ); BEGIN OPEN curs_get_mt; FETCH curs_get_mt INTO v_col1, cursor_one, cursor_two, cursor_three; IF curs_get_mt%FOUND THEN -- look in the first OR table FETCH cursor_one INTO v_dummy; IF cursor_one%FOUND THEN v_ret_val.EXTEND; v_ret_val(v_ret_val.LAST) := v_col1; ELSE -- look in the seconds OR table FETCH cursor_two INTO v_dummy; IF cursor_two%FOUND THEN v_ret_val.EXTEND; v_ret_val(v_ret_val.LAST) := v_col1; ELSE - look in the third OR table FETCH cursor_three INTO v_dummy; IF cursor_two%FOUND THEN v_ret_val.EXTEND; v_ret_val(v_ret_val.LAST) := v_col1; END IF; END IF; END IF; END IF; IF cursor_one%ISOPEN THEN CLOSE cursor_one; END IF; IF cursor_two%ISOPEN THEN CLOSE cursor_two; END IF; IF cursor_three%ISOPEN THEN CLOSE cursor_three; END IF; CLOSE curs_get_mt; RETURN(v_ret_val); END; The AUTOTRACE output for the function looks like this. SQL> SELECT * 2 FROM TABLE(nested); COLUMN_VALUE ------------ 1 Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=25 Card=8168 Bytes=16336) 1 0 COLLECTION ITERATOR (PICKLER FETCH) OF 'NESTED' Statistics ---------------------------------------------------------- 13 recursive calls 0 db block gets 3 consistent gets 0 physical reads 0 redo size 397 bytes sent via SQL*Net to client 511 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed The optimizer cost of the function evaluated much higher than the query, 25 to 1. But the database had to do a little less work, 3 consistent gets versus 13.
|
