Chatta subito con l'assistenza
Chat con il supporto

SQL Optimizer for SQL Server 10.1.2 - User Guide

Welcome to SQL Optimizer Optimize SQL
Create Optimize SQL Sessions Open Optimize SQL Sessions Rewrite SQL Plan Control Use Temporary Tables
Optimize Indexes Find SQL Scan SQL Manage Plan Guides Configure Options Tutorials About Us

Optimize SQL

This topic may not include a description for every field in the screen or dialog.

To set intelligence options

  1. Click in the main menu.

  2. Select Optimize SQL | SQL Rewrite | Intelligence.

  3. Select whether to use custom or predefined intelligence levels. If you select predefined, the levels range as follows:

    ClosedIntelligence Level 1
ClosedIntelligence Level 2
ClosedIntelligence Level 3
ClosedIntelligence Level 4
ClosedIntelligence Level 5

Note: The Result Options page is adjusted independent of the optimization intelligence level.

 

Related Topics

Hints Options

Optimization Options

Quota Options

Result Options

SQL Rewrite

This topic may not include a description for every field in the screen or dialog.

To set intelligence options

  1. Click in the main menu.

  2. Select Optimize SQL | SQL Rewrite | Intelligence.

  3. Select whether to use custom or predefined intelligence levels. If you select predefined, the levels range as follows:

    ClosedIntelligence Level 1
ClosedIntelligence Level 2
ClosedIntelligence Level 3
ClosedIntelligence Level 4
ClosedIntelligence Level 5

Note: The Result Options page is adjusted independent of the optimization intelligence level.

 

Related Topics

Hints Options

Optimization Options

Quota Options

Result Options

Intelligence Options (Optimization)

This topic may not include a description for every field in the screen or dialog.

To set intelligence options

  1. Click in the main menu.

  2. Select Optimize SQL | SQL Rewrite | Intelligence.

  3. Select whether to use custom or predefined intelligence levels. If you select predefined, the levels range as follows:

    ClosedIntelligence Level 1
ClosedIntelligence Level 2
ClosedIntelligence Level 3
ClosedIntelligence Level 4
ClosedIntelligence Level 5

Note: The Result Options page is adjusted independent of the optimization intelligence level.

 

Related Topics

Hints Options

Optimization Options

Quota Options

Result Options

Optimization Options

This topic may not include a description for every field in the screen or dialog.

To set optimization options

  1. Click in the main menu.

  2. Select Optimize SQL | SQL Rewrite | Optimization.

  3. Review the following for additional information:

    Temp Table Description

    Temp table generation

    		 Select this checkbox to generate alternative SQL statements that use temporary tables.

    Apply selected hints to SQL statements that create temporary tables.

    		 Select this checkbox to apply selected hints to SQL statements that create temporary tables.
    View to derive table transformation Description

    Transform view to derived table

    Select this checkbox to insert the view's SQL into the source SQL statement at each location that references the view. This results in a rewrite for the view SQL and original SQL statement. This is useful when you want to optimize a SQL statement that uses a poorly performing view. For example:

    Source SQL

    SELECT *

    FROM   V_DEPT

    WHERE DPT_MANAGER = 'SMITH' 

     

    Alternative SQL

    SELECT *

    FROM   (SELECT DPT_ID, DPT_MANAGER FROM DEPARTMENT)

    WHERE DPT_MANAGER = 'SMITH' 

    Transformation level

    		 Enter the recursive level to transform views inside the subquery of a derived table.
    Query to derived table transformation Description

    Transform query to derived table

    Select this checkbox to transform the query to a derived table (a subquery used as a table in a FROM clause).

    This transformation takes a source SQL statement with an IN or EXISTS clause and rewrites it as a derived table. For example: 

    Source SQL

    SELECT

    WHERE DPT_ID IN (SELECT EMP_DEPT

    FROM EMPLOYEE)                   

    FROM DEPARTMENT      *

     

    Alternative SQL

    SELECT

    (SELECT DISTINCT COL1 = EMP_DEPT

    FROM EMPLOYEE) TEMP0

    WHERE DPT_ID = TEMP0.COL1               

    FROM DEPARTMENT,      .*DEPARTMENT

    Join Tables Description
    Rewrite SQL

    Select one of the following:

    • Rewrite SQL using the same JOIN syntax as the source SQL—Join tables in the FROM clause using the same SQL syntax as the original SQL statement. If the original statement contains multiple syntax types, SQL Optimizer rewrites the syntax using the Ansi-92 JOIN syntax.

    • Rewrite SQL using the Ansi-92 JOIN syntax—Join tables in the FROM clause using the Ansi-92 JOIN syntax. During optimization, SQL Optimizer converts the SQL statement to the Ansi-92 SQL standard and applies SQL syntax transformation rules to rewrite the converted SQL statement. SQL Optimizer then applies SQL Server hints to the source SQL and the transformed SQL. You may see SQL alternatives that use the join syntax from the source SQL, but these SQL alternatives are simply the source SQL statement with a SQL Server hint applied.

      The OUTER JOIN is not included in this conversion because Ansi-92 OUTER JOIN syntax does not always retrieve the same result set as the OUTER JOIN using the (+) operator. The conversion of the OUTER JOIN syntax is not applied to avoid producing the wrong result set.

      For example:

      SELECT DPT_ID

        FROM EMPLOYEE

             INNER JOIN DEPARTMENT

                ON EMP_DEPT = DPT_ID

    • Rewrite SQL without using the Ansi-92 JOIN syntax—Join tables in the FROM clause without using the Ansi-92 JOIN syntax or a comma. The join analysis occurs in the WHERE clause which specifies the column in one table that is compared to a column in another table. During optimization, SQL Optimizer converts the SQL statement from the Ansi-92 SQL standard and applies SQL syntax transformation rules to rewrite the converted SQL. SQL Optimizer then applies SQL Server hints to the source SQL and the transformed SQL. You may see SQL alternatives that use the JOIN syntax from the source SQL, but these SQL alternatives are simply the source SQL statement with a SQL Server hint applied.

      The OUTER JOIN is not included in this conversion because Ansi-92 OUTER JOIN syntax does not always retrieve the same result set as the OUTER JOIN using the (+) operator. The conversion of the OUTER JOIN syntax is not applied to avoid producing the wrong result set.

      For example:

      SELECT DPT_ID

        FROM EMPLOYEE,

             DEPARTMENT

       WHERE DPT_ID = EMP_DEPT

    • Rewrite SQL with and without using the ANSI-92 JOIN syntax—Join tables using either one of the JOIN syntax methods.

 

Related Topics

Hints Options

Intelligence Options (Optimization)

Quota Options

Result Options

Related Documents

The document was helpful.

Seleziona valutazione

I easily found the information I needed.

Seleziona valutazione