Forum Stats

  • 3,826,771 Users
  • 2,260,705 Discussions
  • 7,897,072 Comments

Discussions

Parameterized Views

1356

Comments

  • William Robertson
    William Robertson Member Posts: 9,567 Bronze Crown

    SQL Server (the second best RDBMS ) already has them. They're called inline table valued functions. We shouldn't fall behind SQL Server...

    I just had a look, but what I could find looked like Oracle table functions. Am I missing something?

    https://technet.microsoft.com/en-us/library/aa175924(v=sql.80).aspx

  • Mike Kutz
    Mike Kutz Member Posts: 6,195 Silver Crown

    What it really sounds like people are wanting is:

    an SQL Macro.  (think C/C++)

    However, this "Macro" would only be valid within SQL statements.

    Example Macro Description

    -- exact syntax negotiatable

    create or replace

    macro view f( p_x in T.x%type ) as

    select x, sum( y ) sum_y

    from T

    where x = p_x

    group by x

    ;

    Example Usage

    select *

    from f( :P25_X )

    Resulting Expansion

    select *

    from (

    select x, sum( y ) sum_y

    from T

    where x = :P25_X

    group by x

    )

    This would be just another form of an SQL Rewrite.

    MK

    William RobertsonDejan T.Lukas Ederrober584812
  • Sven W.
    Sven W. Member Posts: 10,535 Gold Crown

    The example could be a case for the new analytical views in 12.2.

  • Sven W.
    Sven W. Member Posts: 10,535 Gold Crown
    edited Jul 28, 2017 7:10AM

    Hi Bryn,

    while I agree with the first part (views are similar to sub routines) I do not think it is self evident that formal parameters are missing in SQL.

    Lets take a step back and look at the whole SQL language (or even just at the SELECT statement). The same types of problems can be solved using pure SQL as can be done useing  procedural or oo-languages.

    SQL is turing complete, even a single select is turing complete because of CTE (recursive with clause). Since SQL has no need for parameters (and its close cousins variables), why do we suddenly feel the need to add them for the very specific notion of a parameterized view. Question is: what resembles a parameter or a variable from a 3rdGL language closest in a 4thGL language like SQL?

    Since parameters and variables mostly store short lived data, the closest counterpart in SQL is a temp table or sometimes a subquery.

    We can put data in there and when we loose context the data there is gone or not relevant anymore. And yes, we could build a view using a subquery to a (temp) table as input.

    Additionally a view, which acts like a formal api or subroutine, exposes parameters as columns.

    However here is also a major difference. Columns per se do not distinguish between IN parameters and OUT parameters. It depends how the column is used.

    It is an IN parameter if the column is used as a filter in the where clause. It is an OUT parameter if the column is used in the SELECT clause.

    So I feel that the language itself already has what it needs. There is no "gap" in the SQL language itself as your statement seems to imply.

    This generalization is what made me vote against the proposal in the first place. However I tend now to swing around in favor for it, but for slightly different reasons.

    So now lets look at the other side. Why do we feel there is something missing? And me too encountered several cases where I wished for the proposed construct.

    The arguments in favor (an I'd like to add one more example in a moment) of the suggested enhancement concentrate on two things

    a) easyness to use

    b) performance

    =>a) A parameterized view seems to be easy to use, because we know the same concept from other languages.

    Using a temp table would require us to insert some data there frst, then make sure we query the view which uses the temp table in the same session.

    This seems a bit cumbersome. Especially because we only wanted to query things, not do DML at the same time. But also this is enforced because we are calling our SQL from inside a different language (even if it happens to be plsql).

    And we just trying to enforce the notion of parameters from the calling environement into SQL. The complexity that we see exists only at this specific bridging point from one language to the other.

    In general it is a good idea to improve a language to make it easier to use. That is a very strong and very solid argument in favor of the proposal.

    =>b) Performance (especially what Lukas mentioned). Using a parametrized view would give the optimizer more information and enable it to find a good execution plan. This is especially true if we compare it with a plsql based table function.

    Usually performance is influenced by statistic information and hints. Maybe statistics on view columns is what is really missing? Also in 12c we now have the possibility to get correct statistics on temp table data in our session.

    In general it is a good idea to enhance the language if performance can be improved by that enhancement (without sacrificing too much on other fronts).

    The example where I would like to use parameterized views, is when I want to make sure that the view is only used via this parameter and never without.

    Typically if we need to filter on a very large dataset and I want to avoid running the view without any filter at all.

    => So this is a mix of access privileges and performance reasons. Typical for an api.

    I finally made my mind up about this now and vote in favor. But not because the language is missing something. Instead because there are typical cases out there in reality where this would help us to write better maintainable and more performant applications.

    Regards

    Sven

  • William Robertson
    William Robertson Member Posts: 9,567 Bronze Crown

    Hi Bryn,

    while I agree with the first part (views are similar to sub routines) I do not think it is self evident that formal parameters are missing in SQL.

    Lets take a step back and look at the whole SQL language (or even just at the SELECT statement). The same types of problems can be solved using pure SQL as can be done useing  procedural or oo-languages.

    SQL is turing complete, even a single select is turing complete because of CTE (recursive with clause). Since SQL has no need for parameters (and its close cousins variables), why do we suddenly feel the need to add them for the very specific notion of a parameterized view. Question is: what resembles a parameter or a variable from a 3rdGL language closest in a 4thGL language like SQL?

    Since parameters and variables mostly store short lived data, the closest counterpart in SQL is a temp table or sometimes a subquery.

    We can put data in there and when we loose context the data there is gone or not relevant anymore. And yes, we could build a view using a subquery to a (temp) table as input.

    Additionally a view, which acts like a formal api or subroutine, exposes parameters as columns.

    However here is also a major difference. Columns per se do not distinguish between IN parameters and OUT parameters. It depends how the column is used.

    It is an IN parameter if the column is used as a filter in the where clause. It is an OUT parameter if the column is used in the SELECT clause.

    So I feel that the language itself already has what it needs. There is no "gap" in the SQL language itself as your statement seems to imply.

    This generalization is what made me vote against the proposal in the first place. However I tend now to swing around in favor for it, but for slightly different reasons.

    So now lets look at the other side. Why do we feel there is something missing? And me too encountered several cases where I wished for the proposed construct.

    The arguments in favor (an I'd like to add one more example in a moment) of the suggested enhancement concentrate on two things

    a) easyness to use

    b) performance

    =>a) A parameterized view seems to be easy to use, because we know the same concept from other languages.

    Using a temp table would require us to insert some data there frst, then make sure we query the view which uses the temp table in the same session.

    This seems a bit cumbersome. Especially because we only wanted to query things, not do DML at the same time. But also this is enforced because we are calling our SQL from inside a different language (even if it happens to be plsql).

    And we just trying to enforce the notion of parameters from the calling environement into SQL. The complexity that we see exists only at this specific bridging point from one language to the other.

    In general it is a good idea to improve a language to make it easier to use. That is a very strong and very solid argument in favor of the proposal.

    =>b) Performance (especially what Lukas mentioned). Using a parametrized view would give the optimizer more information and enable it to find a good execution plan. This is especially true if we compare it with a plsql based table function.

    Usually performance is influenced by statistic information and hints. Maybe statistics on view columns is what is really missing? Also in 12c we now have the possibility to get correct statistics on temp table data in our session.

    In general it is a good idea to enhance the language if performance can be improved by that enhancement (without sacrificing too much on other fronts).

    The example where I would like to use parameterized views, is when I want to make sure that the view is only used via this parameter and never without.

    Typically if we need to filter on a very large dataset and I want to avoid running the view without any filter at all.

    => So this is a mix of access privileges and performance reasons. Typical for an api.

    I finally made my mind up about this now and vote in favor. But not because the language is missing something. Instead because there are typical cases out there in reality where this would help us to write better maintainable and more performant applications.

    Regards

    Sven

    I'm not sure where my examples ('start with' value for recursive queries and date context for time series data) fit in. When we create a view we are presenting the user with an interface, and the language currently lacks a way for a user to specify those things when querying the view.

  • Sven W.
    Sven W. Member Posts: 10,535 Gold Crown

    I'm not sure where my examples ('start with' value for recursive queries and date context for time series data) fit in. When we create a view we are presenting the user with an interface, and the language currently lacks a way for a user to specify those things when querying the view.

    Could also being solved with using a temp table inserting the values there and doing a subquery on this temp table.

  • Bryn.Llewellyn-Oracle
    Bryn.Llewellyn-Oracle Member Posts: 7 Green Ribbon

    Hello, Sven, my old friend. You and I do like to disagree from time to time, it seems! (But at least we agree on our “up vote” decision.) Look at the SQL statement below. I don’t need to say what it does. Suffice it to say that our good friend Stew Ashton wrote it to meet the requirements I explain in my “Transforming one table to another” talk and whitepaper—and that none of my exhaustive testing has found it ever to give the wrong answer.

    Notice that the text of the statement is about seventy lines. Of course, that’s nothing compared to some statements we see. But it’s still a lot. Notice too that the placeholder :Nof_Chunks is mentioned nine times. I can’t see any way to expose it as a projected column against which I could express a restriction—as you argue I should be able to do. Can you?

    I wanted to test the correctness of this query by running it by binding lots of different actual values to the placeholder. Of course, I found a way by encapsulating it as static SQL in a PL/SQL subprogram. But just think how more natural it would have been to hide all this complexity from sight in a single view parameterized by Nof_Chunks.

    with

      Extents_With_Sums as (

        select

          Sum(Nof_Blocks) over()                                            as Total_Blocks,

          Sum(Nof_Blocks) over(order by Dobj#, File#, Block#) - Nof_Blocks  as First_Extent_Block, 

          Sum(Nof_Blocks) over(order by Dobj#, File#, Block#)               as Next_Extent_Block,

          e.*

        from Extents e

      ),

      Sum_Blocks as (select Total_Blocks v from Extents_With_Sums where rownum = 1),

      Assert as (

        select

          x.Error_On_Sql_Assert_Failure(

            case

              when :Nof_Chunks > 0 and :Nof_Chunks <= Sum_Blocks.v then 1

              else                                                    0

            end,

            'Assert failed. Sum_Blocks: '||To_Char(Sum_Blocks.v)||

                          '; Nof_Chunks: '||To_Char(:Nof_Chunks)) as v

        from Sum_Blocks

      ),

      Filtered_Extents as (

        select * from (

          select

            Width_Bucket(First_Extent_Block-1, 0, Total_Blocks, :Nof_Chunks) as Prev_Chunk,

            Width_Bucket(First_Extent_Block,   0, Total_Blocks, :Nof_Chunks) as First_Chunk,

            Width_Bucket(Next_Extent_Block-1,  0, Total_Blocks, :Nof_Chunks) as Last_Chunk,

            Width_Bucket(Next_Extent_Block,    0, Total_Blocks, :Nof_Chunks) as Next_Chunk,

            e.*

          from Extents_With_Sums e), Assert

        where Prev_Chunk < Next_Chunk

        and Assert.v = 1

      ),

      Expanded_Extents as (

        select

          First_Chunk + Level - 1 as Chunk#,

          Prev_Chunk, Next_Chunk,

          Dobj#, File#, Block#,

          Total_Blocks, First_Extent_Block

        from Filtered_Extents

        connect by First_Extent_Block = prior First_Extent_Block

        and prior Sys_Guid() is not null

        and First_Chunk + Level - 1 <= Last_Chunk

      ),

      RowIDs as (

        select Chunk#,

          case when Chunk# > Prev_Chunk then

            CharToRowID(Rid.Create_Min(

              Dobj#,

              File#,

              Block# + Floor(((Chunk#-1) * Total_Blocks)/ :Nof_Chunks - 1) + 1 - First_Extent_Block))

          end as Start_RowID,

          case when Chunk# < Next_Chunk then

            CharToRowID(Rid.Create_Max(

              Dobj#,

              File#,

              Block# + Floor((Chunk# * Total_Blocks)/ :Nof_Chunks - 1) - First_Extent_Block))

          end as End_RowID

        from Expanded_Extents

      )

    select

      'Extent_Based_Sql_Ashton_2',

        Chunk#,

        Min(Start_RowID) as Start_RowID,

        Max(End_RowID)   as End_RowID

    from RowIDs

    group by Chunk#

    Sven W.ApexBine
  • As already commented, just because something is already "solvable" doesn't remove the benefit of being able to solve it more easily...

    Sure I can use SQL to work out the median with this query:

    SQL> select deptno, avg(distinct sal) median

      2  from

      3    (select cp1.deptno, cp1.sal

      4     from emp cp1, emp cp2

      5     where cp1.deptno = cp2.deptno

      6     group by cp1.deptno, cp1.sal

      7     having sum(decode(cp1.sal, cp2.sal, 1, 0)) >=

      8                 abs(sum(sign(cp1.sal - cp2.sal))))

      9  group by deptno;

    but I generally prefer using this one :-)

    SQL> select

      2      deptno,

      3      median(sal)

      4  from emp

      5  group by deptno;

    So the fact that I can use pipelined functions, or context variables, or global temp tables, or just rely on the optimizer to sort things out (whilst each having their own merits) doesn't strike down the merit of parameterised views.

    gassenmjApexBine