Views have always been an efficient tool for encapsulating complex logic, creating defined access structures and so on. But there is one thing views cannot do: accept parameters. And this is unfortunately a big disadvantage in terms of flexibility. As a result, there are a number of workarounds, none of them without their drawbacks.
I will use a little bit complicated example but the one that have a real practical application in the domain of data warehousing and ETL. It’s about historizing all changes according to slowly changing dimensions type II principle. My colleague Philipp Savlisberg has posted a historized version of the famous SCOTT schema to look at joining temporal intervals. He also provides a script to create this schema and data.
To simplify the queries, two types of the views are often placed on top of the versioned tables: the one to query a version of the data at any point in time and the other to query all versions within a time interval.
Let’s look at the data for one employee (EMPNO=7788):
SQL> SELECT empvid, empno, valid_from, valid_to, ename, sal
FROM empv
WHERE empno = 7788
EMPVID EMPNO VALID_FROM VALID_TO ENAME SAL
---------- ---------- ---------- --------- -------- ----------
8 7788 19-APR-87 31-DEC-89 SCOTT 3000
22 7788 01-JAN-90 31-MAR-91 Scott 3000
36 7788 01-APR-91 31-DEC-99 Scott 3300
3 rows selected.
Listing 1: keeping all changes in a historized EMP table
Would be great, if we could get a view with the following query:
SELECT *
FROM empv
WHERE p_eff_date BETWEEN valid_from AND valid_to
Listing 2: the desired query for the view
From my point of view, the best known workaround is that a view is defined using SYS_CONTEXT calls instead of the desired parameters. You have to create a user context and set the variables your view should use before querying the view. That alone is quite inconvenient. But another drawback is that you are always using bind variables then. Yes, I said that! Bind variables are good most of the time but not always. I’m doing a lot of ETL and I would often prefer literals over binds.
-- create package and context
...
-- create view
CREATE VIEW emp_hist_v AS
SELECT *
FROM empv
WHERE SYS_CONTEXT('MY_VIEW_CONTEXT','EFF_DATE')
BETWEEN valid_from AND valid_to;
-- Set the context variable
...
-- Query is now possible
SELECT * FROM emp_hist_view;
Listing 3: simplified description of SYS_CONTEXT workaround
Another one is to use a package and package variables as parameters. Define the setter and getter methods and use the getter methods instead of parameters in your view. Well, the drawbacks are the same: you have to set the variables first and you cannot switch to literals too.
CREATE OR REPLACE PACKAGE emp_hist_pkg IS
g_eff_date date;
PROCEDURE set_eff_date(p_date IN DATE);
FUNCTION get_eff_date RETURN DATE DETERMINISTIC PARALLEL_ENABLE;
END;
/
CREATE VIEW emp_hist_v AS
SELECT *
FROM empv
WHERE emp_hist_pkg.get_eff_date BETWEEN valid_from AND valid_to;
-- set parameter
BEGIN
emp_hist_pkg.set_eff_date(DATE '1990-01-01');
END;
-- Query is now possible
SELECT * FROM emp_hist_view;
Listing 4: simplified description of a workaround with package variables
Yet another popular option is not having a view at all, but rather to query a pipelined function which acts as view and can accept parameter. But again, mixing SQL and PL/SQL means you have to keep context switches in mind.
Okay, you might ask, what’s the problem here? Just query the view with the desired parameters as WHERE predicates and they will hopefully be pushed down into the view query. It may be or maybe not, depending of how complex your view is. And, first of all, you have to expose those columns from the view to be able to filter on them. What if your view has a scalar subquery and that’s where you want to introduce a parameter? How simple would it be just to return another column from a scalar subquery? Not at all! Maybe you have to rewrite the whole query because of that.
SQL Macros
Let’s again look at the query, we would like to implemet as a view:
SELECT *
FROM empv
WHERE p_eff_date BETWEEN valid_from AND valid_to
Listing 5: the desired query for the view
Well, we know, with a plain view it is just not possible. How to solve the task with a SQL macro? First, we need a table macro (default), because we will use it in the FROM clause. Then just take the query from above and put it into a SQL macro function:
CREATE OR REPLACE FUNCTION emp_view (p_eff_date date)
RETURN VARCHAR2 SQL_MACRO IS
v_query varchar2(500);
BEGIN
v_query := q'!SELECT *
FROM empv
WHERE p_eff_date between valid_from AND valid_to!';
RETURN v_query;
END;
/
SQL> SELECT empvid, empno, valid_from, valid_to, ename, sal
FROM emp_view(DATE '1990-02-01')
WHERE empno = 7788
EMPVID EMPNO VALID_FROM VALID_TO ENAME SAL
---------- ---------- ---------- --------- -------- ----------
22 7788 01-JAN-90 31-MAR-91 Scott 3000
1 row selected.
DECLARE
l_clob CLOB;
BEGIN
DBMS_UTILITY.expand_sql_text (
input_sql_text => q'!SELECT *
FROM emp_view(DATE '1990-02-01')
WHERE empno = 7788!',
output_sql_text => l_clob );
DBMS_OUTPUT.put_line(l_clob);
END;
/
SELECT "A1"."EMPVID" "EMPVID","A1"."EMPNO" "EMPNO","A1"."ENAME" "ENAME","A1"."JOBNO" "JOBNO","A1"."MGR" "MGR","A1"."HIREDATE" "HIREDATE","A1"."SAL" "SAL","A1"."COMM" "COMM","A1"."DEPTNO" "DEPTNO","A1"."VALID_FROM" "VALID_FROM","A1"."VALID_TO" "VALID_TO"
FROM (SELECT "A2"."EMPVID" "EMPVID","A2"."EMPNO" "EMPNO","A2"."ENAME" "ENAME","A2"."JOBNO" "JOBNO","A2"."MGR" "MGR","A2"."HIREDATE" "HIREDATE","A2"."SAL" "SAL","A2"."COMM" "COMM","A2"."DEPTNO" "DEPTNO","A2"."VALID_FROM" "VALID_FROM","A2"."VALID_TO" "VALID_TO"
FROM (SELECT "A3"."EMPVID" "EMPVID","A3"."EMPNO" "EMPNO","A3"."ENAME" "ENAME","A3"."JOBNO" "JOBNO","A3"."MGR" "MGR","A3"."HIREDATE" "HIREDATE","A3"."SAL" "SAL","A3"."COMM" "COMM","A3"."DEPTNO" "DEPTNO","A3"."VALID_FROM" "VALID_FROM","A3"."VALID_TO" "VALID_TO"
FROM "ONFTEST"."EMPV" "A3"
WHERE TO_DATE(' 1990-02-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')>="A3"."VALID_FROM" AND
TO_DATE(' 1990-02-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')<="A3"."VALID_TO") "A2") "A1" WHERE "A1"."EMPNO"=7788
Listing 6: create and query SQL macro
That’s it! It looks like we were quering a function, just like in a workaround with pipelined functions, but the difference is huge. The function will run only at parse time and two substitutions are made then. First, the parameter reference will be substituted in the return string of a function. Second, the return string of the function (now with a literal instead of a parameter) will be substitued in the query text instead of a function call. Using DBMS_UTILITY.expand_sql_text, we can see the query actually running. No context switch at all!
But the requirement is actually generic. We need views for all versioned tables. Do we have to create a SQL macro function for every table? No! Being able to pass a table parameter into a SQL macro function, we can just create very simple interface for querieng those tables. With function overloading capabilities of PL/SQL we can even use the “same” function for both scenarios, point in time and interval. See implementation of the package TIME_MACHINE in Listing 7. I’ve borrowed this “time machnine” analogy from my colleague Dani Schnider, but his presentation is only available in german.
CREATE OR REPLACE PACKAGE time_machine IS
-- point in time
FUNCTION vw (p_tab DBMS_TF.Table_t, p_eff_date date)
RETURN VARCHAR2 SQL_MACRO;
--interval
FUNCTION vw (p_tab DBMS_TF.Table_t, p_eff_date date, p_exp_date date)
RETURN VARCHAR2 SQL_MACRO;
END time_machine;
CREATE OR REPLACE PACKAGE BODY time_machine IS
FUNCTION vw (p_tab DBMS_TF.Table_t, p_eff_date date)
RETURN VARCHAR2 SQL_MACRO
IS
v_query varchar2(500);
BEGIN
v_query := q'!SELECT *
FROM p_tab
WHERE p_eff_date BETWEEN valid_from AND valid_to!';
RETURN v_query;
END;
FUNCTION vw (p_tab DBMS_TF.Table_t, p_eff_date date, p_exp_date date)
RETURN VARCHAR2 SQL_MACRO IS
v_query varchar2(500);
BEGIN
v_query := q'!SELECT *
FROM p_tab
WHERE valid_from <= p_exp_date
AND valid_to >= p_eff_date!';
RETURN v_query;
END;
END time_machine;
/
Listing 7: creating the time machine interface
This is how we can use the “time machine” to get both point in time as well as interval data from EMPV and DEPTV tables:
SQL> SELECT empvid, empno, valid_from, valid_to, ename, sal
FROM time_machine.vw(empv, DATE '1990-02-01')
WHERE empno = 7788
EMPVID EMPNO VALID_FROM VALID_TO ENAME SAL
---------- ---------- ---------- --------- -------- ----------
22 7788 01-JAN-90 31-MAR-91 Scott 3000
1 row selected.
SQL> SELECT empvid, empno, valid_from, valid_to, ename, sal
FROM time_machine.vw(empv, DATE '1990-01-01', DATE '2020-01-01')
WHERE empno = 7788
EMPVID EMPNO VALID_FROM VALID_TO ENAME SAL
---------- ---------- ---------- --------- -------- ----------
22 7788 01-JAN-90 31-MAR-91 Scott 3000
36 7788 01-APR-91 31-DEC-99 Scott 3300
2 rows selected.
SQL> SELECT *
FROM time_machine.vw(deptv, DATE '1990-02-01')
DEPTVID DEPTNO DNAME LOC VALID_FROM VALID_TO
---------- ---------- -------------- ------------- ---------- ---------
1 10 ACCOUNTING NEW YORK 01-JAN-80 28-FEB-90
2 20 RESEARCH DALLAS 01-JAN-80 28-FEB-90
3 30 SALES CHICAGO 01-JAN-80 28-FEB-90
4 40 OPERATIONS BOSTON 01-JAN-80 28-FEB-90
4 rows selected.
SQL> SELECT *
FROM time_machine.vw(deptv, DATE '1980-02-01', DATE '2020-01-01')
WHERE DEPTNO = 10
DEPTVID DEPTNO DNAME LOC VALID_FROM VALID_TO
---------- ---------- -------------- ------------- ---------- ---------
1 10 ACCOUNTING NEW YORK 01-JAN-80 28-FEB-90
5 10 Accounting NEW YORK 01-MAR-90 31-MAR-90
9 10 Accounting New York 01-APR-90 31-DEC-99
3 rows selected.
Listing 8: querying using the new generic interface
And another nice thing: you can use binds, if you want. Because it’s not possible to declare bind variable of type DATE in SQL*Plus, I will demonstrate it with a PL/SQL block. We are using a bind variable here and the explain plan is showing that binds were used in a filter expression. We just have full control here: use binds in SQM call and you’ll get binds in your query, use literals and you’ll get literals.
SQL> -- Show I can use binds
SQL> declare
v_cnt pls_integer;
v_date date := DATE '1990-02-01';
begin
select count(*)
into v_cnt
from time_machine.vw(deptv, v_date);
end;
PL/SQL procedure successfully completed.
...
SQL> select * from dbms_xplan.display_cursor(sql_id=>'8qr9z6n9gfm2k')
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 8qr9z6n9gfm2k, child number 0
-------------------------------------
SELECT COUNT(*) FROM TIME_MACHINE.VW(DEPTV, :B1 )
Plan hash value: 1461709607
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| |
| 1 | SORT AGGREGATE | | 1 | 16 | | |
|* 2 | TABLE ACCESS FULL| DEPTV | 12 | 192 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(("DEPTV"."VALID_FROM"<=:B1 AND "DEPTV"."VALID_TO">=:B1))
19 rows selected.
Listing 9: using bind variables with SQL macro functions
Summary
I am really impressed with what we have been able to achieve here with SQL macros! On the one hand, one can disagree that we don’t actually have a parameterized view here. Yes, we don’t have a view at all. But on the other hand, we can use the SQM-function exactly the same way. We have built one function that can handle ALL tables, instead of having a lot of views. And moreover, we have not hidden any information from query optimizer. It can see literals or do bind peeking and use histograms if available.
what about the following trick to create parametrized views where we set the parameter(s) in the where-clause.
(modified version of listing 4, difference: no need to set parameter via pl/sql before querying the view)
Nice trick, Matthias!
Thanks for sharing! Setting the parameter in the same query is better, although it has to be done in quite unusual way.
What I continue to see as a drawback is that you still have no control over using literals or binds.
And setting both functions to
DETERMINISTICandPARALLEL_ENABLEis quite important, I think.Regards
Andrej
Interesting trick…Matthias
As we don’t have control over which predicate gets evaluated first, won’t this kind of usage is likely to produce unreliable results? Or am I missing something obvious?
Hello there,
Thanks for sharing this helps me in a lot of our queries. But I wanted to ask question and i.e. if I wanted to use this method and I have 2 dates one is From and other is To how can I do that? Following is the updated Package;
CREATE OR REPLACE PACKAGE OFUSION.VW_DATE IS
G_DATE DATE;
FUNCTION SET_DATE(P_DATE IN DATE) RETURN INT;
FUNCTION GET_DATE RETURN DATE;
END;
CREATE OR REPLACE PACKAGE BODY OFUSION.VW_DATE IS
FUNCTION SET_DATE(P_DATE IN DATE) RETURN INT IS
BEGIN G_DATE := P_DATE;
RETURN 0;
END SET_DATE;
–FUNCTION GET_DATE RETURN DATE IS RETURN G_DATE;
FUNCTION GET_DATE RETURN DATE IS
BEGIN
RETURN G_DATE;
END;
END;
I have done it from below Package, in case anyone needs.
CREATE OR REPLACE PACKAGE OFUSION.VW_DATE2 IS
G_FROM_DATE DATE;
G_TO_DATE DATE;
FUNCTION SET_FROM_DATE(P_DATE IN DATE) RETURN INT;
FUNCTION SET_TO_DATE(P_DATE IN DATE) RETURN INT;
FUNCTION GET_FROM_DATE RETURN DATE;
FUNCTION GET_TO_DATE RETURN DATE;
END;
CREATE OR REPLACE NONEDITIONABLE PACKAGE BODY OFUSION.VW_DATE2 IS
FUNCTION SET_FROM_DATE (P_DATE IN DATE) RETURN INT IS
BEGIN G_FROM_DATE := P_DATE;
RETURN 0;
END SET_FROM_DATE;
FUNCTION GET_FROM_DATE RETURN DATE IS
BEGIN
RETURN G_FROM_DATE;
END;
FUNCTION SET_TO_DATE (P_DATE IN DATE) RETURN INT IS
BEGIN G_TO_DATE := P_DATE;
RETURN 0;
END SET_TO_DATE;
FUNCTION GET_TO_DATE RETURN DATE IS
BEGIN
RETURN G_TO_DATE;
END;
END;