Can SAS code create an index on a volatile table in Teradata?

[ScottBass]

Hi,

In this code:

%let server=yourserver;
%let authdomain=TeraDevAuth;

options sastrace=',,,d' sastraceloc=saslog;

/* Set global connection for all tables. */
libname x  teradata server=&server authdomain=&authdomain connection=global;

/* Create a volatile table. */
proc sql;
   connect to teradata(server=&server authdomain=&authdomain connection=global);
   execute (CREATE VOLATILE TABLE temp1 (col1 INT) 
            ON COMMIT PRESERVE ROWS) by teradata;
   execute (COMMIT WORK) by teradata;
quit;

/* Insert 1 row into the volatile table. */
proc sql;
   connect to teradata(server=&server authdomain=&authdomain connection=global);
   execute (INSERT INTO temp1 VALUES(1)) by teradata;
   execute (COMMIT WORK) by teradata;
quit;

/* Access the temporary table through the global libref. */
proc print data=x.temp1;
run;

/* Create a volatile table with an index. */
proc sql;
   connect to teradata(server=&server authdomain=&authdomain connection=global);
   execute (CREATE VOLATILE TABLE temp2 (col1 INT) 
            UNIQUE PRIMARY INDEX (col1)
            ON COMMIT PRESERVE ROWS) by teradata;
   execute (COMMIT WORK) by teradata;
quit;

/* Insert 1 row into the volatile table. */
proc sql;
   connect to teradata(server=&server authdomain=&authdomain connection=global);
   execute (INSERT INTO temp2 VALUES(2)) by teradata;
   execute (COMMIT WORK) by teradata;
quit;

/* Access the temporary table through the global libref. */
proc print data=x.temp2;
run;

/* Let SAS do the work of creating the volatile table */
libname x  teradata server=&server authdomain=&authdomain connection=global dbmstemp=yes;

data x.temp3;
   col1=3;
run;

/* Access the temporary table through the global libref. */
proc print data=x.temp3;
run;

/* A more realistic example */
data x.table_with_sk;
   sk+1;
   set sashelp.cars;
run;

proc print data=x.table_with_sk;
run;

The 2nd example is explicitly defining the volatile table (VT), including the index definition.  In the 3rd example, I let SAS do the work of creating the VT table definition.  See the trace output in the log.

But, in the 3rd example, is there a way to create an index on the VT?

From the trace output, It appears to me that the teradata engine is intercepting/parsing the SAS code and translating that into teradata SQL.  If so, then couldn't the parser/engine do something with:

data x.temp3 (index=(col1));
   col1=3;
run;

And translate that to (edited sastrace exerpt):

TERADATA_8: Executed: on connection 2 41 1463718006 no_name 0 DATASTEP
CREATE  VOLATILE MULTISET TABLE "temp3" ("col1" FLOAT) UNIQUE PRIMARY INDEX (col1) ON COMMIT 
PRESERVE ROWS ;COMMIT WORK 42 1463718006 no_name 0 DATASTEP

Regardless of whether I think it should work this way, if there is a way to get SAS to create the table definition per the 3rd example, and also create an index(es), please let me know.

[ChrisNZ]

>if there is a way to get SAS to create the table definition per the 3rd example

I don't think so. Having ot use explicit SQL pass-through to modify internal database organisation or optimisation (such as index management) does not seem like an excessive burden to me. So while having this feature might be an improvement for some, it may be unneeded for most, but as usual ummv.

One thing that's odd: don't you get a meaningful LOG message when you attempt this? Here is what is displayed in the LOG if you try with Oracle.

data ORA.IP_ID1(index=(IP_ID)); 
                -----
                76

WARNING 76-63: The option INDEX is not implemented in the ORACLE engine.