General SQL Planner Options :: SAS® Scalable Performance Data Server 5.3: User's Guide

_method

A method tree is produced in your output if you specify the _method reset option for the SQL Planner. You read the SQL Planner method tree from bottom row to top row. The _method reset option writes a method tree in the output that shows how the SQL was planned and executed.

The following methods are displayed in the SQL _method tree:

sqxcrta

Create table as Select.

sqxslct

Select rows from table.

sqxjsl

Step loop join (Cartesian join).

sqxjm

Merge join execution.

sqxjndx

Index join execution.

sqxjhsh

Hash join execution.

sqxsort

Sort table or rows.

sqxsrc

Read rows from source.

sqxfil

Filter rows from table.

sqxsumg

Summary statistics (with GROUP BY).

sqxsumn

Summary statistics (not grouped).

sqxuniq

Distinct rows only.

sqxstj

STARJOIN

sqxxpgb

Parallel group-by

sqxxpjn

Parallel join with group-by. The SAS log displays the name of the parallel join method that was used.

sqxpll

Parallel join without group-by

Example: Reading the Method Tree

The following example shows how to interpret the method tree by substituting the type of method that was used in each step.

proc sql ;
create table tbl1 as
 select *
  from path1.dansjunk1 a,
   path1.dansjunk2 b,
   path1.dansjunk3 c
  where a.i = b.i
   and a.i = c.i ;
quit ;

The following example method tree is printed:

SPDS_NOTE: SQL execution methods chosen are:
<0x00000001006BBD78> sqxslct
<0x00000001006BBBF8>     sqxjm
<0x00000001006BBB38>               sqxsort
<0x0000000100691058>                   sqxsrc
<0x0000000100667280>               sqxjm
<0x0000000100666C50>                       sqxsort
<0x0000000100690BD8>                           sqxsrc
<0x00000001006AE600>                       sqxsort
<0x0000000100694748>                           sqxsrc

You can review the sequence of methods that were invoked by reading the tree from bottom to top.

SPDS_NOTE: SQL execution methods chosen are:
<0x00000001006BBD78> step 9
<0x00000001006BBBF8>     step 8
<0x00000001006BBB38>               step 7
<0x0000000100691058>                    step 6
<0x0000000100667280>                 step 5
<0x0000000100666C50>                        step 4
<0x0000000100690BD8>                            step 3
<0x00000001006AE600>                       step 2
<0x0000000100694748>                              step 1

In step 1, sqxsrc reads rows from the source. In step 2, sqxsort sorts the table rows. Then in steps 3 and 4, more rows are read and sorted. In step 5, the tables are joined by sqxjm, and so on.

BUFFERSIZE=

The server query optimizer considers a number of join strategies. Some of the join strategies require memory buffers. In these cases, BUFFERSIZE= specifies the amount of memory that the server should reserve for memory buffers.

For example, the server SQL might consider a hash join when an index join is not possible. A hash join reconfigures the smaller table in memory as a hash table. SQL sequentially scans the larger table and performs a hash lookup row-by-row against the smaller table to form the result set. On a memory-rich system, consider increasing the BUFFERSIZE= option to increase the likelihood that a hash join is chosen. The default BUFFERSIZE= setting is 64 K. You can specify the amount of memory that you want the server to use for hash joins.

Usage:

/* Increase buffersize from 64K   */
execute(reset 
  buffersize=n)
by sasspds ;

n

the maximum number of rows in the smaller table for a hash join that can use the inset size hash join optimization.

DETAILS=

Use the DETAILS= reset switch to provide additional information in the SAS log about the SQL joins that the server made.

Usage:

