162.Oracle数据库SQL开发之 SQL优化——优化器传递提示
欢迎转载,转载请标明出处:http://blog.csdn.net/notbaron/article/details/50186065
可以为优化器传递提示。提示是一个优化器指令,影响优化器对执行计划的选择。正确的提示可以改进SQL语句的性能。通过比较使用和不使用提示的SQL语句的执行计划成本,检查提示的有效性。
FIRST_ROWS(N)提示告诉优化器生成一个执行计划,来最小化查询中返回前n行所用的事件。
store@PDB1> explain plan set statement_id='HINT'for select /*+ FIRST_ROWS(2) */ p.name,pt.name
fromproducts p,product_types pt where p.product_type_id=pt.product_type_id;
Explained.
查询计划如下:
store@PDB1> @explain_plan.sql
Enter value for v_statement_id: HINT
old 12: AND statement_id = '&&v_statement_id'
new 12: AND statement_id = 'HINT'
old 14: AND statement_id = '&v_statement_id'
new 14: AND statement_id = 'HINT'
EXECUTION_PLAN
----------------------------------------------------------------------------------------------------
0 SELECT STATEMENT Cost = 4
1 NESTED LOOPS Cost = 1
2 NESTED LOOPS Cost = 1
3 TABLEACCESS FULL PRODUCTS TABLE Cost = 1
4 INDEXUNIQUE SCAN PRODUCT_TYPES_PK INDEX (UNIQUE) Cost = 2
5 TABLE ACCESS BY INDEX ROWID PRODUCT_TYPES TABLE Cost = 2
6 rows selected.
接着同样的查询生成执行计划,但不使用提示:
store@PDB1> explain plan setstatement_id='no_hint' for
selectp.name,pt.name from products p,product_types pt wherep.product_type_id=pt.product_type_id;
Explained.
查看如下:
store@PDB1> @explain_plan.sql
Enter value for v_statement_id: no_hint
old 12: AND statement_id = '&&v_statement_id'
new 12: AND statement_id = 'no_hint'
old 14: AND statement_id = '&v_statement_id'
new 14: AND statement_id = 'no_hint'
EXECUTION_PLAN
----------------------------------------------------------------------------------------------------
0 SELECT STATEMENT Cost = 6
1 MERGE JOIN Cost = 1
2 TABLE ACCESS BY INDEX ROWID PRODUCT_TYPES TABLE Cost = 1
3 INDEXFULL SCAN PRODUCT_TYPES_PK INDEX (UNIQUE) Cost = 1
4 SORT JOIN Cost = 2
5 TABLEACCESS FULL PRODUCTS TABLE Cost = 1
6 rows selected.
使用提示使得查询的运行成本减少了两个工作单元。