Home Page for the TeradataForum

Archives of the TeradataForum

Message Posted: Thu, 03 Feb 2005 @ 22:16:59 GMT

  <Prev Next>   <<First <Prev

Subj:   Re: Secondary Index on Volatile tables
From:   Michael Larkins

Hi All:

Life is really worth living when you learn something new! I stand corrected and this was a great thread because I had been told that SPOOL would not support secondary index definitions. Either this was true and has changed, or is was never true.

I created a volatile table with a USI and sure enough, the EXPLAIN comes back as a two-AMP operation. So, a USI can be a good thing to use with a volatile table as long as you have sufficient SPOOL to contain both the base rows and the index rows.

With that being said, one posting to this thread indicated that statistics could not be collected. Assuming that this is a true statement, that would seem to indicate little reason to create a NUSI definition for a volatile table because I have also been told that the optimizer will not use a NUSI without statistics and that is my experience even with real tables.

Thanks for teaching an Old Dog a new trick - I love it.

Best regards,

Michael Larkins
Certified Teradata Master
Certified Teradata SQL Instructor

  <Prev Next>   <<First <Prev
  Top Home Privacy Feedback  
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 15 Jun 2023