| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: internal handling of views
On Mon, 07 Aug 2006 08:17:44 -0500, Charles M wrote:
>
> Thanks for the reply Hans. The problem I have with the concept manual is
> that it states that a view can be THOUGHT OF (emphisis mine) as a stored
> query or virtual table. Well, when someone says something can be thought of,
I think they are trying to tell you that a view can be used in several different ways depending on what you want to accomplish. Don't get hung up on it.
> they usually mean thats not what it truly is, or they would have use IS
> instead of THOUGHT OF. So, I'm unsure if the concept manual is just
> talking this tact from there on forward or if they really, really mean
> thats what it is.
When you get comfortable with them, you'll find that it really doen't matter how you interpret the way vies work.
In fact, my personal interpretation is exactly the opposite - I believe when we create a table we are doing two things
and ALL of our SQL is done through views - often through the view implicit in the table definition.
>
> Also in discussing mechanics of a view the manual says that sql
> involving a view is merged with the views definition (its stored sql).
That is correct.
> Well, thats all fine and for selecting from a view, but how does it work
> for updating a view? You can't do an 'update .. select from' if the
Actually, you DO. The way you write is:
UPDATE table
SET list-of-things = list-of-new-value-expressions WHERE selection-criteria
compare that with
SELECT list-of-things
FROM table
WHERE selection-critera
and
CREATE VIEW AS
SELECT list-of-things
FROM table
WHERE selection-critera
Besides, you can write
UPDATE ( SELECT last_name
FROM emp
WHERE empno=554)
You must remember that the SQL statement is NEVER EXECUTED. It is only a declaration of the intended operation.
> from doesn't contain any data. It would have to do (possibly complex)
> logic to figure out where the elements go, not merely merging a select
The purpose of and Entity-Relationship logic and it's derivatives (rdbms) and implementations (SQL and others) is to isolate you from HOW it works.
SQL, in particular, is a 4GL (Fourth Generation Lanuage) because you specify WHAT you want acomplished, and let the language implementation determine HOW to accomplish that.
Yes, it can be complex. Who really cares? That's why we pay Oracle and IBM the big bucks for the product ... so we get something that isolates us from the HOW, no matter how complex it is.
After all, DB2 and Oracle both have had over 25 years to make it work. There are constant improvements.
> query. On the other hand, the pointer description works just fine for
> this, and as best I can tell gives all the other characteristics of a
> view as well. But I've only run across the pointer definition once
> (read somewhere or told by a DBA - don't recall) and every book I've
IMO, the pointer definition is simply a waste of time. There is no data involved in a view ...
Again, the view is simply used to provide part of the final statement in a format that happens to be convenient to us.
The whole statement [including the view(s)] is loaded into memory, the list of tables is extracted, the columns are extracted and checked against the dictionary, and a 'plan to execute the statement' is created. That plan is passed to the execution engine to actually run.
Basically:
If the view is
CREATE VIEW BASIC_EMP AS SELECT EMPNO, DEPTNO FROM EMP
and the query is
SELECT EMPNO FROM BASIC_EMP WHERE DEPTNO=30;
this becomes
SELECT EMPNO
FROM (SELECT EMPNO, DEPTNO
FROM EMP) TEMP_SET
WHERE TEMP_SET.DEPTNO=0;
Which tables? Emp
Which columns? EMPNO, DEPTNO
First rewrite:
SELECT EMPNO
FROM EMP
WHERE DEPTNO=30;
Once the tables and columns are extracted and apprpriate symbols are
substituted, there are only a handful of rules for transformation to
accomplish a specific task. It's all 'divide and conquor' ... break that
big, complex, ugly SQL statement into it's basic pieces and handle the
pieces. Combine only two tables at a time - the trick being to figure
out in multi-table operations which pairs of tables will work well an
quickly.
The execution plan, perhaps surprisingly, has little resemblance to the original SQL statement. The system is absolutely free to rearrange things as it feels necessary. Yes, there is a bit of AI technology behind this.
> seen since mentions the stored query definition. So I'm faced with an
> answer that makes more sense but from a source that may be suspect, or
The Concepts manual is correct. There are actually only a few areas I have found in the Concepts manual that are incorrect. Most of the time, what really is incorrect is my interpretation of the statement.
Sadly, since we use a crude form of symbolism in our language, and the intepretation of those symbols iss related to our experience and upbringing, we may not interpret them the way the author intended. That seems to be the case here.
As several key people in the Oracle community have repeatedly said (and I paraphrase): don't believe everything outright ... devise a repeatable test, with measurements, to prove it to yourself. Merge the results of the test into your world view, even if that means rebuilding your world view.
If you really want to understand how things work internally, pull up a chair beside the fire, pull out your favorite intoxicant ... it's gonna be a long, long night.
However, picking up a few books especially those by Jonathan Lewis or Thomas Kyte (or anyone else from the Oak Table - http:/www.oaktable.net) will help.
Good luck ... and keep asking. These kinds of questions are useful.
-- Hans Forbrich (mailto: Fuzzy.GreyBeard_at_gmail.com) *** Feel free to correct me when I'm wrong! *** Top posting [replies] guarantees I won't respond.Received on Mon Aug 07 2006 - 12:30:48 CDT
 |
 |