StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POOracle Anti-Join Execution plan question
text
Body
copied!<p>We have two tables like so:</p> <pre><code>Event id type ... a bunch of other columns ProcessedEvent event_id process </code></pre> <p>There are indexes defined for</p> <ul> <li>Event(id) (PK)</li> <li>ProcessedEvent (event_id, process) </li> </ul> <p>The first represents events in an application.</p> <p>The second represents the fact that a certain event got processes by a certain process. There are many processes that need to process a certain event, so there are multiple entries in the second table for each entry in the first.</p> <p>In order to find all the events that need processing we execute the following query:</p> <pre><code>select * // of course we do name the columns in the production code from Event where type in ( 'typeA', 'typeB', 'typeC') and id not in ( select event_id from ProcessedEvent where process = :1 ) </code></pre> <p>Statistics are up to date</p> <p>Since most events are processed, I think the best execution plan should look something like this</p> <ul> <li>full index scan on the ProcessedEvent Index</li> <li>full index scan on the Event Index</li> <li>anti join between the two</li> <li>table access with the rest</li> <li>filter</li> </ul> <p>Instead Oracle does the following</p> <ul> <li>full index scan on the ProcessedEvent Index</li> <li>full table scan on the Event table</li> <li>filter the Event table</li> <li>anti join between the two sets</li> </ul> <p>With an index hint I get Oracle to do the following:</p> <ul> <li>full index scan on the ProcessedEvent Index</li> <li>full index scan on the Event Index</li> <li>table acces on the Event table</li> <li>filter the Event table</li> <li>anti join between the two sets</li> </ul> <p>which is really stupid IMHO.</p> <p>So my question is: what might be the reason for oracle to insist on the early table access?</p> <hr> <p>Addition: The performance is bad. We are fixing the performance problem by selecting just the Event.IDs and then fetching the needed rows 'manually'. But of course that is just a work around.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload