ALL_ROWS

to optimize a statement block with a goal of best throughput (that is,

minimum total resource consumption).

/*+ ALL_ROWS */

set linesize 121

col name format a30

col value format a30

SELECT name, value

FROM gv$parameter

WHERE name LIKE ‘%optimizer%’;

ALTER SYSTEM SET optimizer_mode=RULE SCOPE=MEMORY;

set autotrace traceonly explain

SELECT table_name

FROM dba_tables

WHERE owner = ‘SYS’

AND table_name LIKE ‘%$’

ORDER BY 1;

SELECT /*+ ALL_ROWS */ table_name

FROM dba_tables

WHERE owner = ‘SYS’

AND table_name LIKE ‘%$’

ORDER BY 1;

ALTER SYSTEM SET optimizer_mode=ALL_ROWS SCOPE=MEMORY;

FIRST_ROWS(n)

optimize a statement block with a goal of best response time

(Minimum resource usage to return first row).

This hint causes the optimizer to make these choices:

• If an index scan is available, the optimizer may choose it over a full

table scan.

• If an index scan is available, the optimizer may choose a nested loops

join over a sort-merge join whenever the associated table is the potential

inner table of the nested loops.

• If an index scan is made available by an ORDER BY clause, the optimizer

may choose it to avoid a sort operation.

• The optimizer ignores this hint in DELETE and UPDATE statement blocks

and in SELECT statement blocks that contain any of the following:

UNION, INTERSECT, MINUS, UNION ALL, GROUP BY,

FOR UPDATE, aggregating function and the DISTINCT operator.

/*+ FIRST_ROWS(<integer>) */

SELECT table_name

FROM dba_tables

WHERE owner = ‘SYS’

AND table_name LIKE ‘%$’

ORDER BY 1;

SELECT /*+ FIRST_ROWS(10) */ table_name

FROM dba_tables

WHERE owner = ‘SYS’

AND table_name LIKE ‘%$’

ORDER BY 1;

— the differences are subtle so look closely

RULE

and should not be used.

/*+ RULE */

SELECT table_name

FROM dba_tables

WHERE owner = ‘SYS’

AND table_name LIKE ‘%$’

ORDER BY 1;

SELECT /*+ RULE */ table_name

FROM dba_tables

WHERE owner = ‘SYS’

AND table_name LIKE ‘%$’

ORDER BY 1;

APPEND

is running in serial mode. Your database is in serial mode if you are

not using Enterprise Edition. Conventional INSERT is the default in

serial mode, and direct-path INSERT is the default in parallel mode.

In direct-path INSERT, data is appended to the end of the table,

rather than using existing space currently allocated to the table.

As a result, direct-path INSERT can be considerably faster than

conventional INSERT.

When you use the APPEND hint for INSERT, data is simply appended

to a table above the HWM which has the effect of not creating UNDO.

Existing free space in blocks is not used.

/*+ APPEND */

SELECT *

FROM servers

WHERE 1=2;

INSERT /*+ NO_APPEND */ INTO t

SELECT * FROM servers;

SELECT COUNT(*) FROM t;

INSERT INTO t

SELECT * FROM servers;

SELECT COUNT(*) FROM t;

INSERT /*+ APPEND */ INTO t

SELECT * FROM servers;

SELECT COUNT(*) FROM t;

COMMIT;

SELECT COUNT(*) FROM t;

NOAPPEND

parallel mode for the duration of the INSERT statement.

Conventional INSERT is the default in serial mode,

and direct-path INSERT is the default in parallel mode.

/*+ NOAPPEND */

CACHE

at the most recently used end of the LRU list in the buffer cache

when a full table scan is performed. This hint is useful for small lookup tables.

/*+ CACHE([@queryblock] <tablespec>) */

set autotrace traceonly exp

SELECT /*+ FULL (hr_emp) CACHE(hr_emp) */ last_name

FROM employees hr_emp;

SELECT /*+ FULL(hr_emp) NOCACHE(hr_emp) */ last_name

FROM employees hr_emp;

NOCACHE

/*+ NOCACHE([@queryblock] <tablespec>]) */

CURSOR_SHARING_EXACT

when it is safe to do so. This replacement is controlled with the

CURSOR_SHARING initialization parameter.

The CURSOR_SHARING_EXACT hint instructs the optimizer to

switch this behavior off. In other words, Oracle executes the

SQL statement without any attempt to replace literals with bind variables.

/*+ CURSOR_SHARING_EXACT */

ALTER SYSTEM SET cursor_sharing=’SIMILAR’ SCOPE=BOTH;

