Filter Subqueries

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.

create table emp(
	dept_no		not null,
	sal,
	emp_no		not null,
	padding,
	constraint e_pk primary key(emp_no)
)
as
with generator as (
	select	--+ materialize
		rownum 		id
	from	all_objects
	where	rownum <= 1000
)
select
	mod(rownum,6),
	rownum,
	rownum,
	rpad('x',60)
from
	generator	v1,
	generator	v2
where
	rownum <= 20000
;
begin
	dbms_stats.gather_table_stats(
		ownname		 => user,
		tabname		 => 'EMP',
		estimate_percent => null,
		block_sample 	 => true,
		method_opt 	 => 'for all columns size 1',
		degree		 => null,
		granularity	 => 'default',
		cascade		 => true
	);
end;
/               

select
	count(*)
from	(
	select	/*+ no_merge */
		outer.*
	from	emp outer
	where	outer.sal >
		(
			select	/*+ no_unnest */
				avg(inner.sal)
		 	from	emp inner
			where	inner.dept_no = outer.dept_no
		)
	)
;               

The query is a little strange as it uses the /*+ no_unnest */ hint to stop the optimizer from unnesting the subquery. Unnesting would normally happen in simple cases like this one, but more complex queries aren’t alway able to undergo the unnest transformation. The query also has a /*+ no_merge */ hint so that it can execute and instantiate the main body of the query, then only output a count. You should find that the query takes a fraction of a second to run - possibly less than one hundredth if you are able to hold the emp table cached in memory - returning the value 9,998.

Now execute the following update, and run the query again:

update emp
	set dept_no = 432
where
	dept_no = 1
and	rownum = 1
;

Running at 1.6GHz, my laptop takes about 21 seconds of flat out CPU time to complete the query with just this one row-change, so be prepared for a bit of a wait. This change in performance is what happens when a particular run-time optimisation for scalar subqueries fails (and this correlated subquery is just one example of the more general class of scalar subquery).

When the query ran quickly, Oracle managed to cache the average salaries for the six departments as it ran through the first six rows in the tablescan. But these values were stored in a hash table keyed by the department number.

When I changed the first occurrence of department 1 to 432, Oracle got to the row with department 432 before it got to a row with department 1, and stored the average salary for department 432 in the hash table location that would otherwise have been used by department 1.

Unfortunately, the caching algorithm does not allow for hash collisions of this type, so the next 3,333 times Oracle needed the average salary for department 1 it discovered that it wasn’t cached, and ran the subquery again, .. and again, .. and again …

If you do find that you have a particular query that is subject to this problem you may have to write it to avoid the filter subquery (and you may find that a simple /*+ unnest */ hint will be enough to pre-empt the issue).

Here is a counter-intuitive mechanism that will be highly cost-effective in some cases - I replace the first occurence of the emp table with a non-mergeable inline view that sorts the emp table by department number **:

select
	count(*)
from	(
	select	/*+ no_merge */
		outer.*
	from	(
		select
			/*+
				no_merge
				qb_name(ordering)
				no_eliminate_oby(@ordering)
			*/
			*
		from	emp
		order by
			dept_no
		)	outer
	where	outer.sal >
		(
			select	/*+ no_unnest */
				avg(inner.sal)
		 	from 	emp inner
			where	inner.dept_no = outer.dept_no
		)
	)
/          

On my laptop, using this sorting strategy, my response time dropped back to 0.02 seconds. This works because the code now acquires the department numbers in order, and Oracle doesn’t even do a lookup to the hash table if the current row uses the same input as the previous row.

** It has occurred to me that in 10gR2 you may need to include a qb_name() hint and a no_eliminate_oby hint in the inline view to stop the optimizer from eliminating what it sees as a redundant order by. (See the end of this article for an example of the necessary hints). But I don’t have a 10.2 database to hand to check this at the moment - so I’ll have to test that some time in the next couple of days. (Relevant 10g hint introduced in ordering sub-query after receiving note from Jeff Moss - see comments below).