Problems with dynamic creation of Volatile tables

Database
Enthusiast

Problems with dynamic creation of Volatile tables

Guys,

Playing with some engine designs that require volatile tables structures based on existing tables structures on a system. The number of unique table structures is very high, so minimal customization should be required or it becomes a sustainability problem.

Very simple example: 

Take any existing table structure and create an empty Volatile table. Sounds simple:


CREATE VOLATILE TABLE myTemp AS SourceDb.SourceTable 


WITH NO DATA NO PRIMARY INDEX ON COMMIT PRESERVE ROWS;

Simple enough.

Here is the issue: On certain structures tht have named SIs on them, the above statement will fail:


CREATE VOLATILE TABLE myTemp AS SourceDb.SourceTable 


WITH NO DATA NO PRIMARY INDEX ON COMMIT PRESERVE ROWS;


Sep 1, 2012 7:37:28 AM groovy.sql.Sql eachRow


WARNING: Failed to execute: CREATE VOLATILE TABLE myTemp AS SourceDb.SourceTable 


WITH NO DATA NO PRIMARY INDEX ON COMMIT PRESERVE ROWS;


 because: [Teradata Database] [TeraJDBC 14.00.00.13] [Error 5339] [SQLState HY000] Index name not allowed for volatile table.

We dont need nor want any SIs on the Volatile table. Looked through all the Database documentation for 13.10 (only JDBC driver above is 14.x) could not find anything to prevent this.

Any ideas how do deal with that? 
16 REPLIES
Teradata Employee

Re: Problems with dynamic creation of Volatile tables

Since you are specifying NoPI (vs. trying to copy the existing PI):


CREATE VOLATILE TABLE myTemp AS (SELECT * FROM SourceDb.SourceTable)


WITH NO DATA NO PRIMARY INDEX ON COMMIT PRESERVE ROWS;

Enthusiast

Re: Problems with dynamic creation of Volatile tables

Love it when the simple solution right in front of your own eyes is making you go in cricles. 

Much appriciated!

Enthusiast

Re: Problems with dynamic creation of Volatile tables