ALTER SYSTEM FLUSH SHARED_POOL;

ALTER SYSTEM FLUSH SHARED_POOL;

— as the client run two similar SQL statements

SELECT latitude FROM uwclass.servers WHERE srvr_id = 1;

SELECT latitude FROM uwclass.servers WHERE srvr_id = 2;

SELECT latitude FROM uwclass.servers WHERE srvr_id = 3;

— as SYS look in the shared pool

set linesize 121

col sql_text format a50

SELECT address, child_address, sql_text, sql_id

FROM gv$sql

WHERE sql_fulltext LIKE ‘%uwclass%’;

SELECT /*+ CURSOR_SHARING_EXACT */ latitude FROM uwclass.servers WHERE srvr_id = 3;

SELECT address, child_address, sql_text, sql_id

FROM gv$sql

WHERE sql_fulltext LIKE ‘%uwclass%’;

DRIVING_SITE

than at a site selected by the database. This hint is useful

if you are using distributed query optimization.

/*+ DRIVING_SITE([@queryblock] <tablespec>) */

FROM person p1, person@psoug_user p2

WHERE p1.person_id = p2.person_id

AND p1.first_name <> p2.first_name;

SELECT /*+ DRIVING_SITE(p1) AAA */ p1.first_name, p2.first_name, p2.last_name

FROM person p1, person@psoug_user p2

WHERE p1.person_id = p2.person_id

AND p1.first_name <> p2.first_name;

SELECT sql_text, remote

FROM v$sql

WHERE sql_text LIKE ‘%AAA%’;

SELECT /*+ DRIVING_SITE(p2) BBB */ p1.first_name, p2.first_name, p2.last_name

FROM person p1, person@psoug_user p2

WHERE p1.person_id = p2.person_id

AND p1.first_name <> p2.first_name;

SELECT sql_text, remote

FROM v$sql

WHERE sql_text LIKE ‘%BBB%’;

DYNAMIC_SAMPLING

control dynamic sampling to improve server performance by

determining more accurate predicate selectivity and statistics

for tables and indexes.

You can set the value of DYNAMIC_SAMPLING to a value

from 0 to 10. The higher the level, the more effort the compiler

puts into dynamic sampling and the more broadly it is applied.

Sampling defaults to cursor level unless you specify tablespec.

The integer value is 0 to 10, indicating the degree of sampling.

Force dynamic sampling of tables where statistics do not exist

such as Global Temporary Tables.

If the table is aliased the alias name, not the table name must be used

/*+ DYNAMIC_SAMPLING([@queryblock] [<tablespec>] <integer>) */

CREATE TABLE ds AS

SELECT * FROM all_objects

WHERE SUBSTR(object_name,1,1) BETWEEN ‘A’ AND ‘W’;

CREATE INDEX ds_objtype

ON ds(object_type);

SELECT object_type, COUNT(*)

FROM ds

GROUP BY object_type;

set autotrace trace exp

SELECT object_name

FROM ds

WHERE object_type = ‘JAVA CLASS’;

SELECT /*+ DYNAMIC_SAMPLING(ds 0) */ object_name

FROM ds

WHERE object_type = ‘JAVA CLASS’;

SELECT /*+ DYNAMIC_SAMPLING(ds 4) */ object_name

FROM ds

WHERE object_type = ‘JAVA CLASS’;

SELECT /*+ DYNAMIC_SAMPLING(ds 9) */ object_name

FROM ds

WHERE object_type = ‘JAVA CLASS’;

MODEL_MIN_ANALYSIS

of spreadsheet rules primarily detailed dependency graph analysis.

Other spreadsheet optimizations, such as creating filters to

selectively populate spreadsheet access structures and limited

rule pruning, are still used by the optimizer.

This hint reduces compilation time because spreadsheet analysis

can be lengthy if the number of spreadsheet rules is more than several hundreds.

MONITOR

is not long running. This hint is valid only when the parameter

CONTROL_MANAGEMENT_PACK_ACCESS is set to

DIAGNOSTIC+TUNING.

/*+ MONITOR */

FROM v$parameter

WHERE name = ‘control_management_pack_access’;

SELECT /*+ MONITOR */ COUNT(*)

FROM user_tables;

the query is long running.

/*+ NO_MONITOR */

SELECT /*+ NO_MONITOR */ COUNT(*)

FROM dba_segments s, dba_extents e

WHERE s.owner = e.owner;

OPT_PARAM

the current query only. This hint is valid only for the  following

parameters: OPTIMIZER_DYNAMIC_SAMPLING, OPTIMIZER_INDEX_CACHING, OPTIMIZER_INDEX_COST_ADJ, OPTIMIZER_SECURE_VIEW_MERGING,

