1 2 3 4 5 Previous Next 66 Replies Latest reply: Mar 15, 2013 9:34 AM by BluShadow Go to original post RSS
      • 15. Re: NOT NULL
        stefan nebesnak
        Ikrischer wrote:
        I agree not to complicate things, but I can remember my first time in contact with views. They told me the same (view is a stored statement) and I was more confused with that concept, specially when I started to work with them. <font style="background-color: #FFFFCC">Do not laugh, but I really tried this out:

        view1 UNION ALL view2;</font>

        Specially when it came to DML statements using a view it broke my mind. I find it way more easier to handle a view like what it is, a virtual table based on records of a sql statement, then I have a good guess how and where I can bring in my views in a sql statement. What you want to tell a more advanced sql user, the same that a view is a stored sql statement ?
        You can use UNION ALL on Views as well..
        <tt>
        <font color="#FF0000">--create table temp as (</font>
        select t.id id from <font style="background-color: #FFFFCC">view1</font> t
        <font style="background-color: #FFFFCC">union all</font>
        select t.id from <font style="background-color: #FFFFCC">view2</font> t
        <font color="#FF0000">--);</font>
        </tt>
        • 16. Re: NOT NULL
          BluShadow
          Stew Ashton wrote:
          How about "a view appears logically as a table (when you write SQL, you use it as you would a table); it is defined and implemented by a stored select statement that is integrated into your SQL statement as a subquery"?
          That sounds ok. ;)
          • 17. Re: NOT NULL
            BluShadow
            stefan nebesnak wrote:
            You can use UNION ALL on Views as well..
            <tt>
            <font color="#FF0000">--create table temp as (</font>
            select t.id id from <font style="background-color: #FFFFCC">view1</font> t
            <font style="background-color: #FFFFCC">union all</font>
            select t.id from <font style="background-color: #FFFFCC">view2</font> t
            <font color="#FF0000">--);</font>
            </tt>
            Yes, he knows that. he was just explaining that because he was told a view was a stored select statement, he thought he could just do:
            view1 UNION ALL view2
            and the views would magically expand out to their stored selects and become
            select ... -- as defined in view 1
            from ...
            ..
            UNION ALL
            select ... -- as defined in view 2
            from ...
            ...
            but it wasn't so magical. ;)
            • 18. Re: NOT NULL
              sql_coder
              BluShadow wrote:
              but it wasn't so magical. ;)
              the magic came, when someone told me it is a virtual table (logical if you prefer it, but it is the same). Oracle says it is a logical table, Wikipedia says, it is one, mssql says the same....of course it is not a real table with their own records, so you have some restrictions, thats why you make a difference between simple and complex view, etc., but it is a table. And when you handle a View like a special table (logical one), you know how to use it and why it has a relationship to its base tables. I think your main problem is, that you only accept tables that are physically stored somewhere. Free yourself from that, be more logical ^^.

              Ikrischer
              • 19. Re: NOT NULL
                BluShadow
                Ikrischer wrote:
                BluShadow wrote:
                but it wasn't so magical. ;)
                the magic came, when someone told me it is a virtual table (logical if you prefer it, but it is the same).
                Not quite. There's a difference between something being virtually a table, and something that acts logically like a table.
                Oracle says it is a logical table,
                Which is ok.
                Wikipedia says, it is one, mssql says the same....
                Wikipedia is written by members of the public, trying to give a generic description of a database view, which will never completely work generically as different RDBMS's have implemented views in different ways. It doesn't matter what the description of a view is for mssql, or Ingres or any other database, the description we're refering to here is about Oracle views specifically.
                of course it is not a real table with their own records, so you have some restrictions, thats why you make a difference between simple and complex view, etc., but it is a table.
                Not according to the data dictionary, it says it's a view, and not according to the optimizer that treats it as a subquery. Tables have a physical structure and store data, views don't.
                And when you handle a View like a special table (logical one), you know how to use it and why it has a relationship to its base tables.
                I've never had a need to handle a view like a special table (on Oracle)... I've always handled it as a subquery.
                I think your main problem is, that you only accept tables that are physically stored somewhere. Free yourself from that, be more logical ^^.
                I don't have a problem. Views work for me as a stored select statement/subquery. Never had a need to treat them as a table, and still can't see any need to do that.
                • 20. Re: NOT NULL
                  sql_coder
                  BluShadow wrote:
                  Ikrischer wrote:
                  BluShadow wrote:
                  but it wasn't so magical. ;)
                  the magic came, when someone told me it is a virtual table (logical if you prefer it, but it is the same).
                  Not quite. There's a difference between something being virtually a table, and something that acts logically like a table.
                  ok lets make it short, oracle says it does not act like a logical table, but it is a logical table. In the context of oracle everytime you are telling others, it is a stored statement (or subquery), you are passing out wrong information.

                  Ikrischer
                  • 21. Re: NOT NULL
                    BluShadow
                    Ikrischer wrote:
                    BluShadow wrote:
                    Ikrischer wrote:
                    BluShadow wrote:
                    but it wasn't so magical. ;)
                    the magic came, when someone told me it is a virtual table (logical if you prefer it, but it is the same).
                    Not quite. There's a difference between something being virtually a table, and something that acts logically like a table.
                    ok lets make it short, oracle says it does not act like a logical table, but it is a logical table. In the context of oracle everytime you are telling others, it is a stored statement (or subquery), you are passing out wrong information.
                    Please demonstrate how it is wrong information. I've clearly demonstrated that it IS a stored SQL statement... black and white... there in the data dictionary. How is that wrong? How can you say it is not a stored statement or subquery when it's right there in front of you?
                    • 22. Re: NOT NULL
                      sql_coder
                      BluShadow wrote:
                      Ikrischer wrote:
                      BluShadow wrote:
                      Ikrischer wrote:
                      BluShadow wrote:
                      but it wasn't so magical. ;)
                      the magic came, when someone told me it is a virtual table (logical if you prefer it, but it is the same).
                      Not quite. There's a difference between something being virtually a table, and something that acts logically like a table.
                      ok lets make it short, oracle says it does not act like a logical table, but it is a logical table. In the context of oracle everytime you are telling others, it is a stored statement (or subquery), you are passing out wrong information.
                      Please demonstrate how it is wrong information. I've clearly demonstrated that it IS a stored SQL statement... black and white... there in the data dictionary. How is that wrong? How can you say it is not a stored statement or subquery when it's right there in front of you?
                      All you showed is, that a view is defined by a select statement, nothing more. Please refer to Oracle documents to show it is a stored statement. Oracle cleary says it is a logical table. If you think they said seomthing else, prove it with Oracle documents.

                      Ikrischer
                      • 23. Re: NOT NULL
                        stefan nebesnak
                        Yes, View (as logical entity) is the representation of a SQL statement that is stored in memory so that it can easily be re-used.

                        It is not a table nor a command or a function that returns some 'command' type like 'select id from table'.
                        <tt>
                        <font color="#FF0000">--create table temp as (</font>
                        select t.id id from <font style="background-color: #FFFFCC">(select id from table)</font> t <font color="#FF0000">--view1 </font>
                        union all
                        select t.id from <font style="background-color: #FFFFCC">(select id from table2)</font> t <font color="#FF0000">--view2 </font>
                        <font color="#FF0000">--);</font>
                        </tt>
                        • 24. Re: NOT NULL
                          BluShadow
                          Ikrischer wrote:
                          BluShadow wrote:
                          Please demonstrate how it is wrong information. I've clearly demonstrated that it IS a stored SQL statement... black and white... there in the data dictionary. How is that wrong? How can you say it is not a stored statement or subquery when it's right there in front of you?
                          All you showed is, that a view is defined by a select statement, nothing more. Please refer to Oracle documents to show it is a stored statement. Oracle cleary says it is a logical table. If you think they said seomthing else, prove it with Oracle documents.
                          The view documentation itself refers to the query and subquery of the view throughout... it's all there in the documentation.

                          If a view is just defined by a select statement, then it is used to create the table and that's it, it's use is up. But that's not the case. I also demonstrated that when the view is used in a query, the optimiser doesn't just treat it as if it's a table, but it treats it as a subquery by taking the stored select statement and optimizing it into the statement to produce an overall optimized statement.

                          In a query, if you have somehthing like:
                          select rownum as rank, last_name, salary
                          from (select last_name, salary
                                from   employees
                                order by salary desc)
                          where rownum <= 5;
                          your query is not selecting from a virtual or logical table, it's selecting from a subquery, which is also called an inline view.

                          Here's the documentation for you:

                          http://docs.oracle.com/cd/B19306_01/server.102/b14200/queries007.htm

                          >
                          A subquery answers multiple-part questions. For example, to determine who works in Taylor's department, you can first use a subquery to determine the department in which Taylor works. You can then answer the original question with the parent SELECT statement. A subquery in the FROM clause of a SELECT statement is also called an inline view
                          >

                          If that inline view is taken out of line from the main query, we create a view from it e.g.
                          create view myemps as
                          select last_name, salary
                          from employees
                          order by salary desc;
                          And the main query becomes:
                          select rownum as rank, last_name, salary
                          from myemps
                          where rownum <= 5;
                          The main query is selecting from a view (not a virtual table) and the view itself is just the subquery (aka inline view) that has been taken out of line. It doesn't magically turn into a table.

                          http://webhelp.esri.com/arcgisserver/9.3/java/index.htm#geodatabases/views_in_oracle.htm
                          >
                          Generally speaking, a view is a predefined, named query stored in the database. Once created, views can be queried in much the same way that tables can be queried. Views contain rows and columns like tables do. However, views are not tables but representations of data stored in, or calculated from, one or more other tables (business tables) or views. The columns of the view are the items from the select list of the query that defines the view.
                          >

                          If we start thinking that views are tables, we would start to think that we can do things like altering the view to add columns, just as we could a table, or altering it to change the datatypes of columns etc. (which is how this thread got started in the first place). The only similarity between a view and a table is that a view presents data in the form of rows and columns like a table does. Aside from that it exhibits non of the other properties of a table, whilst it does however exibit more of the properties of a subquery or stored select statement.

                          So a view is not just defined by a select statement, a view IS the select statement.
                          Your saying that a view is just defined by a select statement is like equating it to a "create table" statement that defines a table. That's simply not the case, as the subquery is retrieved and used by queries.
                          If you cannot see that, then your understanding of views is seriously flawed, but like I say, if it works for you then fine, I'm certainly not changing my technical understanding of what is, just to suit your abstract and logical view (scuse the pun :D) of what views are.
                          • 25. Re: NOT NULL
                            sql_coder
                            BluShadow wrote:
                            Ikrischer wrote:
                            BluShadow wrote:
                            Please demonstrate how it is wrong information. I've clearly demonstrated that it IS a stored SQL statement... black and white... there in the data dictionary. How is that wrong? How can you say it is not a stored statement or subquery when it's right there in front of you?
                            All you showed is, that a view is defined by a select statement, nothing more. Please refer to Oracle documents to show it is a stored statement. Oracle cleary says it is a logical table. If you think they said seomthing else, prove it with Oracle documents.
                            The view documentation itself refers to the query and subquery of the view throughout... it's all there in the documentation.
                            The Oracle ducmentation cleary says it is a logical table and there is no place in the Documentation where it says: A view is a stored SQL statement. If you can find that sentence rather saying it is all alround, I will change my mind.
                            BluShadow wrote:
                            If a view is just defined by a select statement, then it is used to create the table and that's it, it's use is up. But that's not the case. I also demonstrated that when the view is used in a query, the optimiser doesn't just treat it as if it's a table, but it treats it as a subquery by taking the stored select statement and optimizing it into the statement to produce an overall optimized statement.
                            Of course is does not treat a view 100% like a normal table, no one said that. A view does not have their own real records, cause it is a logical table. And to "collect" those logical records the sql statement is needed. If a view would be a stored statement, why do we need a second statement to get a result set ? Wont make sense if you ask me.
                            BluShadow wrote:
                            A subquery answers multiple-part questions. For example, to determine who works in Taylor's department, you can first use a subquery to determine the department in which Taylor works. You can then answer the original question with the parent SELECT statement. A subquery in the FROM clause of a SELECT statement is also called an inline view
                            Dont forget the next sentence "A subquery in the WHERE clause of a SELECT statement is also called a nested subquery." So subqueries used in a different way are different things. If a View would only be a stored statement, I could either use it at the same spot where I can use an inline view or where a nested subquery is used, but I cannot. I need to place a view in the FROM clause, I cannot not use it in the SELECT or WHERE clause without the help of another subquery.

                            This is not working:
                            SELECT * FROM t1 WHERE column_name = (view_name); -- should be a scalar view

                            this is working:
                            SELECT * FROM t1 WHERE column_name = (select_statement); -- should be a scalar select statement

                            There must be any difference between a view and a stored select statement or else I would be able to use it as a nested subquery as well.
                            BluShadow wrote:
                            The main query is selecting from a view (not a virtual table) and the view itself is just the subquery (aka inline view) that has been taken out of line. It doesn't magically turn into a table.
                            It turns into a logical table.
                            BluShadow wrote:
                            http://webhelp.esri.com/arcgisserver/9.3/java/index.htm#geodatabases/views_in_oracle.htm
                            Generally speaking, a view is a predefined, named query stored in the database. Once created, views can be queried in much the same way that tables can be queried. Views contain rows and columns like tables do. However, views are not tables but representations of data stored in, or calculated from, one or more other tables (business tables) or views. The columns of the view are the items from the select list of the query that defines the view.
                            uhh, you dont accept documents like wikipedia and you refer to a non oracle website ? Dont you think that is a bit unfair ? ;-) On the other hand, I agree with it, since they said "..of the query that defines the view."
                            BluShadow wrote:
                            If we start thinking that views are tables, we would start to think that we can do things like altering the view to add columns, just as we could a table, or altering it to change the datatypes of columns etc. (which is how this thread got started in the first place).
                            again, no one said it is a normal table, but a logical table. And cause of that, there must be any differences, that is no surprise.
                            BluShadow wrote:
                            So a view is not just defined by a select statement, a view IS the select statement.
                            Your saying that a view is just defined by a select statement is like equating it to a "create table" statement that defines a table.
                            Same here, I never said that and I wont say it. A view is not a normal table, but a logical table. It is similar to a normal table in many cases, but not in all. I dont know why you think that is the case over and over again ? If you can show me in a explicit way (the same way I showed you it is a logical table), Oracle says a view is a stored select statement, I will change my mind in the context of Oracle.

                            Ikrischer
                            • 26. Re: NOT NULL
                              6363
                              BluShadow wrote:
                              Stew Ashton wrote:
                              To quote the 11.2 Concepts Guide:
                              view
                              A custom-tailored presentation of the data in one or more tables. The views do not actually contain or store data, but derive it from the tables on which they are based.
                              I cannot find the expressions "virtual table" or "stored select statement" in the Oracle documentation, at least in the context of views.
                              No, it doesn't called it a "stored select statement", but does refer to it as the views "subquery":

                              http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_8004.htm#SQLRF54780, and this subquery is stored as the defining query of the view.
                              The Concepts manual does refer to a view as a stored query in the definition.

                              http://docs.oracle.com/cd/E11882_01/server.112/e25789/schemaob.htm#i20690

                              >
                              A view is a logical representation of one or more tables. In essence, a view is a stored query. A view derives its data from the tables on which it is based, called base tables. Base tables can be tables or other views.
                              >

                              In select statements a view could additionally be thought of as a virtual table, though this is in my opinion a gross simplification that is only really useful for very simple views. In other DML views behave differently dependent on the view definition. Very simple views could be thought of as a virtual table, but in most cases where the view contains joins or aggregates they cannot be used as a virtual table for DML and it is not useful to think of them as such.
                              • 27. Re: NOT NULL
                                Stew Ashton
                                Ikrischer wrote:
                                The Oracle ducmentation cleary says it is a logical table and there is no place in the Documentation where it says: A view is a stored SQL statement. If you can find that sentence rather saying it is all alround, I will change my mind.
                                From the Concepts document, http://docs.oracle.com/cd/E11882_01/server.112/e25789/schemaob.htm#i20690
                                A view is a logical representation of one or more tables. In essence, a view is a stored query.
                                Interestingly, a bit further down the document states:
                                Just as a view is a virtual table, an object view is a virtual object table.
                                So I stand by my position that you are both right, from different points of view: when we write a SQL statement, the view presents itself to us as a logical (or virtual) table, with restrictions that do not apply to real tables. When the database parses the SQL statement, the view name is just a place where the parser plugs in the defining query as a subquery.

                                P.S. I didn't see 3360's reply until I had posted mine.

                                Edited by: Stew Ashton on Feb 20, 2013 3:02 PM
                                • 28. Re: NOT NULL
                                  BluShadow
                                  Well found 3360 and Stew, I didn't think to look in the concepts manual (doh! silly me).

                                  I still don't like the word "virtual" for it, as a view is no way near being virtually a table. The only table properties it exhibits is that it presents data in rows and columns, but there are so many more properties of tables that it doesn't exhibit. Because there are far more table properties it doesn't posses than it does, I would say that it only resembles a table in the way it presents data, so would agree on using the word "logical" in that we can logically obtain data from it as we would a table, but the similarity stops there.

                                  As for Ikrisher's arguing that it's not a stored select statement, I still fail to see why he chooses to argue this. A view is clearly a select statement/query/subquery (take your choice) and it is clearly stored on the database (there in black and white in the data dictionary) and it clearly exhibits the behaviours of a subquery when used within a queries FROM clause, as demonstrated by the optimizer. Along with that we have now found (well 3360 and Stew have) where it states it in the documentation (and lets face it Oracle's documentation isn't know for it's consistency), so fortunately, there is somewhere in the documentation that it says it's a stored query.
                                  • 29. Re: NOT NULL
                                    sql_coder
                                    BluShadow wrote:
                                    As for Ikrisher's arguing that it's not a stored select statement, I still fail to see why he chooses to argue this.
                                    for the same reason a view is not a table. I wont even say a materialized view is the same as a normal table.

                                    - if you treat a view like a table, that wont be correct for all cases
                                    - if you treat a view like a stored select statement, that wont be correct for all cases
                                    -> if you treat a view like a logical/virtual table, that will be correct for all cases.

                                    Ikrischer