Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2015
    Posts
    4

    Unanswered: VIEW SQL statement in ORACLE

    Hi All,

    I am new to Oracle and came to this view statement. I am familiar with view statement in mySQL but this doesnt work in mySQL so most probably its something only in Oracle.

    Is the below view statement rebuilds the existing view if one already exists with the output of select query? what does that X do here?

    Code:
    CREATE OR REPLACE VIEW VIEWNAME AS
    WITH X AS (
      SELECT QUERY
    )
    Thanks in advance.

  2. #2
    Join Date
    Nov 2003
    Posts
    2,934
    Provided Answers: 12
    x is a "common table expression" which is a way to modularize SQL statements.
    It's nothing Oracle specific. It's part of the SQL:2003 standard and is supported by all modern DBMS nowadays (except for MySQL which has failed to keep up-to-date with many other modern SQL features).

    It's also not limited to a view definition. You can use it in any query:
    Code:
    with t as (
       select a,b,c 
       from some_table
    )
    select ot.a,
           ot.b,
           t.c
    from other_table ot
      join t on ot.a = t.a;
    Oracle calls it "subquery factoring", not "common table expression": https://docs.oracle.com/cd/E11882_01...2.htm#i2077142

    I find the Postgres tutorial on this very nice: http://www.postgresql.org/docs/curre...ries-with.html
    Last edited by shammat; 01-03-15 at 10:55.
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

  3. #3
    Join Date
    Jan 2015
    Posts
    4
    Hi Shammat,

    Thanks for the reply.

    The links were very helpful, thanks.

    In the 'with' part it should be with 'st' right ?
    Code:
    with t as (
       select a,b,c 
       from some_table
    )
    but the thing I didnt understand is, why use it under view? So far what I understand its not gonna change anything in the view data right?

    so the sql I am puzzled is as follows:

    CREATE OR REPLACE VIEW VIEW1 AS
    WITH X AS (SELECT QUERY)
    SELECT * FROM X
    SELECT * FROM VIEW1

    CREATE OR REPLACE VIEW VIEW2 AS
    WITH X AS (SELECT QUERY)
    SELECT * FROM X
    SELECT * FROM VIEW2

    So at this step doesn't x gets overridden by running it again under view2 and does view1/view2 content gets changed or they will be same as before? At this point there will be only 1 X table holding the 2nd query output under view2 right?
    Last edited by maronvomra; 01-03-15 at 10:55.

  4. #4
    Join Date
    Nov 2003
    Posts
    2,934
    Provided Answers: 12
    Quote Originally Posted by maronvomra View Post
    In the 'with' part it should be with 'st' right
    Yes, sorry.

    Quote Originally Posted by maronvomra View Post
    but the thing I didnt understand is, why use it under view?
    No idea. You need ask whoever wrote that statement. Most of the time CTEs are used to either make a sub-query re-usable (and only evaluate it once) or simply to make the statement more readable (at least in the eyes of the author).

    Your example also looks like a massively stripped down version of the real query, so this is really impossible to answer.
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

  5. #5
    Join Date
    Jan 2015
    Posts
    4
    Thanks for helping me out on this.

    so if it has this line
    Code:
    SELECT * FROM TABLE (dbms_xplan.dispaly_cursor(NULL,NULL, 'iostats last'))
    ;
    immediately after this block
    Code:
    CREATE OR REPLACE VIEW VIEW1 AS
    WITH X AS (SELECT QUERY)
    SELECT * FROM X
    SELECT /*+ gather_plan_statistics */ * FROM VIEW1
    it means what? its choosing the execution path of X instead of VIEW1 from now onwards?

    Thanks.
    Last edited by maronvomra; 01-03-15 at 11:49.

  6. #6
    Join Date
    Nov 2003
    Posts
    2,934
    Provided Answers: 12
    Quote Originally Posted by maronvomra View Post
    it means what?
    It will display the execution plan for select from the view

    So at this step doesn't x gets overridden by running it again under view2 and does view1/view2 content gets changed or they will be same as before
    The name of a CTE is an alias. It is not "stored" anywhere and "disappears" as soon as the query is gone.

    The visibility of that name is the same as an alias for a table:
    Code:
    create view something
    as
    select *
    from some_table x;
    So you can re-write your view definition as:
    Code:
    CREATE OR REPLACE VIEW VIEW1 AS
    SELECT * 
    FROM (
       SELECT ...
       FROM ...
    ) x
    Now ask yourself what about the alias "x".
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

  7. #7
    Join Date
    Jan 2015
    Posts
    4
    thanks Sammat .. got it...

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •