Archives of the TeradataForum

Message Posted: Tue, 05 Jun 2001 @ 14:08:21 GMT

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

Does anybody have experience with how the optimizer evaluates BETWEEN?

I have a large table, 921 million rows. Table has a NUSI on it of date, with about 20 months worth of daily data. There are current statistics on the table.

If a user writes a query using a between as in scan_date between May 19 and May 21, Teradata will do a table scan, if it's written as scan_date = may 19 or scan_date = may 20 or scan_date = may 21, Teradata uses the index.

If it's written as scan_date >= May 19 and Scan_date <= may 21, Teradata does a full table scan

If it's written as scan_date in (may19, may 20, may 21), Teradata uses the index

So what does everyone do? Do you just recommend that we never user BETWEEN?

Ruth Fenwick
Teradata Certified Professional
Hudson's Bay Company

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