index$_join$_001 appears with CPU-costly hash joins - why?
Jason Buchanan
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.
Jonathan Lewis
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/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.b...@gmail.com> wrote in message
news:5f258456.04070...@posting.google.com...