Page History
...
cs.customerid, cs.firstname, cs.lastname,
mr.rentalid, mr.duedate, mr.totalcharge, ri.itemnumber
FROM
(
SELECT
c1.customerid, c1.firstname, c1.lastname, c1.phone
FROM
MOVIES.customer c1
WHERE
EXISTS (SELECT NULL) cs, (FROM MOVIES.customer c2
WHERE
c1.customerid <>c2.customerid AND c1.lastname=c2.lastname AND c1.phone BETWEEN 0 AND 9999569900)
SELECT
customerid, rentalid, duedate, totalcharge, rentaldate
FROM
MOVIES.movierental
WHERE
totalcharge > 10
)
mr, MOVIES.rentalitem ri
WHERE
LENGTH (cs.lastname) = 10
AND
...
1 < cs.customerid
AND
...
ROUND (ri.rentalid) > 10
AND
...
TRUNC (ri.itemnumber) > 1
AND
...
mr.totalcharge > (SELECT
...
AVG (totalcharge)
FROM
MOVIES.movierental
...
WHERE
TOTALCHARGE >= 40)
AND
...
ri.moviecopyid
NOT IN (
...
SELECT mc.moviecopyid
...
FROM MOVIES.moviecopy mc
WHERE
mc.copyformat = 'vhs'
AND
...
mc.copycondition = 'new'
AND
...
mc.movieid IN (SELECT mt.movieid
...
FROM MOVIES.movietitle mt
WHERE
mt.year < 1990
AND mt.rating IN ('pg','r')
AND
...
mt.categoryid IN (SELECT
...
mc.
...
categoryid
FROM
MOVIES.moviecategory mc
...
WHERE
mc.rentalprice=(SELECT MAX (rentalprice)
FROM
MOVIES.moviecategory
WHERE
categoryid=mc.categoryid)))
...
AND
...
mr.CUSTOMERID=cs.CUSTOMERID
AND
...
ri.RENTALID=
...
mr.RENTALID
| Anchor | ||||
|---|---|---|---|---|
|
...
Outer Join
The bold SQL predicate in the statement below defines the outer join between customer and movierental.
select cs.*
from MOVIES.
...
customercs, MOVIES.
...
movierentalmr
where
length (cs.lastname) = 8 and cs.zip > 75062 and
...
1 < cs.customerid + 2 and
...
cs.phone between 9625569900 and 9999569900 and mr.rentalid = (select max (ri.rentalid)
from MOVIES.rentalitem ri, MOVIES.moviecopy mc
where
ri.itemnumber > 1 and mc.moviecopyid = 700) and
...
mr.customerid
...
(+)=cs.customerid;
The following screen shot illustrates how the outer join is displayed in the VST diagram.
| Anchor | ||||
|---|---|---|---|---|
|
...
Unique
The subquery below illustrates a unique relationship between two primary keys.
...select max(rentalprice) from MOVIES.moviecategory where categoryid = mc.categoryid...
| Anchor | ||||
|---|---|---|---|---|
|
...
Not In or Not Exists Join
The following SQL contains a NOT IN subquery (shown in bold below) that is graphically represented with the Subquery summary icon and the NOT IN join.
SELECT CS.*
FROM
MOVIES.
...
CUSTOMERCS,
MOVIES.
...
MOVIERENTALMR
WHERE
CS.ZIP > '75062'
AND
...
MR.RENTALID NOT IN (SELECT MAX ( MOVIES.BIG_STATEMENT5.CUSTOMERID)
FROM
MOVIES.
...
RENTALITEMRI,
MOVIES.
...
MOVIECOPYMC,
MOVIES.BIG_STATEMENT5
WHERE
RI.
...
ITEMNUMBER>1
AND
...
MC.MOVIECOPYID=700)
AND MR.CUSTOMERID=CS.CUSTOMERID;
Graphically, this statement would look like this:
...
Viewing Object SQL
Hover over the name of an object to view the object SQL as shown in the diagram below.
...
Refreshing Tuning Statements
At times you may see an error on the Overview page, which when you mouse over it, indicates that the tuning statements are out of sync and need to be refreshed. This can happen, for example, if you tune a statement, then delete it, and insert another SQL query for tuning.
To refresh the tuning statements
In the Tuning Statements area of the Overview tab, right-click the tuning statement and select Refresh Tuning Statements.
...
Refreshing the VST
...
Diagram
There are two refresh options available: Refresh and Refresh All. Click the Refresh list as shown below to gain access to these options.
...
- Refresh: Regenerates the Analysis tab including the VST diagram. Any changes made on the tab are reflected in the diagram.
...
- Full Refresh: Re-caches all objects used in (or related to) the query, then regenerates the Analysis tab including the VST diagram. This option is typically used when the underlying objects have been recently changed.