Volatile Table question

Database
Enthusiast

Volatile Table question

Hi,I have few questions about VT

1. is it possible to check space and skew factor of a voilatile table just like a physical table? HELP VT does nto provide this. 

2. Does Teradata allows to collect stats on VT, it does not throw any error while collect stats but wondering does it really help.

3. Somestimes i lose my Volatile tables though i dont drop them and i do use "on commit poreserve rows", any reason that could happen?

 

Thanks 

7 REPLIES
Senior Supporter

Re: Volatile Table question

1. not on table level. But you can check your spool space - if no SQL is running. Will be the sum of all existing VT.

select * from dbc.allspaceV where databasename = user

2. yes it has impact

create volatile table vt_test as (select * from sys_calendar.calendar where day_of _Calendar <= 1000) With data primary index (calendar_date) on commit preserve rows;

create volatile table vt_test2 as (select * from sys_calendar.calendar where day_of _Calendar <= 1000) With data primary index (day_of _calendar) on commit preserve rows;

explain select * from vt_test c join vt_test2 t on c.month_of_year = t.month_of_year;

result in

is built locally on the AMPs. The size of Spool 1 is estimated
with no confidence to be 31,623 rows (4,458,843 bytes). The

 

collect stats on vt_test column (month_of _year);
collect stats on vt_test2 column (month_of _year);

explain select * from vt_test c join vt_test2 t on c.month_of_year = t.month_of_year;

result in 

is built locally on the AMPs. The size of Spool 1 is estimated
with low confidence to be 83,334 rows (11,750,094 bytes). The

 

so there is new info available for the Optimizer...

 

3. Do you use SQLA? And run multiple SQL at the same time? In this case you have also multiple sessions and they don't share the VT...

Enthusiast

Re: Volatile Table question

Thanks, i dont think below query is giving VT info.

select * from dbc.allspaceV where databasename = user

 yes i use SQLA and run multiple sessions in the same window and i am able to us VT down the line if i create a VT in the step before but only some times i find Vt is not available. 

Senior Supporter

Re: Volatile Table question

If I run no SQL I get with the following query

select vproc, currentSpool from dbc.allspaceV where databasename = user order by 1

Vproc CurrentSpool
0 0.
1 0.
2 0.
3 0.
4 0.
5 0.
6 0.
7 0.

 Now create a skewed VT by

create volatile table vt_test as (select * from sys_calendar.calendar where day_of_Calendar <= 1000) With data primary index (year_of_calendar) on commit preserve rows;

and check the spool again - while no otheres queries running from you...

select vproc, currentSpool from dbc.allspaceV where databasename = user order by 1

Vproc CurrentSpool
0 1024.
1 22016.
2 1024.
3 29184.
4 1024.
5 29184.
6 1024.
7 1024.

 

so there is spool used by the VT and you can see that it is skewed...

 

On the SQLA - Running multiple SQL via SQLA can result in what you observe... You can always check via a 

select session;

which session you currently have - check DBQL to get the session which created the VT and check if they match...

 

 

 

 

Teradata Employee

Re: Volatile Table question

This is documented behavior for SQL Assistant. If you execute a request from a tab while the main session for that window is already in use, SQL Assistant will temporarily connect a new session just to run the additional request (and then disconnect the additional session when it becomes idle). So when using volatile tables or setting session-specific options, you should run only one request at a time per query window. Consider opening multiple query windows rather than using multiple tabs.

 

And just an observation: Typically I find it clearer to use DBC.DiskSpaceV (or DBC.TableSpaceV for table PermSpace) instead of DBC.AllSpaceV.

 

Enthusiast

Re: Volatile Table question

in my case i am not running multiple queries at the same time but i am runing the 2nd qry only after the 1st qry is finished but on the same tab. most of my work depends on the result set of the previous VT executed and so i have to run them in the same session.

Teradata Employee

Re: Volatile Table question

I am not very certain if Space and Skew Factor for a VT can be determined just like for a physical table. But if you have to find it, then you have the option to materialise it into a physical table.

Senior Apprentice

Re: Volatile Table question

It depends on exact circumstances but you can often get Space and Skew Factor for VT's by looking at the user's spool space usage.

 

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com