oracle usehash,SQL优化，加use_hash(a,b,c)

select /*+ gather_plan_statistics use_hash(a,b,c)*/DISTINCT c.log_id

FROM UCR_CRM1.tab_ren_ykc_02a,

UOP_CRM1.TF_B_VALUECARD_SALE_DETAIL b,

uop_crm1.TF_B_RES_SALE_LOG c

WHERE a.sno >= b.start_value

AND a.sno <= b.end_value

AND b.log_id = c.log_id;

…….

2.0100E+15

2.0100E+15

2.0100E+15

2.0100E+15

2.0100E+15

2.0100E+15

5851 rows selected.

Elapsed: 00:00:01.79

SQL>select sql_id,child_number from v$sql where sql_text like 'select /*+ gather_plan_statistics use_hash(a,b,c)*/DISTINCT c.log_i%';

SQL_IDCHILD_NUMBER

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

3nmkyfatckg9j0

Elapsed: 00:00:00.90

SQL> select * from table(dbms_xplan.display_cursor('3nmkyfatckg9j',0,'allstats last'));

PLAN_TABLE_OUTPUT

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

SQL_ID3nmkyfatckg9j, child number 0

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

select /*+ gather_plan_statistics use_hash(a,b,c)*/DISTINCT c.log_idFROM UCR_CRM1.tab_ren_ykc_02a,

UOP_CRM1.TF_B_VALUECARD_SALE_DETAIL b,uop_crm1.TF_B_RES_SALE_LOG cWHERE a.sno >= b.start_valueAND a.sno <= b.end_valueAND

b.log_id = c.log_id

Plan hash value: 3730029898

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

| Id| Operation| Name| Starts | E-Rows | A-Rows |A-Time| Buffers | Reads|OMem |1Mem | Used-Mem |

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

PLAN_TABLE_OUTPUT