Postgres Plus Advanced Server Oracle Compatibility Developer's Guide : 3.4 Optimizer Hints

Previous PageTable Of ContentsNext Page

Postgres Plus Advanced Server Oracle Compatibility Developer's Guide

 

3.4 Optimizer Hints

When a DELETE, SELECT, or UPDATE command is issued, the Postgres Plus Advanced Server database server goes through a process to produce the result set of the command which is the final set of rows returned by the database server. How this result set is produced is the job of the query planner, also known as the query optimizer. Depending upon the specific command, there may be one or more alternatives, called query plans, the planner may consider as possible ways to create the result set. The selection of the plan to be used to actually execute the command is dependent upon various factors including:

    ● Costs assigned to various operations to retrieve the data (see the Planner Cost Constants in the postgresql.conf file).

    ● Settings of various planner method parameters (see the Planner Method Configuration section in the postgresql.conf file).

    ● Column statistics that have been gathered on the table data by the ANALYZE command (see the Postgres Plus documentation set for information on the ANALYZE command and column statistics).

Generally speaking, of the various feasible plans, the query planner chooses the one of least estimated cost for actual execution.

However, it is possible in any given DELETE, SELECT, or UPDATE command to directly influence selection of all or part of the final plan by using optimizer hints. Optimizer hints are directives embedded in comment-like syntax immediately following the DELETE, SELECT, or UPDATE key words that tell the planner to utilize or not utilize a certain approach for producing the result set.

Synopsis

{ DELETE | SELECT | UPDATE } /*+ { hint [ comment ] } [...] */
  statement_body

{ DELETE | SELECT | UPDATE } --+ { hint [ comment ] } [...]
  statement_body

Optimizer hints may be given in two different formats as shown above. Note that in both formats, a plus sign (+) must immediately follow the /* or -- opening comment symbols with no intervening space in order for the following tokens to be interpreted as hints.

In the first format, the hint and optional comment may span multiple lines. In the second format, all hints and comments must be on a single line. The remainder of the statement must start on a new line.

Description

The following points regarding the usage of optimizer hints should be noted:

    ● The database server will always try to use the specified hints if at all possible.

    ● If a planner method parameter is set so as to disable a certain plan type, then this plan will not be used even if it is specified in a hint, unless there are no other possible options for the planner. Examples of planner method parameters are enable_indexscan, enable_seqscan, enable_hashjoin, enable_mergejoin, and enable_nestloop. These are all Boolean parameters.

    ● Remember that the hint is embedded within a comment. As a consequence, if the hint is misspelled or if any parameter to a hint such as view, table, or column name is misspelled, or non-existent in the SQL command, there will be no indication that any sort of error has occurred. No syntax error will be given and the entire hint is simply ignored.

    ● If an alias is used for a table or view name in the SQL command, then the alias name, not the original object name, must be used in the hint. For example, in the command, SELECT /*+ FULL(acct) */ * FROM accounts acct ..., acct, the alias for accounts, must be specified in the FULL hint, not the table name, accounts.

    ● Use the EXPLAIN command to ensure that the hint is correctly formed and the planner is using the hint. See the Postgres Plus documentation set for information on the EXPLAIN command.

    ● In general, optimizer hints should not be used in production applications. Typically, the table data changes throughout the life of the application. By ensuring that the more dynamic columns are ANALYZEd frequently, the column statistics will be updated to reflect value changes and the planner will use such information to produce the least cost plan for any given command execution. Use of optimizer hints defeats the purpose of this process and will result in the same plan regardless of how the table data changes.

Parameters

hint

An optimizer hint directive.

comment

A string with additional information. Note that there are restrictions as to what characters may be included in the comment. Generally, comment may only consist of alphabetic, numeric, the underscore, dollar sign, number sign and space characters. These must also conform to the syntax of an identifier. See Section 3.1.2 for more information on identifiers. Any subsequent hint will be ignored if the comment is not in this form.

statement_body

The remainder of the DELETE, SELECT, or UPDATE command.

The following sections describe the various optimizer hint directives in more detail.

3.4.1 Default Optimization Modes

