This question is especially dedicated to @"Chris Saxon-Oracle" since he has access to 20c. But all others are invited to the discusssion as well.
In one of his 20c new features presentations Chris mentioned and demonstrated SQL macros.
SQL Macros can solve some problem that we currently want to solve using parameterized views (which at the moment are implemented only by pipelined table functions).
See also the discussion here in Database Ideas:
I'm pondering if SQL macros can help in the following situation. Currently I doubt it, but I would be happy to be proved wrong.
Scenario
We have several (e.g. 3) filter parameters that are each mapped to a column.
If one of the parameter values is NULL then the filter should not be applied.
The parameters should be considered bind parameters. For example they could be variables in plsql or maybe binded page items in APEX.
The APEX case might be a very different possible scenario, since APEX will always need to parse the statement first.
So the query could look like this
select * from hr.employees
where job_id = nvl(:param1, job_id)
and manager_id = nvl(:param2, manager_id)
and salary = nvl(:param3, salary);
The problem is that this query prevents index usage on each of the columns if values are applied.
Here we assume that it would be beneficial to the performance if we use the column index when there is a parameter value and use an FTS (or an index on a different column) when the parameter is NULL.
Please note that the optimizer can rewrite such NVL expressions in certain cases (rewrite to UNION ALL) as Jonathan Lewis pointed out some time ago.
But the optimizer will fail to do so after some number of parameters (i didn't test if it is already after 2 or much later).
This has to do with the complexity - for 3 parameters there are already 8 (=2³) [Edit: after JL pointed out the correct calculation] possible combinations to consider. The resulting statement quickly becomes to large to handle.
Currently we can use dynamic SQL to solve the performance problem or use a set of conditional PLSQL expressions to manage different versions of the same select.
Such solutions can then be put into a pipelined table function, so that it looks like a parameterized view.
Are SQL macros also able to provide a well performing solution?