and STAR_TRANSFORMATION_ENABLED. For example,

the following hint sets the parameter STAR_TRANSFORMATION_ENABLED

to TRUE for the statement to which it is added.

/*+ OPT_PARAM(parameter_name, parameter_value) */

FROM v$parameter

WHERE name LIKE ‘optimizer_index%’;

SELECT /*+ OPT_PARAM(‘optimizer_index_cost_adj’ ’42’) */ *

FROM servers;

PUSH_PRED

/*+ PUSH_PRED(<@queryblock> | <[@queryblock> <tablespec>]) */

set autotrace trace exp

SELECT *

FROM employees e, (

SELECT manager_id

FROM employees) v

WHERE e.manager_id = v.manager_id(+)

AND e.employee_id = 100;

SELECT /*+ NO_MERGE(v) PUSH_PRED(v) */ *

FROM employees e, (

SELECT manager_id

FROM employees) v

WHERE e.manager_id = v.manager_id(+)

AND e.employee_id = 100;

NO_PUSH_PRED

/*+ NO_PUSH_PRED(<@queryblock> | <[@queryblock> <tablespec>]) */

set autotrace traceonly exp

SELECT *

FROM employees e, (

SELECT manager_id

FROM employees) v

WHERE e.manager_id = v.manager_id(+)

AND e.employee_id = 100;

SELECT /*+ NO_MERGE(v) NO_PUSH_PRED(v) */ *

FROM employees e, (

SELECT manager_id

FROM employees) v

WHERE e.manager_id = v.manager_id(+)

AND e.employee_id = 100;

PUSH_SUBQ

earliest possible step in the execution plan. Generally, subqueries

that are not merged are executed as the last step in the execution

plan. If the subquery is relatively inexpensive and reduces

the number of rows significantly, then evaluating the subquery

earlier can improve performance.

This hint has no effect if the subquery is applied to a remote

table or one that is joined using a merge join.

NO_PUSH_SUBQ

last step in the execution plan. Doing so can improve performance

if the subquery is relatively expensive or does not reduce the

number of rows significantly.

NO_PX_JOIN_FILTER

QB_NAME

can then be used in a hint in the outer query or even in a hint in an

inline view to affect query execution on the tables appearing in the

named query block.

If two or more query blocks have the same name, or if the same query

block is hinted twice with different names, then the optimizer ignores all

the names and the hints referencing that query block. Query blocks that

are not named using this hint have unique system-generated names.

These names can be displayed in the plan table and can also be used

in hints within the query block, or in query block hints.

/*+ QB_NAME(<query_block_name>) */

set autotrace traceonly exp

SELECT employee_id, last_name

FROM employees e

WHERE last_name = ‘Smith’;

SELECT /*+ QB_NAME(qb) FULL(@qb e) */ employee_id, last_name

FROM employees e

WHERE last_name = ‘Smith’;

RESULT_CACHE

query fragment in memory and then to use the cached results in

future executions of the query or query fragment. The hint is

recognized in the top-level query, the subquery_factoring_clause,

,or FROM clause inline view. The cached results reside in the

result cache memory portion of the shared pool.

NO_RESULT_CACHE

RESULT_CACHE_MODE initialization parameter is set to FORCE.

In this case, the NO_RESULT_CACHE hint disables such

caching for the current query.

FULL

/*+ FULL(<tablespec>) */

set autotrace traceonly explain

SELECT latitude

FROM servers

WHERE srvr_id = 1;

SELECT /*+ FULL(servers) */ latitude

FROM servers

WHERE srvr_id = 1;

INDEX

use the INDEX hint for domain, B*-tree, and bitmap indexes.

However, Oracle recommends using INDEX_COMBINE rather

than INDEX for bitmap indexes because it is a more versatile hint

/*+ INDEX([@queryblock] <tablespec> <index_name>) */

CREATE INDEX ix_customers_gender

ON customers(gender);

set autotrace traceonly explain

SELECT *

FROM customers

WHERE gender = ‘M’;

SELECT /*+ INDEX(customers ix_customers_gender) */ *

FROM customers

WHERE gender = ‘M’;

SELECT /*+ INDEX_ASC(customers ix_customers_gender) */ *

FROM customers

WHERE gender = ‘M’;

SELECT /*+ INDEX_DESC(customers ix_customers_gender) */ *

FROM customers

WHERE gender = ‘M’;

INDEX_ASC

statement uses an index range scan, Oracle scans the index

entries in ascending order of their indexed values