There are a number of optimization modes that can be chosen as the default setting for a Postgres Plus Advanced Server database cluster. This setting can also be changed on a per session basis by using the ALTER SESSION command as well as in individual DELETE, SELECT, and UPDATE commands within an optimizer hint. The configuration parameter that controls these default modes is named OPTIMIZER_MODE. The following table shows the possible values.

Table 3-3-10 Default Optimization Modes

Hint

Description

ALL_ROWS

Optimizes for retrieval of all rows of the result set.

CHOOSE

Does no default optimization based on assumed number of rows to be retrieved from the result set. This is the default.

FIRST_ROWS

Optimizes for retrieval of only the first row of the result set.

FIRST_ROWS_10

Optimizes for retrieval of the first 10 rows of the results set.

FIRST_ROWS_100

Optimizes for retrieval of the first 100 rows of the result set.

FIRST_ROWS_1000

Optimizes for retrieval of the first 1000 rows of the result set.

FIRST_ROWS(n)

Optimizes for retrieval of the first n rows of the result set. This form may not be used as the object of the ALTER SESSION SET OPTIMIZER_MODE command. It may only be used in the form of a hint in a SQL command.

These optimization modes are based upon the assumption that the client submitting the SQL command is interested in viewing only the first “n” rows of the result set and will then abandon the remainder of the result set. Resources allocated to the query are adjusted as such.

Examples

Alter the current session to optimize for retrieval of the first 10 rows of the result set.

ALTER SESSION SET OPTIMIZER_MODE = FIRST_ROWS_10;

The current value of the OPTIMIZER_MODE parameter can be shown by using the SHOW command. Note that this command is a utility dependent command. In PSQL, the SHOW command is used as follows:

SHOW OPTIMIZER_MODE;

optimizer_mode
----------------
 first_rows_10
(1 row)

The Oracle compatible SHOW command has the following syntax:

SHOW PARAMETER OPTIMIZER_MODE;

NAME
--------------------------------------------------
VALUE
--------------------------------------------------
optimizer_mode
first_rows_10

The following example shows an optimization mode used in a SELECT command as a hint:

SELECT /*+ FIRST_ROWS(7) */ * FROM emp;

 empno | ename  |    job    | mgr  |      hiredate      |   sal   |  comm   | deptno
-------+--------+-----------+------+--------------------+---------+---------+--------
  7369 | SMITH  | CLERK     | 7902 | 17-DEC-80 00:00:00 |  800.00 |         |     20
  7499 | ALLEN  | SALESMAN  | 7698 | 20-FEB-81 00:00:00 | 1600.00 |  300.00 |     30
  7521 | WARD   | SALESMAN  | 7698 | 22-FEB-81 00:00:00 | 1250.00 |  500.00 |     30
  7566 | JONES  | MANAGER   | 7839 | 02-APR-81 00:00:00 | 2975.00 |         |     20
  7654 | MARTIN | SALESMAN  | 7698 | 28-SEP-81 00:00:00 | 1250.00 | 1400.00 |     30
  7698 | BLAKE  | MANAGER   | 7839 | 01-MAY-81 00:00:00 | 2850.00 |         |     30
  7782 | CLARK  | MANAGER   | 7839 | 09-JUN-81 00:00:00 | 2450.00 |         |     10
  7788 | SCOTT  | ANALYST   | 7566 | 19-APR-87 00:00:00 | 3000.00 |         |     20
  7839 | KING   | PRESIDENT |      | 17-NOV-81 00:00:00 | 5000.00 |         |     10
  7844 | TURNER | SALESMAN  | 7698 | 08-SEP-81 00:00:00 | 1500.00 |    0.00 |     30
  7876 | ADAMS  | CLERK     | 7788 | 23-MAY-87 00:00:00 | 1100.00 |         |     20
  7900 | JAMES  | CLERK     | 7698 | 03-DEC-81 00:00:00 |  950.00 |         |     30
  7902 | FORD   | ANALYST   | 7566 | 03-DEC-81 00:00:00 | 3000.00 |         |     20
  7934 | MILLER | CLERK     | 7782 | 23-JAN-82 00:00:00 | 1300.00 |         |     10