execute(reset 
  details=("what_join$"|"why_join$"|"what_join$why_join$")
  by sasspds ;

DETAILS="what_join$"

adds additional information in the SAS log documenting the join plan that was selected.

DETAILS="why_join$"

adds additional information in the SAS log documenting why the join plan that was selected was chosen.

DETAILS="what_why_join$"

adds additional information in the SAS log documenting the join plan that was selected, and why the join plan that was selected was chosen.

EXEC / NOEXEC

You use the server SQL Planner EXEC / NOEXEC option to turn the server SQL execution on or off.

Usage:

/* This SQL explicit Pass-Through  */
/* prints the method tree without */
/* executing the SQL code. */

PROC SQL ;
connect to sasspds
  (dbq=domain
   server=<host-name>.<port-number>
   user='username') ;

execute (reset _method noexec)
 by sasspds ; /* turns SQL exec off */

execute (SQL statements)
 by sasspds ;

disconnect from sasspds ;
quit ;

HASHINSETSIZE

You use the server SQL planner HASHINSETSIZE reset option to influence when the hash join inset size optimization can be used. The hash-join, inset size optimization gathers join keys from the smaller join table, and then generates a query to the larger table. The query to the larger table selects only rows that can be joined to the smaller table. The join keys for the selected rows of the larger table are then hashed with the smaller table in order to perform the hash join.

Usage:

execute(reset 
  hashinsetsize=n)by sasspds;

n

the maximum number of rows in the smaller table for a hash join that can use the inset size hash join optimization.

INDEXSELECTIVITY=

The INDEXSELECTIVITY= option enables you to tune the SQL join planner strategy for more efficient or robust index join methods. The INDEXSELECTIVITY= setting is a continuous value in the range 0–1 that acts as a minimum threshold value for the server cardinality ratio when selecting a join method. The server cardinality ratio is a heuristic that acts as a measure of the cardinality of the inner table index, relative to the frequency of index values as they occur in the outer table. Both INDEXSELECTIVITY= and the server cardinality ratio are continuous values between 0 and 1. The server compares the calculated cardinality ratio for a server index join to the value that you specify in the INDEXSELECTIVITY= option. If the calculated cardinality ratio is greater than or equal to the value that is specified in the INDEXSELECTIVITY= option, SPD server chooses the index join method. The default setting for the INDEXSELECTIVITY= option is 0.7.

How does the server calculate the cardinality ratio? The cardinality ratio of an indexed column is calculated as the number of unique values in the index column, divided by the number of rows in the outer table. As the value of the cardinality ratio approaches 0, which indicates low cardinality, the greater the number of duplicate values that exist in the rows of the outer table. As the value of the cardinality ratio approaches 1, which indicates high cardinality, the fewer the number of duplicate index values in the rows of the outer table. For example, a cardinality ratio of 1/1, or 1, represents a unique index value for every row in the outer table, a unique index. A cardinality ratio value of 1/2, or 0.5, represents a unique index value for every two rows in the outer table. A cardinality ratio value of 1/4, or 0.25, represents a unique index value for every four rows in the outer table. The default setting of INDEXSELECTIVITY= is 0.7, which represents a unique index value for every 1.43 rows in the outer table.

For example, consider an outer table that contains 100 rows that match join key values in the inner table, and a calculated server cardinality ratio of 0.7 (a unique index value per 1.43 rows in the outer table). The expected result set is 100*1.43, or 143 rows.

Higher cardinality and higher index cardinality ratios are associated with an efficient index join. Cardinality ratios near 1 result in more efficient processing during probes between the outer table rows and the inner table index, because each probe has fewer rows to retrieve. In turn, the work that the server index must do to find and retrieve the matching rows during the join operation is maximized, which results in an optimized index join.

You can use INDEXSELECTIVITY= to configure the index join to be more or less tightly constrained by the number of duplicate values in the join table rows. Increasing the value of INDEXSELECTIVITY= makes the cardinality criteria more selective by decreasing the allowable average number of rows per probe of the inner table. Setting INDEXSELECTIVITY= equal to 1.0 allows only a join with a unique index. Setting INDEXSELECTIVITY= to a value greater than 1.0 allows no index joins. Decreasing the value of INDEXSELECTIVITY= makes the cardinality criteria more flexible by increasing the allowable average number of rows per probe of the inner table. Setting INDEXSELECTIVITY= equal to 0.0 allows joins with any amount of cardinality.

Usage:

execute(reset indexselectivity=<0.0 ... 1.0>)
  by sasspds ;

INOBS

Use the INOBS option to specify the specific number of rows that you want to read from input tables.

Usage:

execute(reset inobs=<n>)
  by sasspds ;

The integer value <n> is the number of rows that you want to read.

JTECH PREF | JOINTECH_PREF

You use the server SQL Planner JTECH PREF | JOINTECH_PREF reset option to control how the server SQL Planner executes join statements. The option has four settings: seq, merge, hash, and index.

Usage:

execute(reset 
  jointech_pref=<seq|merge|hash|index>)
   by sasspds ;

JOINTECH_PREF=seq

The server performs sequential loop joins. Sequential loop joins are brute force joins that match every row of the first table to every row of the second table.

JOINTECH_PREF=merge

The server performs sort merge joins. Sort merge joins force a sort on all tables that are involved in the join.

JOINTECH_PREF=hash

The server performs hash joins. Hash joins require the server to create a memory table in order to perform the join. The size of the memory table is limited based on the available memory.

JOINTECH_PREF=index

The index join requires an index on the join column of one table, the indexselectivity requirement must be met (see the indexselectivity reset option), and reading the table via the index is more beneficial than doing a full table read. Preferring the index join removes the beneficial index read check.

MAXHASHJOIN

You use the server SQL planner MAXHASHJOIN reset option to control how many hash joins can be planned in a single statement.

Usage:

execute(reset  maxhashjoins=<n>)
by sasspds;

n

the number of hash joins that can be planned.

Note: Hash joins in the server can be memory intensive. Increasing the number of hash joins is likely to increase the memory requirements for the query plan.

OUTOBS

Use the OUTOBS option to specify the specific number of rows that you want to create or print in your output.

Usage:

execute(reset outobs=<n>)
  by sasspds ;

The integer value <n>is the number of rows that you want to create or print.

OUTRSRTJNDX / NOOUTRSRTJNDX

Use the OUTRSRTJNDX / NOOUTRSRTJNDX option to configure the sort behavior for a server join index. OUTRSRTJNDX sorts the outer table for a join index by the join key. This setting is the default server setting. NOOUTRSRTJNDX does not sort the outer table for a join index.

Usage:

/* Disable outer table      */
/* sorting for a join index */
execute(reset nooutrsrtjndx)
  by sasspds ;


/* Enable outer table       */
/* sorting for a join index */
execute(reset outrsrtjndx)
  by sasspds ;

PRINTLOG / NOPRINTLOG

You use the PRINTLOG / NOPRINTLOG option of the server SQL Planner to turn on or off the printing of the SQL statement text to the server log.

Usage:

PROC SQL ;
connect to sasspds
  (dbq=domain
   server=<host-name>.<port-number>
   user='username') ;

/* turn SQL statement printing on */
execute (reset printlog)
by sasspds ;

/* all statements will be printed to SPD Server log */
execute (SQL statements)
by sasspds ;

/* turn SQL statement printing off */
execute (reset noprintlog)
by sasspds ;

disconnect from sasspds ;
quit ;

SASVIEW / NOSASVIEW

Use the SASVIEW / NOSASVIEW option to enable or disable SAS PROC SQL views that use a server LIBNAME. SAS PROC SQL views use a generic transport format to represent numeric values, which the server converts to native numeric values. When extremely large or extremely small numeric values are conveyed in a SAS PROC SQL view to the server, extreme values might not be as precise during the server numeric conversion.

Usage:

/* Disable SAS PROC SQL views     */
/* that use an SPD Server LIBNAME */
execute(reset nosasview)
  by sasspds ;

/* Enable SAS PROC SQL views that */
/* use an SPD Server LIBNAME      */
execute(reset sasview)
  by sasspds ;

If SAS PROC SQL views are disabled and the server SQL pass-through uses a view that was created by PROC SQL, the server rejects the PROC SQL statement and inserts the following error message in the SAS log:

SPDS_WARNING: SAS View and SASVIEW Reset Option equals No.
SPDS_ERROR: An error has occured.

If SAS PROC SQL views are enabled and the server SQL pass-through uses a view that was created by PROC SQL, the server prints the following note in the SAS log:

SPDS_NOTE: SPDS using SAS View in transport mode.

SPDSIPDB

When you use the server SQL implicit pass-through facility, the server must first parse and prepare the SQL implicit pass-through statement, and then the server must execute the SQL implicit pass-through statement. Both the Prepare and Execute operations must complete successfully in order for the SQL implicit pass-through statement to be performed.

If the server cannot execute the implicit SQL submitted to SAS PROC SQL, PROC SQL will simplify the query, and the server will iteratively retry the simplified SQL query until it succeeds. By default, when implicit PROC SQL pass-through queries to the server fail, the event is not reported in the SAS log.

To enable SQL implicit pass-through statement error reporting in the server SAS log, set the SPDSIPDB implicit SQL code reporting macro to YES. SQL implicit pass-through statement errors appear in the SAS log as a NOTE: entry, and not as an ERROR: entry.

Example

%let SPDSIPDB=YES;

If undeclared, the default setting for the SPDSIPDB macro variable is NO.

UNDO_POLICY=

Use the UNDO_POLICY option in the server PROC SQL and RESET statements to configure the server PROC SQL error recovery. When you update or insert rows in a table, you might receive an error message that states that the Update or Insert operation cannot be performed. The UNDO_POLICY option specifies how you want the server to handle rows that were affected by INSERT or UPDATE statements that preceded a processing error.

Usage:

/* Do not undo any updates or inserts */
execute(reset undo_policy=none)
  by sasspds ;

/* Permit row inserts and updates to  */
/* be done up to the point of error   */
execute(reset undo_policy=required)
  by sasspds ;

UNDO_POLICY=NONE

the default setting for the server. This setting does not undo any updates or inserts.

UNDO_POLICY=REQUIRED

undoes all row updates or inserts up to the point of error.

UNDO_POLICY=OPTIONAL

undoes any updates or inserts that it can undo reliably.

If the UNDO policy is not required, you get the following warning message for an insert into the table:

WARNING: The SQL option UNDO_POLICY=REQUIRED is not in effect. If an
error is detected when processing this insert statement, that error
will not cause the entire statement to fail.