Archives of the TeradataForum

Message Posted: Wed, 02 Feb 2005 @ 20:20:01 GMT


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

Subj:		Secondary Index on Volatile tables

From:		Anomy Anom

<-- Anonymously Posted: Wednesday, February 02, 2005 12:48 -->

Hi All,

I have been told that one could not create indexes on volatile tables and the manual also says that:

     SQL Reference

     Data Definition Statements

     Release V2R5.1

     Teradata Database


     Page 1-172

It states "You cannot create indexes on a volatile table."

We are on V2R5.1.9

However, the DDL below works:

     CREATE SET VOLATILE TABLE vt_tmp ,NO FALLBACK ,
          CHECKSUM = DEFAULT,
          NO LOG
          (
           X_id VARCHAR(15) CHARACTER SET LATIN NOT CASESPECIFIC,
           SI1 VARCHAR(15) CHARACTER SET LATIN NOT CASESPECIFIC,
           SI2 VARCHAR(15) CHARACTER SET LATIN NOT CASESPECIFIC,
           Col1 VARCHAR(15) CHARACTER SET LATIN NOT CASESPECIFIC)
     PRIMARY INDEX ( X_id )
     INDEX ( SI1 )
     INDEX ( SI2 )
      ON COMMIT PRESERVE ROWS;

`Explanation --------------------------------------------------`
	`1)`	`First, we create the table header.`
	`2)`	`Next, we create the index subtable on vt_tmp.`
	`3)`	`We modify the table header vt_tmp.`
	`4)`	`We create the index subtable on vt_tmp.`
	`5)`	`We modify the table header vt_tmp.`
	`6)`	`Finally, we send out an END TRANSACTION step to all AMPs involved in processing the request.`
	`->`	`No rows are returned to the user as the result of statement 1.`

The manual obviously does not show how to drop or create an index on an existing volatile table and the normal syntax does not work.

Questions:

1. Can you drop an index on an existing volatile table, and if so how?

2. Can you create an index on an existing volatile table, and if so how?

3. Stats can't be collected but I thought stats on SI's were important?

4. Is this an undocumented feature?

I don't believe you could do this in the past and I was interested on any feedback you may have.

Thanks in advance.


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

Attachments

Library

Quick Reference