/*+ INDEX_ASC([@queryblock] <tablespec> <index_name>) */

INDEX_DESC

statement uses an index range scan, Oracle scans the index

entries in descending order of their indexed values.

/*+ INDEX_DESC([@queryblock] <tablespec> <indexspec>) */

NO_INDEX

The NO_INDEX hint applies to function-based, B*-tree,

bitmap, cluster, or domain indexes.

/*+ NO_INDEX([@queryblock] <tablespec> <indexspec>) */

set autotrace traceonly explain

SELECT latitude

FROM servers s, serv_inst i

WHERE s.srvr_id = i.srvr_id;

SELECT /*+ NO_INDEX(i pk_serv_inst) */ latitude

FROM servers s, serv_inst i

WHERE s.srvr_id = i.srvr_id;

INDEX_FFS

Appears to be identical to INDEX_FFS_ASC.

/*+ INDEX_FFS([@queryblock] <tablespec> <indexspec>) */

INDEX_FFS_ASC

/*+ INDEX_FFS_ASC([@queryblock] <tablespec> <indexspec>) */

INDEX_FFS_DESC

than a full table scan

/*+ INDEX_FFS_DESC([@queryblock] <tablespec> <indexspec>) */

NO_INDEX_FFS

the specified indexes.

/*+ NO_INDEX_FFS([@queryblock] <tablespec> <indexspec>) */

set autotrace traceonly exp

SELECT latitude

FROM servers s, serv_inst i

WHERE s.srvr_id = i.srvr_id;

SELECT /*+ NO_INDEX_FFS(i pk_serv_inst) NO_INDEX_FFS(i ix_serv_inst) */ latitude

FROM servers s, serv_inst i

WHERE s.srvr_id = i.srvr_id;

INDEX_RS

scan for the specified table.

INDEX_RS_ASC

scan for the specified table.

INDEX_RS_DESC

scan for the specified table.

NO_INDEX_RS

scan of the specified indexes.

/*+ NO_INDEX_RS([@queryblock] <tablespec> <indexspec>) */

col column_name format a30

SELECT column_position, column_name

FROM user_ind_columns

WHERE index_name = ‘EMP_NAME_IX’;

set autotrace traceonly explain

SELECT first_name

FROM employees e

WHERE last_name BETWEEN ‘A’ AND ‘B’;

SELECT /*+ NO_INDEX_RS(e emp_name_ix) */ last_name

FROM employees e

WHERE first_name BETWEEN ‘A’ AND ‘B’;

INDEX_SS

the specified table. If the statement uses an index range

scan, then Oracle scans the index entries in ascending

order of their indexed values. In a partitioned index,

the results are in ascending order within each partition.

/*+ INDEX_SS([@queryblock] <tablespec> <indexspec>) */

INDEX_SS_ASC

the specified table. If the statement uses an index

range scan, then Oracle Database scans the index entries

in ascending order of their indexed values. In a partitioned

index, the results are in ascending order within each partition.

Each parameter serves the same purpose as in “INDEX Hint”.

The default behavior for a range scan is to scan index entries

in ascending order of their indexed values, or in descending

order for a descending index. This hint does not change the

default order of the index, and therefore does not specify

anything more than the INDEX_SS hint. However, you can

use the INDEX_SS_ASC hint to specify ascending range

scans explicitly should the default behavior change.

/*+ INDEX_SS_ASC([@queryblock] <tablespec> <indexspec>) */

INDEX_SS_DESC

specified table. If the statement uses an index range scan and

the index is ascending, then Oracle scans the index entries

in descending order of their indexed values. In a partitioned index,

the results are in descending order within each partition.

For a descending index, this hint effectively cancels out the

descending order, resulting in a scan of the index entries in

ascending order.

/*+ INDEX_SS_DESC([@queryblock] <tablespec> <indexspec>) */

NO_INDEX_SS

INDEX_COMBINE

indexes are given as arguments for the INDEX_COMBINE hint,

the optimizer uses whatever Boolean combination of bitmap

indexes has the best cost estimate for the table. If certain

indexes are given as arguments, the optimizer tries to use

some Boolean combination of those particular bitmap indexes.

/*+ INDEX_DESC([@queryblock] <tablespec> <indexspec>) */

set autotrace traceonly explain

SELECT *

FROM employees e

WHERE (manager_id = 108) OR (department_id = 110);

SELECT /*+ INDEX_COMBINE(e emp_manager_ix emp_department_ix) */ *

FROM employees e

WHERE (manager_id = 108) OR (department_id = 110);

INDEX_JOIN

