1 2 3 4 5 Previous Next 66 Replies Latest reply: Mar 15, 2013 9:34 AM by BluShadow Go to original post RSS
      • 45. Re: NOT NULL
        sql_coder
        3360 wrote:
        Ikrischer wrote:
        - A view is a virtual Table (what you denied)
        It does not say this, the virtual table is something you have incorrectly made up.
        "Just as a view is a virtual table" statement from the same document that you linked (Database Concepts)

        Ikrischer
        • 46. Re: NOT NULL
          6363
          Ikrischer wrote:
          3360 wrote:
          Ikrischer wrote:
          - A view is a virtual Table (what you denied)
          It does not say this, the virtual table is something you have incorrectly made up.
          "Just as a view is a virtual table" statement from the same document that you linked (Database Concepts)
          So you are saying the documentation means to define a view as this based on that phrase?

          http://docs.oracle.com/cd/E11882_01/java.112/e10588/chtwelve.htm#JJDEV13418

          >
          To access data that is returned from single or multiple Web service invocations, create a virtual table using a Web service data source. This table lets you query a set of returned rows as though it were a table.

          The client calls a Web service and the results are stored in a virtual table in the database.
          >

          Which is consistent with virtual columns where data is stored.

          And still no response on the view is a stored query?

          Why do you never address that definition except to completely ignore it?
          • 47. Re: NOT NULL
            BluShadow
            Pointless, illogical and innacurate arguments based on misrepresentation. Not worth answering all the points you've raised as they've already been answered before.

            All I can say is, you made an outright statement...
            this is wrong, a view is not a stored select statement
            ... and you are proven wrong. (yes, not all select statements are subqueries, but all subqueries are select statements)
            • 48. Re: NOT NULL
              sql_coder
              BluShadow wrote:
              this is wrong, a view is not a stored select statement
              ... and you are proven wrong.
              same for you, you said it is not a virtual table and it is a virutal table, "proven" in the same oracle document. So both made a wrong statement but that is not the point, this is not about who is wrong or right, but what is wrong or right. The informations from oracle document wont fit together if you ask me, they are inconsistent. So we should focus on the content. A view is a set (for me as a resultset from a query) and thats why it is a virtual table and not a statement.
              BluShadow wrote:
              (yes, not all select statements are subqueries, but all subqueries are select statements)
              well, I agree on that, all subqueries are statements, but that wont make a view the same as a select statement. Besides that a view is also not a subquery, but a set. The point is not what I use (query, statement, subquery), but where I use it, at the same place where I would use a table, for example in the from clause.

              Ikrischer
              • 49. Re: NOT NULL
                BluShadow
                Ikrischer wrote:
                BluShadow wrote:
                this is wrong, a view is not a stored select statement
                ... and you are proven wrong.
                same for you, you said it is not a virtual table and it is a virutal table, "proven" in the same oracle document. So both made a wrong statement but that is not the point, this is not about who is wrong or right, but what is wrong or right. The informations from oracle document wont fit together if you ask me, they are inconsistent. So we should focus on the content. A view is a set (for me as a resultset from a query) and thats why it is a virtual table and not a statement.
                A view is not a resultset. The results of querying a view is a result set of data, but the view itself is not a result set. It's a query (subquery). Otherwise you could just as easily say that any select statement is a virtual table or result set... which is clearly wrong.

                Documentation: http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_8004.htm#SQLRF01504
                >
                A view contains no data itself
                >

                >
                BluShadow wrote:
                (yes, not all select statements are subqueries, but all subqueries are select statements)
                well, I agree on that, all subqueries are statements, but that wont make a view the same as a select statement. Besides that a view is also not a subquery
                How can you say a view is not a subquery?

                Documentation: http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_8004.htm#SQLRF01504
                Syntax shows clearly:
                create view <viewname> as <subquery>
                , but a set. The point is not what I use (query, statement, subquery), but where I use it, at the same place where I would use a table, for example in the from clause.
                Well, I use it where I would use a subquery in the from clause. And knowing it's a subquery rather than a table, I know that the optimizer will potentially optimize the subquery into the main query to provide the most optimal data access from the base tables of the subquery and main query tables. If I just treated the view as some sort of table, I would not expect the optimizer to treat it any differently from a table... but the fact is that it does... it treats it like a subquery.

                Documentation clearly states it's a subquery...
                >
                Specify a subquery that identifies columns and rows of the table(s) that the view is based on. The select list of the subquery can contain up to 1000 expressions.

                If you create views that refer to remote tables and views, then the database links you specify must have been created using the CONNECT TO clause of the CREATE DATABASE LINK statement, and you must qualify them with a schema name in the view subquery.
                >


                So, you can say as many times as you like that it's not a stored select statement/subquery and that you think it's a virtual table, but that doesn't change the fact that it is proven that the view is a subquery that is stored on the database, and is treated like a subquery by the optimiser when it's used in queries, rather than just a table, and that it certainly does not store any set of data like a table.

                I would agree with you that the documentation is ambiguous in it's descriptions across all the documentation available, but the main ambiguity is that they've referred to it as anything but a subquery.

                If it really is some sort of virtual table, then please demonstrate where this "result set" of data is? After all, I went to the trouble of demonstrating where the subquery is.... stored on the database, as well as demonstrating that the optimizer will treat it as a subquery when optimizing the main query. I work on the technical development of databases, so understanding how the technicalities of the database and works together is important. A "virtual table" is not something technical, and doesn't relate to what the database is doing internally. To an end user who doesn't need to understand the internal technicalities, or concern themselves with query optimization, yes, they could easily get away with a high level concept of a view being some kind of table (they don't even need to know it's a view, they can just be told it's a table), but such a high level concept doesn't lend itself to good technical design at the backend of the database.

                So, like I said at the beginning, if you want to see it as a virtual table, and that works for you, then that's fine and that's your choice, but please don't try and dictate to a technical community that they are wrong in understanding a view as a stored subquery with all the properties of a subquery that go along with it, and tell them that it should be considered some sort of high level "virtual table" concept that ignores the important aspects of technical database and application design.
                • 50. Re: NOT NULL
                  Nitesh.
                  So you ppl started again .. I posted 3 threads but no one having time to reply or give any suggestions but her still debate is continuing .. Especiially Blushadow whom i expected as will give atleast one reply to my thread but you are buzz here over long debate which dont have any end seems ...


                  Sorry but i will call it as argumentations onlyy..


                  Rgds,
                  Nit..
                  • 51. Re: NOT NULL
                    Stew Ashton
                    Niteshprk wrote:
                    I posted 3 threads but no one having time to reply or give any suggestions but her still debate is continuing
                    I don't understand. All your recent questions are marked as answered?
                    • 52. Re: NOT NULL
                      Nitesh.
                      Ofcourse stew you only help'd know .. Forgot ah :) But i meant as here debate goin on but there was no reply when i posted threads dude..


                      Rds,
                      Nit.
                      • 53. Re: NOT NULL
                        BluShadow
                        Niteshprk wrote:
                        So you ppl started again .. I posted 3 threads but no one having time to reply or give any suggestions but her still debate is continuing .. Especiially Blushadow whom i expected as will give atleast one reply to my thread but you are buzz here over long debate which dont have any end seems ...


                        Sorry but i will call it as argumentations onlyy..
                        You shouldn't expect anyone to give answers to your threads.
                        Everyone here is a volunteer with their own jobs to do, so it is entirely up to each individual how much they post, and what they choose to answer (and of course it will depend if they are able to answer, depending on what the question is about, and whether the question has been asked well).
                        I've responded on this thread as it's a discussion that I'm involved in. There's nothing that says you should receive special treatment and everyone should drop what they're doing to come and answer your question, and it's damn rude to assume otherwise.
                        • 54. Re: NOT NULL
                          BluShadow
                          Niteshprk wrote:
                          Ofcourse stew you only help'd know .. Forgot ah :) But i meant as here debate goin on but there was no reply when i posted threads dude..


                          Rds,
                          Nit.
                          If you have nothing to contribute to the topic of this thread, then it will be considered as 'hijacking' or 'spam' and dealt with as necessary. Please do not continue to post here unless you have something of value to add.
                          • 55. Re: NOT NULL
                            Nitesh.
                            See you got angry once again .. Okay fine continue sorry for interruption that may have caused due to me in this thread..
                            • 56. Re: NOT NULL
                              sql_coder
                              BluShadow wrote:
                              >
                              A view is not a resultset. The results of querying a view is a result set of data, but the view itself is not a result set. It's a query (subquery). Otherwise you could just as easily say that any select statement is a virtual table or result set... which is clearly wrong.
                              A select statement is not a result set, but if you execute it, you will get a result set. And the question is, how you interpret a view, like the query statement or like the result set of that query if you excecute it. You say treat it like a query statement, I say you better handle it like a set (result set), that you get from executing of the defining query. Or in answer words, you better query a set than query a query.
                              BluShadow wrote:
                              Documentation: http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_8004.htm#SQLRF01504
                              >
                              A view contains no data itself
                              >
                              Yes, a view is a logical/virtuall objekt, it is not a base table, so it does not "own" real data, we had this issue already. But it wont even make sense to highlight a view has no own data, if a view would be a statement, since a query statement can never have their own data, not matter if you talk about real or virtuell data. And as you can see, they talk about a logical table in the context of no data.
                              BluShadow wrote:
                              How can you say a view is not a subquery?

                              Documentation: http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_8004.htm#SQLRF01504
                              Syntax shows clearly:
                              create view <viewname> as <subquery>
                              So what, you can do the same with a table, CREATE TABLE <tablename> AS <subquery>. Does that mean the table is a query statement like a view ? It is just the way to create an objekt, but the way is not the objekt.
                              BluShadow wrote:
                              Well, I use it where I would use a subquery in the from clause. And knowing it's a subquery rather than a table, I know that the optimizer will potentially optimize the subquery into the main query to provide the most optimal data access from the base tables of the subquery and main query tables. If I just treated the view as some sort of table, I would not expect the optimizer to treat it any differently from a table... but the fact is that it does... it treats it like a subquery.
                              well, first you said to use it like a select statement, now it is a subquery, but it is the same problem, you can use a subquery not only in the FROM clause. So you have two different ways to handle the view, in the FROM where you dont need to enclose the view into another subquery and for example in the WHERE clause where you need to write another subquery "around" the view. If you use a view as a set, there are no different ways to handle it, it will be always used as a set.
                              BluShadow wrote:
                              Documentation clearly states it's a subquery...
                              >
                              Specify a subquery that identifies columns and rows of the table(s) that the view is based on. The select list of the subquery can contain up to 1000 expressions.

                              If you create views that refer to remote tables and views, then the database links you specify must have been created using the CONNECT TO clause of the CREATE DATABASE LINK statement, and you must qualify them with a schema name in the view subquery.
                              >
                              sorry BluShadow, but I cannot see one word that a view is a subquery. All I can see is, that the subquery defines (identifies) which rows and columns of a base table belongs to the view.
                              BluShadow wrote:
                              So, you can say as many times as you like that it's not a stored select statement/subquery and that you think it's a virtual table, but that doesn't change the fact that it is proven that the view is a subquery that is stored on the database, and is treated like a subquery by the optimiser when it's used in queries, rather than just a table, and that it certainly does not store any set of data like a table.
                              Besides the fact, that the optimizer wont prove if a view is a subquery or defined by a subquery, do you mean the optimizer will always use the full statement of a view ?
                              BluShadow wrote:
                              I would agree with you that the documentation is ambiguous in it's descriptions across all the documentation available, but the main ambiguity is that they've referred to it as anything but a subquery.
                              well this makes it hard for both sides. We could skip the Oracle documents till they are not ambiguous anymore and refer to Wikipedia defintion of a view. But I fear I cannot convince you to do this ;-) Or we need to wait and hope it will clear out in future documentations.
                              BluShadow wrote:
                              If it really is some sort of virtual table, then please demonstrate where this "result set" of data is?
                              I dont really know what you mean, do you mean every result set needs to be stored somewhere ?
                              BluShadow wrote:
                              A "virtual table" is not something technical, and doesn't relate to what the database is doing internally. To an end user who doesn't need to understand the internal technicalities, or concern themselves with query optimization, yes, they could easily get away with a high level concept of a view being some kind of table (they don't even need to know it's a view, they can just be told it's a table), but such a high level concept doesn't lend itself to good technical design at the backend of the database.
                              Well, even SQL is not what the computer is doing internal, a table is nothing more then a logical objekt. Yes the table data is stored somewhere on a hard disk, but not as a table. So basicly every step besides byte and machine code is just logical. Now we could start a new debate if a view is on a higher logical level than a table is, but let us skip that...

                              Ikrischer
                              • 57. Re: NOT NULL
                                BluShadow
                                Ikrischer wrote:
                                BluShadow wrote:
                                >
                                A view is not a resultset. The results of querying a view is a result set of data, but the view itself is not a result set. It's a query (subquery). Otherwise you could just as easily say that any select statement is a virtual table or result set... which is clearly wrong.
                                A select statement is not a result set, but if you execute it, you will get a result set. And the question is, how you interpret a view, like the query statement or like the result set of that query if you excecute it. You say treat it like a query statement, I say you better handle it like a set (result set), that you get from executing of the defining query. Or in answer words, you better query a set than query a query.
                                But looking at a view at such an abstract level removes any knowledge of how the optimizer is actually going to process your query. If you include a view in a query you write, it is the same as if you had written the views subquery as a subquery in your statement, and it will be optimized as such. If you start to treat it like a table or set, then you are ignoring key technological design considerations for your overall query. As I said, if you want to do that and it works for you then fine, but that's not what Oracle is doing with it.
                                BluShadow wrote:
                                How can you say a view is not a subquery?

                                Documentation: http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_8004.htm#SQLRF01504
                                Syntax shows clearly:
                                create view <viewname> as <subquery>
                                So what, you can do the same with a table, CREATE TABLE <tablename> AS <subquery>. Does that mean the table is a query statement like a view ? It is just the way to create an objekt, but the way is not the objekt.
                                You're clearly confusing (or being deliberately obtuse about) the fact that a create table statement with a subquery, performs the operation of creating the physical table structure and executes that subquery to populate the table with data. That is irrelevant and incomparible to the creation of a view, where the creation takes the create statement with it's subquery and stores that subquery on the database... a subquery which will be retrieved and used by Oracle when the view is specified in other queries.
                                BluShadow wrote:
                                Well, I use it where I would use a subquery in the from clause. And knowing it's a subquery rather than a table, I know that the optimizer will potentially optimize the subquery into the main query to provide the most optimal data access from the base tables of the subquery and main query tables. If I just treated the view as some sort of table, I would not expect the optimizer to treat it any differently from a table... but the fact is that it does... it treats it like a subquery.
                                well, first you said to use it like a select statement, now it is a subquery, but it is the same problem, you can use a subquery not only in the FROM clause. So you have two different ways to handle the view, in the FROM where you dont need to enclose the view into another subquery and for example in the WHERE clause where you need to write another subquery "around" the view. If you use a view as a set, there are no different ways to handle it, it will be always used as a set.
                                I put my hands up and admit I made a mistake. When I agreed with you (and you agreed with me) that a select statement is not a subquery but a subquery was a select statement, I was actually wrong, I don't agree with that. A select statement IS a subquery... they are synonymous.

                                Documentation: http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_10002.htm#i2065706

                                >
                                select::=subquery [ for_update_clause ] ;
                                >

                                So the terms "Select statement", "query" and "subquery" can all be used interchangably.

                                First you said a view was a virtual table and now you say a view is a "set". The results returned from querying a view may be a set of records, just as if you queried a table, or a pipelined function that generates data as if it were a table or set of results, but a view in itself is NOT a set... never has been. I've provided technical evidence to show that when a view is created, the subquery is stored on the database (the stored select statement), and I've provided evidence to show that the optimizer will take that stored subquery and integrate it into the main query you use the view in, so that the subquery is optimized down to the base tables etc. clearly showing the view is taken as a subquery and not just like a table or result set. Could you provide a demonstration of how a view is a table or set, and how it is not a stored subquery? So far, you've not done this, you've just talked at some abstract level that ignores all the technical properties a view has in relation to it being a stored subquery.
                                BluShadow wrote:
                                Documentation clearly states it's a subquery...
                                >
                                Specify a subquery that identifies columns and rows of the table(s) that the view is based on. The select list of the subquery can contain up to 1000 expressions.

                                If you create views that refer to remote tables and views, then the database links you specify must have been created using the CONNECT TO clause of the CREATE DATABASE LINK statement, and you must qualify them with a schema name in the view subquery.
                                >
                                sorry BluShadow, but I cannot see one word that a view is a subquery. All I can see is, that the subquery defines (identifies) which rows and columns of a base table belongs to the view.
                                When Oracle comes to use a view, it doesn't just look at the view and get information back about what rows and columns it will provide and then magically provide a set of date in table format for the rest of the main query to use... it actually takes the subquery that is stored on the database, places that into the main query, and then goes about optimizing the whole statement. The subquery of the view itself does not necessarily remain as some isolated unit providing data to the main query, it becomes optimized into it, just as if the stored subquery had been written directly in the main query itself. If you cannot see that, you are completely missing all the evidence presented to you, and that's your issue not ours.
                                BluShadow wrote:
                                So, you can say as many times as you like that it's not a stored select statement/subquery and that you think it's a virtual table, but that doesn't change the fact that it is proven that the view is a subquery that is stored on the database, and is treated like a subquery by the optimiser when it's used in queries, rather than just a table, and that it certainly does not store any set of data like a table.
                                Besides the fact, that the optimizer wont prove if a view is a subquery or defined by a subquery, do you mean the optimizer will always use the full statement of a view ?
                                Do we need to say it again? The optimizer will take the stored subquery of the view and optimize it into the query that is using it.
                                BluShadow wrote:
                                I would agree with you that the documentation is ambiguous in it's descriptions across all the documentation available, but the main ambiguity is that they've referred to it as anything but a subquery.
                                well this makes it hard for both sides. We could skip the Oracle documents till they are not ambiguous anymore and refer to Wikipedia defintion of a view. But I fear I cannot convince you to do this ;-) Or we need to wait and hope it will clear out in future documentations.
                                Not sure why you choose to believe Wikipedia as if it is the holy grail of documentation. a) it's written by the general public b) it's peer reviewed by the general public c) it's written generically to cover more than one RDBMS (as I mentioned previously, views on other RDBMS's are implemented differently). d) I could go onto that page on Wiki and edit it myself to say something different... does that mean that what it would then say would be right?

                                Oracle itself admits that it's documentation is not (and will never likely be) 100% accurate, and that's why they provide the facility to report issues or inaccuracies. At one point PL/SQL arrays and collections were called "PL/SQL Tables", but Oracle recognised it's error (and the confusion caused) and has corrected the documentation to give them their more accurate name, though it's possible to still find some deep dark recesses of the documentation that have been missed and still refers to PL/SQL Tables. At an abstract level, yes you could look at a view in terms of it being a kind of table, and that will be suitable for some end user (reporting user perhaps?) who doesn't need to understand the technicalities... but for those who are technically designing queries and heavily involved in the design of the database, understanding that views are stored subqueries are key to understanding the design and optimization that takes place.
                                BluShadow wrote:
                                If it really is some sort of virtual table, then please demonstrate where this "result set" of data is?
                                I dont really know what you mean, do you mean every result set needs to be stored somewhere ?
                                No, you say a view is a result set. Sure, a view can produce a result set when it's queried as a product of it's use, but in and of itself a view is not a result set. If you believe otherwise, please demonstrate how this view IS a (result) set, in and of itself.
                                BluShadow wrote:
                                A "virtual table" is not something technical, and doesn't relate to what the database is doing internally. To an end user who doesn't need to understand the internal technicalities, or concern themselves with query optimization, yes, they could easily get away with a high level concept of a view being some kind of table (they don't even need to know it's a view, they can just be told it's a table), but such a high level concept doesn't lend itself to good technical design at the backend of the database.
                                Well, even SQL is not what the computer is doing internal, a table is nothing more then a logical objekt. Yes the table data is stored somewhere on a hard disk, but not as a table. So basicly every step besides byte and machine code is just logical. Now we could start a new debate if a view is on a higher logical level than a table is, but let us skip that...
                                We're not talking about what the computer is doing internally, otherwise I could go down to the level of p-code on the BIOS and the movement of data on the memory bus... To suggest such is just being antagonistic. The point was that a virtual table is at an abstract level that does not describe the technical truth of how a view is stored on the database or used within queries. As a technical designer and user of views, they need to be understood as being stored subqueries, as proven and demonstrated.
                                • 58. Re: NOT NULL
                                  APC
                                  Ikrischer, Blu, are you two still at it? Well we must salute your indefatigability!


                                  Cheers, APC
                                  • 59. Re: NOT NULL
                                    BluShadow
                                    APC wrote:
                                    Ikrischer, Blu, are you two still at it? Well we must salute your indefatigability!


                                    Cheers, APC
                                    :)

                                    What is one to do when being told that what has been said is wrong, when it's proven otherwise. Apparently, it seems, I have to change my understanding...

                                    repeat after me... a view is not a stored subquery
                                    repeat after me... a view is not a stored select statement
                                    repeat after me... a view is not treated by oracle or the optimizer as a stored subquery
                                    repeat after me... a view is a virtual table
                                    repeat after me... a view is a result set

                                    ... hmmmm... nope it just doesn't seem to be working. :D


                                    Edit: Just done a quick search on the CREATE VIEW documentation page (http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_8004.htm#SQLRF01504)...

                                    Word "virtual" : 0 matches
                                    Word "set" : 3 matches (non of which refer to a result set)
                                    Word "result" : 1 match (not referring to a result set)
                                    Word "logical" : 1 match (at the start referring to a view being a logical table)
                                    Word "subquery" : 32 matches (all referring to the subquery of the view)

                                    Sure looks like a subquery to me. :)