First_rows hash

Just like my posting on an index hash, this posting is about a problem as well as being about a hash join. The article has its roots in a question posted on the OTN database forum, where a user has shown us the following execution plan:

---------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name                    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                         |    14 |   896 |    63   (7)| 00:00:01 |
|   1 |  SORT GROUP BY                |                         |    14 |   896 |    63   (7)| 00:00:01 |
|   2 |   NESTED LOOPS                |                         |       |       |            |          |
|   3 |    NESTED LOOPS               |                         |    14 |   896 |    62   (5)| 00:00:01 |
|*  4 |     HASH JOIN                 |                         |    14 |   280 |    48   (7)| 00:00:01 |
|   5 |      VIEW                     | V_SALES_ALL             |   200 |  1800 |     4   (0)| 00:00:01 |
|   6 |       UNION-ALL               |                         |       |       |            |          |
|   7 |        INDEX FAST FULL SCAN   | PRODUCTS_DATES_IDX      |   100 |   900 |     2   (0)| 00:00:01 |
|   8 |        INDEX FAST FULL SCAN   | PRODUCTS_DATES_IDX_HARD |   100 |   900 |     2   (0)| 00:00:01 |
|*  9 |      VIEW                     | index$_join$_003        |  2238 | 24618 |    44   (7)| 00:00:01 |
|* 10 |       HASH JOIN               |                         |       |       |            |          |
|* 11 |        INDEX RANGE SCAN       | PRODUCTS_GF_INDEX2      |  2238 | 24618 |     6   (0)| 00:00:01 |
|  12 |        INDEX FAST FULL SCAN   | PRODUCTS_GF_PK          |  2238 | 24618 |    45   (3)| 00:00:01 |
|* 13 |     INDEX UNIQUE SCAN         | DATES_PK                |     1 |       |     0   (0)| 00:00:01 |
|  14 |    TABLE ACCESS BY INDEX ROWID| DATES                   |     1 |    44 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------

Note particularly that line 6 is a union all of two index fast full scans that are predicted to produce 100 rows each, and that the resulting 200 rows are passed through a view to become the build table for a hash join. The problem, as the OP points out, is that the estimates are very bad and the indexes return 1.7 million and 78 million rows respectively, leading to a very large hash table and a very slow query.

The problem is that the system is running with the optimizer_mode set to first_rows_100 – and there’s a bug in the code that has made the optimizer “forget” that it can’t stop after 100 rows when it’s supposed to be building a hash table. Here’s a test case to reproduce the problem. I’ve run this on Oracle 11.1.0.7 using my standard setup (8KB blocks, locally managed tablespace with 1MB uniform extents, freelist management, and system statistics disabled):

create table t1
as
with generator as (
	select	--+ materialize
		rownum id
	from dual
	connect by
		level <= 10000)
select
	rownum					id,
	trunc(dbms_random.value(1,1000))	n1,
	lpad(rownum,10,'0')	small_vc,
	rpad('x',100)		padding
from
	generator	v1,
	generator	v2
where
	rownum <= 1000000;

create index t1_n1 on t1(id, n1);

create table t2
as
with generator as (
	select	--+ materialize
		rownum id
	from dual
	connect by
		level <= 10000)
select
	rownum					id,
	trunc(dbms_random.value(10001,20001))	x1,
	lpad(rownum,10,'0')	small_vc,
	rpad('x',100)		padding
from
	generator	v1,
	generator	v2
where
	rownum <= 1000000;

create index t2_i1 on t2(x1);

-- collect stats on both tables, no histograms

create or replace view  v1
as
select
	id, n1, small_vc, padding
from	t1
where	n1 between 101 and 300
union all
select
	id, n1, small_vc, padding
from	t1
where	n1 between 501 and 700
;

alter session set optimizer_mode = first_rows_10;

explain plan for
select
	v1.small_vc,
	v1.n1
from
	v1,
	t2
where
	t2.id = v1.id
and	t2.x1 between 1000 and 5000
;

select * from table(dbms_xplan.display(null,null,'outline'));

I’ve used the same table twice in my view – and the final query is actually going to use tablescans on the two accesses to the t1 table, ignoring the index which I happen to have left in place from my first attempts to model the original scenario. With this data in place, and running with first_rows_10, the execution plan is as follows:

-----------------------------------------------------------------------
| Id  | Operation                     | Name  | Rows  | Bytes | Cost  |
-----------------------------------------------------------------------
|   0 | SELECT STATEMENT              |       |     1 |    50 |   108 |
|*  1 |  HASH JOIN                    |       |     1 |    50 |   108 |
|   2 |   VIEW                        | V1    |    24 |   960 |     4 |
|   3 |    UNION-ALL                  |       |       |       |       |
|*  4 |     TABLE ACCESS FULL         | T1    |    12 |   240 |     2 |
|*  5 |     TABLE ACCESS FULL         | T1    |    12 |   240 |     2 |
|   6 |   TABLE ACCESS BY INDEX ROWID | T2    |   100 |  1000 |   103 |
|*  7 |    INDEX RANGE SCAN           | T2_I1 |   100 |       |     3 |
-----------------------------------------------------------------------

As you can see, the optimizer thinks it will be very quick and cheap to do two full tablescans of t1 to collect a total of 24 rows to feed into a hash table before doing an index range scan against t2 to collect 100 rows to probe the hash table. But even though it may be quick and cheap for Oracle to collect the first 12 rows from each tablescan, at runtime we actually have to complete both tablescans and build the entire hash table before we can start the index range scan of t2. To see how nasty this plan will be I generated the outline for the query, which gave me all the hints I needed to reproduce the plan while running all_rows – so here’s the plan again, giving you a better indication of how expensive it will be to build the hash table:

------------------------------------------------------------------------------
| Id  | Operation                    | Name  | Rows  | Bytes |TempSpc| Cost  |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |       |   200 | 11600 |       |  5854 |
|*  1 |  HASH JOIN                   |       |   200 | 11600 |    23M|  5854 |
|   2 |   VIEW                       | V1    |   402K|    18M|       |  5382 |
|   3 |    UNION-ALL                 |       |       |       |       |       |
|*  4 |     TABLE ACCESS FULL        | T1    |   201K|  3933K|       |  2691 |
|*  5 |     TABLE ACCESS FULL        | T1    |   201K|  3933K|       |  2691 |
|   6 |   TABLE ACCESS BY INDEX ROWID| T2    |   100 |  1000 |       |   103 |
|*  7 |    INDEX RANGE SCAN          | T2_I1 |   100 |       |       |     3 |
------------------------------------------------------------------------------

See how expensive the two full tablescans are, relative to the cost of getting the 100 rows held by t2. The fastest way of running this queryto get the first 10 rows is probably to use t2 as the build table of a hash join (so that we can stop the tablescans of t1 early).

Remember – if you’re running first_rows_N as the optimizer_mode you’re in the minority, so you’re running code that has been subject to less testing than the rest of the optimizer code: you’re likely to run into some very odd bugs. Despite comments you might hear on the internet from time to time along the lines of “first_rows_N is for OLTP systems” (that’s not really correct, by the way), it’s probably best to stick with all_rows unless you can come up with a very sound argument for doing otherwise.

Footnote:
This looks as if it mighe be bug 9633142: FIRST_ROWS OPTIMIZER DOES NOT PUSH PREDICATES INTO UNION INLINE VIEW; and if it is not the same bug it is closely related to it. The bug is reported as fixed in 12.1

Update (August 2015)

The problem still reproduces in 12.1.0.2