Hints 应该慎用,收集相关表的统计信息,根据执行计划,来改变查询方式
只能在SELECT, UPDATE, INSERT, MERGE, or DELETE 关键字后面,只有insert可以用2个hint(append跟parallel),其他只能only one—如果sql中使用了hint,则就意味着启用了CBO
1 /*+ gather_plan_statistics*/
用于在目标sql执行时收集一些额外的统计信息
select /*+ gather_plan_statistics*/ t1.empno,t2.deptno,t1.ename,t2.dname fromemp t1,dept t2where t1.deptno=t2.deptno;--查看该语句的详细执行计划
select * from table(dbms_xplan.display_cursor(null,null,‘allstats last‘));
SQL_ID bzymbk118976v, childnumber 0
-------------------------------------
select /*+ gather_plan_statistics*/ t1.empno,t2.deptno,t1.ename,t2.dname fromemp t1,dept
t2where t1.deptno=t2.deptnoPlan hash value: 351108634
---------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------
| 1 | NESTED LOOPS | | 1 | 14 | 14 |00:00:00.01 | 23 |
| 2 | TABLE ACCESS FULL | EMP | 1 | 14 | 14 |00:00:00.01 | 7 |
| 3 | TABLE ACCESS BY INDEX ROWID| DEPT | 14 | 1 | 14 |00:00:00.01 | 16 |
|* 4 | INDEX UNIQUE SCAN | PK_DEPT | 14 | 1 | 14 |00:00:00.01 | 2 |
---------------------------------------------------------------------
Predicate Information (identified byoperation id):---------------------------------------------------
4 - access("T1"."DEPTNO"="T2"."DEPTNO")
Starts—每一个具体执行步骤被重复执行的次数
E-Rows—cbo对每一个具体执行步骤的返回的cardinality的预估值
A-Rows—每一个步骤返回的实际值
A-Time—每一个步骤实际执行的时间
Buffers—每一个步骤的逻辑读
Reads—每一个步骤的物理读
SQL>show parameter optimizer_mode
NAME TYPE VALUE------------------------------------ ---------- ----------------
optimizer_mode string ALL_ROWSselect * from v$parameter where name=‘optimizer_mode‘SQL> alter session set optimizer_mode=‘RULE‘;
Oracle的hint可以直接影响优化器解析目标sql产生执行计划
Hint:可以影响目标sql是否能够被查询改写,merge,unnest,use_concat等
:优化器对执行路径的选择,full,index等
:对表连接方法的选择,use_hash,use_nl等
:影响优化器对于执行计划中执行步骤返回结果集(cardinality),dynamic_sampling,cardinal
SQL> select t1.empno,t2.deptno,t1.ename,t2.dname fromemp t1,dept2 t2 where t1.deptno=t2.deptno and t1.empno=7369;
ExecutionPlan
----------------------------------------------------------
Plan hash value: 2385808155
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 27 | 2 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 1 | 27 | 2 (0)| 00:00:01 |
|* 2 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 14 | 1 (0)| 00:00:01 |
|* 3 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 0 (0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| DEPT | 5 | 65 | 1 (0)| 00:00:01 |
|* 5 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified byoperation id):---------------------------------------------------
2 - filter("T1"."DEPTNO" IS NOT NULL)3 - access("T1"."EMPNO"=7369)5 - access("T1"."DEPTNO"="T2"."DEPTNO")
SQL> select/*+ full(t1)*/ t1.empno,t2.deptno,t1.ename,t2.dname fromemp t1,dept2 t2 where t1.deptno=t2.deptno and t1.empno=7369;
ExecutionPlan----t1表 全表扫描----------------------------------------------------------
Plan hash value: 351108634
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 27 | 4 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 1 | 27 | 4 (0)| 00:00:01 |
|* 2 | TABLE ACCESS FULL | EMP | 1 | 14 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| DEPT | 5 | 65 | 1 (0)| 00:00:01 |
|* 4 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)| 00:00:01 |
-------------------------------------------------------------------------------
Predicate Information (identified byoperation id):---------------------------------------------------
2 - filter("T1"."EMPNO"=7369 AND "T1"."DEPTNO" IS NOT NULL)4 - access("T1"."DEPTNO"="T2"."DEPTNO")
2 Hint的用法
/*+ xx xx*/第一个*跟+直接没有空格,多个hint直接用空格隔开
SQL> select /*+ full(emp)*/ * from emp where empno=7369;
ExecutionPlan
----------------------------------------------------------
Plan hash value: 3956160932
---------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 37 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMP | 1 | 37 | 3 (0)| 00:00:01 |SQL> select * /*+ full(emp)*/ from emp where empno=7369;
ExecutionPlan
----------------------------------------------------------
Plan hash value: 2949544139
---------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 37 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 37 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 0 (0)| 00:00:01 |
Hint中,sql文本中有别名,在hint就应该使用表的别名,
不能加schema名
3 hint被忽略的常见情形
1使用hint有语法或拼写错误
空格,加号等
select /*+ index(emp emp_idx)*/ * from emp where ename=‘hongquan1‘;
2使用的hint无效
--无效的hint
--对于非分区索引而言,索引范围扫描或者索引全扫描都不可以并行执行,hint就没意义
User_hash(t),哈希连接,里面目标表是哈希的被驱动表,被驱动表应该是数据量多的那个表
3使用的hint自相矛盾
4使用的hint受到了查询转换的干扰
5使用的hint受到了保留关键字的干扰commint ,is
3 常见的hint
3.1与优化器相关的hint
/*+ALL_ROWS*/10g默认的模式
表明对语句块选择基于开销的优化方法,并获得最佳吞吐量,使资源消耗最小化. ALL_ROWS 、FIRST_ROWS在收集统计信息后才能更准确
例如:
SELECT /*+ALL+_ROWS*/ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO=‘SCOTT‘;
/*+FIRST_ROWS*//*+FIRST_ROWS(1)*/
表明对语句块选择基于开销的优化方法,并获得最佳响应时间,使资源消耗
最小化. "ALL_ROWS Hint" for additional information on the FIRST_ROWS hint and statistics
SQL> select /*+ first_rows(2)*/ empno,ename,sal fromemp2 where deptno=10;
ExecutionPlan
----------------------------------------------------------
Plan hash value: 3956160932
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 36 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMP | 2 | 36 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------
Predicate Information (identified byoperation id):---------------------------------------------------
快速响应返回头2条记录
:集合运算(union all,minus)等
:group by
:for update
:聚合函数(sum)等
:distinct
:order by(对应的排序列上没有索引)
这里优化器会忽略first_rows(n),Oracle必须访问所有的行记录后才能返回满足条件的头n行记录
/*+ FIRST_ROWS(10) */
例如:
SELECT /*+FIRST_ROWS*/ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO=‘SCOTT‘;
/*+ CHOOSE*/
表明如果数据字典中有访问表的统计信息,将基于开销的优化方法,并获得最佳的吞吐量;
表明如果数据字典中没有访问表的统计信息,将基于规则开销的优化方法;
例如:
SELECT /*+CHOOSE*/ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO=‘SCOTT‘;
/*+ RULE*/
表明对语句块选择基于规则的优化方法.
例如:
SELECT /*+ RULE */ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO=‘SCOTT‘;
/*+ FULL(TABLE)*/
表明对表选择全局扫描的方法.
例如: /*+ FULL(e) */ from hr.employees e
SELECT /*+FULL(A)*/ EMP_NO,EMP_NAM FROM BSEMPMS A WHERE EMP_NO=‘SCOTT‘;
3.2与index相关的hint
1 Index–4种方式
Index(table pk_emp)
Index(table pk_emp idx_emp)
Index(table (empno) (mgr))
SQL> select /*+ index(c1 (empno))*/ c1.empno,c1.ename fromemp c1;15rows selected.
ExecutionPlan
----------------------------------------------------------
Plan hash value: 4170700152
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 15 | 165 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 15 | 165 | 3 (0)| 00:00:01 |
| 2 | INDEX FULL SCAN | PK_EMP | 15 | | 1 (0)| 00:00:01 |
Index(table)
2 no_index
No_index(emp)
3 index_desc
针对单个目标表的hint
优化器对目标表上的目标索引执行索引降序扫描操作,if 目标索引是asc,index_desc hint会使Oracle以降序的方式扫描该表,if目标索引是desc,会使asc来扫描该表。
SQL> select /*+ index_desc(emp pk_emp)*/ empno,ename,sal,job fromemp2 where empno=7369 and mgr=7902 and deptno=20;
ExecutionPlan
----------------------------------------------------------
Plan hash value: 2707196548
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 30 | 1 (0)| 00:00:01 |
|* 1 | TABLE ACCESS BY INDEX ROWID | EMP | 1 | 30 | 1 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN DESCENDING| PK_EMP | 1 | | 0 (0)| 00:00:01 |
---------------------------------------------------------------------
Index_des(emp pk_emp)
4 index_combine
针对目标单个目标表上的多个目标index执行位图布尔运算
5 index_ffs
针对单个目标表,执行索引快速全扫描(select所查询的所有列都在目标index中)
6 index_join
目标表上的多个目标index执行index join,select所有列都在目标表的index中,可以避免回表
3.3与表连接顺序相关的hint
1 ordered
在where条件中出现的顺序从左到右依次连接
2 leading
将leading中出现的表作为整个过程中的驱动表
select /*+ leading (c1) use_hash(c2) */ c1.empno,c1.ename,c2.loc from emp c1,dept c2
where c1.deptno=c2.deptno;
3.4与表连接方法有关的hint
1 use_merge—no_use_merge
所指定的表为排序合并连接中的被驱动表,通常与leading一起
2 use_nl—no_use_nl
嵌套连接中的被驱动表,通常与leading一起
select /*+ leading (c1) use_nl(c2) */ c1.empno,c1.ename,c2.loc from emp c1,dept c2
where c1.deptno=c2.deptno;
3 use_hash—no_use_hash
Hash连接中的被驱动表,通常与leading一起
select /*+ leading (c1) use_hash(c2) */ c1.empno,c1.ename,c2.loc from emp c1,dept c2
where c1.deptno=c2.deptno;
4 merge_aj,nl_aj,hash_aj—not in
排序合并反连接,嵌套合并反连接,哈希反连接
5 merge_sj,nl_sj,hash_sj—exists
排序合并半连接
3.5与查询转换相关的hint
1 Use_concat
是让sql使用in_list优化或者or扩展
select /*+ USE_CONCAT */ c1.empno,c1.ename from emp c1 where c1.empno in(7521,7566,7698)---CONCATEATION
2 NO_EXPAND
USE_CONCAT的反义hint
3 merge—no_merge
对目标视图执行视图合并
4 unnest—un_unnest
对sql 的子查询进行展开
5 expand_table—no_expand_table
让优化器不考虑成本的情况下进行sql的目标表进行扩展
3.6与并行相关的hint
1 parallel
Parallel
Parallel(auto)
Parallel(manual)
Parallel(table n/defult)
Show parameter parallel_min_services;
2 no_parallel
3 parallel_Index
对分区index进行并行扫描
No-parallel_Index
3.7其他常见的hint
1 driving_site
使用于dblink
2 append
让执行insert的语句绕开cache buffer,直接路径插入
3 append_values
11gr2开始的让带values字句的insert直接路径插入
4 push_pred—no_push_pred
对目标视图进行连接谓词推入—sql中处于该视图定义sql语句以外的谓词连接条件推入到该视图定义中去
5 push_subq—no_push_subq
Sql中不能做子查询展开的子查询
6 opt_param
修改优化器的参数
7 cardinality
设置对目标表执行扫描操作后返回结果集的cardinality的值
select /*+ cardinality(emp 200)*/ empno,ename fromemp| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 200 | 1600 | 3 (0)| 00:00:01
| 1 | TABLE ACCESS FULL| EMP | 200 | 1600 | 3 (0)| 00:00:01
8 swap_join_inputs
交换哈希连接的驱动表与被驱动表的顺序
1. /*+ROWID(TABLE)*/
提示明确表明对指定表根据ROWID进行访问.
例如:
SELECT /*+ROWID(BSEMPMS)*/ * FROM BSEMPMS WHERE ROWID>=‘AAAAAAAAAAAAAA‘
AND EMP_NO=‘SCOTT‘;
2. /*+CLUSTER(TABLE)*/
提示明确表明对指定表选择簇扫描的访问方法,它只对簇对象有效.
例如:
SELECT /*+CLUSTER */ BSEMPMS.EMP_NO,DPT_NO FROM BSEMPMS,BSDPTMS
WHERE DPT_NO=‘TEC304‘ AND BSEMPMS.DPT_NO=BSDPTMS.DPT_NO;
3. /*+INDEX(TABLE INDEX_NAME)*/
表明对表选择索引的扫描方法.
例如:
SELECT /*+INDEX(BSEMPMS SEX_INDEX) USE SEX_INDEX BECAUSE THERE ARE FEWMALE BSEMPMS */ FROM BSEMPMS WHERE SEX=‘M‘;
4. /*+INDEX_ASC(TABLE INDEX_NAME)*/
表明对表选择索引升序的扫描方法.
例如:
SELECT /*+INDEX_ASC(BSEMPMS PK_BSEMPMS) */ FROM BSEMPMS WHERE DPT_NO=‘SCOTT‘;
5. /*+INDEX_COMBINE*/
为指定表选择位图访问路经,如果INDEX_COMBINE中没有提供作为参数的索引,将选择出位图索引的布尔组合方式.
例如:
SELECT /*+INDEX_COMBINE(BSEMPMS SAL_BMI HIREDATE_BMI)*/ * FROM BSEMPMS
WHERE SAL<5000000 AND HIREDATE
6. /*+INDEX_JOIN(TABLE INDEX_NAME)*/
提示明确命令优化器使用索引作为访问路径. to use an index join as an access path
例如:
SELECT /* +INDEX_JOIN(BSEMPMS SAL_HMI HIREDATE_BMI)*/ SAL,HIREDATE
FROM BSEMPMS WHERE SAL<60000;
7. /*+ INDEX_DESC(TABLE INDEX_NAME)*/
表明对表选择索引降序的扫描方法.
例如:
SELECT /* +INDEX_DESC(BSEMPMS PK_BSEMPMS) */ FROM BSEMPMS WHERE DPT_NO=‘SCOTT‘;
8. /*+INDEX_FFS(TABLE INDEX_NAME)*/
对指定的表执行快速全索引扫描,而不是全表扫描的办法.
例如:
SELECT /*+INDEX_FFS(BSEMPMS IN_EMPNAM)*/ * FROM BSEMPMS WHERE DPT_NO=‘TEC305‘;
9. /*+ADD_EQUAL TABLE INDEX_NAM1,INDEX_NAM2,...*/
提示明确进行执行规划的选择,将几个单列索引的扫描合起来.
例如:
SELECT /*+INDEX_FFS(BSEMPMS IN_DPTNO,IN_EMPNO,IN_SEX)*/ * FROM BSEMPMS WHERE EMP_NO=‘SCOTT‘ AND DPT_NO=‘TDC306‘;
15. /*+USE_CONCAT*/
对查询中的WHERE后面的OR条件进行转换为UNION ALL的组合查询.
例如:
SELECT /*+USE_CONCAT*/ * FROM BSEMPMS WHERE DPT_NO=‘TDC506‘ AND SEX=‘M‘;
or条件中有一个为子查询的情况,无法index scan
使用use_concat也无效 修改成union可应用index
10. /*+NO_EXPAND*/
对于WHERE后面的OR 或者IN-LIST的查询语句,NO_EXPAND将阻止其基于优化器对其进行扩展.
例如:
SELECT /*+NO_EXPAND*/ * FROM BSEMPMS WHERE DPT_NO=‘TDC506‘ AND SEX=‘M‘;
11. /*+NOWRITE*/
禁止对查询块的查询重写操作.
12. /*+REWRITE*/
可以将视图作为参数.
13. /*+MERGE(TABLE)*/
能够对视图的各个查询进行相应的合并.
例如: The MERGE hint lets you merge views in a query
SELECT /*+MERGE(V) */ A.EMP_NO,A.EMP_NAM,B.DPT_NO FROM BSEMPMS A (SELET DPT_NO
,AVG(SAL) AS AVG_SAL FROM BSEMPMS B GROUP BY DPT_NO) V WHERE A.DPT_NO=V.DPT_NO
AND A.SAL>V.AVG_SAL;
14. /*+NO_MERGE(TABLE)*/
对于有可合并的视图不再合并.
例如:
SELECT /*+NO_MERGE(V) */ A.EMP_NO,A.EMP_NAM,B.DPT_NO FROM BSEMPMS A (SELECT DPT_NO,AVG(SAL) AS AVG_SAL FROM BSEMPMS B GROUP BY DPT_NO) V WHERE A.DPT_NO=V.DPT_NO AND A.SAL>V.AVG_SAL;
15. /*+ORDERED*/
根据表出现在FROM中的顺序,ORDERED使ORACLE依此顺序对其连接.
例如:
SELECT /*+ORDERED*/ A.COL1,B.COL2,C.COL3 FROM TABLE1 A,TABLE2 B,TABLE3 C WHERE A.COL1=B.COL1 AND B.COL1=C.COL1;
16. /*+USE_NL(TABLE)*/ 别名
将指定表与嵌套的连接的行源进行连接,并把指定表作为内部表.
例如: a nested loops join
explain plan for SELECT /*+USE_NL(s,d)*/ s.USER_ID ,D.ROWID AS RW FROM SUBSCRIBER_DETAIL D,SUBSCRIBER S WHERE D.USER_ID=S.USER_ID AND S.PROVIDER_ID=‘GLOBALROAM‘
SELECT /*+ORDERED USE_NL(BSEMPMS)*/ BSDPTMS.DPT_NO,BSEMPMS.EMP_NO,BSEMPMS.EMP_NAM FROM BSEMPMS,BSDPTMS WHERE BSEMPMS.DPT_NO=BSDPTMS.DPT_NO;
17. /*+USE_MERGE(TABLE)*/
将指定的表与其他行源通过合并排序连接方式连接起来.
例如: a sort-merge join
SELECT /*+USE_MERGE(e,d)*/ e.ename,e.job,d.deptno,d.dname FROM emp e,dept1 d WHERE e.deptno=d.deptno order by 1;
18. /*+USE_HASH(TABLE)*/ 别名 using a hash join
explain plan for select /*+ leading(t) use_hash(s)*/ t.number_id from bulk_numbers s,bulk_tab t where s.number_id=t.number_id t为外部表,s为内表SELECT /*+leading(d) USE_HASH(e,d)*/ e.ename,e.job,d.deptno,d.dname FROM emp e,dept1 d WHERE e.deptno=d.deptno order by 1;/*+ leading(CDR) use_hash(SUBSCRIBER_DETAIL)*/
/*+ leading(SUBSCRIBER_DETAIL) use_NL(CDR)*/
将指定的表与其他行源通过哈希连接方式连接起来.
例如:
SELECT /*+USE_HASH(BSEMPMS,BSDPTMS)*/ * FROM BSEMPMS,BSDPTMS WHERE BSEMPMS.DPT_NO=BSDPTMS.DPT_NO;
19. /*+DRIVING_SITE(TABLE)*/
强制与ORACLE所选择的位置不同的表进行查询执行.
例如:2个数据库之间让其中一个表作为驱动表
SELECT /*+DRIVING_SITE(DEPT)*/ * FROM BSEMPMS,[email protected] WHERE BSEMPMS.DPT_NO=DEPT.DPT_NO;
20. /*+LEADING(TABLE)*/
/*+ LEADING(e j) */ *
将指定的表作为连接次序中的首表.
21. /*+CACHE(TABLE)*/
当进行全表扫描时,CACHE提示能够将表的检索块放置在缓冲区缓存中最近最少列表LRU的最近使用端,适合于小表全扫描
例如:
SELECT /*+ FULL (hr_emp) CACHE(hr_emp) */ last_name
FROM employees hr_emp;
SELECT /*+FULL(BSEMPMS) CAHE(BSEMPMS) */ EMP_NAM FROM BSEMPMS;
22. /*+NOCACHE(TABLE)*/
当进行全表扫描时,CACHE提示能够将表的检索块放置在缓冲区缓存中最近最少列表LRU的最近使用端
例如:
SELECT /*+FULL(BSEMPMS) NOCAHE(BSEMPMS) */ EMP_NAM FROM BSEMPMS;
23. /*+APPEND*/
直接插入到表的最后,可以提高速度.
insert /*+append*/ into test1 select * from test4 ;
ALTER SESSION { ENABLE | FORCE } PARALLEL DML
Note:
If the database or tablespace is in FORCE LOGGING mode, then direct path INSERT always logs, regardless of the logging setting.
24. /*+NOAPPEND*/
通过在插入语句生存期内停止并行模式来启动常规插入.
insert /*+noappend*/ into test1 select * from test4 ;
/*+ HASH_AJ */
/*+ NL_AJ */
/*+ DRIVING_SITE(T1) USE_HASH(T1 T2) USE_HASH(T3)
25. NO_INDEX: 指定不使用哪些索引
/*+ NO_INDEX ( table [index [index]...] ) */
select /*+ no_index(emp ind_emp_sal ind_emp_deptno)*/ * from emp where deptno=200 and sal>300;select /*+ NO_INDEX ( emp1 EMP1_PK )*/* from emp1 where empno=‘7369‘
26. parallel
alter table t01 parallel 4;
/*+ PARALLEL
( [ @ qb_name ] tablespec [ integer | DEFAULT ] ) */ 加表名
select /*+ parallel(emp,4)*/ * from emp where deptno=200 and sal>300;SELECT /*+ FULL(hr_emp) PARALLEL(hr_emp, 5)*/last_nameFROMemployees hr_emp;SELECT /*+ FULL(hr_emp) PARALLEL(hr_emp, DEFAULT)*/ last_name FROM employees hr_emp
PARALLEL_INDEX hint instructs the optimizer to use the specified number of concurrent servers to parallelize index range scans for partitioned indexes.
SELECT /*+ PARALLEL_INDEX(table1, index1, 3) */
另:每个SELECT/INSERT/UPDATE/DELETE命令后只能有一个/*+ */,但提示内容可以有多个,可以用逗号分开,空格也可以。
如:/*+ ordered index() use_nl() */
---------
alter session enable dml parallel;
alter table t01 parallel 4;
insert /*+parallel(xxxx,4) */ into xxxx select /*+parallel(a) */ * from xxx a;