Equivalent SQL to a view

From: Russell Brooks <russell.brooks_at_amctechnology.com>
Date: Wed, 16 Aug 2000 09:39:31 -0400
Message-Id: <10591.114720@fatcity.com>

Hi,
  We have a view, indices, and a select statement in the form:

create view ztest
(field1, field2, field3, field4)

as select
t1.field1, t1.field2, t1.field3, t1.field4 from
table1 t1, table2 t2
where

t1.field1 = t2.field1 and
t1.field2 = t2.field2 and
t1.field3 = t2.field3 and
t1.field5 <> 'something' and
t2.field6 <> 'else'

/

table1 index	table2 index
field1		field1
field2		field2
field3		field3

select * from ztest
where (field1 = 'a' and field2 = 'b' and field3 = 'c') or
(field1 = '1' and field2 = '2' and field3 = '3')
/

I was under the mistaken impression that this select statement is equivalent to:

select
t1.field1, t1.field2, t1.field3, t1.field4 from
table1 t1, table2 t2
where

t1.field1 = t2.field1 and
t1.field2 = t2.field2 and
t1.field3 = t2.field3 and
t1.field5 <> 'something' and
t2.field6 <> 'else' and

(t1.field1 = 'a' and t1.field2 = 'b' and t1.field3 = 'c') or
(t1.field1 = '1' and t1.field2 = '2' and t1.field3 = '3')
/

An explain plan indicates clearly that this is not the case. What would the equivalent statement look like? Received on Wed Aug 16 2000 - 08:39:31 CDT