Skew issue

Database
Enthusiast

Skew issue

Hi Friends...

CT dbname.tablename as sel * from dbname1.tablename1 with data;

tablename1 is 70 gb ,and dbname has 110 available space. Query retuned with spool space error, during MERGE step of EXPLAIN.
I have checked row distrubution of source table using HASH function query and found that data is well equally distributed among AMPS which hints that the issue is with target database and its existing tables distribution.
I need to know ,what other steps/query/scripts are advisable to pinpoint the problem.
Tags (1)
7 REPLIES
Teradata Employee

Re: Skew issue

Note that "CREATE AS SELECT" is creating the table from a spool file (resulting from the SELECT), not from the source table. Most table attributes are not carried forward; in particular, the system assigns a default Primary Index: first column alone as NUPI.

If you mean to copy both structure and entire content, use the form:
CT dbname.tablename as dbname1.tablename1 with data;
which will copy many (though not all) attributes.

Otherwise explicitly specify PI:
CT dbname.tablename as (sel ... from dbname1.tablename1 ...) with data PRIMARY INDEX (...);
If FORMAT, DEFAULT, COMPRESS, etc. attributes are important, you will need to explicitly specify those in the SELECT list, in addition to specifying PI.

Enthusiast

Re: Skew issue

Thanks for the info,in fact I used method suggested by you (
CT dbname.tablename as dbname1.tablename1 with data;)
at the 1st place.
But the issue remains unanswered.Can anyonethrow some light on skewing?
Senior Apprentice

Re: Skew issue

It was answered by Fred:
"in particular, the system assigns a default Primary Index: first column alone as NUPI."

Just run that HASHAMP query you used to check the distribution of the source table again using the first column of that table instead of the PI.

But if you mean that it still fails then your target db is skewed, check permspace per AMP using dbc.diskspace.
But this is also answered by "bad PI on the first column because of missing PI-definition" :-)

Dieter
Enthusiast

Re: Skew issue

hello Dieter

Thanks for your valued suggestion.Here are few sharable points:
1. Source database and target dabase is same, only table name is changing.
2. Database permspace is almost even across all AMPs.
3. As mentioned in my earlier note, source table is 76 GB and the database have around 110 GB free space.
Despite that spool space error resulted.
On chosing another dataase ,with greater free space, the CT was a success.
Any thoughts on such an issue?
Senior Apprentice

Re: Skew issue

select a.vproc, needed, available - needed
from
(
select vproc, currentperm as needed
from dbc.tablesize where databasename = 'newdb' and tablename = 'tablename1'
) as a
join
(
select vproc, maxperm - currentperm as available
from dbc.diskspace where databasename = 'dbname1'
) as b
on a.vproc = b.vproc
order by 3;

Dieter

Re: Skew issue

Hi dnoeth,

                i like to find the current load to terada,

and i need a query to find the data skewness and wich amps get filled.

Re: Skew issue

Hi dnoeth,

                i like to find the current load to teradata,

and i need a query to find the data skewness and wich amps get filled.