Example using Hint

[해리]

1. Run profiled SQL , get Plan Outline

SQL> select

  2  count(*)

  3  from eds.eds_finance_data

  4  where period in ('2014_11') and version='SL' and system_source in ('AUS_E','AUS_L','VERO_E','NZ_E') AND not exists

  5  (select

  6  1

  7  FROM eds.eds_links a

  8  where a.apra_flag = 'Y' and a.policy_no = eds.eds_finance_data.policy_no

  9  and system_source in ('AUS_E','AUS_L','VERO_E','NZ_E')

 10  and substr(a.period,1,4)||'_'||substr(a.period,5,2) = eds.eds_finance_data.period);

  COUNT(*)

----------

   3628900

SQL> set pagesize 2000

SQL> set linesize 160

SQL> select * from table(dbms_xplan.display_cursor(format => 'ADVANCED'));

 ......

Or check with SQL ID

SQL> select * from table(dbms_xplan.display_awr('c68kk08jd34g8',format => 'ADVANCED'));

2. Outline Data Example

Outline Data

-------------

  /*+

      BEGIN_OUTLINE_DATA

      IGNORE_OPTIM_EMBEDDED_HINTS

      OPTIMIZER_FEATURES_ENABLE('11.2.0.3')

      DB_VERSION('11.2.0.3')

      OPT_PARAM('optimizer_dynamic_sampling' 6)

      ALL_ROWS

      NO_PARALLEL

      OUTLINE_LEAF(@"SEL$5DA710D3")

      UNNEST(@"SEL$2")

      OUTLINE(@"SEL$1")

      OUTLINE(@"SEL$2")

      INDEX_RS_ASC(@"SEL$5DA710D3" "EDS_FINANCE_DATA"@"SEL$1" ("EDS_FINANCE_DATA"."VERSION"))

      FULL(@"SEL$5DA710D3" "A"@"SEL$2")

      LEADING(@"SEL$5DA710D3" "EDS_FINANCE_DATA"@"SEL$1" "A"@"SEL$2")

      USE_HASH(@"SEL$5DA710D3" "A"@"SEL$2")

      PQ_DISTRIBUTE(@"SEL$5DA710D3" "A"@"SEL$2" HASH HASH)

      SWAP_JOIN_INPUTS(@"SEL$5DA710D3" "A"@"SEL$2")

      END_OUTLINE_DATA

  */

 3. Use Hint 

 select  /*+ INDEX_RS_ASC(EDS_FINANCE_DATA@SEL$1) FULL(A@SEL$2) LEADING(EDS_FINANCE_DATA@SEL$1 A@SEL$2) USE_HASH(A@SEL$2) PQ_DISTRIBUTE(A@SEL$2 HASH HASH) SWAP_JOIN_INPUTS(A@SEL$2) */

count(*) ....

 *  HASH JOIN ANTI RIGHT Option was applied through SWAP_JOIN_INPUTS  . 

- Previous Plan : It took more than 1 hour. 

  Use Hint  Plan : It just takes 1 minute. The below is modified Plan