(14 rows)

3.4.2 Access Method Hints

The following hints influence how the optimizer accesses relations to create the result set.

Table 3-3-11 Access Method Hints

Hint

Description

FULL(table)

Perform a full sequential scan on table.

INDEX(table [ index ] [...])

Use index on table to access the relation.

NO_INDEX(table [ index ] [...])

Do not use index on table to access the relation.

In addition, the ALL_ROWS, FIRST_ROWS, and FIRST_ROWS(n) hints of Table 3-3-10 can be used.

Examples

The sample application does not have sufficient data to illustrate the effects of optimizer hints so the remainder of the examples in this section will use a banking database created by the pgbench application located in the PostgresPlus\9.0AS\bin subdirectory.

The following steps create a database named, bank, populated by the tables, accounts, branches, tellers, and history. The –s 5 option specifies a scaling factor of five which results in the creation of five branches, each with 100,000 accounts, resulting in a total of 500,000 rows in the accounts table and five rows in the branches table. Ten tellers are assigned to each branch resulting in a total of 50 rows in the tellers table.

Note, if using Linux use the export command instead of the SET PATH command as shown below.

export PATH=/opt/PostgresPlus/9.0AS/bin:$PATH

The following example was run in Windows.

SET PATH=C:\PostgresPlus\9.0AS\bin;%PATH%

createdb -U enterprisedb bank
CREATE DATABASE

pgbench -i -s 5 -U enterprisedb -d bank

creating tables...
10000 tuples done.
20000 tuples done.
30000 tuples done.
        .
        .
        .
470000 tuples done.
480000 tuples done.
490000 tuples done.
500000 tuples done.
set primary key...
vacuum...done.

Ten transactions per client are then processed for eight clients for a total of 80 transactions. This will populate the history table with 80 rows.

pgbench –U enterprisedb –d bank –c 8 –t 10
        .
        .
        .
transaction type: TPC-B (sort of)
scaling factor: 5
number of clients: 8
number of transactions per client: 10
number of transactions actually processed: 80/80
tps = 6.023189 (including connections establishing)
tps = 7.140944 (excluding connections establishing)

The table definitions are shown below:

\d accounts

       Table "public.accounts"
  Column  |     Type      | Modifiers
----------+---------------+-----------
 aid      | integer       | not null
 bid      | integer       |
 abalance | integer       |
 filler   | character(84) |
Indexes:
    "accounts_pkey" PRIMARY KEY, btree (aid)

\d branches

       Table "public.branches"
  Column  |     Type      | Modifiers
----------+---------------+-----------
 bid      | integer       | not null
 bbalance | integer       |
 filler   | character(88) |
Indexes:
    "branches_pkey" PRIMARY KEY, btree (bid)

\d tellers

        Table "public.tellers"
  Column  |     Type      | Modifiers
----------+---------------+-----------
 tid      | integer       | not null
 bid      | integer       |
 tbalance | integer       |
 filler   | character(84) |
Indexes:
    "tellers_pkey" PRIMARY KEY, btree (tid)

\d history

              Table "public.history"
 Column |            Type             | Modifiers
--------+-----------------------------+-----------
 tid    | integer                     |
 bid    | integer                     |
 aid    | integer                     |
 delta  | integer                     |
 mtime  | timestamp without time zone |
 filler | character(22)               |

The EXPLAIN command shows the plan selected by the query planner. In the following example, aid is the primary key column, so an indexed search is used on index, accounts_pkey.

EXPLAIN SELECT * FROM accounts WHERE aid = 100;

                                  QUERY PLAN
-------------------------------------------------------------------------------
 Index Scan using accounts_pkey on accounts  (cost=0.00..8.32 rows=1 width=97)
   Index Cond: (aid = 100)
(2 rows)

The FULL hint is used to force a full sequential scan instead of using the index as shown below:

EXPLAIN SELECT /*+ FULL(accounts) */ * FROM accounts WHERE aid = 100;

                         QUERY PLAN
