RE: Spool file journalling

[jpcarr@xxxxxxxxxxxx]

>3. Why would what seems like a pretty simple syntax change cause such
>drastic difference in response time?


Joe

The first with a IN statement would be done in one pass,   doing
sortof a "Lookup" compare of the values in the list.


The second has an OR statement,   One way the optimizer would do this is to
create a bitmap of both sides of the OR predicate then XOR to a final
bitmap.
(Not saying that is what it did, just that it could have taken this
route)

The Second method would build three bitmap indexes on the fly, the first
None.

John

------------------------------------------------------
select * from sccc1 a, sccc1 b
 where a.c1flag3 = 'V' and
 a.c1source in ('WRITE', 'READ')
 and a.c1kwid = b.c1prnt
 and b.c1source in ('SCREEN-REC', 'SCREEN-FILE')

select * from sccc1 a, sccc1 b
 where a.c1flag3 = 'V' and
 ((a.c1source = 'WRITE'
   and a.c1kwid = b.c1prnt
   and b.c1source = 'SCREEN-REC') or
 (a.c1source = 'READ'
   and a.c1kwid = b.c1prnt
   and b.c1source = 'SCREEN-FILE'))