1 2 3 4 5 Previous Next 66 Replies Latest reply: Mar 15, 2013 9:34 AM by BluShadow Go to original post RSS
      • 30. Re: NOT NULL
        Stew Ashton
        Ikrischer wrote:
        -> if you treat a view like a logical/virtual table, that will be correct for all cases.
        Are you familiar with the expression "begging the question"?

        Since you say a view is a logical/virtual table, "treating a view like a logical/virtual table" simply means "treating a view like a view".

        What if I said "If you treat an animal like a human, that will be correct for all cases". No, because a human belongs to a species of animal. There are animals that are not humans.

        In the same way, a view is a specific kind of logical table. The Oracle documentation mentions other kinds of "logical tables" that are not views.

        Anyway, you said
        there is no place in the Documentation where it says: A view is a stored SQL statement. If you can find that sentence ... I will change my mind.
        We found the sentence, so start changing.
        • 31. Re: NOT NULL
          6363
          Ikrischer wrote:
          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
          Correct
          - if you treat a view like a stored select statement, that wont be correct for all cases
          Incorrect, a view will always be the result of a stored select statement.
          -> if you treat a view like a logical/virtual table, that will be correct for all cases.
          Incorrect, a view with aggregates or non-key preserved joins for example, cannot be inserted into or updated so is unlike a logical / virtual table in these instances.
          • 32. Re: NOT NULL
            sql_coder
            3360 wrote:
            Incorrect, a view will always be the result of a stored select statement.
            I never disagreed a view is the resultset of a stored select statement, I said it is not a stored select statement, that is a difference.
            3360 wrote:
            Incorrect, a view with aggregates or non-key preserved joins for example, cannot be inserted into or updated so is unlike a logical / virtual table in these instances.
            Do not mix the features of a normal table with a view again. Even there are updatable an non updatebale views, both are still logical tables. No one said all views/logical tables are updatable (also there exists a WITH READ ONLY clause for updatable Views). So an external table it is not table anymore cause you cannot update the table ? If you would read the articale you linked an scroll a bit more down you could see that Oracle talks from a query that defines a view rather then it is a query.

            - "For example, a single view can be defined with a join..."
            - "For example, if the defining query of a view..."
            - "As with all subqueries, the query that defines a view..."
            - "Rather, a view is defined by a query that extracts or derives data from the base tables referenced by the view."
            - "The defining query..."

            how to access a view:

            - "Merges a query (whenever possible) against a view with the queries that define the view and any underlying views"
            - "Sometimes Oracle Database cannot merge the view definition with the user query. In such cases, Oracle Database may not use all indexes on referenced columns."

            here is one more info about the data dictionary:
            - "Because a view is based on other objects, it requires no storage other than storage for the query that defines the view in the data dictionary."
            - "Oracle Database stores a view definition in the data dictionary as the text of the query that defines the view."

            if you read this document http://docs.oracle.com/cd/E11882_01/server.112/e25494/views001.htm#ADMIN11774 you get more infos thats a query defines a view:
            - "the database can create the view even if the defining query of the view cannot be executed"
            - "If the definition of a view must change, the view must be replaced; you cannot use an ALTER VIEW statement to change the definition of a view"

            and that is exactly what I say, a query is defined by a query and the resultset is the view / logical table

            Ikrischer
            • 33. Re: NOT NULL
              6363
              Ikrischer wrote:
              3360 wrote:
              Incorrect, a view will always be the result of a stored select statement.
              I never disagreed a view is the resultset of a stored select statement, I said it is not a stored select statement,
              And we said a view is a stored select statement and that you are wrong.

              {message:id=10862009}
              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.
              Which I did.

              {message:id=10862094}
              3360 wrote:

              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.
              >
              So have you changed your mind about a view not being a stored query because it says it is in the documentation like you said you would?

              >
              3360 wrote:
              Incorrect, a view with aggregates or non-key preserved joins for example, cannot be inserted into or updated so is unlike a logical / virtual table in these instances.
              Do not mix the features of a normal table with a view again.
              I am not, I am making a clear distinction and saying that a view, being a stored select statement, has completely different features of a table and cannot be mixed. It is this simplistic virtual table definition of yours that mixes the features of views and tables
              If you would read the articale you linked an scroll a bit more down you could see that Oracle talks from a query that defines a view rather then it is a query.

              - "For example, a single view can be defined with a join..."
              Yes a view can be defined with a join because a query can be defined with a join and a view is a stored query and you were wrong when you said it wasn’t.
              - "For example, if the defining query of a view..."
              - "As with all subqueries, the query that defines a view..."
              - "Rather, a view is defined by a query that extracts or derives data from the base tables referenced by the view."
              - "The defining query..."
              Are you having difficulty with the word defining? The view is a stored query and that query defines the view.

              And so on endlessly.
              and that is exactly what I say, a query is defined by a query and the resultset is the view / logical table
              This still makes no sense. You are saying a view is a query but the result of a view is a logical view or table which are both the same thing except a view is a query? What about tables and views combined in a query that have no result set? What are they? Have you got something better to do than make this stuff up like organize a kindergarten debating society or something?
              • 34. Re: NOT NULL
                894936
                Hi Team,

                Thanks a lot

                Thanks,
                MR.
                • 35. Re: NOT NULL
                  894936
                  Hi All,

                  thanks a lot for your support.

                  Thanks,
                  MR
                  • 36. Re: NOT NULL
                    Nitesh.
                    Frankly saying, i have enjoyed this thread a lot as this much argumentations ..
                    • 37. Re: NOT NULL
                      BluShadow
                      Ikrischer wrote:
                      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
                      It's been correct for all cases I've every worked on. Knowing the view is a select statement (subquery) that is stored on the database and integrated into queries that use it, especially by the optimizer, means I know all well and fine how it's going to work with my queries.
                      -> if you treat a view like a logical/virtual table, that will be correct for all cases.
                      So, let's alter the view to add a column like we can do with tables...
                      SQL> alter view myemps add (bonus number);
                      alter view myemps add (bonus number)
                                             *
                      ERROR at line 1:
                      ORA-00922: missing or invalid option
                      Well, it doesn't work in that case.

                      I never disagreed a view is the resultset of a stored select statement,
                      A view isn't a resultset. A resultset is a set of data and a view does not store data. It may present a result set when it is queried, but it isn't a result set in itself.
                      I said it is not a stored select statement, that is a difference.
                      If you create a table using:
                      create table mytable (x number, y number);
                      ... this statement defines the structure of the table. The structure is created within the database, but the statement that defined the table (the create table statement) is not stored in the database, though it could be reverse engineered from the information stored in the dictionary.
                      The columns x and y are split out from the statement and stored in their own rows within the data dictionary, assigned other internal properties that are not included in the create statement etc. etc.

                      If we create a view using:
                      create view myview as select * from emp;
                      ... likewise the "create view" statement is not stored in the database. However the select statement (subquery) itself is taken and stored in the database (after the statements projection is parsed and expanded e.g. "*" becomes the column names at the time it's compiled)

                      When a table is used in a query, the optimiser looks at the table as a whole, along with it's statistics, to determine whether to incorporate the table itself or use it's indexes etc. before it starts to actually access the data from it.
                      When a view is used in a query, the select statement (subquery) that has been stored in the database is retrieved and put into the main query in the style of an inline view, before the optimiser then determines the best access path for the overall query. In the main orginal query the view name is just a placeholder (almost like a substitution variable in SQL*Plus, but not quite) where the stored subquery of the view is put before optimization takes place.

                      Those two things are very very different.

                      The view is created by specifying a select statement (subquery). That select statement is stored on the database (proven fact, you've seen it). How can you say that it is not a stored select statement, when it's proven that the select statement is stored on the database, and you've even now been provided with a place in the documentation where it states it?

                      The word "virtual" means something is almost, but just not quite, as if it were the real thing. You call a view a virtual table.
                      So what properties does a tables and views have and what is similar between them:

                      1.
                      t) Table is created with a create table statement that defines a physical structure for storing data - The statement itself is not stored.
                      v) View is created with a create view statement that defines a query to be stored on the database - The subquery part of the statement is stored.
                      Case 1: Tables and views create different things in the database in different ways

                      2.
                      t) Table has a physical structure for storing data within the database.
                      v) View does not have a physical structure for storing data within the database.
                      Case 2: Tables and views are different

                      3.
                      t) Tables presents data in rows and columns
                      v) View presents data in rows and columns
                      Case 3: Tables and views present data the same

                      4.
                      t) Tables can have indexes to change the access to the data
                      v) Views cannot have indexes on them
                      Case 4: Tables and views are different

                      5.
                      t) Tables can have their structure altered to add columns or change properties at an atomic column level
                      v) Views cannot have their structure altered without replacing the whole subquery that is stored on the database
                      Case 5: Tables and views are altered differently

                      6.
                      t) Tables can have data inserted, updated and deleted
                      v) Views act as an interface to the base tables for inserted, updated and deleted data, which also has restrictions based on whether the view contains aggregates, or distinct etc. often requiring special "instead of" triggers to handle such.
                      Case 6: Tables and views typically differ greatly in how DML can be performed on them.

                      7.
                      t) Tables can be defined to have different physical structures for storing and accessing data such as IOT tables, External Tables, Compression etc.
                      v) A view has one structure... that of a subquery, as it doesn't have a physical way of storing data
                      Case 7: Tables can store data in different ways, while Views do not store data at all

                      8.
                      t) Tables can enforce data integrity contraints with other tables
                      v) Views do not enforce data integrity across tables
                      Case 8: Tables and views are different in terms of data integrity

                      9.
                      t) Tables are optimized in queries based on their statistics and indexes
                      v) Views are optimized in queries by embedding the stored subquery into the main query and optimizing the base tables.
                      Case 9: Tables and views are treated differently by the CBO

                      10.
                      t) Tables can have virtual columns to provide data that is dynamically calculated from the content of other data, but those virtual columns must be defined as deterministic.
                      v) Views can supply data that is dynamically calculated from the content of other data, but these do not need to be deterministic.
                      Case 10: Calculated data is defined as deterministic for tables, but not for views.

                      ... and there are others if we want to get more technical.

                      If a view is virtually anything, it is virtually not a table. The only passing resemblence it has to a table is the way it presents the data in rows and columns. There are far more differences than there are similarites, so it is certainly wrong to say a view is virtually a table (or a "virtual table").
                      Logically, the data is presented to us in the form of a tables, but that is just logically when looking at it from the data presentation point of view, that's all.

                      So, the documenation says it's a stored subquery. Do you still want to deny that fact?

                      After all, you've quoted it yourself...
                      here is one more info about the data dictionary:
                      - "Because a view is based on other objects, it requires no storage other than storage for the query that defines the view in the data dictionary."
                      - "Oracle Database stores a view definition in the data dictionary as the text of the query that defines the view."
                      Clearly stating that the query is stored in the data dictionary.

                      As for your trying to use those as arguments that it's just a 'definition' that is seriously flawed as you are looking at it from the wrong angle. The query defines what the view is in itself, but that query is also extracted and used, it's not just a definition that does nothing afterwards like you would have with a "create table " definition statement.
                      In fairness though, I would say the Oracle documentation is using poor English in terms of the word "define".
                      The view (create view statement) defines the query that is stored on the database, but the query itself does not define the view, as it's just a query. It's like them saying that "mammals" defines humans, dogs, cats etc. and therefore "humans" can be used to define mammals... which is clearly wrong.

                      They should say something like "Oracle Database stores a view definition in the data dictionary as the text of the query that _is defined by_ the view."

                      Ah, well, it's a lot of documentation, and not everything is going to be perfect. ;)
                      • 38. Re: NOT NULL
                        BluShadow
                        Niteshkhush wrote:
                        Frankly saying, i have enjoyed this thread a lot as this much argumentations ..
                        There's a difference between arguments and debate. I personally see this as a debate. An arguments would be agressive and offensive, and I for one certainly have not intended any aggression or offence.
                        • 39. Re: NOT NULL
                          Paul  Horth
                          BluShadow wrote:
                          Niteshkhush wrote:
                          Frankly saying, i have enjoyed this thread a lot as this much argumentations ..
                          There's a difference between arguments and debate. I personally see this as a debate. An arguments would be agressive and offensive, and I for one certainly have not intended any aggression or offence.
                          Well there's many shades of meaning of argument:

                          >
                          1.
                          an oral disagreement; verbal opposition; contention; altercation: a violent argument.
                          2.
                          a discussion involving differing points of view; debate: They were deeply involved in an argument about inflation.
                          3.
                          a process of reasoning; series of reasons: I couldn't follow his argument.
                          4.
                          a statement, reason, or fact for or against a point: This is a strong argument in favor of her theory.
                          5.
                          an address or composition intended to convince or persuade; persuasive discourse.
                          • 40. Re: NOT NULL
                            APC
                            BluShadow wrote:
                            There's a difference between arguments and debate.
                            No there isn't. Oh, I'm sorry, just one moment. Is this a five minute argument or the full half hour?

                            Cheers, APC
                            • 41. Re: NOT NULL
                              Paul  Horth
                              No there isn't.
                              That's not an argument, that's just contradiction.
                              Oh, I'm sorry, just one moment. Is this a five minute argument or the full half hour?
                              You only paid for the 5 minute argument.

                              Edited by: Paul Horth on Feb 21, 2013 10:05 AM
                              • 42. Re: NOT NULL
                                Nitesh.
                                Now once again frankly saying as i dont want to argue on this as 'Whether this is an argument or debate' and yes APC I am satisfied with your words as even i too unable to understand as how debate can be extended to this much :)

                                Edited by: Niteshkhush on Feb 21, 2013 4:02 PM
                                • 43. Re: NOT NULL
                                  sql_coder
                                  Hello BluShadow,
                                  BluShadow wrote:
                                  It's been correct for all cases I've every worked on. Knowing the view is a select statement (subquery) that is stored on the database and integrated into queries that use it, especially by the optimizer, means I know all well and fine how it's going to work with my queries.
                                  Not every select statement is a subquery. So do you want to say a view is a stored select statement or is a stored subquery ?
                                  BluShadow wrote:
                                  Ikrischer wrote:
                                  -> if you treat a view like a logical/virtual table, that will be correct for all cases.
                                  So, let's alter the view to add a column like we can do with tables...
                                  I dont know why you still try to handle a virtual table like a normal table for all cases, no one said you can do it. Views are similar to normal tables, but not the same, what you want to prove with it ?
                                  BluShadow wrote:
                                  A view isn't a resultset. A resultset is a set of data and a view does not store data. It may present a result set when it is queried, but it isn't a result set in itself.
                                  Do you want to say every record and data in a resultset must be stored in a table ?
                                  BluShadow wrote:
                                  The view is created by specifying a select statement (subquery). That select statement is stored on the database (proven fact, you've seen it). How can you say that it is not a stored select statement, when it's proven that the select statement is stored on the database, and you've even now been provided with a place in the documentation where it states it?
                                  The statement needs to be stored, no matter if you think a view is a virtual table and defined by a select statement or if you think it is a stored statement. That wont prove anything. A virtual table needs to be "filled" with their virtual records and what is the best way in a rdbms to grab a record set ? it is a sql query. But in theory, you dont need to use a query, you could use other ways to build a view and their virtual records out of the base tables. It would be transparent for you and the use of a view, if the view is build out of a sql query or a different way, it wont matter, but it wont makes sense, since you already have sql.

                                  We talk in the context of oracle and what is proven in the documents is:
                                  - A view is a logical Table (that term is used in the CREATE Section)
                                  - A view is a virtual Table (what you denied)
                                  - A view is a stored Query (what I denied)
                                  - A view is defined by a query (you deny that ?)

                                  I dont know what you about think about it, but for me those sentences wont fit together.
                                  BluShadow wrote:
                                  If a view is virtually anything, it is virtually not a table.
                                  well, Oracle says a view is a virtual table, of course there are differences between a virtual and a normal table, but thats why it is a virtual and not a normal table ^^.
                                  BluShadow wrote:
                                  The only passing resemblence it has to a table is the way it presents the data in rows and columns.
                                  Why do you say only ? You query a view like a normal table, I mean that is a huge similarity. And everything that gets "physical" and the way you define a table and a view, it can be different, but also have some similarities. Sounds pretty much what it is...
                                  BluShadow wrote:
                                  so it is certainly wrong to say a view is virtually a table (or a "virtual table").
                                  again, Wikipedia says it is a virtual table, but for you more importend Oracle says it is a virtual table.
                                  BluShadow wrote:
                                  So, the documenation says it's a stored subquery. Do you still want to deny that fact?
                                  Ah, well, it's a lot of documentation, and not everything is going to be perfect. ;)
                                  On that point I agree, I cannot see how all those four statements should be all correct.

                                  Ikrischer
                                  • 44. Re: NOT NULL
                                    6363
                                    Ikrischer wrote:
                                    Hello BluShadow,
                                    BluShadow wrote:
                                    It's been correct for all cases I've every worked on. Knowing the view is a select statement (subquery) that is stored on the database and integrated into queries that use it, especially by the optimizer, means I know all well and fine how it's going to work with my queries.
                                    Not every select statement is a subquery.
                                    Here we go again. BluShadow did not say that. You are just making a pointless argument.
                                    So do you want to say a view is a stored select statement or is a stored subquery ?
                                    BluShadow said, as we all said, that a view is a stored query. It is only you who said it wasn't and you were wrong, and every time that is pointed out, you start an unnecessary argument with what was not said.

                                    >
                                    BluShadow wrote:
                                    Ikrischer wrote:
                                    -> if you treat a view like a logical/virtual table, that will be correct for all cases.
                                    So, let's alter the view to add a column like we can do with tables...
                                    I dont know why you still try to handle a virtual table like a normal table for all cases,
                                    BluShadow is not doing that, no one knows what the virtual table concept is since it is only something you have made up and you change the definition of to suit your endless, pointless argument.

                                    >
                                    BluShadow wrote:
                                    A view isn't a resultset. A resultset is a set of data and a view does not store data. It may present a result set when it is queried, but it isn't a result set in itself.
                                    Do you want to say every record and data in a resultset must be stored in a table ?
                                    Result sets don't have records.

                                    >
                                    We talk in the context of oracle and what is proven in the documents is:
                                    - A view is a logical Table (that term is used in the CREATE Section)
                                    Ok, the document does say that, it is a questionable statement but still I will give you that.
                                    - A view is a virtual Table (what you denied)
                                    It does not say this, the virtual table is something you have incorrectly made up.
                                    - A view is a stored Query (what I denied)
                                    A view is a stored query, and was documented and posted and you said you would change your mind if it was posted, but I guess you were wrong about that too. Reposting here for the third time, so hopefully you won't ignore it again.

                                    {message:id=10862009}
                                    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.
                                    Which I did.

                                    {message:id=10862094}
                                    3360 wrote:

                                    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.
                                    >
                                    Ikrischer wrote:- A view is defined by a query (you deny that ?)
                                    Correct a view is defined by the stored query.

                                    >
                                    I dont know what you about think about it, but for me those sentences wont fit together.
                                    The only one that doesn't fit together is the one you made up because you were wrong.

                                    >
                                    BluShadow wrote:
                                    If a view is virtually anything, it is virtually not a table.
                                    well, Oracle says a view is a virtual table, of course there are differences between a virtual and a normal table, but thats why it is a virtual and not a normal table ^^.
                                    No it doesn't you are just making this up, please post a link.
                                    but for you more importend Oracle says it is a virtual table.
                                    No it doesn't. You are wrong.
                                    I cannot see how all those four statements should be all correct.
                                    They aren't, the statement you made up is wrong.