-------------------------------------------------------------
 Seq Scan on accounts  (cost=0.00..14461.10 rows=1 width=97)
   Filter: (aid = 100)
(2 rows)

The NO_INDEX hint also forces a sequential scan as shown below:

EXPLAIN SELECT /*+ NO_INDEX(accounts accounts_pkey) */ * FROM accounts WHERE aid = 100;

                         QUERY PLAN
-------------------------------------------------------------
 Seq Scan on accounts  (cost=0.00..14461.10 rows=1 width=97)
   Filter: (aid = 100)
(2 rows)

In addition to using the EXPLAIN command as shown in the prior examples, more detailed information regarding whether or not a hint was used by the planner can be obtained by setting the client_min_messages and trace_hints configuration parameters as follows:

SET client_min_messages TO info;
SET trace_hints TO true;

The SELECT command with the NO_INDEX hint is repeated below to illustrate the additional information produced when the aforementioned configuration parameters are set.

EXPLAIN SELECT /*+ NO_INDEX(accounts accounts_pkey) */ * FROM accounts WHERE aid = 100;

INFO:  [HINTS] Index Scan of [accounts].[accounts_pkey] rejected because of NO_INDEX hint.

INFO:  [HINTS] Bitmap Heap Scan of [accounts].[accounts_pkey] rejected because of NO_INDEX hint.
                         QUERY PLAN
-------------------------------------------------------------
 Seq Scan on accounts  (cost=0.00..14461.10 rows=1 width=97)
   Filter: (aid = 100)
(2 rows)

Note that if a hint is ignored, the INFO: [HINTS] line will not appear. This may be an indication that there was a syntax error or some other misspelling in the hint as shown in the following example where the index name is misspelled.

EXPLAIN SELECT /*+ NO_INDEX(accounts accounts_xxx) */ * FROM accounts WHERE aid = 100;

                                  QUERY PLAN
-------------------------------------------------------------------------------
 Index Scan using accounts_pkey on accounts  (cost=0.00..8.32 rows=1
 width=97)
   Index Cond: (aid = 100)