/*+ INDEX_JOIN([@queryblock] <tablespec> <indexspec>) */

set autotrace traceonly explain

SELECT department_id

FROM employees e

WHERE manager_id < 110

AND department_id < 50;

———————————————————————

| Id | Operation                    | Name              | Cost(%CPU)|

———————————————————————

|  0 | SELECT STATEMENT             |                   |    2  (0) |

|* 1 |  TABLE ACCESS BY INDEX ROWID | EMPLOYEES         |    2  (0) |

|* 2 |   INDEX RANGE SCAN           | EMP_DEPARTMENT_IX |    1  (0) |

———————————————————————

SELECT /*+ INDEX_JOIN(e emp_manager_ix emp_department_ix) */ department_id

FROM employees e

WHERE manager_id < 110

AND department_id < 50;

————————————————————-

| Id | Operation           | Name              | Cost(%CPU) |

————————————————————-

|  0 | SELECT STATEMENT    |                   |    3 (34)  |

|* 1 |  VIEW               | index$_join$_001  |    3 (34)  |

|* 2 |   HASH JOIN         |                   |            |

|* 3 |    INDEX RANGE SCAN | EMP_DEPARTMENT_IX |    2 (50)  |

|* 4 |    INDEX RANGE SCAN | EMP_MANAGER_IX    |    2 (50)  |

————————————————————-

Index Scan Demos

col column_name format a30

SELECT column_position, column_name

FROM user_ind_columns

WHERE index_name = ‘EMP_NAME_IX’;

set autotrace traceonly explain

SELECT last_name

FROM employees e;

SELECT /*+ INDEX_FFS(e emp_name_ix) */ last_name

FROM employees e;

SELECT /*+ INDEX_SS(e emp_name_ix) */ last_name

FROM employees e;

SELECT /*+ INDEX_SS_ASC(e emp_name_ix) */ last_name

FROM employees e;

SELECT /*+ INDEX_DESC(e emp_name_ix) */ last_name

FROM employees e;

LEADING

specified set of tables as the prefix

in the execution plan.

/*+ LEADING([@queryblock] <table_name> <table_name>) */

set autotrace traceonly explain

SELECT *

FROM employees e, departments d, job_history j

WHERE e.department_id = d.department_id

AND e.hire_date = j.start_date;

SELECT /*+ LEADING(e j) */ *

FROM employees e, departments d, job_history j

WHERE e.department_id = d.department_id

AND e.hire_date = j.start_date;

ORDERED

in the order in which they appear

in the FROM clause.

/*+ ORDERED */

set autotrace traceonly explain

SELECT o.order_id, c.customer_id, l.unit_price * l.quantity

FROM customers c, order_items l, orders o

WHERE c.cust_last_name = ‘Mastroianni’

AND o.customer_id = c.customer_id

AND o.order_id = l.order_id;

SELECT /*+ ORDERED */ o.order_id, c.customer_id, l.unit_price * l.quantity

FROM customers c, order_items l, orders o

WHERE c.cust_last_name = ‘Mastroianni’

AND o.customer_id = c.customer_id

AND o.order_id = l.order_id;

USE_HASH

table with another row source with

a hash join.

/*+ USE_HASH([@queryblock] <tablespec> <tablespec>) */

set autotrace traceonly explain

SELECT DISTINCT s.srvr_id

FROM servers s, serv_inst i

WHERE s.srvr_id = i.srvr_id;

SELECT /*+ USE_HASH (s i) */ DISTINCT s.srvr_id

FROM servers s, serv_inst i

WHERE s.srvr_id = i.srvr_id;

SELECT /*+ USE_MERGE (s i) */ DISTINCT s.srvr_id

FROM servers s, serv_inst i

WHERE s.srvr_id = i.srvr_id;

NO_USE_HASH

joins when joining each specified table

to another row source using the specified

table as the inner table.

/*+ NO_USE_HASH([@queryblock] <tablespec> <tablespec>) */

USE_MERGE

another row source with a sort-merge join.

/*+ USE_MERGE([@queryblock] <tablespec> <tablespec>) */

NO_USE_MERGE

sort-merge joins when joining each

specified table to another row source

using the specified table as the inner table.

/*+ NO_USE_MERGE([@queryblock] <tablespec> <tablespec>) */

set autotrace traceonly explain

SELECT *

FROM employees e, departments d

WHERE e.department_id = d.department_id;

SELECT /*+ NO_USE_MERGE(e d) */ *

FROM employees e, departments d

WHERE e.department_id = d.department_id;

USE_NL

to another row source with a nested loops

join using the specified table as the inner table.

