Archives of the TeradataForum

Message Posted: Fri, 25 Apr 2003 @ 09:30:01 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

I've got a query that has predicates on two indicator columns ... ideal for NUSI bit mapping, one would have thought.

If I code it with an AND between the two columns then the explain happily shows that bit mapping will take effect, but if I code an OR (as is the actual requirement) it does not use bit mapping.

Can anyone tell me whether bit mapping occurs for only AND conditions or whether (in theory) it could apply to both AND and OR conditions?

This is what I've tried

Where
oppc.CON_DELIV_IN = 'N'
OR  oppc.CON_LATE2_IN = 'Y'
(this being the business requirement)
does not trigger bit mapping
----------------------------------------------------
And (oppc.CON_DELIV_IN = 'N'
  AND  oppc.CON_LATE2_IN = 'Y')

does trigger bit mapping
----------------------------------------------------
 NOT (oppc.CON_DELIV_IN = 'Y'
  AND  oppc.CON_LATE2_IN = 'N')

does not trigger bit mapping
----------------------------------------------------
(oppc.CON_DELIV_IN = 'Y'
  AND  oppc.CON_LATE2_IN = 'N')
OR
(oppc.CON_DELIV_IN = 'Y'
  AND  oppc.CON_LATE2_IN = 'Y')
OR
(oppc.CON_DELIV_IN = 'N'
  AND  oppc.CON_LATE2_IN = 'N')

does not trigger bit mapping
----------------------------------------------------

(thanks for the comments about Stats on Join indexes, by the way)

Dave Clough
Database Designer

	<Prev	Next>		<<First	<Prev	Next>	Last>>