Using Optimizer Hints

What is the ROWID hint? What does it do?

The ROWID hint explicitly chooses a table scan by ROWID for the specified table.

What are the dangers of Hints?

There can be a negative performance impact of hard-coded hints when they become less valid. Ignoring optimizer can lead to suboptimal execution Using hints involves extra code that must be managed, checked, and controlled.

What are some rules for hints?

- Place hints immediately after first SQL keyword in statement - Each statement block can only have 1 hint comment, but can contain multiple hints - Hints apply to only the statement block they appear in - If a statement uses aliases, hints must reference the aliases rather than the table names - Optimizer ignores misspelled hints without raising errors.

What is the ORDERED hint? What does it do?

Causes the Oracle server to join tables in the order in which they appear in the FROM clause

What is the major rule about Hints and Views?

Do not use hints in or on views. Views can be defined in one context and used in another. You can get unexpected plans.

What are the Hints for Access Paths (how Oracle accesses data)?

FULL CLUSTER HASH ROWID INDEX INDEX_ASC INDEX_DESC INDEX_COMBINE

What are some Hints that guide Query Transformation?

NO_QUERY_TRANSFORMATION USE_CONCAT NO_EXPAND REWRITE NO_REWRITE UNNEST NO_UNNEST

with the hints: USE_NL, NO_USE_NL, USE_NL_WITH_INDEX what does NL mean?

Nested Loop. These are hints to either force using or not using nested loops for join operations

What are the two hints regarding Join Orders?

ORDERED LEADING

What are the types of hints?

Single-table hints multitable hints query block hints statement hints

What is the ALL_ROWS hint? What does it do?

The ALL_ROWS hint Selects a cost-based approach with a goal of best throughput. That is, minimum total resource consumption. SELECT /*+ ALL_ROWS */ employee_id, last_name, salary, job_id FROM employees WHERE employee_id = 7566;

What is the CLUSTER hint? What does it do?

The CLUSTER hint instructs the optimizer to use a cluster scan to access the specified table. This hint applies only to clustered tables.

What is the HASH hint? What does it do?

The HASH hint instructs the optimizer to use a hash scan to access the specified table. This hint applies only to tables stored in a table cluster.

What is the INDEX hint? What does it do?

The INDEX hint explicitly chooses an index scan for the specified table. You can use the INDEX hint for domain, B*-tree, bitmap, and bitmap join indexes. SELECT /*+ INDEX(p sex_index) use sex_index because there are few male patients */ name, height, weight FROM patients p WHERE sex = 'm';

What is the INDEX_COMBINE hint? What does it do?

The INDEX_COMBINE hint explicitly chooses a bitmap access path for the table. If no indexes are given as arguments for the INDEX_COMBINE hint, the optimizer uses a Boolean combination of bitmap indexes that 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. For example: SELECT /*+INDEX_COMBINE(c cust_gender_bix cust_yob_bix)*/ * FROM customers c WHERE cust_year_of_birth < 70 AND cust_gender = 'M';

What is the NO_INDEX hint? What does it do?

The NO_INDEX hint explicitly disallows a set of indexes for the specified table. you can specify one index, or many indexes, or none at all (which disallows any index use) SELECT /*+NO_INDEX(e emp_empid)*/ employee_id FROM employees e WHERE employee_id > 200;

What are the Hint Categories? What are there hints for?

There are hints for: • Optimization approaches and goals • Access paths • Query transformations • Join orders • Join operation • Parallel execution • Additional hints

What are the hints USE_MERGE and NO_USE_MERGE? What do they do?

These specify to use or not use sort-merge joins

What are the hints USE_HASH and NO_USE_HASH? What do they do?

They specify to use or not use hash joins for join operations

What is the FIRST_ROWS(n) hint? What does it do?

This hint instructs the server to select the plan that returns the first (n) rows most efficiently SELECT /*+ FIRST_ROWS(10) */ employee_id, last_name, salary, job_id FROM employees WHERE department_id = 20;

What are some hints regarding Join Operations?

USE_NL NO_USE_NL USE_NL_WITH_INDEX USE_MERGE NO_USE_MERGE USE_HASH NO_USE_HASH DRIVING_SITE

What is the LEADING hint? What does it do?

Uses the specified tables as the first table in the join order

When should you use optimizer hints?

VERY sparingly. Only after you gather statistics and evaluated the optimizer plan without hints. Using hints involves extra code that must be managed, checked, and controlled.

What is the syntax for a Hint?

comment followed by + (no space allowed) --+ hint or /*+ hint */ SELECT /*+ INDEX(e empfirstname_idx) skewed col */ * FROM employees e WHERE first_name='David'

What does the FULL hint do?

explicitly selects a full table scan for a specific table. ignores indexes. SELECT /*+ FULL(e) */ employee_id, last_name FROM hr.employees e WHERE last_name LIKE 'K%';

What is the NO_QUERY_TRANSFORMATION hint? What does it do?

skips all query transformations. including but not limited to: OR-expansion view merging subquery unnesting star transformation materialized view rewrite

Describe what is going on in this statement: SELECT /*+ LEADING(e2 e1) USE_NL(e1) INDEX(e1 emp_emp_id_pk) USE_MERGE(j) FULL(j) */ e1.first_name, e1.last_name, j.job_id, sum(e2.salary) total_sal FROM hr.employees e1, hr.employees e2, hr.job_history j WHERE e1.employee_id = e2.manager_id AND e1.employee_id = j.employee_id AND e1.hire_date = j.start_date GROUP BY e1.first_name, e1.last_name, j.job_id ORDER BY total_sal;

the LEADING hint specifies the exact join order. The join methods are also specified for the different tables.

what does the hint do here? UPDATE /*+ INDEX(p PRODUCTS_PROD_CAT_IX)*/ products p SET p.prod_min_price = (SELECT (pr.prod_list_price*.95) FROM products pr WHERE p.prod_id = pr.prod_id) WHERE p.prod_category = 'Men' AND p.prod_status = 'available, on stock' /

the hint forces the optimizer to use an index called PRODUCTS_PROD_CAT_IX on the products table in the prod_category column.

What do Optimizer Hints allow you to do?

they allow you to influence the decisions made by the optimizer

What is the USE_CONCAT hint? What does it do?

this hint forces OR conditions in the WHERE clause to be rewritten using UNION ALL. disables IN-list processing

What is the NO_EXPAND hint? What does it do?

this prevents OR expansion. ( no UNION ALL)