/*+ USE_NL([@queryblock] <tablespec> <tablespec>) */

set autotrace traceonly explain

SELECT DISTINCT s.srvr_id

FROM servers s, serv_inst i

WHERE s.srvr_id+0 = i.srvr_id+0;

SELECT /*+ USE_NL (i s) */ DISTINCT s.srvr_id

FROM servers s, serv_inst i

WHERE s.srvr_id+0 = i.srvr_id+0;

USE_NL_WITH_INDEX

specified table to another row source

with a nested loops join using the

specified table as the inner table.

/*+ USE_NL_WITH_INDEX([@queryblock] <tablespec> <index_name>) */

set autotrace traceonly explain

SELECT *

FROM orders h, order_items l

WHERE l.order_id = h.order_id

AND l.order_id > 3500;

SELECT /*+ USE_NL_WITH_INDEX(l item_order_ix) */ *

FROM orders h, order_items l

WHERE l.order_id = h.order_id

AND l.order_id > 3500;

NO_USE_NL

loops joins when joining each specified

table to another row source using the

specified table as the inner table.

/*+ NO_USE_NL([@queryblock] <tablespec> <tablespec>) */

set autotrace traceonly explain

SELECT *

FROM orders h, order_items l

WHERE l.order_id = h.order_id

AND l.order_id > 3500;

SELECT /*+ NO_USE_NL(l h) */ *

FROM orders h, order_items l

WHERE l.order_id = h.order_id

AND l.order_id > 3500;

NATIVE_FULL_OUTER_JOIN

full outer join, which is a native execution

method based on a hash join.

/*+ NATIVE_FULL_OUTER_JOIN( */

NO_NATIVE_FULL_OUTER_JOIN

native execution method when joining

each specified table. Instead, the full

outer join is executed as a union of left

outer join and anti-join.

/*+ NO_NATIVE_FULL_OUTER_JOIN */

PARALLEL

servers that can be used for a parallel

operation. The hint applies to the INSERT,

UPDATE, and DELETE portions of a statement

as well as to the table scan portion. If

any parallel restrictions are violated,

the hint is ignored.

/*+ PARALLEL([@queryblock] <tablespec> <degree | DEFAULT>) */

set autotrace traceonly exp

SELECT last_name

FROM employees hr_emp;

SELECT /*+ FULL(hr_emp) PARALLEL(hr_emp, 2) */ last_name

FROM employees hr_emp;

— overrides table definition and uses init parmameter

SELECT /*+ FULL(hr_emp) PARALLEL(hr_emp, DEFAULT) */ last_name

FROM employees hr_emp;

NO_PARALLEL

table clause. In general, hints take

precedence over table clauses.

/*+ NO_PARALLEL([@queryblock] <tablespec>) */

CREATE TABLE employees_demo

PARALLEL (DEGREE 4) AS

SELECT * FROM employees;

SELECT table_name, degree

FROM user_tables;

set autotrace traceonly exp

SELECT last_name

FROM employees_demo hr_emp;

SELECT /*+ NO_PARALLEL(hr_emp) */ last_name

FROM employees_demo hr_emp;

PARALLEL_INDEX

servers that can be used to parallelize

index range scans for partitioned indexes.

/*+ PARALLEL_INDEX([@queryblock] <tablespec> <index_name> <degree | DEFAULT>) */

NO_PARALLEL_INDEX

/*+ NO_PARALLEL_INDEX([@queryblock] <tablespec> <index_name>) */

PQ_DISTRIBUTE

Do this by specifying how rows of joined

tables should be distributed among producer

and consumer query servers. Using this

hint overrides decisions the optimizer

would normally make.

Outer_distribution is the distribution for the outer table.

Inner_distribution is the distribution for the inner table.

/*+ PQ_DISTRIBUTE([@queryblock] <tablespec> <outer_distribution> <inner_distribution>) */

FACT

It instructs the optimizer that the table

specified in table specification should

be considered as a fact table.

/*+ FACT([@queryblock] <tablespec>) */

NO_FACT

It instruct the optimizer that the

queried table should not be considered

as a fact table.

/*+ NO_FACT([@queryblock] <tablespec>) */

NO_EXPAND

considering OR-expansion for queries having

OR conditions or INLISTS in the WHERE clause.

Normally, the optimizer would consider using

OR expansion and use this method if it

decides the cost is lower than not using it.

/*+ NO_EXPAND(<@queryblock>);

set autotrace traceonly explain

SELECT *

FROM employees e, departments d

WHERE e.manager_id = 108

OR d.department_id = 110;

SELECT /*+ NO_EXPAND */ *