I consider this to be a documentation issue (it could be an implementation issue - but we don't know).

The CT AS documentation for 13.10 is not at all clear about the behavior of indexes following the table creation. One could assume it does or does not. Example: what is the behavior for a table with a referencing Join Index?

It might well be in some other manual an an inherited behavior from CT SET AS [Tablename], but if INDEX Creation is going to follow the Table Reference in the default CT Volatile [name] AS [tablename], it should say that.  

Enthusiast

Re: Problems with dynamic creation of Volatile tables

And to add on to this the work around that is great for this one usecase, opens up another set of issues: What about the likes of Stats?

Creating a copy of an existing table without SIs but with existing Stats should be possible but this solution prevents stats from coming with the table definition. Same for compression and other features... 

Supporter

Re: Problems with dynamic creation of Volatile tables

just to play arround - not fully tested, no exception handling etc. maybe more index types to consider but a SP might become handy for this...

REPLACE PROCEDURE test_db_uli.p_create_tb_copy
(IN source_db VARCHAR(30),
IN source_tb VARCHAR(30),
IN target_db VARCHAR(30),
IN target_tb VARCHAR(30),
IN with_data CHAR(1),
IN WITH_STATS CHAR(1),
IN WITH_NPI CHAR(1),
OUT status VARCHAR(2000 )
)
BEGIN

DECLARE CNT SMALLINT;
DECLARE SQL_TEXT VARCHAR(10000);

SELECT COUNT(*) INTO :cnt
FROM dbc.indices
WHERE databasename = :source_db
AND tablename = :source_tb
AND columnposition = 1
AND indextype = 'S'
;

IF ( cnt = 0 )
THEN
BEGIN
SET SQL_TEXT = 'Create table ' !! target_db !! '.' !! target_tb !! ' as ' !! source_db !! '.' !! source_tb !! ' WITH ' !! CASE WHEN with_data = 'N' THEN ' NO ' ELSE ' ' END !! 'DATA ' !! CASE WHEN WITH_STATS = 'Y' THEN 'AND STATISTICS' ELSE '' END !! CASE WHEN WITH_NPI = 'Y' THEN 'NO PRIMARY INDEX' ELSE '' END ;
CALL DBC.SYSEXECSQL( :SQL_TEXT);
SET status = 'NO INDEX' !! SQL_TEXT;
END;
ELSE
BEGIN

SET SQL_TEXT = 'Create table ' !! target_db !! '.' !! target_tb !! ' as ' !! source_db !! '.' !! source_tb !! ' WITH NO DATA ' !! CASE WHEN WITH_STATS = 'Y' THEN 'AND STATISTICS' ELSE '' END !! CASE WHEN WITH_NPI = 'Y' THEN 'NO PRIMARY INDEX' ELSE '' END ;
CALL DBC.SYSEXECSQL( :SQL_TEXT);
SET status = 'NO INDEX' !! SQL_TEXT;

SET SQL_TEXT = 'delete from test_db_uli.gt_drop_index_stmt ';

CALL DBC.SYSEXECSQL( :SQL_TEXT);

SET SQL_TEXT = '
insert into test_db_uli.gt_drop_index_stmt
WITH RECURSIVE base (databasename, tablename, indexnumber, columnname, columnposition)
AS
(
SELECT databasename, tablename, indexnumber, CAST(columnname AS VARCHAR(1000)), columnposition
FROM DBC.Indices
WHERE columnposition = 1
AND INDEXTYPE = ''s''
UNION ALL
SELECT i.databasename,
i.tablename,
i.indexnumber,
b.columnname !! '','' !! TRIM(i.columnname),
i.columnposition
FROM DBC.Indices i
JOIN
base b
ON b.databasename = i.databasename
AND b.tablename = i.tablename
AND b.indexnumber = i.indexnumber
AND b.columnposition + 1 = i.columnposition
)
SELECT ''DROP INDEX ('' !! columnname !! '') ON '!! target_db !! '.' !! target_tb !! ';'' AS txt
FROM base
WHERE databasename = ''' !! source_db !! '''
AND tablename = ''' !! source_tb !! '''
QUALIFY columnposition = MAX(columnposition) OVER (PARTITION BY databasename, tablename, indexnumber)
';

CALL DBC.SYSEXECSQL( :SQL_TEXT);

FOR STCURSOR AS RESULTSET CURSOR FOR
SELECT TXT FROM test_db_uli.gt_drop_index_stmt

DO
BEGIN
SET SQL_TEXT = STCURSOR.txt;
CALL DBC.SYSEXECSQL( :SQL_TEXT);
END;

END FOR;

IF ( with_data = 'Y' )
THEN
BEGIN
SET SQL_TEXT = 'INSERT INTO '!! target_db !! '.' !! target_tb !! ' select * from ' !! source_db !! '.' !! source_tb;
CALL DBC.SYSEXECSQL( :SQL_TEXT);
END;
END IF;
SET status = 'with index ' !! SQL_TEXT;

END;

END IF;

END;

In case secondary indies exists the table will be created with no data option.

then the indices are droped and if with data was specified the table gets populated - durch die brust ins auge

Supporter

Re: Problems with dynamic creation of Volatile tables

Forgot the GT for the drop index statements...

CREATE GLOBAL TEMPORARY TABLE gt_drop_index_stmt ( txt VARCHAR(1000)) ON COMMIT PRESERVE ROWS;

Enthusiast

Re: Problems with dynamic creation of Volatile tables

Nice! Thanks a lot!

And I was hoping a single line CT VOLITABLE TABLE AS might do... ;-)

Was just rewriting that old TeraCompress tool from years ago, but with dynamic sampling - hence the Volatile tables. The first workaround works better in this case - even though not perfect, since it requires the least amount of code to be maintained. 

As Michael said the documentation definitelly needs to speel this out.

Enthusiast

Re: Problems with dynamic creation of Volatile tables

The forums software here needs a "Like" or "Thank You" button on posts. Would make search so much smarter when you can actually ranked liked responses higher... 

Sorry for the transgression - I could not help... ;-)

Teradata Employee

Re: Problems with dynamic creation of Volatile tables

@ratzesberger: it'll be coming soon... add any other items to the wishlist here:

http://forums.teradata.com/forum/general/wish-list-for-forum-development#comment-23180