Index Join Oddity

From: Niall Litchfield <niall.litchfield_at_gmail.com>
Date: Fri, 5 Apr 2013 12:44:50 +0100
Message-ID: <CABe10sY6smFe-9C9p0PM=h0Y0EFM+TrQ+qpaP2twVxLH6OFeBA_at_mail.gmail.com>

so I'm used to seeing index$_join$_001 ... in explain plans but we have the following plan for one of our queries. This query plan results in good enough performance, but I haven't seen lines like those in 6,7,8 before. The pk is a composite index on <partition_key>,<join predicate to T1> plus 2 others. IDX_1 is a local index on a filter column. The conventional reading of this section of the plan would be range scan IDX1 for the filter and then look up the join predicate in the PK. Except I don't see how that would work. Any insight into this operation (including RTFM or pag 42 of Jonathan's excellent CBO book) would be welcome. eg if you imagine T2 as being defined like

create table t2(

t2_datecol date,
t1_pkcol number,
t2_vc varchar2,
t2_otherdatecol date,
t2_morecols...

partition by range t2_datecol);

then t2_pk is on (t2_datecol,t1_pkcol,t2_vc) whilst t2_idx1 is a local index on t2_otherdatecol.

---

| Id  | Operation               | Name                       | Rows  |
Bytes | Cost (%CPU)| Time     | Pstart| Pstop |



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





|   0 | SELECT STATEMENT        |                            |       |

  |   146K(100)|          |       |       |




|   1 |  HASH GROUP BY          |                            |    22 |

 2420 |   146K  (1)| 00:29:13 |       |       |





|   2 |   FILTER                |                            |       |

  |            |          |       |       |




|   3 |    HASH JOIN            |                            |  2094 |

224K|   146K  (1)| 00:29:13 |       |       |




|   4 |     HASH JOIN           |                            |  2094 |

192K|   145K  (1)| 00:29:10 |       |       |




|   5 |      TABLE ACCESS FULL  | T1                         |    43 |

473 |     3   (0)| 00:00:01 |       |       |




|   6 |      PARTITION RANGE ALL|                            |  2167 |

175K|   145K  (1)| 00:29:10 |     1 |    69 |




|   7 |       INDEX UNIQUE SCAN | T2_PK                      |  2167 |

175K|   145K  (1)| 00:29:10 |     1 |    69 |




|   8 |        INDEX RANGE SCAN | T2_IDX1                    |  2167 |

  |   238   (0)| 00:00:03 |     1 |    69 |




|   9 |     TABLE ACCESS FULL   | T3                         | 41694 |

651K|   273   (1)| 00:00:04 |       |       |


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

-- 
Niall Litchfield
Oracle DBA
http://www.orawin.info


--
http://www.freelists.org/webpage/oracle-l

Received on Fri Apr 05 2013 - 13:44:50 CEST