Optimizer Hints,Objectives,After completing this lesson, you should be able to specify hints for: Optimizer mode Query transformation Access path Join orders Join methods,Optimizer Hints: Overview,Optimizer hints: Are used to alter execution plans Influence optimizer decisions Provide a mechanism to instruct the optimizer to choose a certain query execution plan,Types of Hints,Specifying Hints,hint,comment text,*/,/*+,hint,comment text,-+,Rules for Hints,Place hints immediately after the first SQL keyword of a statement block. Each statement block can have only one hint comment, but it can contain multiple hints. Hints apply to only the statement block in which they appear. If a statement uses aliases, hints must reference aliases rather than table names.,Hint Recommendations,Use hints carefully because they imply a high maintenance load. Be aware of the performance impact of hard-coded hints when they become less valid.,Optimizer Hint Syntax: Example,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 /,Hint Categories,There are hints for: Optimization approaches and goals Access paths Query transformations Join orders Join operation Parallel execution,Optimization Goals and Approaches,Hints for Access Paths,Full Notes Page,Hints for Access Paths,Full Notes Page,INDEX_COMBINE Hint: Example,SELECT -+INDEX_COMBINE(CUSTOMERS) cust_last_name FROM SH.CUSTOMERS WHERE ( CUST_GENDER= F AND CUST_MARITAL_STATUS = single) OR CUST_YEAR_OF_BIRTH BETWEEN 1917 AND 1920;,INDEX_COMBINE Hint: Example,Execution Plan - 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=491 Card=10481 Bytes =167696) 1 0 TABLE ACCESS (BY INDEX ROWID) OF CUSTOMERS (Cost=491 ) 2 1 BITMAP CONVERSION (TO ROWIDS) 3 2 BITMAP OR 4 3 BITMAP AND 5 4 BITMAP INDEX (SINGLE VALUE) OF CUST_MARITAL_BIX 6 4 BITMAP INDEX (SINGLE VALUE) OF CUST_GENDER_BIX 7 3 BITMAP MERGE 7 BITMAP INDEX (RANGE SCAN) OF CUST_YOB_BIX,Hints for Query Transformation,Hints for Query Transformation,Full Notes Page,Hints for Join Orders,Full Notes Page,Hints for Join Operations,Full Notes Page,Other Hints,Full Notes Page,Hints for Suppressing Index Usage,Hints and Views,Do not use hints in views. Use view-optimization techniques: Statement transformation Results accessed like a table Hints can be used on mergeable views and nonmergeable views.,Full Notes Page,Hints for View Processing,Global and Local Hints,Extended hint syntax enables the specifying of (global) hints through views. References a table name in the hint with a dot notation,CREATE view city_view AS SELECT * FROM customers c WHERE cust_city like S%; SELECT /*+ index(v.c cust_credit_limit_idx) */ v.cust_last_name, v.cust_credit_limit FROM city_view v WHERE cust_credit_limit 5000;,Specifying a Query Block in a Hint,Explain plan for SELECT employee_id, last_name FROM hr.employees e WHERE last_name = Smith; SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY(NULL, NULL, ALL);,SELECT /*+ QB_NAME(qb) FULL(qb e) */ employee_id, last_name FROM hr.employees e WHERE employee_id = 100;,1,2,Full Notes Page,Specifying a Full Set of Hints,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;,Summary,In this lesson, you should have learned how to: Set the optimizer mode Use optimizer hint syntax Determine access-path hints Analyze hints and their impact on views,