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

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. Received on Thu Jul 01 2004 - 08:26:03 CDT