Re: HOW 'VIEW' WORKS

Alex Yau (achyau_at_msn.com) wrote:
: I have a question about exactly how VIEW works in ORACLE.
 

: I am working a bunch of reports which use a VIEW which is a UNION of
: 2 hugh tables(150000 and 100000 rows respectively.) The report SQL
: statement will use this view to join with other tables to select the
: columns required.
 

: I would like to know how the SQL statement will be processed
: internally? Will the VIEW be created first(i.e. full table scan,
: sort/merge ...etc) THEN join with other tables OR the create
: statement of the VIEW will be combined with the report SQL statement
: first then process in one go.
 

: Thanks,
 

: Alex

This is how i figure it, in laymans terms (and without respect to any particular version or data dictionary).

The view gets created by issuing the 'create view as' syntax. The engine tries the SQL just as it would any other SQL, and if it is OK, it creates an entry into some system tables. The View is actually stored as the text of the SQL command you use in the create statement. (It is not magically precompiled, or pre-executed or anything)

When you select something from a view, the engine figures out that the object you reference in the where clause is a view (not a table or anything else, based on some of the entries into the data dictionary), and since it knows its a view, it substitues the SQL statement into the parser for you.

Once the parser has been filled with all the components of the query, then the engine tries to execute it. By using the appropriate execution plan (generated by the optimizer, or any hints you supplied)

So if you say SELECT * FROM viewname; then you will get the same exact execution plan as when you issue the full SQL of the original view (AS IT WAS WHEN IT WAS CREATED).

So the short answer to your question is that you should run explain plan on the original SQL text of the view to figure out what the execution plan will be when it is queried.

Of course this may change as soon as you request another table or something in the FROM and WHERE clauses. You should perform the same analysis (EXPLAIN PLAN, UTLBSTAT, UTLESTAT etc.) for every query that you want to optimize. You may find that it is simpler to write out a similar 'full' version of the query without the view (but including the original view text ) and then optimize it seperately. (there are many times when a single view can not be optimized for all possible uses.)

Hope it helps
Randy :)

--
    ..uu.                                     ----------------------
  .?$" '?i     .                              I  Randy DeWoolfson  I
 .T^M  ._at_"    d9    .     f   ,.un.  b,    i  I--------------------I
 "  Z :#"    M `8   U    <  .dP"``"# `M   _at_"  I  randyd_at_cais.com   I
    &H?`    Xl _R   $5.  $  ?*    _at_   'P,#"   I--------------------I
  ,d#^*L   :RP'~$b  f`$L:M  Xf  .f'    dH`    I        ,\//.       I
    &  'M ,P    `E  M   "$  Mux~      n!`     I        |o o|       I
   dk   `h"       ' j     " y"       *~       I====oOO==(_)==Ooo===I