FROM employees e, departments d

WHERE e.manager_id = 108

OR d.department_id = 110;

MERGE

query. If a view’s query block contains

a GROUP BY clause or DISTINCT operator in

the SELECT list, then the optimizer can

merge the view into the accessing statement

only if complex view merging is enabled.

Complex merging can also be used to merge

an IN subquery into the accessing statement

if the subquery is uncorrelated.

/*+ MERGE(<@queryblock> [tablespec]); */

set autotrace traceonly explain

SELECT e1.last_name, e1.salary, v.avg_salary

FROM employees e1, (

SELECT department_id, AVG(salary) avg_salary

FROM employees e2

GROUP BY department_id) v

WHERE e1.department_id = v.department_id

AND e1.salary > v.avg_salary

SELECT /*+ MERGE(v) */ e1.last_name, e1.salary, v.avg_salary

FROM employees e1, (

SELECT department_id, AVG(salary) avg_salary

FROM employees e2

GROUP BY department_id) v

WHERE e1.department_id = v.department_id

AND e1.salary > v.avg_salary;

NO_MERGE

outer query and any inline view queries

into a single query.

/*+ NO_MERGE(<@queryblock> [tablespecification]); */

set autotrace traceonly explain

SELECT /*+NO_MERGE(seattle_dept)*/ e1.last_name, seattle_dept.department_name

FROM employees e1,

(SELECT location_id, department_id, department_name

FROM departments

WHERE location_id = 1700) seattle_dept

WHERE e1.department_id = seattle_dept.department_id;

SELECT /*+ NO_MERGE(seattle_dept) */ e1.last_name, seattle_dept.department_name

FROM employees e1, (

SELECT location_id, department_id, department_name

FROM departments

WHERE location_id = 1700) seattle_dept

WHERE e1.department_id = seattle_dept.department_id;

NO_QUERY_TRANSFORMATION

transformations, including but not limited

to OR-expansion, view merging, subquery

unnesting, star transformation, and

materialized view rewrite.

/*+ NO_QUERY_TRANSFORMATION) */

set autotrace traceonly explain

SELECT DISTINCT srvr_id

FROM servers

WHERE srvr_id NOT IN (

SELECT srvr_id

FROM servers

MINUS

SELECT srvr_id

FROM serv_inst);

SELECT /*+ NO_QUERY_TRANSFORMATION */ DISTINCT srvr_id

FROM servers

WHERE srvr_id NOT IN (

SELECT srvr_id

FROM servers

MINUS

SELECT srvr_id

FROM serv_inst);

NO_REWRITE

hint disables query rewrite for the query

block, overriding the setting of the

parameter QUERY_REWRITE_ENABLED.

/*+ NO_REWRITE(<@queryblock>) */

set autotrace traceonly explain

SELECT SUM(s.amount_sold) AS dollars

FROM sales s, times t

WHERE s.time_id = t.time_id

GROUP BY t.calendar_month_desc;

SELECT /*+ NO_REWRITE */ SUM(s.amount_sold) AS dollars

FROM sales s, times t

WHERE s.time_id = t.time_id

GROUP BY t.calendar_month_desc;

NO_UNNEST

/*+ NO_UNNEST(<@queryblock>) */

set autotrace traceonly explain

SELECT srvr_id

FROM servers

WHERE srvr_id IN (

SELECT /*+ unnest */ srvr_id FROM serv_inst);

——————————————————————-

| Id | Operation              | Name         | Rows | Cost (%CPU) |

——————————————————————-

|  0 | SELECT STATEMENT       |              |   11 |    5   (20) |

|* 1 |  HASH JOIN SEMI        |              |   11 |    5   (20) |

|  2 |    INDEX FULL SCAN     | PK_SERVERS   |  141 |    1    (0) |

|  3 |   INDEX FAST FULL SCAN | PK_SERVERS   |  999 |    3    (0) |

——————————————————————-

Predicate Information (identified by operation id):

—————————————————

1 – access(“SRVR_ID”=”SRVR_ID”)

SELECT srvr_id

FROM servers

WHERE srvr_id IN (

SELECT /*+ no_unnest */ srvr_id FROM serv_inst);

—————————————————————

| Id | Operation          | Name         | Rows | Cost (%CPU) |

—————————————————————

|  0 | SELECT STATEMENT   |              |    1 |  128    (0) |

|* 1 |  INDEX FULL SCAN   | PK_SERVERS   |    7 |    1    (0) |

|* 2 |   INDEX FULL SCAN  | PK_SERV_INST |    2 |    2    (0) |

