PRIMARY INDEX ON VOLATILE Tables cause SPOOL SPACE ERRORS

Database

PRIMARY INDEX ON VOLATILE Tables cause SPOOL SPACE ERRORS

I have found that when I create a volatile table with say 200million rows where the primary index is explicitely stated and matches the first column in the table, I use much more spool space than if I just let Teradata defined the primary index.   Should, you define a primary index on a volatile table if the index is only on the first column in the temp table?   I have to say it greatly slows down response time and usually causes me spool space errors - so I have begun leaving out the primary index statement on temp tables if it is just the first column in the table and even sometimes when it is more, due to the spool space issues.   Anyone have insight on this?

1 REPLY
Teradata Employee

Re: PRIMARY INDEX ON VOLATILE Tables cause SPOOL SPACE ERRORS

Very likely your system is configured to default not to first column alone as  PI, as in earlier releases, but rather to NO PRIMARY INDEX. Do a SHOW TABLE after creating your volatile table to verify. (Or if you have access to dbscontrol, you can check PrimaryIndexDefault setting.)

If you can specify a "good PI", that would be preferred. Otherwise NoPI may be a reasonable choice. Even if your system is configured to default PI to the first column, you can explicitly define NO PRIMARY INDEX when you create a table.