My colleague found that some queries on Teradata are improved by Short-circuit evaluation. This is common knowledge among software engineers, but DBA may not know it. I knew it short circuit, but I didn't know it is effective to sql.
For example, following query seems not bad and you may think everything is ok. (Please forget 'like any' since it is rewritten internally)
select
*
from t1
where
c1 like '%a0001%'
or c1 like '%a0002%'
or c1 like '%a0003%'
...
or c1 like '%a9999%'
;
;
By using short circuit, this query can be rewritten like this.
select
*
from t1
where
c1 like '%a%' and
(
c1 like '%a0001%'
or c1 like '%a0002%'
;
*
from t1
where
c1 like '%a%' and
(
c1 like '%a0001%'
or c1 like '%a0002%'
or c1 like '%a0003%'
...
or c1 like '%a9999%'
);
Of course, this rewrite isn't effective for all situations. It depends on the data characteristic. If all records of t1 contains a, it will be slower than before. In our case, the rewrite works well and it became 10-15x faster. Also, you need to know teradata optimizer can reorder evaluations. It means left most operation (c1 like '%a%') may be operated after right (c1 like '%a0001%' ... or c1 like '%a9999%') if optimizer judge it is the fastest order.