Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.server -> 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
![]() |
![]() |