Home Page for the TeradataForum
 

Archives of the TeradataForum

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


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


Subj:   Re: TeraData's optimizer
 
From:   Dieter Nöth

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>>  
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 27 Dec 2016