Re: index$_join$_001 appears with CPU-costly hash joins - why?

This appears in an 'index join'

If you look carefully at the plan, you will see that table "article" is not being visited at all. Instead, Oracle is ranging two indexes on the table, and hash joining data from the indexes on common rowids to create the required
"article" data.

As you observed, if it's not a good idea (which could mean something about the statistics has fooled the optimizer) it can be CPU intensive. (It can also be disk intensive if things go really badly wrong).

-- 
Regards

Jonathan Lewis

http://www.jlcomp.demon.co.uk

http://www.jlcomp.demon.co.uk/faq/ind_faq.html
The Co-operative Oracle Users' FAQ

http://www.jlcomp.demon.co.uk/seminar.html
Optimising Oracle Seminar - schedule updated May 1st


"Jason Buchanan" <jason.buchanan_at_gmail.com> wrote in message
news:5f258456.0407010526.5643fc3e_at_posting.google.com...


> index$_join$_001 appears with some hash joins - why?  Is Oracle

> building this on the fly?

>

>

> For this statement:

>

>  SELECT DISTINCT t1.art_id,t1.publish_date

>    FROM article t1, article_taxonomy t2

>   WHERE t2.art_id=t1.art_id

>     AND (t2.taxonomy_element_id IN (:1)

> AND (t1.publish_date <= :2))

>  ORDER BY t1.publish_date DESC

>

>

> This becomes the explain plan:

>

>            Rows Row Source Operation (Object Id)

> --------------- ----------------------------------------------------------

-----


>              10 SORT UNIQUE

>           4,804  HASH JOIN

>           4,804   TABLE ACCESS BY INDEX ROWID ARTICLE_TAXONOMY(5226)

>           4,804    INDEX RANGE SCAN(5229)

>         195,279   VIEW index$_join$_001

>         195,279    HASH JOIN

>         195,279     INDEX RANGE SCAN(5185)

>         195,509     INDEX FAST FULL SCAN(5181)

>

>

> Am I right in thinking that Oracle is building VIEW index$_join$_001

> on the fly to speed up the performance of the query?  In testing this

> appears to (sometimes) be a heavy operation, at the expense of CPU.