Oracle 12c 新SQL提示（hint）

Oracle 12c中引入了许多新特性，其中部分是和SQL相关的特性．而一些新的SQL提示也随着这些新特性被引入．

enable_parallel_dml
Syntax: enable_parallel_dml
Description: Enable parallel dml. Same effect as "alter session enable parallel dml"

SQL??

1. HelloDBA.com> create table t_obj as select * from dba_objects;  
2. HelloDBA.com> create table t_tab as select * from dba_tables;  
3. HelloDBA.com> exec sql_explain('delete /*+enable_parallel_dml parallel(o)*/from t_obj o where exists (select 1 from t_tab t where t.owner=o.owner and t.table_name = o.object_name)','TYPICAL');  
4. Plan hash value: 409221376  
5.   
6. ----------------------------------------------------------------------------------------------------------------------------  
7. | Id  | Operation                          | Name     | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |  
8. ----------------------------------------------------------------------------------------------------------------------------  
9. |   0 | DELETE STATEMENT                   |          |  3160 |   175K|    44   (0)| 00:00:01 |        |      |            |  
10. |   1 |  PX COORDINATOR                    |          |       |       |            |          |        |      |            |  
11. |   2 |   PX SEND QC (RANDOM)              | :TQ10003 |  3160 |   175K|    44   (0)| 00:00:01 |  Q1,03 | P->S | QC (RAND)  |  
12. |   3 |    DELETE                          | T_OBJ    |       |       |            |          |  Q1,03 | PCWP |            |  
13. |   4 |     PX RECEIVE                     |          |  3160 |   175K|    44   (0)| 00:00:01 |  Q1,03 | PCWP |            |  
14. |   5 |      PX SEND HASH (BLOCK ADDRESS)  | :TQ10002 |  3160 |   175K|    44   (0)| 00:00:01 |  Q1,02 | P->P | HASH (BLOCK|  
15. |*  6 |       HASH JOIN RIGHT SEMI BUFFERED|          |  3160 |   175K|    44   (0)| 00:00:01 |  Q1,02 | PCWP |            |  
16. |   7 |        PX RECEIVE                  |          |  2426 | 63076 |    29   (0)| 00:00:01 |  Q1,02 | PCWP |            |  
17. |   8 |         PX SEND HASH               | :TQ10000 |  2426 | 63076 |    29   (0)| 00:00:01 |  Q1,00 | S->P | HASH       |  
18. |   9 |          PX SELECTOR               |          |       |       |            |          |  Q1,00 | SCWC |            |  
19. |  10 |           TABLE ACCESS FULL        | T_TAB    |  2426 | 63076 |    29   (0)| 00:00:01 |  Q1,00 | SCWP |            |  
20. |  11 |        PX RECEIVE                  |          | 92299 |  2794K|    15   (0)| 00:00:01 |  Q1,02 | PCWP |            |  
21. |  12 |         PX SEND HASH               | :TQ10001 | 92299 |  2794K|    15   (0)| 00:00:01 |  Q1,01 | P->P | HASH       |  
22. |  13 |          PX BLOCK ITERATOR         |          | 92299 |  2794K|    15   (0)| 00:00:01 |  Q1,01 | PCWC |            |  
23. |  14 |           TABLE ACCESS FULL        | T_OBJ    | 92299 |  2794K|    15   (0)| 00:00:01 |  Q1,01 | PCWP |            |  
24. ----------------------------------------------------------------------------------------------------------------------------  

disable_parallel_dml
Syntax: disable_parallel_dml
Description: Disable parallel dml. Same effect as "alter session disable parallel dml"

SQL??

1. HelloDBA.com> alter session enable parallel dml;  
2. HelloDBA.com> exec sql_explain('delete /*+disable_parallel_dml parallel(o)*/from t_obj o where exists (select 1 from t_tab t where t.owner=o.owner and t.table_name = o.object_name)','TYPICAL');  
3. Plan hash value: 1357806520  
4.   
5. ------------------------------------------------------------------------------------------------------------------  
6. | Id  | Operation                | Name     | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |  
7. ------------------------------------------------------------------------------------------------------------------  
8. |   0 | DELETE STATEMENT         |          |  3160 |   175K|   460   (1)| 00:00:01 |        |      |            |  
9. |   1 |  DELETE                  | T_OBJ    |       |       |            |          |        |      |            |  
10. |   2 |   PX COORDINATOR         |          |       |       |            |          |        |      |            |  
11. |   3 |    PX SEND QC (RANDOM)   | :TQ10001 |  3160 |   175K|   460   (1)| 00:00:01 |  Q1,01 | P->S | QC (RAND)  |  
12. |*  4 |     HASH JOIN RIGHT SEMI |          |  3160 |   175K|   460   (1)| 00:00:01 |  Q1,01 | PCWP |            |  
13. |   5 |      PX RECEIVE          |          |  2426 | 63076 |    29   (0)| 00:00:01 |  Q1,01 | PCWP |            |  
14. |   6 |       PX SEND BROADCAST  | :TQ10000 |  2426 | 63076 |    29   (0)| 00:00:01 |  Q1,00 | S->P | BROADCAST  |  
15. |   7 |        PX SELECTOR       |          |       |       |            |          |  Q1,00 | SCWC |            |  
16. |   8 |         TABLE ACCESS FULL| T_TAB    |  2426 | 63076 |    29   (0)| 00:00:01 |  Q1,00 | SCWP |            |  
17. |   9 |      PX BLOCK ITERATOR   |          | 92299 |  2794K|   430   (0)| 00:00:01 |  Q1,01 | PCWC |            |  
18. |  10 |       TABLE ACCESS FULL  | T_OBJ    | 92299 |  2794K|   430   (0)| 00:00:01 |  Q1,01 | PCWP |            |  
19. ------------------------------------------------------------------------------------------------------------------  

USE_CUBE
Sytntax: USE_CUBE ( [ @ queryblock ] tablespec [ tablespec ]... )
Description:When the right-hand side of the join is a cube, the USE_CUBE hint instructs the optimizer to join each specified table with another row source using a cube join. If the optimizer decides not to use the cube join based on statistical analysis, then you can use USE_CUBE to override that decision.
Note: Sample schema comes from oracle 11.2 BI sample codes.

SQL??

1. HelloDBA.com> create view cube_view as select * from table(cube_table('GLOBAL.UNITS_CUBE'));  
2. HelloDBA.com> create view time_dim_view as select * from global.time_dim;  
3. HelloDBA.com> create view channel_dim_view as select * from global.channel_dim;  
4. HelloDBA.com> create view customer_dim_view as select * from global.customer_dim;  
5. HelloDBA.com> create view product_dim_view as select * from global.product_dim;  
6. HelloDBA.com> alter session set "_optimizer_cube_join_enabled"=false;  
7. HelloDBA.com> exec sql_explain('select /*+use_cube(t cn cm cb)*/* from cube_view cb, time_dim_view t, channel_dim_view cn, customer_dim_view cm where cb.time=t.month_id and cb.channel=cn.channel_id and cb.customer=cm.account_id','typical');  
8. Plan hash value: 1674841133  
9.   
10. ------------------------------------------------------------------------------------------  
11. | Id  | Operation                 | Name         | Rows  | Bytes | Cost (%CPU)| Time     |  
12. ------------------------------------------------------------------------------------------  
13. |   0 | SELECT STATEMENT          |              |   316K|   401M|    19  (48)| 00:00:01 |  
14. |*  1 |  HASH JOIN                |              |   316K|   401M|    19  (48)| 00:00:01 |  
15. |   2 |   TABLE ACCESS FULL       | TIME_DIM     |   120 | 28680 |     3   (0)| 00:00:01 |  
16. |*  3 |   CUBE JOIN               |              |   585K|   609M|    14  (50)| 00:00:01 |  
17. |   4 |    MERGE JOIN CARTESIAN   |              |   183 | 71919 |     6   (0)| 00:00:01 |  
18. |   5 |     TABLE ACCESS FULL     | CHANNEL_DIM  |     3 |   237 |     2   (0)| 00:00:01 |  
19. |   6 |     BUFFER SORT           |              |    61 | 19154 |     4   (0)| 00:00:01 |  
20. |   7 |      TABLE ACCESS FULL    | CUSTOMER_DIM |    61 | 19154 |     1   (0)| 00:00:01 |  
21. |   8 |    CUBE SCAN PARTIAL OUTER| UNITS_CUBE   |  1342K|   896M|     5  (80)| 00:00:01 |  
22. ------------------------------------------------------------------------------------------  

NO_USE_CUBE
Syntax:NO_USE_CUBE ( [ @ queryblock ] tablespec [ tablespec ]... ) 
Description: The NO_USE_CUBE hint instructs the optimizer to exclude cube joins when joining each specified table to another row source using the specified table as the inner table.
Note: Sample schema comes from oracle 11.2 BI sample codes.

SQL??

1. HelloDBA.com> alter session set "_optimizer_cube_join_enabled"=true;  
2. HelloDBA.com> exec sql_explain('select /*+no_use_cube(cb)*/* from cube_view cb, time_dim_view t, channel_dim_view cn, customer_dim_view cm where cb.time=t.month_id and cb.channel=cn.channel_id and cb.customer=cm.account_id','typical');  
3. Plan hash value: 2364382396  
4.   
5. ------------------------------------------------------------------------------------------  
6. | Id  | Operation                 | Name         | Rows  | Bytes | Cost (%CPU)| Time     |  
7. ------------------------------------------------------------------------------------------  
8. |   0 | SELECT STATEMENT          |              |   316K|   401M|    21  (48)| 00:00:01 |  
9. |*  1 |  HASH JOIN                |              |   316K|   401M|    21  (48)| 00:00:01 |  
10. |   2 |   TABLE ACCESS FULL       | TIME_DIM     |   120 | 28680 |     3   (0)| 00:00:01 |  
11. |*  3 |   HASH JOIN               |              |   585K|   609M|    16  (50)| 00:00:01 |  
12. |   4 |    MERGE JOIN CARTESIAN   |              |   183 | 71919 |     6   (0)| 00:00:01 |  
13. |   5 |     TABLE ACCESS FULL     | CHANNEL_DIM  |     3 |   237 |     2   (0)| 00:00:01 |  
14. |   6 |     BUFFER SORT           |              |    61 | 19154 |     4   (0)| 00:00:01 |  
15. |   7 |      TABLE ACCESS FULL    | CUSTOMER_DIM |    61 | 19154 |     1   (0)| 00:00:01 |  
16. |   8 |    CUBE SCAN PARTIAL OUTER| UNITS_CUBE   |  1342K|   896M|     7  (72)| 00:00:01 |  
17. ------------------------------------------------------------------------------------------  

CUBE_AJ
Syntax:CUBE_AJ ( [ @ queryblock ] tablespec [ tablespec ]... ) 
Description: The CUBE_AJ hint instructs the optimizer to clude cube anti joins when joining each specified table to another row source.
Note: Sample schema comes from oracle 11.2 BI sample codes.

SQL??

1. HelloDBA.com> alter session set "_optimizer_cube_join_enabled"=false;  
2. HelloDBA.com> exec sql_explain('select * from product_dim_view pd where not exists (select /*+cube_aj*/1 from cube_view cb where cb.product=pd.item_id)','typical');  
3. Plan hash value: 2520340146  
4.   
5. ----------------------------------------------------------------------------------------  
6. | Id  | Operation                | Name        | Rows  | Bytes | Cost (%CPU)| Time     |  
7. ----------------------------------------------------------------------------------------  
8. |   0 | SELECT STATEMENT         |             |    36 |  8208 |    11  (64)| 00:00:01 |  
9. |*  1 |  CUBE JOIN ANTI          |             |    36 |  8208 |    11  (64)| 00:00:01 |  
10. |   2 |   TABLE ACCESS FULL      | PRODUCT_DIM |    36 |  7488 |     2   (0)| 00:00:01 |  
11. |   3 |   CUBE SCAN PARTIAL OUTER| UNITS_CUBE  |  1342K|    25M|     5  (60)| 00:00:01 |  
12. ----------------------------------------------------------------------------------------  

CUBE_SJ
Syntax:CUBE_SJ ( [ @ queryblock ] tablespec [ tablespec ]... ) 
Description: The CUBE_SJ hint instructs the optimizer to clude cube semi joins when joining each specified table to another row source.
Note: Sample schema comes from oracle 11.2 BI sample codes.

SQL??

1. HelloDBA.com> alter session set "_optimizer_cube_join_enabled"=false;  
2. HelloDBA.com> exec sql_explain('select * from product_dim_view pd where exists (select /*+cube_sj*/1 from cube_view cb where cb.product=pd.item_id)','typical');  
3. Plan hash value: 2914263671  
4.   
5. ----------------------------------------------------------------------------------------  
6. | Id  | Operation                | Name        | Rows  | Bytes | Cost (%CPU)| Time     |  
7. ----------------------------------------------------------------------------------------  
8. |   0 | SELECT STATEMENT         |             |    36 |  8604 |    13  (62)| 00:00:01 |  
9. |*  1 |  HASH JOIN SEMI          |             |    36 |  8604 |    13  (62)| 00:00:01 |  
10. |   2 |   TABLE ACCESS FULL      | PRODUCT_DIM |    36 |  7884 |     3   (0)| 00:00:01 |  
11. |   3 |   CUBE SCAN PARTIAL OUTER| UNITS_CUBE  |  1342K|    25M|     7  (72)| 00:00:01 |  
12. ----------------------------------------------------------------------------------------  

AUTO_REOPTIMIZE
Syntax:AUTO_REOPTIMIZE
Description:The AUTO_REOPTIMIZE hint instructs the optimizer to automatically change a plan on subsequent executions of a SQL statement. 
Note: The "automatic reoptimization" is the ability of the optimizer to automatically change a plan on subsequent executions of a SQL statement. Automatic reoptimization can fix any suboptimal plan chosen due to incorrect optimizer estimates, from a suboptimal distribution method to an incorrect choice of degree of parallelism.

SQL??

1. HelloDBA.com> create table t_user as select * from dba_users;  
2. HelloDBA.com> create table t_ind as select * from dba_indexes;  
3. HelloDBA.com> create index t_tab_idx1 on t_tab(owner, table_name);  
4. HelloDBA.com> create table t_obj1 as select * from t_obj;  
5. HelloDBA.com> create index t_obj1_idx1 on t_obj1(owner);  
6. HelloDBA.com> create index t_obj1_idx2 on t_obj1(owner, object_name);  
7. HelloDBA.com> exec dbms_stats.set_table_stats(user,'T_OBJ1', numrows=>1);  
8. HelloDBA.com> alter session set "_optimizer_use_feedback"=false;  
9. HelloDBA.com> alter system flush shared_pool;  
10. -- First running  
11. HelloDBA.com> exec sql_explain('select /*+AUTO_REOPTIMIZE*/* from t_tab t, t_obj1 o where o.owner=t.owner and o.object_name=t.table_name and exists (select 1 from t_ind i, t_user u where o.owner=i.owner and o.object_name = i.index_name and o.owner=u.username and u.default_tablespace=''SYSAUX'')', 'typical', false);  
12. SQL_ID  5a4p65yb97cs6, child number 0  
13. -------------------------------------  
14. select /*+AUTO_REOPTIMIZE*/* from t_tab t, t_obj1 o where  
15. o.owner=t.owner and o.object_name=t.table_name and exists (select 1  
16. from t_ind i, t_user u where o.owner=i.owner and o.object_name =  
17. i.index_name and o.owner=u.username and u.default_tablespace='SYSAUX')  
18.   
19. Plan hash value: 2082622820  
20.   
21. --------------------------------------------------------------------------------------------  
22. | Id  | Operation                     | Name       | Rows  | Bytes | Cost (%CPU)| Time     |  
23. --------------------------------------------------------------------------------------------  
24. |   0 | SELECT STATEMENT              |            |       |       |   484 (100)|          |  
25. |*  1 |  FILTER                       |            |       |       |            |          |  
26. |   2 |   NESTED LOOPS                |            |     1 |   374 |   432   (0)| 00:00:01 |  
27. |   3 |    NESTED LOOPS               |            |     1 |   374 |   432   (0)| 00:00:01 |  
28. |   4 |     TABLE ACCESS FULL         | T_OBJ1     |     1 |   115 |   430   (0)| 00:00:01 |  
29. |*  5 |     INDEX RANGE SCAN          | T_TAB_IDX1 |     1 |       |     1   (0)| 00:00:01 |  
30. |   6 |    TABLE ACCESS BY INDEX ROWID| T_TAB      |     1 |   259 |     2   (0)| 00:00:01 |  
31. |   7 |   MERGE JOIN CARTESIAN        |            |     1 |    47 |    52   (0)| 00:00:01 |  
32. |*  8 |    TABLE ACCESS FULL          | T_IND      |     1 |    30 |    49   (0)| 00:00:01 |  
33. |   9 |    BUFFER SORT                |            |     1 |    17 |     3   (0)| 00:00:01 |  
34. |* 10 |     TABLE ACCESS FULL         | T_USER     |     1 |    17 |     3   (0)| 00:00:01 |  
35. --------------------------------------------------------------------------------------------  
36.   
37. Note  
38. -----  
39.    - this is an adaptive plan  
40.   
41. -- Second running  
42. HelloDBA.com> exec sql_explain('select /*+AUTO_REOPTIMIZE*/* from t_tab t, t_obj1 o where o.owner=t.owner and o.object_name=t.table_name and exists (select 1 from t_ind i, t_user u where o.owner=i.owner and o.object_name = i.index_name and o.owner=u.username and u.default_tablespace=''SYSAUX'')', 'typical', false);  
43. SQL_ID  5a4p65yb97cs6, child number 1  
44. -------------------------------------  
45. select /*+AUTO_REOPTIMIZE*/* from t_tab t, t_obj1 o where  
46. o.owner=t.owner and o.object_name=t.table_name and exists (select 1  
47. from t_ind i, t_user u where o.owner=i.owner and o.object_name =  
48. i.index_name and o.owner=u.username and u.default_tablespace='SYSAUX')  
49.   
50. Plan hash value: 1348485435  
51.   
52. --------------------------------------------------------------------------------------  
53. | Id  | Operation                | Name      | Rows  | Bytes | Cost (%CPU)| Time     |  
54. --------------------------------------------------------------------------------------  
55. |   0 | SELECT STATEMENT         |           |       |       |   514 (100)|          |  
56. |   1 |  VIEW                    | VM_NWVW_2 |   300 |   362K|   514   (1)| 00:00:01 |  
57. |   2 |   HASH UNIQUE            |           |   300 |   123K|   514   (1)| 00:00:01 |  
58. |*  3 |    HASH JOIN             |           |   300 |   123K|   513   (1)| 00:00:01 |  
59. |*  4 |     HASH JOIN RIGHT SEMI |           |   300 | 48600 |   483   (1)| 00:00:01 |  
60. |*  5 |      TABLE ACCESS FULL   | T_USER    |    10 |   170 |     3   (0)| 00:00:01 |  
61. |*  6 |      HASH JOIN RIGHT SEMI|           |   308 | 44660 |   480   (1)| 00:00:01 |  
62. |   7 |       TABLE ACCESS FULL  | T_IND     |  4713 |   138K|    49   (0)| 00:00:01 |  
63. |   8 |       TABLE ACCESS FULL  | T_OBJ1    | 92299 |    10M|   431   (1)| 00:00:01 |  
64. |   9 |     TABLE ACCESS FULL    | T_TAB     |  2426 |   613K|    29   (0)| 00:00:01 |  
65. --------------------------------------------------------------------------------------  
66.   
67. Predicate Information (identified by operation id):  
68. ---------------------------------------------------  
69.   
70.    3 - access("O"."OWNER"="T"."OWNER" AND "O"."OBJECT_NAME"="T"."TABLE_NAME")  
71.    4 - access("O"."OWNER"="U"."USERNAME")  
72.    5 - filter("U"."DEFAULT_TABLESPACE"='SYSAUX')  
73.    6 - access("O"."OWNER"="I"."OWNER" AND "O"."OBJECT_NAME"="I"."INDEX_NAME")  
74.   
75. Note  
76. -----  
77.    - statistics feedback used for this statement  

NO_AUTO_REOPTIMIZE
Syntax:AUTO_REOPTIMIZE
Description:The AUTO_REOPTIMIZE hint instructs the optimizer not to automatically change a plan on subsequent executions of a SQL statement.

SQL??

1. HelloDBA.com> alter session set "_optimizer_use_feedback"=true;  
2. HelloDBA.com> alter system flush shared_pool;  
3. HelloDBA.com> exec sql_explain('select /*+NO_AUTO_REOPTIMIZE*/* from t_tab t, t_obj1 o where o.owner=t.owner and o.object_name=t.table_name and exists (select 1 from t_ind i, t_user u where o.owner=i.owner and o.object_name = i.index_name and o.owner=u.username and u.default_tablespace=''SYSAUX'')', 'typical', false);  
4. SQL_ID  0g6swgrm8tt0s, child number 0  
5. -------------------------------------  
6. select /*+NO_AUTO_REOPTIMIZE*/* from t_tab t, t_obj1 o where  
7. o.owner=t.owner and o.object_name=t.table_name and exists (select 1  
8. from t_ind i, t_user u where o.owner=i.owner and o.object_name =  
9. i.index_name and o.owner=u.username and u.default_tablespace='SYSAUX')  
10.   
11. Plan hash value: 2082622820  
12.   
13. --------------------------------------------------------------------------------------------  
14. | Id  | Operation                     | Name       | Rows  | Bytes | Cost (%CPU)| Time     |  
15. --------------------------------------------------------------------------------------------  
16. |   0 | SELECT STATEMENT              |            |       |       |   484 (100)|          |  
17. |*  1 |  FILTER                       |            |       |       |            |          |  
18. |   2 |   NESTED LOOPS                |            |     1 |   374 |   432   (0)| 00:00:01 |  
19. |   3 |    NESTED LOOPS               |            |     1 |   374 |   432   (0)| 00:00:01 |  
20. |   4 |     TABLE ACCESS FULL         | T_OBJ1     |     1 |   115 |   430   (0)| 00:00:01 |  
21. |*  5 |     INDEX RANGE SCAN          | T_TAB_IDX1 |     1 |       |     1   (0)| 00:00:01 |  
22. |   6 |    TABLE ACCESS BY INDEX ROWID| T_TAB      |     1 |   259 |     2   (0)| 00:00:01 |  
23. |   7 |   MERGE JOIN CARTESIAN        |            |     1 |    47 |    52   (0)| 00:00:01 |  
24. |*  8 |    TABLE ACCESS FULL          | T_IND      |     1 |    30 |    49   (0)| 00:00:01 |  
25. |   9 |    BUFFER SORT                |            |     1 |    17 |     3   (0)| 00:00:01 |  
26. |* 10 |     TABLE ACCESS FULL         | T_USER     |     1 |    17 |     3   (0)| 00:00:01 |  
27. --------------------------------------------------------------------------------------------  
28.   
29. Predicate Information (identified by operation id):  
30. ---------------------------------------------------  
31.   
32.    1 - filter( IS NOT NULL)  
33.    5 - access("O"."OWNER"="T"."OWNER" AND "O"."OBJECT_NAME"="T"."TABLE_NAME")  
34.    8 - filter(("I"."INDEX_NAME"=:B1 AND "I"."OWNER"=:B2))  
35.   10 - filter(("U"."USERNAME"=:B1 AND "U"."DEFAULT_TABLESPACE"='SYSAUX'))  
36.   
37. Note  
38. -----  
39.    - this is an adaptive plan  
40.   
41. HelloDBA.com> exec sql_explain('select /*+NO_AUTO_REOPTIMIZE*/* from t_tab t, t_obj1 o where o.owner=t.owner and o.object_name=t.table_name and exists (select 1 from t_ind i, t_user u where o.owner=i.owner and o.object_name = i.index_name and o.owner=u.username and u.default_tablespace=''SYSAUX'')', 'typical', false);  
42. SQL_ID  0g6swgrm8tt0s, child number 0  
43. -------------------------------------  
44. select /*+NO_AUTO_REOPTIMIZE*/* from t_tab t, t_obj1 o where  
45. o.owner=t.owner and o.object_name=t.table_name and exists (select 1  
46. from t_ind i, t_user u where o.owner=i.owner and o.object_name =  
47. i.index_name and o.owner=u.username and u.default_tablespace='SYSAUX')  
48.   
49. Plan hash value: 2082622820  
50.   
51. --------------------------------------------------------------------------------------------  
52. | Id  | Operation                     | Name       | Rows  | Bytes | Cost (%CPU)| Time     |  
53. --------------------------------------------------------------------------------------------  
54. |   0 | SELECT STATEMENT              |            |       |       |   484 (100)|          |  
55. |*  1 |  FILTER                       |            |       |       |            |          |  
56. |   2 |   NESTED LOOPS                |            |     1 |   374 |   432   (0)| 00:00:01 |  
57. |   3 |    NESTED LOOPS               |            |     1 |   374 |   432   (0)| 00:00:01 |  
58. |   4 |     TABLE ACCESS FULL         | T_OBJ1     |     1 |   115 |   430   (0)| 00:00:01 |  
59. |*  5 |     INDEX RANGE SCAN          | T_TAB_IDX1 |     1 |       |     1   (0)| 00:00:01 |  
60. |   6 |    TABLE ACCESS BY INDEX ROWID| T_TAB      |     1 |   259 |     2   (0)| 00:00:01 |  
61. |   7 |   MERGE JOIN CARTESIAN        |            |     1 |    47 |    52   (0)| 00:00:01 |  
62. |*  8 |    TABLE ACCESS FULL          | T_IND      |     1 |    30 |    49   (0)| 00:00:01 |  
63. |   9 |    BUFFER SORT                |            |     1 |    17 |     3   (0)| 00:00:01 |  
64. |* 10 |     TABLE ACCESS FULL         | T_USER     |     1 |    17 |     3   (0)| 00:00:01 |  
65. --------------------------------------------------------------------------------------------  
66.   
67. Predicate Information (identified by operation id):  
68. ---------------------------------------------------  
69.   
70.    1 - filter( IS NOT NULL)  
71.    5 - access("O"."OWNER"="T"."OWNER" AND "O"."OBJECT_NAME"="T"."TABLE_NAME")  
72.    8 - filter(("I"."INDEX_NAME"=:B1 AND "I"."OWNER"=:B2))  
73.   10 - filter(("U"."USERNAME"=:B1 AND "U"."DEFAULT_TABLESPACE"='SYSAUX'))  
74.   
75. Note  
76. -----  
77.    - this is an adaptive plan  

ADAPTIVE_PLAN
Syntax:ADAPTIVE_PLAN
Description:The ADAPTIVE_PLAN hint instructs the optimizer to automatically change a plan on subsequent executions of a SQL statement.

SQL??

1. HelloDBA.com> alter session set "_optimizer_adaptive_plans"=false;  
2. HelloDBA.com> exec sql_explain('select /*+ADAPTIVE_PLAN*/* from t_tab t where table_name = any (select max(object_name) from t_obj o where o.owner=t.owner group by owner)', 'TYPICAL');  
3. Plan hash value: 3428189515  
4.   
5. --------------------------------------------------------------------------------------  
6. | Id  | Operation               | Name       | Rows  | Bytes | Cost (%CPU)| Time     |  
7. --------------------------------------------------------------------------------------  
8. |   0 | SELECT STATEMENT        |            |     1 |   337 |   468   (1)| 00:00:01 |  
9. |*  1 |  HASH JOIN SEMI         |            |     1 |   337 |   468   (1)| 00:00:01 |  
10. |   2 |   TABLE ACCESS FULL     | T_TAB      |  2426 |   613K|    29   (0)| 00:00:01 |  
11. |   3 |   VIEW                  | VW_SQ_1    | 15577 |  1186K|   438   (1)| 00:00:01 |  
12. |*  4 |    HASH JOIN            |            | 15577 |  2296K|   438   (1)| 00:00:01 |  
13. |   5 |     VIEW                | VW_GBD_4   |    26 |  3432 |   433   (1)| 00:00:01 |  
14. |   6 |      HASH GROUP BY      |            |    26 |   806 |   433   (1)| 00:00:01 |  
15. |   7 |       TABLE ACCESS FULL | T_OBJ      | 92299 |  2794K|   431   (1)| 00:00:01 |  
16. |   8 |     INDEX FAST FULL SCAN| T_TAB_IDX1 |  2426 | 46094 |     5   (0)| 00:00:01 |  
17. --------------------------------------------------------------------------------------  
18.   
19. Predicate Information (identified by operation id):  
20. ---------------------------------------------------  
21.   
22.    1 - access("TABLE_NAME"="MAX(OBJECT_NAME)" AND "ITEM_1"=ROWID)  
23.    4 - access("ITEM_1"="T"."OWNER")  
24.   
25. Note  
26. -----  
27.    - this is an adaptive plan  

NO_ADAPTIVE_PLAN
Syntax:NO_ADAPTIVE_PLAN
Description:The NO_ADAPTIVE_PLAN hint instructs the optimizer to automatically change a plan on subsequent executions of a SQL statement.

SQL??

1. HelloDBA.com> alter session set "_optimizer_adaptive_plans"=true;  
2. HelloDBA.com> exec sql_explain('select /*+NO_ADAPTIVE_PLAN*/* from t_tab t where table_name = any (select max(object_name) from t_obj o where o.owner=t.owner group by owner)', 'TYPICAL');  
3. Plan hash value: 3428189515  
4.   
5. --------------------------------------------------------------------------------------  
6. | Id  | Operation               | Name       | Rows  | Bytes | Cost (%CPU)| Time     |  
7. --------------------------------------------------------------------------------------  
8. |   0 | SELECT STATEMENT        |            |     1 |   337 |   468   (1)| 00:00:01 |  
9. |*  1 |  HASH JOIN SEMI         |            |     1 |   337 |   468   (1)| 00:00:01 |  
10. |   2 |   TABLE ACCESS FULL     | T_TAB      |  2426 |   613K|    29   (0)| 00:00:01 |  
11. |   3 |   VIEW                  | VW_SQ_1    | 15577 |  1186K|   438   (1)| 00:00:01 |  
12. |*  4 |    HASH JOIN            |            | 15577 |  2296K|   438   (1)| 00:00:01 |  
13. |   5 |     VIEW                | VW_GBD_4   |    26 |  3432 |   433   (1)| 00:00:01 |  
14. |   6 |      HASH GROUP BY      |            |    26 |   806 |   433   (1)| 00:00:01 |  
15. |   7 |       TABLE ACCESS FULL | T_OBJ      | 92299 |  2794K|   431   (1)| 00:00:01 |  
16. |   8 |     INDEX FAST FULL SCAN| T_TAB_IDX1 |  2426 | 46094 |     5   (0)| 00:00:01 |  
17. --------------------------------------------------------------------------------------  

BATCH_TABLE_ACCESS_BY_ROWID
Syntax:BATCH_TABLE_ACCESS_BY_ROWID ( [ @ queryblock ] tablespec [ tablespec ]... ) 
Description: The BATCH_TABLE_ACCESS_BY_ROWID hint instructs the optimizer to retrieve a few rowids from the index, then access the rows in data block order, to reduce accessing data block times .

SQL??

1. HelloDBA.com> alter session set "_optimizer_batch_table_access_by_rowid"=false;  
2. HelloDBA.com> exec sql_explain('select /*+BATCH_TABLE_ACCESS_BY_ROWID(o)*/* from t_obj1 o where owner=''SYS''', 'typical');  
3. Plan hash value: 323892535  
4.   
5. ---------------------------------------------------------------------------------------------------  
6. | Id  | Operation                           | Name        | Rows  | Bytes | Cost (%CPU)| Time     |  
7. ---------------------------------------------------------------------------------------------------  
8. |   0 | SELECT STATEMENT                    |             |     1 |   115 |   120   (0)| 00:00:01 |  
9. |   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| T_OBJ1      |     1 |   115 |   120   (0)| 00:00:01 |  
10. |*  2 |   INDEX RANGE SCAN                  | T_OBJ1_IDX1 |  3550 |       |     9   (0)| 00:00:01 |  
11. ---------------------------------------------------------------------------------------------------  

NO_BATCH_TABLE_ACCESS_BY_ROWID
Syntax:BATCH_TABLE_ACCESS_BY_ROWID ( [ @ queryblock ] tablespec [ tablespec ]... ) 
Description: The BATCH_TABLE_ACCESS_BY_ROWID hint instructs the optimizer not use batch table access by rowid.

SQL??

1. HelloDBA.com> alter session set "_optimizer_batch_table_access_by_rowid"=true;  
2. HelloDBA.com> exec sql_explain('select /*+NO_BATCH_TABLE_ACCESS_BY_ROWID(o)*/* from t_obj1 o where owner=''SYS''', 'typical');  
3. Plan hash value: 1936150373  
4.   
5. ----------------------------------------------------------------------------  
6. | Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |  
7. ----------------------------------------------------------------------------  
8. |   0 | SELECT STATEMENT  |        | 42109 |  4729K|   431   (1)| 00:00:01 |  
9. |*  1 |  TABLE ACCESS FULL| T_OBJ1 | 42109 |  4729K|   431   (1)| 00:00:01 |  
10. ----------------------------------------------------------------------------  

CLUSTERING
Syntax:CLUSTERING ( [ @ queryblock ] )
Description: This hint is valid only for INSERT and MERGE operations on tables that are enabled for attribute clustering. The CLUSTERING hint enables attribute clustering for direct-path inserts (serial or parallel). This results in partially-clustered data, that is, data that is clustered per each insert or merge operation. This hint overrides a NO ON LOAD setting in the DDL that created or altered the table. This hint has no effect on tables that are not enabled for attribute clustering.

SQL??

1. HelloDBA.com> alter table t_obj1 add clustering by interleaved order (owner) no on load no on data movement;  
2. HelloDBA.com> exec sql_explain('insert /*+APPEND CLUSTERING*/ into t_obj1 select * from t_obj t', 'TYPICAL');  
3. Plan hash value: 761959232  
4.   
5. ---------------------------------------------------------------------------------------------------  
6. | Id  | Operation                        | Name   | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |  
7. ---------------------------------------------------------------------------------------------------  
8. |   0 | INSERT STATEMENT                 |        | 92299 |    10M|       |  2824   (1)| 00:00:01 |  
9. |   1 |  LOAD AS SELECT                  | T_OBJ1 |       |       |       |            |          |  
10. |   2 |   OPTIMIZER STATISTICS GATHERING |        | 92299 |    10M|       |  2824   (1)| 00:00:01 |  
11. |   3 |    SORT ORDER BY                 |        | 92299 |    10M|    13M|  2824   (1)| 00:00:01 |  
12. |   4 |     TABLE ACCESS FULL            | T_OBJ  | 92299 |    10M|       |   431   (1)| 00:00:01 |  
13. ---------------------------------------------------------------------------------------------------  

NO_CLUSTERING
Syntax:NO_CLUSTERING ( [ @ queryblock ] )
Description: This hint is valid only for INSERT and MERGE operations on tables that are enabled for attribute clustering. The NO_CLUSTERING hint disables attribute clustering for direct-path inserts (serial or parallel). This hint overrides a YES ON LOAD setting in the DDL that created or altered the table. This hint has no effect on tables that are not enabled for attribute clustering.

SQL??

1. HelloDBA.com> alter table t_obj1 drop clustering;  
2. HelloDBA.com> alter table t_obj1 add clustering by interleaved order (owner) yes on load yes on data movement;  
3. HelloDBA.com> exec sql_explain('insert /*+APPEND NO_CLUSTERING*/ into t_obj1 select * from t_obj t', 'TYPICAL');  
4. Plan hash value: 1252610668  
5.   
6. -------------------------------------------------------------------------------------------  
7. | Id  | Operation                        | Name   | Rows  | Bytes | Cost (%CPU)| Time     |  
8. -------------------------------------------------------------------------------------------  
9. |   0 | INSERT STATEMENT                 |        | 92299 |    10M|   431   (1)| 00:00:01 |  
10. |   1 |  LOAD AS SELECT                  | T_OBJ1 |       |       |            |          |  
11. |   2 |   OPTIMIZER STATISTICS GATHERING |        | 92299 |    10M|   431   (1)| 00:00:01 |  
12. |   3 |    TABLE ACCESS FULL             | T_OBJ  | 92299 |    10M|   431   (1)| 00:00:01 |  
13. -------------------------------------------------------------------------------------------  

ZONEMAP
Syntax:ZONEMAP ( [ @ queryblock ] tablespec { SCAN | JOIN | PARTITION } ) 
Description: The ZONEMAP hint enables the use of a zone map for different types of pruning. This hint overrides an ENABLE PRUNING setting in the DDL that created or altered the zone map.
Specify one of the following options:
SCAN - Enables the use of a zone map for scan pruning.
JOIN - Enables the use of a zone map for join pruning.
PARTITION - Enables the use of a zone map for partition pruning.

NOTE: Target table should be stored in a tablespace residing on Oracle Exadata storage and then define a ZONEMAP on it.

NO_ZONEMAP
Syntax:NO_ZONEMAP ( [ @ queryblock ] tablespec { SCAN | JOIN | PARTITION } ) 
Description: The NO_ZONEMAP hint disables the use of a zone map for different types of pruning. This hint overrides an ENABLE PRUNING setting in the DDL that created or altered the zone map.
Specify one of the following options:
SCAN - Disables the use of a zone map for scan pruning.
JOIN - Disables the use of a zone map for join pruning.
PARTITION - Disables the use of a zone map for partition pruning.

CLUSTER_BY_ROWID
Syntax:CLUSTER_BY_ROWID ( [ @ queryblock ] tablespec [ tablespec ]... ) 
Description: The CLUSTER_BY_ROWID hint instructs the optimizer enable the cluster by rowid feature.

SQL??

1. HelloDBA.com> exec sql_explain('select /*+CLUSTER_BY_ROWID(t)*/* from t_obj1 t where owner = :A', 'TYPICAL');  
2. Plan hash value: 2919409215  
3.   
4. ---------------------------------------------------------------------------------------------------  
5. | Id  | Operation                           | Name        | Rows  | Bytes | Cost (%CPU)| Time     |  
6. ---------------------------------------------------------------------------------------------------  
7. |   0 | SELECT STATEMENT                    |             |  3550 |   398K|   120   (0)| 00:00:01 |  
8. |   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| T_OBJ1      |  3550 |   398K|   120   (0)| 00:00:01 |  
9. |   2 |   SORT CLUSTER BY ROWID             |             |  3550 |       |     9   (0)| 00:00:01 |  
10. |*  3 |    INDEX RANGE SCAN                 | T_OBJ1_IDX1 |  3550 |       |     9   (0)| 00:00:01 |  
11. ---------------------------------------------------------------------------------------------------  

NO_CLUSTER_BY_ROWID
Syntax:CLUSTER_BY_ROWID ( [ @ queryblock ] tablespec [ tablespec ]... ) 
Description: The CLUSTER_BY_ROWID hint instructs the optimizer disable the cluster by rowid feature.

SQL??

1. HelloDBA.com> alter session set "_optimizer_cluster_by_rowid"=true;  
2. HelloDBA.com> exec sql_explain('select /*+NO_CLUSTER_BY_ROWID(t)*/* from t_obj1 t where owner = :A', 'TYPICAL');  
3. Plan hash value: 323892535  
4.   
5. ---------------------------------------------------------------------------------------------------  
6. | Id  | Operation                           | Name        | Rows  | Bytes | Cost (%CPU)| Time     |  
7. ---------------------------------------------------------------------------------------------------  
8. |   0 | SELECT STATEMENT                    |             |  3550 |   398K|   120   (0)| 00:00:01 |  
9. |   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| T_OBJ1      |  3550 |   398K|   120   (0)| 00:00:01 |  
10. |*  2 |   INDEX RANGE SCAN                  | T_OBJ1_IDX1 |  3550 |       |     9   (0)| 00:00:01 |  
11. ---------------------------------------------------------------------------------------------------  

DECORRELATE
Syntax:DECORRELATE ( [ @ queryblock ] )
Description: The DECORRELATE hint instructs the optimizer to decorrelate the subquery.

SQL??

1. HelloDBA.com> exec sql_explain('select /*+DECORRELATE(@iv)*/owner, subobject_name, v.last_analyzed from t_obj1 o cross apply (select /*+qb_name(iv)*/max(last_analyzed) last_analyzed from t_tab t where t.table_name=o.object_name) v', 'TYPICAL OUTLINE');  
2. Plan hash value: 973442988  
3.   
4. -----------------------------------------------------------------------------------------  
5. | Id  | Operation             | Name            | Rows  | Bytes | Cost (%CPU)| Time     |  
6. -----------------------------------------------------------------------------------------  
7. |   0 | SELECT STATEMENT      |                 | 92299 |  9734K|   461   (1)| 00:00:01 |  
8. |*  1 |  HASH JOIN RIGHT OUTER|                 | 92299 |  9734K|   461   (1)| 00:00:01 |  
9. |   2 |   VIEW                | VW_DCL_922C9664 |  2420 |   177K|    30   (4)| 00:00:01 |  
10. |   3 |    HASH GROUP BY      |                 |  2420 | 65340 |    30   (4)| 00:00:01 |  
11. |   4 |     TABLE ACCESS FULL | T_TAB           |  2426 | 65502 |    29   (0)| 00:00:01 |  
12. |   5 |   TABLE ACCESS FULL   | T_OBJ1          | 92299 |  2974K|   431   (1)| 00:00:01 |  
13. -----------------------------------------------------------------------------------------  
14.   
15. Outline Data  
16. -------------  
17.   
18.   /*+  
19.       BEGIN_OUTLINE_DATA  
20.       USE_HASH_AGGREGATION(@"SEL$ACECEEBF")  
21.       FULL(@"SEL$ACECEEBF" "T"@"IV")  
22.       SWAP_JOIN_INPUTS(@"SEL$C872D39E" "V"@"SEL$1")  
23.       USE_HASH(@"SEL$C872D39E" "V"@"SEL$1")  
24.       LEADING(@"SEL$C872D39E" "O"@"SEL$1" "V"@"SEL$1")  
25.       NO_ACCESS(@"SEL$C872D39E" "V"@"SEL$1")  
26.       FULL(@"SEL$C872D39E" "O"@"SEL$1")  
27.       OUTLINE(@"SEL$1")  
28.       OUTLINE(@"SEL$2")  
29.       DECORRELATE(@"IV")  
30.       OUTLINE(@"SEL$ACECEEBF")  
31.       MERGE(@"SEL$1")  
32.       OUTLINE(@"SEL$58A6D7F6")  
33.       OUTLINE(@"IV")  
34.       DECORRELATE(@"SEL$ACECEEBF")  
35.       OUTLINE_LEAF(@"SEL$C872D39E")  
36.       DECORRELATE(@"IV")  
37.       OUTLINE_LEAF(@"SEL$ACECEEBF")  
38.       ALL_ROWS  
39.       OPT_PARAM('star_transformation_enabled' 'true')  
40.       DB_VERSION('12.1.0.2')  
41.       OPTIMIZER_FEATURES_ENABLE('12.1.0.2')  
42.       IGNORE_OPTIM_EMBEDDED_HINTS  
43.       END_OUTLINE_DATA  
44.   */  

NO_DECORRELATE
Syntax:NO_DECORRELATE ( [ @ queryblock ] )
Description: The NO_DECORRELATE hint instructs the optimizer not to decorrelate the subquery.

SQL??

1. HelloDBA.com> exec sql_explain('select /*+NO_DECORRELATE(@iv)*/owner, subobject_name, v.last_analyzed from t_obj1 o cross apply (select /*+qb_name(iv)*/max(last_analyzed) last_analyzed from t_tab t where t.table_name=o.object_name) v', 'TYPICAL OUTLINE');  
2. Plan hash value: 882957228  
3.   
4. ----------------------------------------------------------------------------------------  
5. | Id  | Operation            | Name            | Rows  | Bytes | Cost (%CPU)| Time     |  
6. ----------------------------------------------------------------------------------------  
7. |   0 | SELECT STATEMENT     |                 | 92299 |  3785K|  2679K  (1)| 00:01:45 |  
8. |   1 |  NESTED LOOPS        |                 | 92299 |  3785K|  2679K  (1)| 00:01:45 |  
9. |   2 |   TABLE ACCESS FULL  | T_OBJ1          | 92299 |  2974K|   431   (1)| 00:00:01 |  
10. |   3 |   VIEW               | VW_LAT_922C9664 |     1 |     9 |    29   (0)| 00:00:01 |  
11. |   4 |    SORT AGGREGATE    |                 |     1 |    27 |            |          |  
12. |*  5 |     TABLE ACCESS FULL| T_TAB           |     1 |    27 |    29   (0)| 00:00:01 |  
13. ----------------------------------------------------------------------------------------  
14.   
15. Outline Data  
16. -------------  
17.   
18.   /*+  
19.       BEGIN_OUTLINE_DATA  
20.       FULL(@"IV" "T"@"IV")  
21.       USE_NL(@"SEL$58A6D7F6" "V"@"SEL$1")  
22.       LEADING(@"SEL$58A6D7F6" "O"@"SEL$1" "V"@"SEL$1")  
23.       NO_ACCESS(@"SEL$58A6D7F6" "V"@"SEL$1")  
24.       FULL(@"SEL$58A6D7F6" "O"@"SEL$1")  
25.       OUTLINE(@"SEL$1")  
26.       OUTLINE(@"SEL$2")  
27.       OUTLINE(@"IV")  
28.       MERGE(@"SEL$1")  
29.       OUTLINE_LEAF(@"SEL$58A6D7F6")  
30.       OUTLINE_LEAF(@"IV")  
31.       ALL_ROWS  
32.       OPT_PARAM('star_transformation_enabled' 'true')  
33.       DB_VERSION('12.1.0.2')  
34.       OPTIMIZER_FEATURES_ENABLE('12.1.0.2')  
35.       IGNORE_OPTIM_EMBEDDED_HINTS  
36.       END_OUTLINE_DATA  
37.   */  

GATHER_OPTIMIZER_STATISTICS
Syntax:GATHER_OPTIMIZER_STATISTICS
Description: The GATHER_OPTIMIZER_STATISTICS hint instructs the optimizer to enable statistics gathering during the following types of bulk loads: 
CREATE TABLE ... AS SELECT
INSERT INTO ... SELECT into an empty table using a direct-path insert

SQL??

1. HelloDBA.com> alter session set "_optimizer_gather_stats_on_load"=false;  
2. HelloDBA.com> exec sql_explain('insert /*+GATHER_OPTIMIZER_STATISTICS append*/into t_obj1 select * from t_obj','typical');  
3. Plan hash value: 761959232  
4.   
5. ---------------------------------------------------------------------------------------------------  
6. | Id  | Operation                        | Name   | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |  
7. ---------------------------------------------------------------------------------------------------  
8. |   0 | INSERT STATEMENT                 |        | 92299 |    10M|       |  2824   (1)| 00:00:01 |  
9. |   1 |  LOAD AS SELECT                  | T_OBJ1 |       |       |       |            |          |  
10. |   2 |   OPTIMIZER STATISTICS GATHERING |        | 92299 |    10M|       |  2824   (1)| 00:00:01 |  
11. |   3 |    SORT ORDER BY                 |        | 92299 |    10M|    13M|  2824   (1)| 00:00:01 |  
12. |   4 |     TABLE ACCESS FULL            | T_OBJ  | 92299 |    10M|       |   431   (1)| 00:00:01 |  
13. ---------------------------------------------------------------------------------------------------  

NO_GATHER_OPTIMIZER_STATISTICS
Syntax:NO_GATHER_OPTIMIZER_STATISTICS
Description: The NO_GATHER_OPTIMIZER_STATISTICS hint instructs the optimizer to disable statistics gathering during the following types of bulk loads:
CREATE TABLE ... AS SELECT
INSERT INTO ... SELECT into an empty table using a direct-path insert

SQL??

1. HelloDBA.com> alter session set "_optimizer_gather_stats_on_load"=true;  
2. HelloDBA.com> exec sql_explain('insert /*+NO_GATHER_OPTIMIZER_STATISTICS append*/into t_obj1 select * from t_obj','typical');  
3. Plan hash value: 761959232  
4.   
5. --------------------------------------------------------------------------------------  
6. | Id  | Operation           | Name   | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |  
7. --------------------------------------------------------------------------------------  
8. |   0 | INSERT STATEMENT    |        | 92299 |    10M|       |  2824   (1)| 00:00:01 |  
9. |   1 |  LOAD AS SELECT     | T_OBJ1 |       |       |       |            |          |  
10. |   2 |   SORT ORDER BY     |        | 92299 |    10M|    13M|  2824   (1)| 00:00:01 |  
11. |   3 |    TABLE ACCESS FULL| T_OBJ  | 92299 |    10M|       |   431   (1)| 00:00:01 |  
12. --------------------------------------------------------------------------------------  

PARTIAL_JOIN
Syntax:PARTIAL_JOIN ( [ @ queryblock ] tablespec [ tablespec ]... ) 
Description: The PARTIAL_JOIN hint instructs the optimizer to evaluate partial join:

SQL??

1. HelloDBA.com> alter session set "_optimizer_partial_join_eval"=false;  
2. HelloDBA.com> exec sql_explain('select /*+PARTIAL_JOIN(o)*/o.owner,o.object_name, max(t.last_analyzed) from t_tab t, t_obj o where t.owner=o.owner and t.table_name=o.object_name group by o.owner, o.object_name','typical outline');  
3. Plan hash value: 1185907781  
4.   
5. -----------------------------------------------------------------------------  
6. | Id  | Operation           | Name  | Rows  | Bytes | Cost (%CPU)| Time     |  
7. -----------------------------------------------------------------------------  
8. |   0 | SELECT STATEMENT    |       |  1017 | 66105 |   461   (1)| 00:00:01 |  
9. |   1 |  HASH GROUP BY      |       |  1017 | 66105 |   461   (1)| 00:00:01 |  
10. |*  2 |   HASH JOIN SEMI    |       |  1017 | 66105 |   460   (1)| 00:00:01 |  
11. |   3 |    TABLE ACCESS FULL| T_TAB |  2426 | 82484 |    29   (0)| 00:00:01 |  
12. |   4 |    TABLE ACCESS FULL| T_OBJ | 92299 |  2794K|   431   (1)| 00:00:01 |  
13. -----------------------------------------------------------------------------  
14.   
15. Outline Data  
16. -------------  
17.   
18.   /*+  
19.       BEGIN_OUTLINE_DATA  
20.       PARTIAL_JOIN(@"SEL$1" "O"@"SEL$1")  
21.       USE_HASH_AGGREGATION(@"SEL$1")  
22.       USE_HASH(@"SEL$1" "O"@"SEL$1")  
23.       LEADING(@"SEL$1" "T"@"SEL$1" "O"@"SEL$1")  
24.       FULL(@"SEL$1" "O"@"SEL$1")  
25.       FULL(@"SEL$1" "T"@"SEL$1")  
26.       OUTLINE_LEAF(@"SEL$1")  
27.       ALL_ROWS  
28.       OPT_PARAM('star_transformation_enabled' 'true')  
29.       OPT_PARAM('_optimizer_partial_join_eval' 'false')  
30.       DB_VERSION('12.1.0.2')  
31.       OPTIMIZER_FEATURES_ENABLE('12.1.0.2')  
32.       IGNORE_OPTIM_EMBEDDED_HINTS  
33.       END_OUTLINE_DATA  
34.   */  

NO_PARTIAL_JOIN
Syntax:NO_PARTIAL_JOIN ( [ @ queryblock ] tablespec [ tablespec ]... ) 
Description: The NO_PARTIAL_JOIN hint instructs the optimizer not to evaluate partial join:

SQL??

1. HelloDBA.com> alter session set "_optimizer_partial_join_eval"=true;  
2. HelloDBA.com> exec sql_explain('select /*+NO_PARTIAL_JOIN(o)*/o.owner,o.object_name, max(t.last_analyzed) from t_tab t, t_obj o where t.owner=o.owner and t.table_name=o.object_name group by o.owner, o.object_name','typical outline');  
3. Plan hash value: 887456466  
4.   
5. -----------------------------------------------------------------------------  
6. | Id  | Operation           | Name  | Rows  | Bytes | Cost (%CPU)| Time     |  
7. -----------------------------------------------------------------------------  
8. |   0 | SELECT STATEMENT    |       |  2426 |   153K|   461   (1)| 00:00:01 |  
9. |   1 |  HASH GROUP BY      |       |  2426 |   153K|   461   (1)| 00:00:01 |  
10. |*  2 |   HASH JOIN         |       |  2426 |   153K|   460   (1)| 00:00:01 |  
11. |   3 |    TABLE ACCESS FULL| T_TAB |  2426 | 82484 |    29   (0)| 00:00:01 |  
12. |   4 |    TABLE ACCESS FULL| T_OBJ | 92299 |  2794K|   431   (1)| 00:00:01 |  
13. -----------------------------------------------------------------------------  
14.   
15. Outline Data  
16. -------------  
17.   
18.   /*+  
19.       BEGIN_OUTLINE_DATA  
20.       USE_HASH_AGGREGATION(@"SEL$1")  
21.       USE_HASH(@"SEL$1" "O"@"SEL$1")  
22.       LEADING(@"SEL$1" "T"@"SEL$1" "O"@"SEL$1")  
23.       FULL(@"SEL$1" "O"@"SEL$1")  
24.       FULL(@"SEL$1" "T"@"SEL$1")  
25.       OUTLINE_LEAF(@"SEL$1")  
26.       ALL_ROWS  
27.       OPT_PARAM('star_transformation_enabled' 'true')  
28.       DB_VERSION('12.1.0.2')  
29.       OPTIMIZER_FEATURES_ENABLE('12.1.0.2')  
30.       IGNORE_OPTIM_EMBEDDED_HINTS  
31.       END_OUTLINE_DATA  
32.   */  

PARTIAL_ROLLUP_PUSHDOWN
Syntax:PARTIAL_ROLLUP_PUSHDOWN ( [ @ queryblock ] )
Description: The PARTIAL_ROLLUP_PUSHDOWN hint instructs the optimizer to perform partial rollup pushdown for parallel execution

SQL??

1. HelloDBA.com> alter session set "_px_partial_rollup_pushdown"=off;  
2. HelloDB