Archives of the TeradataForum

Message Posted: Mon, 17 Oct 2006 @ 00:11:11 GMT

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

Thanks for the post John!

We do DELETE ALL (truncate on the table).

The issue isn't so much that I am having problems with performance and batch load windows (Although I am).

I just don't want to have to take the performance hit of cloning statistics. It seems silly.

Apparently this is a feature with new offered with V2r6.2 as an extension of the "CREATE TABLE AS" DDL statement.

In v2r6.2 This is what you can do as far as I understand from discussions (and given examples) at Partners 2006.

Creating a table with data:

     -- CREATE TABLE tblA as tblB WITH DATA WITH STATISTICS;

(syntax?) This will create a table from tblA with identical statistics (no more manual recollect).

Creating a table with NO data:

     -- CREATE TABLE tblA as tblB WITH NO DATA WITH STATISTICS;

(syntax?) This will create a table from tblA with ZEROED statistics. This can be useful.

I want to do the following.

CREATE TABLE tblA as tblB with NO DATA WITH STATISTICS. However, The POPULATED statistics would be those of tblA, not the ZEROED statistics as given in the above example.

V2r6.2 does not offer that as far as I can tell. But if WITH STATS function works as scenario #2 below It would be reasonable to do this:

As you would only tax the system for the merge and the temp space needed and NOT for the recollect.

For the Teradata folks .. A question.

Now, I am not sure how v2r6.2 will function internally I assume it will be one of the following scenarios.

1.) Does it just generate the collect statements and run them as part of the process.

2.) Does it manage this as an internal process and generate the data derived from the system tables (DBC.TVFields.FieldStatistics & DBC.Indexes.IndexStatistics) and create entries for the new tables kind of like a COPY functionality.

IMHO, it should work like Option 2, or something like that. Mainly because the data already exists in the system tables,. Why tax the system heavily to recreate stats on a copy of a table.

My question is.

Is there a crude or 'creative' way of doing this the following for us lowly v2r6.1 and below users:

     CREATE TABLE tblA as tblB with NO DATA WITH STATISTICS.

However, The POPULATED statistics would be those of TBLA, not the ZEROED statistics as given in the above example.

Everyone's thoughts are greatly appreciated.

Regards,

Eric Barner

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