—————————————————————

Predicate Information (identified by operation id):

—————————————————

1 – filter( EXISTS (SELECT /*+ NO_UNNEST */ 0 FROM “SERV_INST”

“SERV_INST” WHERE “SRVR_ID”=:B1))

2 – access(“SRVR_ID”=:B1)

filter(“SRVR_ID”=:B1)

REWRITE

use REWRITE with a view list and the

list contains an eligible materialized

view, Oracle uses that view regardless of

its cost. Oracle does not consider views

outside of the list. If you do not specify

a view list, Oracle searches for an eligible

materialized view and always uses it regardless

of its cost.

/*+ REWRITE([@queryblock] <view, view, …>) */

CREATE MATERIALIZED VIEW mv_rewrite

TABLESPACE uwdata

REFRESH ON DEMAND

ENABLE QUERY REWRITE

AS SELECT s.srvr_id, i.installstatus, COUNT(*)

FROM servers s, serv_inst i

WHERE s.srvr_id = i.srvr_id

GROUP BY s.srvr_id, i.installstatus;

set autotrace traceonly exp

SELECT s.srvr_id, i.installstatus, COUNT(*)

FROM servers s, serv_inst i

WHERE s.srvr_id = i.srvr_id

AND s.srvr_id = 502

GROUP BY s.srvr_id, i.installstatus;

SELECT /*+ REWRITE */ s.srvr_id, i.installstatus, COUNT(*)

FROM servers s, serv_inst i

WHERE s.srvr_id = i.srvr_id

AND s.srvr_id = 502

GROUP BY s.srvr_id, i.installstatus;

STAR_TRANSFORMATION

which the transformation has been used.

Without the hint, the optimizer could

make a cost-based decision to use the

best plan generated without the transformation,

instead of the best plan for the transformed query.

Even if the hint is given, there is no

guarantee that the transformation will

take place. The optimizer will only

generate the subqueries if it seems

reasonable to do so. If no subqueries

are generated, there is no transformed

query, and the best plan for the untransformed

query will be used regardless of the hint.

/*+ STAR_TRANSFORMATION(<@queryblock>) */

set autotrace traceonly exp

SELECT *

FROM sales s, times t, products p, channels c

WHERE s.time_id = t.time_id

AND s.prod_id = p.prod_id

AND s.channel_id = c.channel_id

AND p.prod_status = ‘obsolete’;

SELECT /*+ STAR_TRANSFORMATION */ *

FROM sales s, times t, products p, channels c

WHERE s.time_id = t.time_id

AND s.prod_id = p.prod_id

AND s.channel_id = c.channel_id

AND p.prod_status = ‘obsolete’;

NO_STAR_TRANSFORMATION

star query transformation.

/*+ NO_STAR_TRANSFORMATION(<@queryblock>) */

UNNEST

merge the body of the subquery into

the body of the query block that contains

it, allowing the optimizer to consider

them together when evaluating access

paths and joins.

/*+ UNNEST(<@queryblock>) */

USE_CONCAT

WHERE clause of a query to be transformed

into a compound query using the UNION ALL

set operator. Normally, this transformation

occurs only if the cost of the query using

the concatenations is cheaper than the

cost without them.

The USE_CONCAT hint turns off inlist processing

and OR-expands all disjunctions, including inlists.

set autotrace traceonly explain

SELECT *

FROM employees e

WHERE manager_id = 108

OR department_id = 110;

SELECT /*+ USE_CONCAT */ *

FROM employees e

WHERE manager_id = 108

OR department_id = 110;

NO_XMLINDEX_REWRITE

the rewriting of XPath expressions

in SQL statements.

/*+ NO_XMLINDEX_REWRITE */

FROM table WHERE existsNode(OBJECT_VALUE, ‘/*’) = 1;

NO_XML_QUERY_REWRITE

the rewriting of XPath expressions

in SQL statements.

/*+ NO_XML_QUERY_REWRITE */

FROM DUAL;

CARDINALITY

provided intgeger as the computed

cardinality of table (tablespace)

without checking.

/*+ CARDINALITY(<tablespec>, <integer>) */

set autotrace traceonly explain

SELECT *

FROM serv_inst si

WHERE srvr_id = 1;

SELECT /*+ cardinality(si 999) */ *

FROM serv_inst si

WHERE srvr_id = 1;

PUSH_JOIN_PRED

the view (found in the 8.1.5 docs)

FROM T1 (

SELECT T2.X, T3.Y

FROM T2, T3

WHERE T2.X = T3.X) v

WHERE t1.x = v.x

AND t1.y = 1;