Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> index$_join$_001 appears with CPU-costly hash joins - why?

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

From: Jason Buchanan <jason.buchanan_at_gmail.com>
Date: 1 Jul 2004 06:26:03 -0700
Message-ID: <5f258456.0407010526.5643fc3e@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. Received on Thu Jul 01 2004 - 08:26:03 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US