When reading data from a relational database, SAS might pass an inefficient query to the database. This behavior occurs when the following conditions are true:

• The SAS library is allocated using the metadata LIBNAME statement with the METAOUT=DATA option.
• The table being queried is not registered in the SAS^® Metadata Repository.

For example, given the following query for the ITEM column when the SALE table is not registered in the SAS Metadata Repository:

libname mylib meta library='Oracle Library' metaout=data;

   /* SALE is not registered in metadata */
proc sql;
   select item
   from mylib.SALE;
quit;

SAS passes SELECT * FROM "SCOTT"."SALE" to the database, as demonstrated by this output from the SASTRACE= option:

0 1595772025 no_name 0 SQL
ORACLE_1: Prepared: 1 1595772025 no_name 0 SQL
SELECT * FROM "SCOTT"."SALE" 2 1595772025 no_name 0 SQL
3 1595772025 no_name 0 SQL
4 1595772025 no_name 0 SQL
ORACLE_2: Executed: 5 1595772025 no_name 0 SQL
SELECT statement ORACLE_1 6 1595772025 no_name 0 SQL
7 1595772025 no_name 0 SQL

The same inefficient query is passed to the database when you use a SET statement with the KEEP= data set option in a DATA step, as in this example:

data work.sale_item;
   set mylib.SALE(keep=ITEM);
run;

SAS should pass a concise query to the database, requesting only the columns that are selected. This more efficient query occurs when you query a table that is registered in the SAS Metadata Repository, as shown in the following examples:

libname mylib meta library='Oracle Library' metaout=data;

   /* CLASS is registered in metadata */
proc sql;
   select name
   from mylib.CLASS;
quit;

data work.class_name;
   set mylib.CLASS(keep=NAME);
run;

SAS passes SELECT "NAME" FROM "SCOTT"."CLASS" to the database, as demonstrated by this SASTRACE= output:

8 1595772025 no_name 0 SQL
ORACLE_3: Prepared: 9 1595772025 no_name 0 SQL
SELECT * FROM "SCOTT"."CLASS" 10 1595772025 no_name 0 SQL
11 1595772025 no_name 0 SQL
12 1595772025 no_name 0 SQL
ORACLE_4: Prepared: 13 1595772025 no_name 0 SQL
SELECT "NAME" FROM "SCOTT"."CLASS" 14 1595772025 no_name 0 SQL
15 1595772025 no_name 0 SQL
16 1595772025 no_name 0 SQL
ORACLE_5: Executed: 17 1595772025 no_name 0 SQL
SELECT statement ORACLE_4 18 1595772025 no_name 0 SQL
19 1595772025 no_name 0 SQL

Operating System and Release Information