leading() hint的用法，连接顺序问题

SQL> select * from ttl; NAME TYPE_TT COL1--------------- -------------------- ----------CALLID NUMBER(20) CALLIDSTART_TIME DATE IMSIEND_TIME DATE START_TIMEIMSI VARCHAR2(128) END_TIMESQL> desc ttl;Name Type Nullable Default Comments ------- ------------- -------- ------- -------- NAME VARCHAR2(300) Y TYPE_TT VARCHAR2(300) Y COL1 VARCHAR2(300) Y SQL> SQL> select t1.col1 ||' '||t2.type_tt||',' 2 from ttl t1,ttl t2 3 where t1.col1 = t2.name 4 / T1.COL1||''||T2.TYPE_TT||','--------------------------------------------------------------------------------CALLID NUMBER(20),START_TIME DATE,END_TIME DATE,IMSI VARCHAR2(128),

OMD_TEST@oran_16>explain plan for select t1.col1 ||' '||t2.type_tt||',' 2 from ttl t1,ttl t2 3 where t1.col1 = t2.name 4 /已解释。OMD_TEST@oran_16>select * from table(dbms_xplan.display(null,null,'alias'));PLAN_TABLE_OUTPUT --------------------------------------------------------------------------------Plan hash value: 3431846397 --------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 4 | 1824 | 7 (15)| 00:00:01 | |* 1 | HASH JOIN | | 4 | 1824 | 7 (15)| 00:00:01 | | 2 | TABLE ACCESS FULL| TTL | 4 | 608 | 3 (0)| 00:00:01 | | 3 | TABLE ACCESS FULL| TTL | 4 | 1216 | 3 (0)| 00:00:01 | --------------------------------------------------------------------------- PLAN_TABLE_OUTPUT --------------------------------------------------------------------------------Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$1 2 - SEL$1 / T1@SEL$1 3 - SEL$1 / T2@SEL$1 Predicate Information (identified by operation id): --------------------------------------------------- 1 - access("T1"."COL1"="T2"."NAME") PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- Note ----- - dynamic sampling used for this statement 已选择26行。OMD_TEST@oran_16>spool off

 

解释：从t2中取出name与t1中的col1关联所以顺序是按照name列的顺序来排列的，主要是通过
hash join来看 因为是t1在t2上面 那么意味着是把t1.col1做成hash表，然后从t2中取出数据
一个个去与t1表匹配，如果我们想要的数据是以col1的顺序来排列的那么需要更改连接顺序也就用到了

leading(t2)使 t2中的数据变为hash表 先从T1取数据来与t2匹配，看下面的更改结果：

SQL> select /*+ leading(t2)*/t1.col1 ||' '||t2.type_tt||',' 2 from ttl t1,ttl t2 3 where t1.col1 = t2.name 4 / T1.COL1||''||T2.TYPE_TT||','--------------------------------------------------------------------------------CALLID NUMBER(20),IMSI VARCHAR2(128),START_TIME DATE,END_TIME DATE,

OMD_TEST@oran_16>explain plan for select /*+ leading(t2)*/t1.col1 ||' '||t2.type_tt||',' 2 from ttl t1,ttl t2 3 where t1.col1 = t2.name 4 /已解释。OMD_TEST@oran_16>select * from table(dbms_xplan.display(null,null,'alias'));PLAN_TABLE_OUTPUT --------------------------------------------------------------------------------Plan hash value: 3431846397 --------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 4 | 1824 | 7 (15)| 00:00:01 | |* 1 | HASH JOIN | | 4 | 1824 | 7 (15)| 00:00:01 | | 2 | TABLE ACCESS FULL| TTL | 4 | 1216 | 3 (0)| 00:00:01 | | 3 | TABLE ACCESS FULL| TTL | 4 | 608 | 3 (0)| 00:00:01 | --------------------------------------------------------------------------- PLAN_TABLE_OUTPUT --------------------------------------------------------------------------------Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$1 2 - SEL$1 / T2@SEL$1 3 - SEL$1 / T1@SEL$1 Predicate Information (identified by operation id): --------------------------------------------------- 1 - access("T1"."COL1"="T2"."NAME") PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- Note ----- - dynamic sampling used for this statement 已选择26行。OMD_TEST@oran_16>spool off

通过观察t2.name做hash table,然后用t1.col1一条条取出与t2.name列去匹配，所以结果是按照t1.col1顺序显示出来