Teradata Identity Columns and CREATE TABLE AS
Facing hurdles with the Teradata Identity columns and Volatile Tables? Buckle up because we’re about to overcome this challenge. Despite the inherent limitation, there’s a clever workaround that lets you leverage the ‘CREATE TABLE AS’ statement to generate Volatile Tables, even with Identity columns. So, stick around as we unpack this ingenious solution together!
The Test Setup
Let’s dive in and start with our experimental setup. This will provide a firm groundwork for understanding the solution:
CREATE MULTISET TABLE DB.IDENTITY_TABLE (
PK INTEGER GENERATED BY DEFAULT AS IDENTITY
(START WITH 1
INCREMENT BY 1
MAXVALUE 1000000
),
VAL INTEGER) PRIMARY INDEX (PK);INSERT INTO DB.IDENTITY_TABLE (PK,VAL) VALUES (NULL,1);To begin, we’ll establish a basic test table with an Identity column and insert a single row. This minimal setup will perfectly demonstrate our workaround when it comes to employing the ‘CREATE TABLE AS’ statement in conjunction with an Identity column.
If we proceed to replicate this table utilizing the ‘CREATE TABLE AS’ statement, we’re met with an error:
CREATE VOLATILE TABLE COPY_OF_IDENTITY_TABLE
AS
(
SELECT PK,VAL
FROM DB.IDENTITY_TABLE
) WITH DATA PRIMARY INDEX (PK) ON COMMIT PRESERVE ROWS;CREATE TABLE Failed. [5788] CREATE TABLE AS currently does not support Identity Columns.Fortunately, a simple yet effective workaround to this issue is to conceal the Identity column by enveloping the logic within a subquery. The following example illustrates this approach:
CREATE VOLATILE TABLE COPY_OF_IDENTITY_TABLE
AS
(
SELECT PK,VAL FROM (
SELECT PK,VAL
FROM DB.IDENTITY_TABLE
) t01
) WITH DATA PRIMARY INDEX (PK) ON COMMIT PRESERVE ROWS;CREATE TABLE completed. 0 rows processed. Elapsed Time = 00:00:01As demonstrated, this enhanced SQL statement enables us to leverage the ‘CREATE TABLE AS’ statement effectively, even when the source table incorporates a Teradata Identity column.
Should you have concerns that this workaround might introduce performance overhead, rest assured there is absolutely no disparity in the execution plan. Thanks to the intelligence of the Optimizer, it recognizes that the subquery can be optimized effectively. To illustrate this, let’s compare the execution plans of both queries. As you will observe, they are identical, affirming that the workaround is efficient:
Execution Plan of the Original Query
EXPLAIN
SELECT PK,VAL FROM DB.IDENTITY_TABLE;
1) First, we lock DB.IDENTITY_TABLE in TD_MAP1 for read on a
reserved RowHash to prevent global deadlock.
2) Next, we lock DB.IDENTITY_TABLE in TD_MAP1 for read.
3) We do an all-AMPs RETRIEVE step in TD_MAP1 from
DB.IDENTITY_TABLE by way of an all-rows scan with no residual conditions into Spool 1 (group_amps), which is built locally on the AMPs. The size of Spool 1 is estimated with low confidence to be 216 rows (6,264 bytes). The estimated time for this step is 0.02 seconds.
-> The contents of Spool 1 are sent back to the user as the result of statement 1. The total estimated time is 0.02 seconds.Execution Plan of the Modified Query
EXPLAIN
SELECT PK,VAL FROM (SELECT PK,VAL FROM DB.IDENTITY_TABLE ) t01
1) First, we lock DB.IDENTITY_TABLE in TD_MAP1 for read on a
reserved RowHash to prevent global deadlock.
2) Next, we lock DB.IDENTITY_TABLE in TD_MAP1 for read.
3) We do an all-AMPs RETRIEVE step in TD_MAP1 from
DB.IDENTITY_TABLE by way of an all-rows scan with no residual conditions into Spool 1 (group_amps), which is built locally on the AMPs. The size of Spool 1 is estimated with low confidence to be 216 rows (6,264 bytes). The estimated time for this step is 0.02 seconds.
-> The contents of Spool 1 are sent back to the user as the result of statement 1. The total estimated time is 0.02 seconds.
Conclusion
In conclusion, the challenge of creating Volatile Tables from tables with Identity columns in Teradata may seem daunting, but as we’ve explored, it’s far from insurmountable. With ingenuity, we’ve successfully utilized a simple subquery to hide the Identity column, effectively allowing us to use the ‘CREATE TABLE AS’ statement.
Moreover, we’ve dispelled any concerns regarding potential performance overhead. By comparing the execution plans of our original and modified queries, we found them to be identical, proving the efficiency of our workaround.
So, the next time you encounter this common Teradata hurdle, remember this clever strategy. It’s another example of how creativity can help us navigate and overcome even the most complex technical challenges. Happy querying!
For more insights on Identity columns, refer to the following article:
https://dbmstutorials.com/random_teradata/teradata-identity-column.html