You are viewing an old version of this page. View the current version.

Compare with Current View Page History

Version 1 Next »

This section contains the following topics that will help you understand the graphics in VST diagrams:
• Viewing the Diagram Legend
• Colors
• Connecting Lines/Joins

VIEWING THE DIAGRAM LEGEND
Click the Diagram Legend Toggle button as shown in the diagram below to see a description of the icons and relationship lines used in VST diagrams.



COLORS
The color of the index entries in the Collect and Create Indexes table is interpreted as follows:
Text ColorInterpretation
Index is used in the query.
Index is usable but not used by the current execution path.
This index is missing. SQL Query Tuner recommends that you create this index. This index exists on the table but not usable in this query as it is written.


CONNECTING LINES/JOINS
!worddavd7bf2257bd7fba20f31eee1984f463c0.png|height=16,width=50!Joins are represented with connecting lines between nodes. You can move tables in the diagram by clicking and dragging them to the desired location. The position of the connecting lines is automatically adjusted. The following describes when a particular type of connecting line is used and the default positioning of the line.
Connecting LinesWhen used
One-to-One Join relationships are graphed horizontally using blue lines. For more information, see One-to-One Join.


Connecting LinesWhen used
One-to-Many Join relationships are graphed with the many table above the one table. For more information, see One-to-Many Join.
Cartesian Join shows the table highlighted in red with no connectors to indicate that it is joined in via a Cartesian join. For more information, see Cartesian Join.
Many-to-Many Join relationships are connected by a red line and the relative location is not restricted. For more information, see Many-to-Many Join.
Indirect Relationship. For more information, see Indirect Relationship.

Outer Join: For more information, see Outer Join.
Unique: For more information, see Unique.
Not Exists and Not in relationship lines connect the subquery to the table being queried.
Notice that when you click this relationship line, the SQL text creating the relationship is also selected. For more information, see Not In or Not Exists Join.
Exists and In relationship lines connect the subquery to the table being queried. Notice that when you click this relationship line, the SQL text creating the relationship is also selected. For more information, see In or Exists Join.


!worddav423d0dfd60ac4b27b61ab070c206108f.png|height=15,width=36!ONE-TO-ONE JOIN
If two tables are joined on their primary key, then graphically, these would be laid out side-by- side, with a one-to-one connector:



ONE-TO-MANY JOIN
This is the default positioning of a one-to-many relationship, where INVESTMENT_TYPE is the master table and INVESTMENT is the details table.


The following is an example of a query that consists of only many-to-one joins, which is more typical:
SELECT

ct.action, c.client_id, i.investment_unit, it.investment_type_name
FROM
client_transaction ct,
client c,
investment_type it,
investment i
WHERE
ct.client_id = c.client_id AND
ct.investment_id = i.investment_id AND
i.investment_type_id = it.investment_type_id and
client_transaction_id=1




CARTESIAN JOIN
A Cartesian join is described in the following example where the query is missing join criteria on the table INVESTMENT:
SELECT

A.BROKER_ID BROKER_ID, A.BROKER_LAST_NAME BROKER_LAST_NAME, A.BROKER_FIRST_NAME BROKER_FIRST_NAME, A.YEARS_WITH_FIRM YEARS_WITH_FIRM, C.OFFICE_NAME OFFICE_NAME, SUM (B.BROKER_COMMISSION) TOTAL_COMMISSIONS
FROM
BROKER A,
CLIENT_TRANSACTION B,
OFFICE_LOCATION C,
INVESTMENT I
WHERE
A.BROKER_ID = B.BROKER_ID AND
A.OFFICE_LOCATION_ID = C.OFFICE_LOCATION_ID
GROUP BY
A.BROKER_ID,
A.BROKER_LAST_NAME,
A.BROKER_FIRST_NAME,
A.YEARS_WITH_FIRM,
C.OFFICE_NAME;

Graphically, this looks like:

INVESTMENT is highlighted in red with no connectors to indicate that it is joined in via a Cartesian join.
Possible missing join conditions are displayed in the Overview tab under Generated Cases in the transformations area. DB Optimize recommends that you create these joins.

NOTE: Transformations are highlighted in yellow.


IMPLIED CARTESIAN JOIN
If there are different details for a master without other criteria then a Cartesian-type join is created:
SELECT * FROM
investment i, broker b,
client c
WHERE
b.manager_id=c.client_id and
i.investment_type_id=c.client_id;





The result set of BROKER to CLIENT will be multiplied by the result set of INVESTMENT to CLIENT.


MANY-TO-MANY JOIN
If there is no unique index at either end of a join then it can be assumed that in some or all cases the join is many-to-many; there are no constraints preventing a many-to-many join. For example, examine the following query:
SELECT * FROM

WHERE


client_transaction ct, client c
ct.transaction_status=c.client_marital_status;
There is no unique index on either of the fields being joined so the optimizer assumes this is a many-to-many join and the relationship is displayed graphically as:

If one of the fields is unique, then the index should be declared as such to help the optimizer.


INDIRECT RELATIONSHIP
Indirect relationships are produced by the following SQL, where BIG_STATEMENT2 is a Materialized View.
SELECT CS.* FROM
MOVIES.CUSTOMER CS, MOVIES.MOVIERENTAL MR, MOVIES.RENTALITEM RI, OE.BIG_STATEMENT2
WHERE
CS.ZIP > '75062' AND MR.RENTALID = RI.RENTALID AND RI.ITEMNUMBER = OE.BIG_STATEMENT2.ITEMNUMBER AND MR.CUSTOMERID = CS.CUSTOMERID;

The following diagram produced by the SQL above shows that an indirect relationship exists between the RENTALITEM(RI) tables inside and outside the materialized view, BIG_STATEMENT2. An indirect relationship also exists between MOVIERENTAL (MR) inside BIG_STATEMENT2 and MOVIERENTAL(MR) inside the RENT_VIEW1 view.





IN OR EXISTS JOIN
The following SQL contains a nest IN subquery (shown in bold text) that is graphically represented with the Subquery summary icon and the IN join.
SELECT 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

    MOVIES.moviecategory mc
    FROM WHERE
    mc.rentalprice = (SELECT MAX (rentalprice) FROM MOVIES.moviecategory
    WHERE categoryid = mc.categoryid))) ) AND
    mr.CUSTOMERID = cs.CUSTOMERID AND
    ri.RENTALID = rnr.RENTALID

    !worddavd8988236c1412be99b1df02ab6a22dec.png|height=60,width=94!Graphically, this would display as the following when the MOVIECOPY (MC) subquery is expanded:




    :' ... .. ...........




    • ...1...
    ;.!illIt< ) i
    '•



    fi}1\10\tlERENTAl

    OUTER JOIN
    The bold SQL predicate in the statement below defines the outer join between customer and movierental.
    select cs.*
    from MOVIES.customer cs, MOVIES.movierental mr
    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 (plus) = cs.customerid ;







    The following screen shot illustrates how the outer join is displayed in the VST diagram.



    UNIQUE
    The subquery below illustrates a unique relationship between two primary keys.
    ...select max (rentalprice) from MOVIES.moviecategory where categoryid = mc.categoryid...




    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.CUSTOMER CS, MOVIES.MOVIERENTAL MR
    WHERE
    CS.ZIP > '75062' AND
    MR.RENTALID NOT IN ( SELECT MAX (MOVIES.BIG_STATEMENT5.CUSTOMERID) FROM
    MOVIES.RENTALITEM RI, MOVIES.MOVIECOPY MC, 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.

  • No labels