Archives of the TeradataForum

Message Posted: Wed, 20 Jun 2012 @ 14:29:37 GMT

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

Q)

We know in TD 12 and below versions there is no option to collect stats on Volatile table.

But I heard we can collect stats on VT in TD 13 and above.

A)

Sugesh:

Syntaxes can be checked with below once you are on Database:

     help 'sql collect statistics';

And currently there is no option directly provided on VT as I see on 13.10.

     help 'sql collect statistics';

      *** Query completed. 93 rows found. One column returned.
      *** Total elapsed time was 1 second.

     On-Line Help
     ---------------------------------------------------------------------------

      /* OPTIMIZER FORM */

     { COLLECT STATISTICS }
     { COLLECT STATS      } [ USING SAMPLE ] [ ON ]
     { COLLECT STAT       }

       { [ TEMPORARY ] [ database_name. ] table_name }
       { [ database_name. ] join_index_name          }
       { [ database_name. ] hash_index_name          }

         [ COLUMN    { column_name }                                         ]
                     { PARTITION   }

         [ COLUMN   ({ column_name } [ ,PARTITION ] [ ..64,column_name  ]  ) ];
                    ({ PARTITION   }                [ ..64,column_name  ]  )

         [ INDEX    (  column_name   [..64,column_name                  ]  ) ]

         [ INDEX       index_name                                            ]

           [ FROM [TEMPORARY] tablename ]
             [{ index_description[ ...,index_description ]   }
              { column_description[ ...,column_description ] }];


        OR

      /* QCD FORM */

     {COLLECT STATISTICS}
     {COLLECT STATS     } { FOR SAMPLE   [PERCENT] INTO  }
     {COLLECT STAT      }

       [SET QUERY (query_id ) ][SAMPLEID (statistics_id)] [UPDATE MODIFIED][ON]

         [ database_name. ] table_name

           { COLUMN     { column_name }                                       }
                        { PARTITION   }

           { COLUMN   ( { column_name } [,PARTITION] [ ..64,column_name ]  )  };
                      ( { PARTITION   }              [ ..64,column_name ]  )

           { INDEX    ( column_name     [..64,column_name               ]   ) }

           { INDEX      index_name                                            }

         where
         Percentage
             The sample data size as a percentage of the total set of rows on
             a given AMP. It is a float value and the value must be less than 10

         QCDName
             Name of the database where QCF tables reside

        OR

      /* "CREATE INDEX" - style syntax */

     {COLLECT STATISTICS}
     {COLLECT STATS     } [ USING SAMPLE ]
     {COLLECT STAT      }

       { index_description[ ...,index_description ]   }    ON
       { column_description[ ...,column_description ] }

         { [ TEMPORARY ][ database_name. ] table_name  }
         { [ database_name. ] join_index_name          };
         { [ database_name. ] hash_index_name          }

             [ FROM [TEMPORARY] tablename ]
             [{ index_description[ ...,index_description ]   }
              { column_description[ ...,column_description ] }];

         where
           index_description:

             [ UNIQUE ] INDEX [ index_name ] [ ALL ]
             ( column_name1 [ ..64,column_name1 ] )
             [ ORDER BY [ VALUES | HASH ](column_name_2) ]

           column_description:

              COLUMN     { column_name }
                         { PARTITION   }
                         {({ column_name } [ ,PARTITION ] [ ..64,column_name  ])
                         {({ PARTITION   }                [ ..64,column_name  ])

              ALL is used for secondary indexes created on join indexes only.

Thanks

Asif - TD

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