1 2 3 4 5 Previous Next 66 Replies Latest reply: Mar 15, 2013 9:34 AM by BluShadow Go to original post RSS
      • 60. Re: NOT NULL
        sql_coder
        BluShadow wrote:
        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. :)
        Since you like the document (CREATE VIEW) that much, I can offer you we only apply to that single document to avoid the inconsistence in the oracle documents. But I guess you will refuse it....

        Ikrischer
        • 61. Re: NOT NULL
          BluShadow
          Ikrischer wrote:
          BluShadow wrote:
          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. :)
          Since you like the document (CREATE VIEW) that much, I can offer you we only apply to that single document to avoid the inconsistence in the oracle documents. But I guess you will refuse it....

          Ikrischer
          Waking up the thread again eh... :)

          It's not about the documentation, it's about the evidence that proves things. I've proven the view is a subquery stored in the database. I've proven the subquery is retrieved and optimised by the optimizer when the view is queried. You've yet to prove the view is a virtual table or a set, and I believe you will struggle to do so, as those things are abstract concepts of a view. Don't get me wrong, as I've already said, I'm happy for you if you want to look at a view in that abstract context, but it doesn't negate the fact that the view is a stored subquery, which is what you seem so insistent to say is wrong, even when it's been proven. That's your choice and not a concern of mine.
          • 62. Re: NOT NULL
            sql_coder
            BluShadow wrote:
            It's not about the documentation, it's about the evidence that proves things.
            the evidence with a search for keywords ? you had better arguments. The fact that the document uses the word subquery proves nothing in our discussion. You wont find one sentence in that document, that a view is a stored select statement (query, subquery), but sentences like "of the defining subquery". No matter if a view is a logical table or a select statement, a query is needed in both cases, so it is no wonder they talk about subquerys.
            BluShadow wrote:
            I've proven the view is a subquery stored in the database.
            What is proven is that a query is stored with the creation of the view. And again, the query, that defines the view, must be stored, even if you handle a view as a logical table. The query defines the view (a lifetime, since it is a virtual table), so no wonder the optimizer uses the query. But fact is, you can only use the view where you could use a set. There is no way you use it as a stored statement. If a "real" stored statement in a database would exist as an object, it could be created the same way like a view with the same content in the object dictionary.

            CREATE OR REPLACE STATEMENT state_1
            AS SELECT * from table1;

            But the way you use that stored statement would be different then how you use a view. A view is handled like a set, a stored statement is handled like a statement, but both have a query stored.

            Ikrischer

            Edited by: Ikrischer on 15.03.2013 13:27
            • 63. Re: NOT NULL
              Stew Ashton
              You continue to deny one thing and affirm the other, when both are true.

              BluShadow has conceded that the documentation uses the terms "virtual table" and "logical table" (although he prefers "logical" to "virtual"). He has conceded that the view "presents itself" to the SQL writer as a table.

              You have not conceded that the documentation also refers to the view as a "stored subquery", and you downplay the importance of the fact that the view is implemented and executed as a subquery.

              In other words, BluShadow emphasizes one aspect of the view, but he at least concedes the other.

              In contrast, you emphasize another aspect while practically denying the first, and you ignore those phrases in the documentation that do not support your point of view.

              Your repeated half-truths are not contributing to a better understanding of the subject.
              • 64. Re: NOT NULL
                BluShadow
                Ikrischer wrote:
                BluShadow wrote:
                It's not about the documentation, it's about the evidence that proves things.
                the evidence with a search for keywords ? you had better arguments. The fact that the document uses the word subquery proves nothing in our discussion.
                It does when you state outright that a view is "not a subquery".
                You wont find one sentence in that document, that a view is a stored select statement (query, subquery), but sentences like "of the defining subquery". No matter if a view is a logical table or a select statement, a query is needed in both cases, so it is no wonder they talk about subquerys.
                Yes, but the documentaiton is not everything.
                Look on the database itself, use queries to technically tell you what's happening... that is drawing from many parts of the documentation. For example, the documentation for creating a view doesn't say that the view is created as an object in the data dictionary tables, but it still is, because other parts of the documentation discuss the data dictionary, and you would have to look at everything as a whole rather than isolated. Technically you can query the database and see for youself that the subquery has been stored... therefore it IS a stored subquery. Technically you can see for youself that the optimizer retrieves that stored subquery and optimizes the whole query as though the view is a subquery within it... therefore it is being treated by the optimizer as a subquery. These are FACTS... black and white, in your face, you cannot say that they are not so. If you believe otherwise... prove it.
                BluShadow wrote:
                I've proven the view is a subquery stored in the database.
                What is proven is that a query is stored with the creation of the view. And again, the query, that defines the view, must be stored, even if you handle a view as a logical table. The query defines the view (a lifetime, since it is a virtual table), so no wonder the optimizer uses the query. But fact is, you can only use the view where you could use a set. There is no way you use it as a stored statement. If a "real" stored statement in a database would exist as an object, it could be created the same way like a view with the same content in the object dictionary.
                Do you understand what is meant by "abstract"? A query isn't a virtual table... the view isn't a virtual table. The results of querying a view give results that appear like you would get if querying data from a table, but in and of itself, the view is not a table of any sort. Yes, you can look in the data dictionary (such as all_tab_cols) and see the column definitions that will be returned by the view, but that is an abstraction of the view itself, providing the details of what the columsn returned will look like, but the optimizer will not use those to look up statistics etc. as it can with a table, because the optimizer will use the subquery itself to determine the base tables being used and look for the statistics for those base tables. It is the base tables (the tables in the stored subquery) that are accessed when you query a view, not some invisible virtual table of the view itself.
                But the way you use that stored statement would be different then how you use a view. A view is handled like a set, a stored statement is handled like a statement, but both have a query stored.
                Please demonstrate to us... with code, how a view is handled by Oracle as a sset, and not as a stored subquery.
                To my understanding, Oracle handles the stored subquery, along with the rest of the query, and from that, by accessing the base tables, produces results that are a set of data. The concept of "set" comes from abstracting a view up to the level of it's results, not of the view itself.

                Still, you continue to say that a view is not a stored subquery. Still you insist that it's some virtual table or set. Even though it's been proven it's a stored subquery, with hard code you can run yourself, you've done nothing to prove with code that it's a virtual table or a set. There really is no point in continuing this conversation if all you are going to do is keep on trying to tell me I'm wrong without any technical evidence to back it up, when you've been provided with technical evidence that your view (scuse the pun) of a view not being a stored subquery is clearly wrong. (as well as the documentation evidence that stated a view is a stored subquery)

                So, if you want to carry on... please do... but I'm not wasting any more of my time on this if you simply choose to ignore the evidence and the fact that you are wanting to abstract your understanding to a different level.
                • 65. Re: NOT NULL
                  sql_coder
                  >
                  you downplay the importance of the fact that the view is implemented and executed as a subquery.

                  No, I always aggreed on that, a view is defined/implemented as a subquery and the query is executed (to represent the set), I never had a problem with that.

                  Ikrischer
                  • 66. Re: NOT NULL
                    BluShadow
                    Ikrischer wrote:
                    >
                    you downplay the importance of the fact that the view is implemented and executed as a subquery.

                    No, I always aggreed on that, a view is defined/implemented as a subquery and the query is executed (to represent the set), I never had a problem with that.
                    Really? :)

                    The query of the view isn't executed to give a set of results.
                    The query of the view is integrated (optimized) into the main query and then the resultant query is executed.
                    SQL> ed
                    Wrote file afiedt.buf
                    
                      1  create or replace view vw_empdept as
                      2  select empno, ename, job, mgr, hiredate, sal, comm, emp.deptno, dname, loc
                      3* from   emp join dept on (emp.deptno = dept.deptno)
                    SQL> /
                    
                    View created.
                    
                    Elapsed: 00:00:00.01
                    SQL> select *
                      2  from   vw_empdept
                      3  where deptno in (10,20);
                    
                         EMPNO ENAME      JOB              MGR HIREDATE                    SAL       COMM     DEPTNO DNAME          LOC
                    ---------- ---------- --------- ---------- -------------------- ---------- ---------- ---------- -------------- -------------
                          7369 SMITH      CLERK           7902 17-DEC-1980 00:00:00        800                    20 RESEARCH       DALLAS
                          7566 JONES      MANAGER         7839 02-APR-1981 00:00:00       2975                    20 RESEARCH       DALLAS
                          7782 CLARK      MANAGER         7839 09-JUN-1981 00:00:00       2450                    10 ACCOUNTING     NEW YORK
                          7788 SCOTT      ANALYST         7566 19-APR-1987 00:00:00       3000                    20 RESEARCH       DALLAS
                          7839 KING       PRESIDENT            17-NOV-1981 00:00:00       5000                    10 ACCOUNTING     NEW YORK
                          7876 ADAMS      CLERK           7788 23-MAY-1987 00:00:00       1100                    20 RESEARCH       DALLAS
                          7902 FORD       ANALYST         7566 03-DEC-1981 00:00:00       3000                    20 RESEARCH       DALLAS
                          7934 MILLER     CLERK           7782 23-JAN-1982 00:00:00       1300                    10 ACCOUNTING     NEW YORK
                    
                    8 rows selected.
                    
                    Elapsed: 00:00:00.04
                    
                    Execution Plan
                    ----------------------------------------------------------
                    Plan hash value: 2582975621
                    
                    ----------------------------------------------------------------------------------------------
                    | Id  | Operation                     | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
                    ----------------------------------------------------------------------------------------------
                    |   0 | SELECT STATEMENT              |              |     8 |   472 |     6  (17)| 00:00:01 |
                    |*  1 |  HASH JOIN                    |              |     8 |   472 |     6  (17)| 00:00:01 |
                    |   2 |   INLIST ITERATOR             |              |       |       |            |          |
                    |   3 |    TABLE ACCESS BY INDEX ROWID| DEPT         |     4 |    84 |     2   (0)| 00:00:01 |
                    |*  4 |     INDEX UNIQUE SCAN         | SYS_C0025084 |     2 |       |     1   (0)| 00:00:01 |
                    |*  5 |   TABLE ACCESS FULL           | EMP          |     8 |   304 |     3   (0)| 00:00:01 |
                    ----------------------------------------------------------------------------------------------
                    
                    Predicate Information (identified by operation id):
                    ---------------------------------------------------
                    
                       1 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")
                       4 - access("DEPT"."DEPTNO"=10 OR "DEPT"."DEPTNO"=20)
                       5 - filter("EMP"."DEPTNO"=10 OR "EMP"."DEPTNO"=20)
                    
                    
                    Statistics
                    ----------------------------------------------------------
                              8  recursive calls
                              0  db block gets
                             12  consistent gets
                              0  physical reads
                              0  redo size
                           1282  bytes sent via SQL*Net to client
                            360  bytes received via SQL*Net from client
                              2  SQL*Net roundtrips to/from client
                              0  sorts (memory)
                              0  sorts (disk)
                              8  rows processed
                    Clearly the view's own subquery is not executed independently to give a 'set' of data from which the main query selects the data and applies it's conditions. The optimiser has integrated the subquery into the main query and thge predicates have been applied to the base tables once the query has been optimized and a single overall query determined. The only "set" is the overall results of the overall query.

                    Treating a view like a set would be like saying that the subquery of the view becomes materialized to give a set of data before being selected from by the main query similar to:
                    SQL> ed
                    Wrote file afiedt.buf
                    
                      1  with empdept as (select /*+ MATERIALIZE */ empno, ename, job, mgr, hiredate, sal, comm, emp.deptno, dname, loc
                      2                   from emp join dept on (emp.deptno = dept.deptno)
                      3                  )
                      4  select *
                      5  from empdept
                      6* where deptno in (10,20)
                    SQL> /
                    
                         EMPNO ENAME      JOB              MGR HIREDATE                    SAL       COMM     DEPTNO DNAME          LOC
                    ---------- ---------- --------- ---------- -------------------- ---------- ---------- ---------- -------------- -------------
                          7782 CLARK      MANAGER         7839 09-JUN-1981 00:00:00       2450                    10 ACCOUNTING     NEW YORK
                          7839 KING       PRESIDENT            17-NOV-1981 00:00:00       5000                    10 ACCOUNTING     NEW YORK
                          7934 MILLER     CLERK           7782 23-JAN-1982 00:00:00       1300                    10 ACCOUNTING     NEW YORK
                          7566 JONES      MANAGER         7839 02-APR-1981 00:00:00       2975                    20 RESEARCH       DALLAS
                          7902 FORD       ANALYST         7566 03-DEC-1981 00:00:00       3000                    20 RESEARCH       DALLAS
                          7876 ADAMS      CLERK           7788 23-MAY-1987 00:00:00       1100                    20 RESEARCH       DALLAS
                          7369 SMITH      CLERK           7902 17-DEC-1980 00:00:00        800                    20 RESEARCH       DALLAS
                          7788 SCOTT      ANALYST         7566 19-APR-1987 00:00:00       3000                    20 RESEARCH       DALLAS
                    
                    8 rows selected.
                    
                    Elapsed: 00:00:00.04
                    
                    Execution Plan
                    ----------------------------------------------------------
                    Plan hash value: 439742928
                    
                    --------------------------------------------------------------------------------------------------------------
                    | Id  | Operation                      | Name                        | Rows  | Bytes | Cost (%CPU)| Time     |
                    --------------------------------------------------------------------------------------------------------------
                    |   0 | SELECT STATEMENT               |                             |    14 |  1456 |     8  (13)| 00:00:01 |
                    |   1 |  TEMP TABLE TRANSFORMATION     |                             |       |       |            |       |
                    |   2 |   LOAD AS SELECT               | SYS_TEMP_0FD9D674F_9E2B4AB6 |       |       |            |       |
                    |   3 |    MERGE JOIN                  |                             |    14 |   826 |     6  (17)| 00:00:01 |
                    |   4 |     TABLE ACCESS BY INDEX ROWID| DEPT                        |     4 |    84 |     2   (0)| 00:00:01 |
                    |   5 |      INDEX FULL SCAN           | SYS_C0025084                |     4 |       |     1   (0)| 00:00:01 |
                    |*  6 |     SORT JOIN                  |                             |    14 |   532 |     4  (25)| 00:00:01 |
                    |   7 |      TABLE ACCESS FULL         | EMP                         |    14 |   532 |     3   (0)| 00:00:01 |
                    |*  8 |   VIEW                         |                             |    14 |  1456 |     2   (0)| 00:00:01 |
                    |   9 |    TABLE ACCESS FULL           | SYS_TEMP_0FD9D674F_9E2B4AB6 |    14 |   798 |     2   (0)| 00:00:01 |
                    --------------------------------------------------------------------------------------------------------------
                    
                    Predicate Information (identified by operation id):
                    ---------------------------------------------------
                    
                       6 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")
                           filter("EMP"."DEPTNO"="DEPT"."DEPTNO")
                       8 - filter("DEPTNO"=10 OR "DEPTNO"=20)
                    
                    
                    Statistics
                    ----------------------------------------------------------
                             53  recursive calls
                              8  db block gets
                             66  consistent gets
                              1  physical reads
                            532  redo size
                           1208  bytes sent via SQL*Net to client
                            360  bytes received via SQL*Net from client
                              2  SQL*Net roundtrips to/from client
                              1  sorts (memory)
                              0  sorts (disk)
                              8  rows processed
                    ... where the factored subquery has been materialized to a TEMP table internally and the main query selects from it, applying the predicates to the returned "set" of data of the TEMP table. But that only applies if the results of the subquery are materialized, which in a standard view they are not.

                    So, just were is this "set" that you claim a view produces? At an abstract level, yes you can choose to treat a view as a set of data, but that's just an abstract level, not a technical level, and certainly not what Oracle is doing.

                    Edited by: BluShadow on 15-Mar-2013 14:33
                    1 2 3 4 5 Previous Next