(2 rows

3.4.3 Specifying a Join Order

Include the ORDERED directive to instruct the query optimizer to join tables in the order in which they are listed in the FROM clause. If you do not include the ORDERED keyword, the query optimizer will choose the order in which to join the tables.

For example, the following command allows the optimizer to choose the order in which to join the tables listed in the FROM clause:

      SELECT e.ename, d.dname, h.startdate
      FROM emp e, dept d, jobhist h
      WHERE d.deptno = e.deptno
      AND h.empno = e.empno;

The following command instructs the optimizer to join the tables in the ordered specified:

      SELECT /*+ ORDERED */ e.ename, d.dname, h.startdate
      FROM emp e, dept d, jobhist h
      WHERE d.deptno = e.deptno
      AND h.empno = e.empno;

In the ORDERED version of the command, Advanced Server will first join emp e with dept d before joining the results with jobhist h. Without the ORDERED directive, the join order is selected by the query optimizer.

Please note: the ORDERED directive does not work for Oracle-style outer joins (those joins that contain a '+' sign).

3.4.4 Joining Relations Hints

When two tables are to be joined, there are three possible plans that may be used to perform the join.

    ● Nested Loop Join – The right table is scanned once for every row in the left table.

    ● Merge Sort Join – Each table is sorted on the join attributes before the join starts. The two tables are then scanned in parallel and the matching rows are combined to form the join rows.

    ● Hash Join – The right table is scanned and its join attributes are loaded into a hash table using its join attributes as hash keys. The left table is then scanned and its join attributes are used as hash keys to locate the matching rows from the right table.

The following table lists the optimizer hints that can be used to influence the planner to use one type of join plan over another.

Table 3-3-12 Join Hints

Hint

Description

USE_HASH(table [...])

Use a hash join with a hash table created from the join attributes of table.

NO_USE_HASH(table [...])

Do not use a hash join created from the join attributes of table.

USE_MERGE(table [...])

Use a merge sort join for table.

NO_USE_MERGE(table [...])

Do not use a merge sort join for table.

USE_NL(table [...])

Use a nested loop join for table.

NO_USE_NL(table [...])

Do not use a nested loop join for table.

Examples

In the following example, a join is performed on the branches and accounts tables. The query plan shows that a hash join is used by creating a hash table from the join attribute of the branches table.

EXPLAIN SELECT b.bid, a.aid, abalance FROM branches b, accounts a WHERE b.bid = a.bid;

                                QUERY PLAN
--------------------------------------------------------------------------
 Hash Join  (cost=1.11..20092.70 rows=500488 width=12)
   Hash Cond: (a.bid = b.bid)
   ->  Seq Scan on accounts a  (cost=0.00..13209.88 rows=500488 width=12)
   ->  Hash  (cost=1.05..1.05 rows=5 width=4)
         ->  Seq Scan on branches b  (cost=0.00..1.05 rows=5 width=4)
(5 rows)

By using the USE_HASH(a) hint, the planner is forced to create the hash table from the accounts join attribute instead of from the branches table. Note the use of the alias, a, for the accounts table in the USE_HASH hint.

EXPLAIN SELECT /*+ USE_HASH(a) */ b.bid, a.aid, abalance FROM branches b, accounts a WHERE b.bid = a.bid;

                                   QUERY PLAN
--------------------------------------------------------------------------------
 Hash Join  (cost=21909.98..30011.52 rows=500488 width=12)
   Hash Cond: (b.bid = a.bid)
   ->  Seq Scan on branches b  (cost=0.00..1.05 rows=5 width=4)
   ->  Hash  (cost=13209.88..13209.88 rows=500488 width=12)
         ->  Seq Scan on accounts a  (cost=0.00..13209.88 rows=500488 width=12)
(5 rows)

Next, the NO_USE_HASH(a b) hint forces the planner to use an approach other than hash tables. The result is a nested loop.

EXPLAIN SELECT /*+ NO_USE_HASH(a b) */ b.bid, a.aid, abalance FROM branches b, accounts a WHERE b.bid = a.bid;

                                QUERY PLAN
--------------------------------------------------------------------------
 Nested Loop  (cost=1.05..69515.84 rows=500488 width=12)
   Join Filter: (b.bid = a.bid)
   ->  Seq Scan on accounts a  (cost=0.00..13209.88 rows=500488 width=12)
   ->  Materialize  (cost=1.05..1.11 rows=5 width=4)
         ->  Seq Scan on branches b  (cost=0.00..1.05 rows=5 width=4)
(5 rows)

Finally, the USE_MERGE hint forces the planner to use a merge join.

EXPLAIN SELECT /*+ USE_MERGE(a) */ b.bid, a.aid, abalance FROM branches b, accounts a WHERE b.bid = a.bid;

                                   QUERY PLAN
--------------------------------------------------------------------------------
 Merge Join  (cost=69143.62..76650.97 rows=500488 width=12)
   Merge Cond: (b.bid = a.bid)
   ->  Sort  (cost=1.11..1.12 rows=5 width=4)
         Sort Key: b.bid
         ->  Seq Scan on branches b  (cost=0.00..1.05 rows=5 width=4)
   ->  Sort  (cost=69142.52..70393.74 rows=500488 width=12)
         Sort Key: a.bid
         ->  Seq Scan on accounts a  (cost=0.00..13209.88 rows=500488 width=12)
(8 rows)

In this three-table join example, the planner first performs a hash join on the branches and history tables, then finally performs a nested loop join of the result with the accounts_pkey index of the accounts table.

EXPLAIN SELECT h.mtime, h.delta, b.bid, a.aid FROM history h, branches b, accounts a WHERE h.bid = b.bid AND h.aid = a.aid;

                                      QUERY PLAN
--------------------------------------------------------------------------------------
 Nested Loop  (cost=1.11..207.95 rows=26 width=20)
   ->  Hash Join  (cost=1.11..25.40 rows=26 width=20)
         Hash Cond: (h.bid = b.bid)
         ->  Seq Scan on history h  (cost=0.00..20.20 rows=1020 width=20)
         ->  Hash  (cost=1.05..1.05 rows=5 width=4)
               ->  Seq Scan on branches b  (cost=0.00..1.05 rows=5 width=4)
   ->  Index Scan using accounts_pkey on accounts a  (cost=0.00..7.01 rows=1
       width=4)
         Index Cond: (h.aid = a.aid)
(8 rows)

This plan is altered by using hints to force a combination of a merge sort join and a hash join.

EXPLAIN SELECT /*+ USE_MERGE(h b) USE_HASH(a) */ h.mtime, h.delta, b.bid, a.aid FROM history h, branches b, accounts a WHERE h.bid = b.bid AND h.aid = a.aid;

                                        QUERY PLAN
-------------------------------------------------------------------------------------------
 Merge Join  (cost=23480.11..23485.60 rows=26 width=20)
   Merge Cond: (h.bid = b.bid)
   ->  Sort  (cost=23479.00..23481.55 rows=1020 width=20)
         Sort Key: h.bid
         ->  Hash Join  (cost=21421.98..23428.03 rows=1020 width=20)
               Hash Cond: (h.aid = a.aid)
               ->  Seq Scan on history h  (cost=0.00..20.20 rows=1020
                   width=20)
               ->  Hash  (cost=13209.88..13209.88 rows=500488 width=4)
                     ->  Seq Scan on accounts a  (cost=0.00..13209.88
                         rows=500488 width=4)
   ->  Sort  (cost=1.11..1.12 rows=5 width=4)
         Sort Key: b.bid
         ->  Seq Scan on branches b  (cost=0.00..1.05 rows=5 width=4)
(12 rows)

3.4.5 Global Hints

Thus far, hints have been applied directly to tables that are referenced in the SQL command. It is also possible to apply hints to tables that appear in a view when the view is referenced in the SQL command. The hint does not appear in the view, itself, but rather in the SQL command that references the view.

When specifying a hint that is to apply to a table within a view, the view and table names are given in dot notation within the hint argument list.

Synopsis

hint(view.table)

Parameters

hint

Any of the hints in Table 3-3-11 or Table 3-3-12.

view

The name of the view containing table.

table

The table on which the hint is to be applied.

Examples

A view named, tx, is created from the three-table join of history, branches, and accounts shown in the final example of Section 3.4.3.

CREATE VIEW tx AS SELECT h.mtime, h.delta, b.bid, a.aid FROM history h, branches b, accounts a WHERE h.bid = b.bid AND h.aid = a.aid;

The query plan produced by selecting from this view is show below:

EXPLAIN SELECT * FROM tx;

                                      QUERY PLAN
--------------------------------------------------------------------------------------
 Nested Loop  (cost=1.11..207.95 rows=26 width=20)
   ->  Hash Join  (cost=1.11..25.40 rows=26 width=20)
         Hash Cond: (h.bid = b.bid)
         ->  Seq Scan on history h  (cost=0.00..20.20 rows=1020 width=20)
         ->  Hash  (cost=1.05..1.05 rows=5 width=4)
               ->  Seq Scan on branches b  (cost=0.00..1.05 rows=5 width=4)
   ->  Index Scan using accounts_pkey on accounts a  (cost=0.00..7.01 rows=1
       width=4)
         Index Cond: (h.aid = a.aid)
(8 rows)

The same hints that were applied to this join at the end of Section 3.4.3 can be applied to the view as follows:

EXPLAIN SELECT /*+ USE_MERGE(tx.h tx.b) USE_HASH(tx.a) */ * FROM tx;

                                        QUERY PLAN

------------------------------------------------------------------------------------------
-
 Merge Join  (cost=23480.11..23485.60 rows=26 width=20)
   Merge Cond: (h.bid = b.bid)
   ->  Sort  (cost=23479.00..23481.55 rows=1020 width=20)
         Sort Key: h.bid
         ->  Hash Join  (cost=21421.98..23428.03 rows=1020 width=20)
               Hash Cond: (h.aid = a.aid)
               ->  Seq Scan on history h  (cost=0.00..20.20 rows=1020
                   width=20)
               ->  Hash  (cost=13209.88..13209.88 rows=500488 width=4)
                     ->  Seq Scan on accounts a  (cost=0.00..13209.88
                         rows=500488 width=4)
   ->  Sort  (cost=1.11..1.12 rows=5 width=4)
         Sort Key: b.bid
         ->  Seq Scan on branches b  (cost=0.00..1.05 rows=5 width=4)
(12 rows)

In addition to applying hints to tables within stored views, hints can be applied to tables within subqueries as illustrated by the following example. In this query on the sample application emp table, employees and their managers are listed by joining the emp table with a subquery of the emp table identified by the alias, b.

SELECT a.empno, a.ename, b.empno "mgr empno", b.ename "mgr ename" FROM emp a, (SELECT * FROM emp) b WHERE a.mgr = b.empno;

empno | ename  | mgr empno | mgr ename
-------+--------+-----------+-----------
  7902 | FORD   |      7566 | JONES
  7788 | SCOTT  |      7566 | JONES
  7521 | WARD   |      7698 | BLAKE
  7844 | TURNER |      7698 | BLAKE
  7654 | MARTIN |      7698 | BLAKE
  7900 | JAMES  |      7698 | BLAKE
  7499 | ALLEN  |      7698 | BLAKE
  7934 | MILLER |      7782 | CLARK
  7876 | ADAMS  |      7788 | SCOTT
  7782 | CLARK  |      7839 | KING
  7698 | BLAKE  |      7839 | KING
  7566 | JONES  |      7839 | KING
  7369 | SMITH  |      7902 | FORD
(13 rows)

The plan chosen by the query planner is shown below:

EXPLAIN SELECT a.empno, a.ename, b.empno "mgr empno", b.ename "mgr ename" FROM emp a, (SELECT * FROM emp) b WHERE a.mgr = b.empno;

                            QUERY PLAN
-------------------------------------------------------------------
 Merge Join  (cost=2.81..3.08 rows=13 width=26)
   Merge Cond: (a.mgr = emp.empno)
   ->  Sort  (cost=1.41..1.44 rows=14 width=20)
         Sort Key: a.mgr
         ->  Seq Scan on emp a  (cost=0.00..1.14 rows=14 width=20)
   ->  Sort  (cost=1.41..1.44 rows=14 width=13)
         Sort Key: emp.empno
         ->  Seq Scan on emp  (cost=0.00..1.14 rows=14 width=13)
(8 rows)

A hint can be applied to the emp table within the subquery to perform an index scan on index, emp_pk, instead of a table scan. Note the difference in the query plans.

EXPLAIN SELECT /*+ INDEX(b.emp emp_pk) */ a.empno, a.ename, b.empno "mgr empno", b.ename "mgr ename" FROM emp a, (SELECT * FROM emp) b WHERE a.mgr = b.empno;

                                QUERY PLAN
---------------------------------------------------------------------------
 Merge Join  (cost=1.41..13.21 rows=13 width=26)
   Merge Cond: (a.mgr = emp.empno)
   ->  Sort  (cost=1.41..1.44 rows=14 width=20)
         Sort Key: a.mgr
         ->  Seq Scan on emp a  (cost=0.00..1.14 rows=14 width=20)
   ->  Index Scan using emp_pk on emp  (cost=0.00..12.46 rows=14 width=13)
(6 rows)

3.4.6 Conflicting Hints

This final section on hints deals with cases where two or more conflicting hints are given in a SQL command. In such cases, the hints that contradict each other are ignored. The following table lists hints that are contradictory to each other.

Table 3-3-13 Conflicting Hints

Hint

Conflicting Hint

ALL_ROWS

FIRST_ROWS - all formats

FULL(table)

INDEX(table [ index ])

INDEX(table)

FULL(table)

NO_INDEX(table)

INDEX(table index)

FULL(table)

NO_INDEX(table index)

USE_HASH(table)

NO_USE_HASH(table)

USE_MERGE(table)

NO_USE_MERGE(table)

USE_NL(table)

NO_USE_NL(table)

Previous PageTable Of ContentsNext Page