My Oracle Support Banner

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

To view full details, sign in with your My Oracle Support account.

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


In this Document
Symptoms
Cause
Solution


This document is being delivered to you via Oracle Support's Rapid Visibility (RaV) process and therefore has not been subject to an independent technical review.
My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.