Wrong result/Query creash on evaopn2/evaopn3 when _replace_virtual_columns is FALSE (Doc ID 1537939.1)

Last updated on MARCH 27, 2014

Applies to:

Oracle Database - Enterprise Edition - Version 10.2.0.4 and later
Information in this document applies to any platform.

Symptoms

 

The following query crashes in evaopn3() when _replace_virtual_columns
is set to FALSE.

create table t1 (c11 varchar(5), c12 varchar(5), c13 number);
create index t1i1 on t1 (upper(c11));
create index t1i2 on t1 (upper(c12));
create table t2 (c21 varchar(5), c22 varchar(5), c23 number);
create index t2i1 on t2 (c23, c21, c22);

insert into t1 values ('AAAAA','BBBBB',9);
insert into t2 values ('AAAAA','BBBBB',9);
commit;
begin
dbms_stats.gather_table_stats(user,'t1');
dbms_stats.gather_table_stats(user,'t2');
end;
/

alter session set "_replace_virtual_columns" = false;

-- this query crashes
select /*+ index_combine(t1 t1i1 t1i2) */
t1.c11
from t1 t1,
t2 t2
where t1.c13 = t2.c23
and t2.c21 = 'AAAAA'
and t2.c22 = 'BBBBB'
and upper(t1.c11) = (t2.c21)
and upper(t1.c12) = (t2.c22);

An equivalent query with the predicates re-ordered works because
the optimizer picks different predicates for the access and filter
keys (as you can see from explain plan):

-- this query works
select /*+ index_combine(t1 t1i1 t1i2) */
t1.c11
from t1 t1,
t2 t2
where upper(t1.c11) = (t2.c21)
and upper(t1.c12) = (t2.c22)
and t1.c13 = t2.c23
and t2.c21 = 'AAAAA'
and t2.c22 = 'BBBBB';

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms