Oracle 用hint来影响执行计划
来源：互联网 发布：linux 删除tomcat日志 编辑：程序博客网 时间：2024/06/05 09:48
全表扫描提示SYS@ prod> grant plustrace to hr ;Grant succeeded.SYS@ prod> conn hr/hrConnected.HR@ prod> set autotrace onHR@ prod> select /*+ full(employees) */ first_name from employees where employee_id = 100 ;FIRST_NAME--------------------StevenExecution Plan----------------------------------------------------------Plan hash value: 1445457117-------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |-------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 11 | 3 (0)| 00:00:01 ||* 1 | TABLE ACCESS FULL| EMPLOYEES | 1 | 11 | 3 (0)| 00:00:01 |-------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 1 - filter("EMPLOYEE_ID"=100)Statistics---------------------------------------------------------- 736 recursive calls 0 db block gets 193 consistent gets 6 physical reads 0 redo size 532 bytes sent via SQL*Net to client 523 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 6 sorts (memory) 0 sorts (disk) 1 rows processedHR@ prod> select first_name from employees where employee_id = 100 ;FIRST_NAME--------------------StevenExecution Plan----------------------------------------------------------Plan hash value: 1833546154---------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |---------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 11 | 1 (0)| 00:00:01 || 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 1 | 11 | 1 (0)| 00:00:01 ||* 2 | INDEX UNIQUE SCAN | EMP_EMP_ID_PK | 1 | | 0 (0)| 00:00:01 |---------------------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 2 - access("EMPLOYEE_ID"=100)Statistics---------------------------------------------------------- 1 recursive calls 0 db block gets 2 consistent gets 1 physical reads 0 redo size 532 bytes sent via SQL*Net to client 523 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk)1rows processed按顺序联结提示，表联结会按照FROM后面的顺序进行。SH@ prod> set autotrace onSH@ prod> select /*+ ordered */ promo_name , cust_first_name , prod_name , amount_sold from sales , promotions , customers , products 2 where sales.prod_id = 100 ;no rows selectedExecution Plan----------------------------------------------------------Plan hash value: 2619432180------------------------------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |------------------------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 713G| 43T| 13G (1)|999:59:59 | | || 1 | MERGE JOIN CARTESIAN | | 713G| 43T| 13G (1)|999:59:59 | | || 2 | MERGE JOIN CARTESIAN | | 9910M| 378G| 71M (1)|239:56:32 | | || 3 | MERGE JOIN CARTESIAN | | 178K| 5928K| 5582 (1)| 00:01:07 | | || 4 | PARTITION RANGE ALL | | 355 | 3195 | 94 (0)| 00:00:02 | 1 | 28 || 5 | TABLE ACCESS BY LOCAL INDEX ROWID| SALES | 355 | 3195 | 94 (0)| 00:00:02 | 1 | 28 || 6 | BITMAP CONVERSION TO ROWIDS | | | | | | | ||* 7 | BITMAP INDEX SINGLE VALUE | SALES_PROD_BIX | | | | | 1 | 28 || 8 | BUFFER SORT | | 503 | 12575 | 5487 (1)| 00:01:06 | | || 9 | TABLE ACCESS FULL | PROMOTIONS | 503 | 12575 | 15 (0)| 00:00:01 | | || 10 | BUFFER SORT | | 55500 | 379K| 71M (1)|239:56:32 | | || 11 | TABLE ACCESS FULL | CUSTOMERS | 55500 | 379K| 403 (1)| 00:00:05 | | || 12 | BUFFER SORT | | 72 | 1872 | 13G (1)|999:59:59 | | || 13 | TABLE ACCESS FULL | PRODUCTS | 72 | 1872 | 1 (0)| 00:00:01 | | |------------------------------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 7 - access("SALES"."PROD_ID"=100)Statistics---------------------------------------------------------- 0 recursive calls 0 db block gets 44 consistent gets 0 physical reads 0 redo size 559 bytes sent via SQL*Net to client 512 bytes received via SQL*Net from client 1 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk)0rows processed去掉提示后，PRODUCTS与CUSTOMERS的顺序发生了颠倒。SH@ prod> select promo_name , cust_first_name , prod_name , amount_sold from sales , promotions , customers , products 2 where sales.prod_id = 100 ;no rows selectedExecution Plan----------------------------------------------------------Plan hash value: 3204799813------------------------------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |------------------------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 713G| 43T| 5182M (1)|999:59:59 | | || 1 | MERGE JOIN CARTESIAN | | 713G| 43T| 5182M (1)|999:59:59 | | || 2 | MERGE JOIN CARTESIAN | | 12M| 735M| 247K (1)| 00:49:32 | | || 3 | MERGE JOIN CARTESIAN | | 178K| 5928K| 5582 (1)| 00:01:07 | | || 4 | PARTITION RANGE ALL | | 355 | 3195 | 94 (0)| 00:00:02 | 1 | 28 || 5 | TABLE ACCESS BY LOCAL INDEX ROWID| SALES | 355 | 3195 | 94 (0)| 00:00:02 | 1 | 28 || 6 | BITMAP CONVERSION TO ROWIDS | | | | | | | ||* 7 | BITMAP INDEX SINGLE VALUE | SALES_PROD_BIX | | | | | 1 | 28 || 8 | BUFFER SORT | | 503 | 12575 | 5487 (1)| 00:01:06 | | || 9 | TABLE ACCESS FULL | PROMOTIONS | 503 | 12575 | 15 (0)| 00:00:01 | | || 10 | BUFFER SORT | | 72 | 1872 | 247K (1)| 00:49:32 | | || 11 | TABLE ACCESS FULL | PRODUCTS | 72 | 1872 | 1 (0)| 00:00:01 | | || 12 | BUFFER SORT | | 55500 | 379K| 5182M (1)|999:59:59 | | || 13 | TABLE ACCESS FULL | CUSTOMERS | 55500 | 379K| 403 (1)| 00:00:05 | | |------------------------------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 7 - access("SALES"."PROD_ID"=100)Statistics---------------------------------------------------------- 1 recursive calls 0 db block gets 44 consistent gets 0 physical reads 0 redo size 559 bytes sent via SQL*Net to client 512 bytes received via SQL*Net from client 1 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk)0rows processed指定索引的指示INDEX（table_name [index_name]）,如果不指定索引名，表示访问这个表要使用索引。HR@ prod> create table employees1 as select * from employees ;create table employees1 as select * from employees *ERROR at line 1:ORA-00955: name is already used by an existing objectHR@ prod> create index test_idx1 on employees1 ( employee_id ) ;Index created.HR@ prod> create index test_idx2 on employees1 ( employee_id , first_name ) ;Index created.Oracle在默认的情况下当然会使用单列索引，而不用复合索引。HR@ prod> select last_name from employees1 where employee_id = 100 ;LAST_NAME-------------------------KingExecution Plan----------------------------------------------------------Plan hash value: 2613790398------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 11 | 2 (0)| 00:00:01 || 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES1 | 1 | 11 | 2 (0)| 00:00:01 ||* 2 | INDEX RANGE SCAN | TEST_IDX1 | 1 | | 1 (0)| 00:00:01 |------------------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 2 - access("EMPLOYEE_ID"=100)Statistics---------------------------------------------------------- 1 recursive calls 0 db block gets 3 consistent gets 0 physical reads 0 redo size 529 bytes sent via SQL*Net to client 523 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed下面这种只指定索引的写法是错的，会被优化器忽略。HR@ prod> select /*+ index(test_idx2) */ last_name from employees1 where employee_id = 100 ;LAST_NAME-------------------------KingExecution Plan----------------------------------------------------------Plan hash value: 2613790398------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 11 | 2 (0)| 00:00:01 || 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES1 | 1 | 11 | 2 (0)| 00:00:01 ||* 2 | INDEX RANGE SCAN | TEST_IDX1 | 1 | | 1 (0)| 00:00:01 |------------------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 2 - access("EMPLOYEE_ID"=100)Statistics---------------------------------------------------------- 1 recursive calls 0 db block gets 3 consistent gets 0 physical reads 0 redo size 529 bytes sent via SQL*Net to client 523 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed正确的写法HR@ prod> select /*+ index(employees1 test_idx2) */ last_name from employees1 where employee_id = 100 ;LAST_NAME-------------------------KingExecution Plan----------------------------------------------------------Plan hash value: 2093088777------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 11 | 2 (0)| 00:00:01 || 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES1 | 1 | 11 | 2 (0)| 00:00:01 ||* 2 | INDEX RANGE SCAN | TEST_IDX2 | 1 | | 1 (0)| 00:00:01 |------------------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 2 - access("EMPLOYEE_ID"=100)Statistics---------------------------------------------------------- 1 recursive calls 0 db block gets 3 consistent gets 0 physical reads 0 redo size 529 bytes sent via SQL*Net to client 523 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk)1rows processed常见的联结提示：Use_merge()Use_ln()Use_hash()Leading()使用提示时要注意，如果表有别名，一定要用别名，否则提示无效。而且，表名一定不能有用户名来限制。阅读全文
0 0
- Oracle 用hint来影响执行计划
- oracle执行计划和hint的认识
- Oracle rownum影响执行计划
- [Oracle]-[索引][HINT][执行计划]-带HINT的索引执行计划
- 如何改变oracle的执行计划（HINT）
- CBO对于Oracle SQL执行计划的影响
- Oracle中CLUSTER_FACTOR对SQL执行计划的影响
- Oracle AWR 阙值影响历史执行计划
- Oracle AWR 阙值影响历史执行计划
- Oracle执行计划——处理一种并行hint不生效的情况
- 使用hint优化Oracle的执行计划 以及 SQL Tune Advisor的使用
- 为什么Oracle有时会用索引来查找数据?--强制Oracle使用最优的“执行计划”
- 分析Oracle有时会用索引来查找数据的原因－oracle执行计划
- 为什么Oracle有时会用索引来查找数据?--强制Oracle使用最优的“执行计划”
- 利用oracle spm来优化SYS_OP_C2C的执行计划
- Oracle执行优化hint使用教程
- index_ss hint 使用的执行计划变化对比
- dblink导致执行计划出错，hint也无效
- 不再是一个学生
- 【HTML5学习笔记】33：CSS3过渡效果
- 病闲细笔
- Unity编辑器拓展之一：ReorderableList可重新排序的列表框（简单使用）
- bootstrap 利用jquery 添加disabled属性
- Oracle 用hint来影响执行计划
- Javaweb基础之cookie&session
- ionic2+启动白屏问题-------之补充解决之道
- Subway
- 信号在android源码/external/dhcpcd 源码项目中的应用解读分析
- （13）会话和cookie技术
- 正则表达式
- X250无法开机问题解决
- 【操作系统学习日记】2.BIOS(基本输入输出系统)
