Re: Index Join Oddity
- From: Niall Litchfield <niall.litchfield@xxxxxxxxx>
- To: ORACLE-L <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 5 Apr 2013 13:52:33 +0100
Thanks to an insightful question offline replace
"range scan IDX1 for the filter and then look up the join predicate in the
PK"
with
"range scan IDX1 for the filter and then look up the join predicate in the
PK *which is the table because this turns out to be an IOT* in order to do
the join.. "
Apologies for any bandwidth taken up with foolishness.
On Fri, Apr 5, 2013 at 12:44 PM, Niall Litchfield <
niall.litchfield@xxxxxxxxx> wrote:
> 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
>
--
Niall Litchfield
Oracle DBA
http://www.orawin.info
--
//www.freelists.org/webpage/oracle-l
Other related posts:
