no_use_hash hint

The NO_USE_HASH hint instructs the optimizer to exclude hash joins when joining each specified table to another row source using the specified table as the inner table.

script

drop table t1_object purge;
drop table t2_object purge;

create table t1_object(id1 number,name varchar2(50));
create table t2_object(id2 number,name varchar2(50));

insert into t1_object select rownum,object_name from dba_objects;
insert into t2_object select rownum + 10000,object_name from dba_objects where rownum <= 500; 
commit; 
begin   
       dbms_stats.gather_table_stats(       
                ownname         => user,
        tabname         => 't1_object',
        cascade         => true,
        estimate_percent    => null, 
        method_opt      =>'for all columns size 1'
    );
end;
/

begin
    dbms_stats.gather_table_stats(
        ownname         => user,
        tabname         => 't2_object',
        cascade         => true,
        estimate_percent    => null, 
        method_opt      =>'for all columns size 1'
    );
end;
/

original sql text:
select  * from t1_object t1,t2_object t2 where t1.id1 = t2.id2;

—no hint—

SQL> explain plan set statement_id = 'use_hash' for select * from t1_object t1,t2_object t2 where t1.id1 = t2.id2;

Explained.

SQL>
SQL> select * from table(dbms_xplan.display(format=>'advanced'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3226144231

--------------------------------------------------------------------------------
| Id  | Operation          | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |           |   500 | 24000 |   118   (2)| 00:00:02 |
|*  1 |  HASH JOIN         |           |   500 | 24000 |   118   (2)| 00:00:02 |
|   2 |   TABLE ACCESS FULL| T2_OBJECT |   500 |  9500 |     2   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| T1_OBJECT | 87020 |  2464K|   115   (1)| 00:00:02 |
--------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1
   2 - SEL$1 / T2@SEL$1
   3 - SEL$1 / T1@SEL$1

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      USE_HASH(@"SEL$1" "T1"@"SEL$1")
      LEADING(@"SEL$1" "T2"@"SEL$1" "T1"@"SEL$1")
      FULL(@"SEL$1" "T1"@"SEL$1")
      FULL(@"SEL$1" "T2"@"SEL$1")
      OUTLINE_LEAF(@"SEL$1")
      ALL_ROWS
      DB_VERSION('11.2.0.4')
      OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("T1"."ID1"="T2"."ID2")

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - (#keys=1) "T2"."ID2"[NUMBER,22], "T1"."ID1"[NUMBER,22],
       "T2"."NAME"[VARCHAR2,50], "T1"."NAME"[VARCHAR2,50]
   2 - "T2"."ID2"[NUMBER,22], "T2"."NAME"[VARCHAR2,50]
   3 - "T1"."ID1"[NUMBER,22], "T1"."NAME"[VARCHAR2,50]

–add no_use_hash hint–

SQL> explain plan set statement_id = 'no_use_hash' for select /*+ no_use_hash(t1 t2)*/ * from t1_object t1,t2_object t2 where t1.id1 = t2.id2;

Explained.

SQL>
SQL>
SQL> select * from table(dbms_xplan.display(format=>'advanced'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 4092208419

-----------------------------------------------------------------------------------------
| Id  | Operation           | Name      | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |           |   500 | 24000 |       |   816   (1)| 00:00:10 |
|   1 |  MERGE JOIN         |           |   500 | 24000 |       |   816   (1)| 00:00:10 |
|   2 |   SORT JOIN         |           |   500 |  9500 |       |     3  (34)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| T2_OBJECT |   500 |  9500 |       |     2   (0)| 00:00:01 |
|*  4 |   SORT JOIN         |           | 87020 |  2464K|  6840K|   813   (1)| 00:00:10 |
|   5 |    TABLE ACCESS FULL| T1_OBJECT | 87020 |  2464K|       |   115   (1)| 00:00:02 |
-----------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1
   3 - SEL$1 / T2@SEL$1
   5 - SEL$1 / T1@SEL$1

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      USE_MERGE(@"SEL$1" "T1"@"SEL$1")
      LEADING(@"SEL$1" "T2"@"SEL$1" "T1"@"SEL$1")
      FULL(@"SEL$1" "T1"@"SEL$1")
      FULL(@"SEL$1" "T2"@"SEL$1")
      OUTLINE_LEAF(@"SEL$1")
      ALL_ROWS
      DB_VERSION('11.2.0.4')
      OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("T1"."ID1"="T2"."ID2")
       filter("T1"."ID1"="T2"."ID2")

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - (#keys=0) "T2"."ID2"[NUMBER,22], "T1"."ID1"[NUMBER,22],
       "T2"."NAME"[VARCHAR2,50], "T1"."NAME"[VARCHAR2,50]
   2 - (#keys=1) "T2"."ID2"[NUMBER,22], "T2"."NAME"[VARCHAR2,50]
   3 - "T2"."ID2"[NUMBER,22], "T2"."NAME"[VARCHAR2,50]
   4 - (#keys=1) "T1"."ID1"[NUMBER,22], "T1"."NAME"[VARCHAR2,50]
   5 - "T1"."ID1"[NUMBER,22], "T1"."NAME"[VARCHAR2,50]
此条目发表在Hint分类目录。将固定链接加入收藏夹。

发表评论

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / 更改 )

Twitter picture

You are commenting using your Twitter account. Log Out / 更改 )

Facebook photo

You are commenting using your Facebook account. Log Out / 更改 )

Google+ photo

You are commenting using your Google+ account. Log Out / 更改 )

Connecting to %s