Filter Subqueries « Oracle Scratchpad

Filter Subqueries
Filed under: Hints, Performance, Troubleshooting, Tuning — Jonathan Lewis @ 11:33 pm UTC Nov 6,2006

Here’s a little demonstration of a feature that can cause random fluctuations in performance because of an unlucky data item. It starts with an emp table holding 20.000 employees spread across six departments, and then moves one employee to a new (carefully chosen) department. You will have to run this in version 9i or later, as it makes use of subquery factoring to generate the emp table.