592- 使用的Hint 无效-续
来源：互联网 发布：宁波大数据培训 编辑：程序博客网 时间：2024/06/08 15:04
Index dropped.---索引都删了，hint 当然失效。
SQL> select /*+ index(dept_cp IDX_DEPT_CP_LOC) */ deptno,dname from dept_cp where loc='CHICAGO';
Execution Plan
----------------------------------------------------------
Plan hash value: 4265608153
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 20 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| DEPT_CP | 1 | 20 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("LOC"='CHICAGO')
SQL>
SQL> select * from dept_cp;
Execution Plan
----------------------------------------------------------
Plan hash value: 4265608153
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 80 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| DEPT_CP | 4 |80 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------
SQL>
SQL> select /*+ parallel(dept_cp 2) */* from dept_cp d;--错误的hint，表有别名 必须要用！！！
Execution Plan
----------------------------------------------------------
Plan hash value: 4265608153
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 80 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| DEPT_CP | 4 | 80 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------
SQL> set lines 300
SQL> select /*+ parallel(d 2) */* from dept_cpd;--并行访问。
Execution Plan
----------------------------------------------------------
Plan hash value: 1553418115
--------------------------------------------------------------------------------------------------------------
| Id | Operation | Name| Rows | Bytes | Cost (%CPU)| Time| TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 80 | 2 (0)| 00:00:01 || | |
| 1 | PX COORDINATOR || | | | | | | |
| 2 | PX SEND QC (RANDOM)| :TQ10000 | 4 | 80 | 2 (0)| 00:00:01 | Q1,00 | P->S | QC (RAND) |
| 3 | PX BLOCK ITERATOR || 4 | 80 | 2 (0)| 00:00:01 | Q1,00 | PCWC | |
| 4 | TABLE ACCESS FULL| DEPT_CP| 4 | 80 | 2 (0)| 00:00:01 | Q1,00 | PCWP | |
--------------------------------------------------------------------------------------------------------------
SQL> select * from dept_cp;
Execution Plan
----------------------------------------------------------
Plan hash value: 4265608153
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 80 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| DEPT_CP | 4 |80 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------
SQL>
===============================================================================================
SQL> select /*+ index(dept_cp PK_DEPT_CP) parallel(dept_cp 2) */deptnofrom dept_cp;
Execution Plan
----------------------------------------------------------
Plan hash value: 1553418115
--------------------------------------------------------------------------------------------------------------
| Id | Operation | Name| Rows | Bytes | Cost (%CPU)| Time| TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT || 4 | 12 | 2 (0)| 00:00:01 || | |
| 1 | PX COORDINATOR ||| | | || | |
| 2 | PX SEND QC (RANDOM)| :TQ10000 | 4 | 12 | 2 (0)| 00:00:01 | Q1,00 | P->S | QC (RAND) |
| 3 | PX BLOCK ITERATOR || 4 | 12 | 2 (0)| 00:00:01 | Q1,00 | PCWC | |
| 4 | TABLE ACCESS FULL| DEPT_CP| 4 | 12 | 2 (0)| 00:00:01 | Q1,00 | PCWP | |
--------------------------------------------------------------------------------------------------------------
--由此可见上面这个hint是“错误的”，或者说不是预期的组合hint没有都执行。
SQL> select /*+ index(dept PK_DEPT) parallel(dept 2) */deptno from dept;
Execution Plan
----------------------------------------------------------
Plan hash value: 2913917002
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | |4 | 12 |1 (0)| 00:00:01 |
| 1 | INDEX FULL SCAN | PK_DEPT |4 | 12 |1 (0)| 00:00:01 |
----------------------------------------------------------------------------
SQL> select t.INDEX_NAME,t.TABLE_NAME,t.COLUMN_NAME,i.index_type from user_ind_columns t,user_indexes i where t.index_name = i.index_name
order by t.TABLE_NAME;
INDEX_NAME TABLE_NAME COLUMN_NAME INDEX_TYPE
-------------------- --------------- --------------- ---------------------------
PK_DEPT DEPT DEPTNO NORMAL
PK_DEPT_CP DEPT_CP DEPTNO NORMAL
SQL> desc DEPT
Name Null?Type
----------------------------------------------------- -------- ------------------------------------
DEPTNO NOT NULL NUMBER(2)
DNAME VARCHAR2(14)
LOC VARCHAR2(13)
SQL> desc DEPT_CP
Name Null? Type
----------------------------------------------------- -------- ------------------------------------
DEPTNO NUMBER(2)
DNAME NOT NULL VARCHAR2(14)
LOC VARCHAR2(13)
--可以发现,dept_cp 只是建立了索引，但是没有建立主键.
SQL> alter table dept_cp modify(deptno not null);
Table altered.
SQL> select /*+ index(dept_cp PK_DEPT_CP) parallel(dept_cp 2) */deptno from dept_cp;
Execution Plan
----------------------------------------------------------
Plan hash value: 637767428
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 12 | 1(0)| 00:00:01 |
| 1 | INDEX FULL SCAN | PK_DEPT_CP | 4 | 12 | 1(0)| 00:00:01 |
-------------------------------------------------------------------------------
--由此可见上面这个hint是“错误的”，或者说不是预期的组合hint没有都执行。
可以看到，执行计划走得是dept 上索引PK_DEPT_CP 的索引全扫描，但是这里是串行执行的。
parallel (dept_cp 2)失效了，因为PK_DEPT_CP 并不是分区索引，而对于非分区索引而言，索引
范围扫描或者索引全扫描都不可能并行执行。
----------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------下面这两个差在哪里--------------------------------------------------------
SQL> select /*+ index(d PK_DEPT_CP) parallel(d 2) */ * from dept_cp d;
Execution Plan
----------------------------------------------------------
Plan hash value: 2095782048
------------------------------------------------------------------------------------------
| Id | Operation | Name| Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT || 4 | 80 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| DEPT_CP| 4 | 80 | 2 (0)| 00:00:01 |
| 2 | INDEX FULL SCAN | PK_DEPT_CP | 4 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------
SQL> select /*+ index(d PK_DEPT_CP) parallel(d 2) */deptnofrom dept_cp d;
Execution Plan
----------------------------------------------------------
Plan hash value: 637767428
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 12 | 1(0)| 00:00:01 |
| 1 | INDEX FULL SCAN | PK_DEPT_CP | 4 | 12 | 1(0)| 00:00:01 |
-------------------------------------------------------------------------------
SQL>
截图如下
select /*+ use_hash(e) */e.empno,e.ename,d.loc from emp_sys e,dept_sys d where e.deptno=d.deptno and d.loc='CHICAGO'; ---未完待续
- 592- 使用的Hint 无效-续
- 592- 使用的Hint 无效
- 简单的Hint使用
- ORACLE使用HINT的方法
- ORACLE使用HINT的方法
- Oracle 中HINT的使用
- Oracle中hint的使用
- 597- 使用的hint 自相矛盾
- 使用Hint时的注意事项
- hint使用
- Hint的常见错误使用方式
- with as与hint materialize的使用
- oracle Hint 使用
- biee hint 使用
- Hint 使用心得
- 优化SQL HINT使用
- Oracle hint使用
- Delphi中使用Hint体验windows的温柔
- Android Bundle类
- 稀疏表达: 向量、矩阵与张量（上篇和中篇）
- 黑马程序员_gets()和scanf()
- 找工作经验之——面试（华为篇）
- 我与C++设计模式（七）——装饰者
- 592- 使用的Hint 无效-续
- PAT 2-08 用扑克牌计算24点（C语言实现）
- uva1306 - The K-League 网络流
- 找工作经验之——笔试
- hdu1074 状态压缩dp+记录方案
- 杭电oj An easy problem
- Json技术使用代码示例
- 《牛腩新闻发布系统》回顾（一）
- 解决Android主线程不能访问网络
