Archives of the TeradataForum

Message Posted: Tue, 05 Jun 2001 @ 19:14:04 GMT

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

Hi Ruth,

i would expect an explain like that:

FTS on the Index-Subtable with a condition of xx <= May 23 and xx >= May 21 extracting rowids only in Spool x and then sorting that spool. In the next step it's retrieving the rows using the rowids.

If you use an value ordered NUSI, the only difference is a range constraint accessing the NULI-Subtable (and the optimizer usually switches to a FTS on the base table at a larger number of records).

So, it's definitly supposed to use that index until it expects maybe 1 to 5 percent of all the records in your answer set. You're extracting only 3 days of ~600, there must be something else wrong??? What's the number of expected rows in the explain. Try selecting 2 days instead of 3.

There's no difference between BETWEEN and >= AND <=, just look at the explain, it's _never_ showing a BETWEEN. It's only recommended NOT to use BETWEEN on the Primary Index. This will result in a FTS, because the optimizer is a smart one, but not in this case: It's not able to modify BETWEEN 1 AND 3 to IN (1,2,3) and even worse, try an explain on BETWEEN 1 and 1 ;-)

So, use IN instead (if it's possible), you may have a lot of PI-steps before it switches to a FTS.

Dieter

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