Database Reference
In-Depth Information
Figure 19.7
A Query
Combined with the
View's Query.
the view with the query that uses the view, into a single query. This query is
parsed and stored in the shared SQL memory. Then the query is executed
and the data retrieved. Figure 19.7 illustrates this activity.
Let's try some examples. Create the following view joining three tables
to list a song and the artist, including any artists making guest appearances.
CREATE VIEW ALLSONGS AS
SELECT S.TITLE, A1.NAME ARTIST, GA.GUESTARTIST_ID
FROM SONG S JOIN ARTIST A1
ON (S.ARTIST_ID = A1.ARTIST_ID)
LEFT OUTER JOIN GUESTAPPEARANCE GA
ON (S.SONG_ID = GA.SONG_ID);
We would like to see the name of the artist making a guest appearance.
The following query joins the view with the ARTIST table, which would
become a very complex query without the view. The complexity is simply
passed on to Oracle Database, potentially hurting performance in larger,
busier environments. Figure 19.8 shows part of the result.
SELECT V.ARTIST, V.TITLE, A.NAME GUEST
FROM ALLSONGS V LEFT OUTER JOIN ARTIST A
ON (V.GUESTARTIST_ID = A.ARTIST_ID)
ORDER BY ARTIST;
Search WWH ::




Custom Search