The server SQL gives
you the ability to use quick index scans on large tables. Rather than
scan entire tables sequentially, which can have millions or billions
of rows, the server SQL can scan cached index metadata. The server
SQL provides enhanced index scan support for the following functions:
MIN, MAX, COUNT, NMISS, RANGE, USS, CSS, STD, STDERR, and VAR. All
of the functions can accept the DISTINCT term.
All the index scan capabilities are available for both standard server tables and
clustered tables, with the exception of the DISTINCT qualifier. The DISTINCT index
scan
function is not available in clustered tables.
The COUNT(*) function is the only function that is included with the index scan support
that does not require
an index on the table. For example, consider the following code:
select count(*) from tablename;
This code returns the
number of rows in the large table Tablename without performing a row
scan of the table. Table metadata returns the correct number of rows.
As a result, the response is as fast as an index scan, even on an
unindexed table.
COUNT(*) functions with WHERE clauses require each column referenced in the
WHERE clause to have an index in order for the index scan feature to improve performance. For
example, suppose the server table Foo has indexes on numeric columns A and B. The
following COUNT(*) functions benefit from the server index scan support:
select count(*)
from Foo
where a = 1;
select count(*)
from Foo
where a LT 4
and b EQ 5;
select count(*)
from Foo
where a in (2,4,5)
or b in (10,20,30);
All functions other than COUNT(*) require an index on function columns in order to
exploit the index scan performance savings. Minimal WHERE clause support is available
for these queries, as long as all functions use the same column,
and the WHERE clause is a simple clause that uses the LT, LE, EQ, GE, GT, IN, or BETWEEN
operator for that column. For example, suppose that the server table Bar has indexes
on numeric columns X and Y. The following SQL submissions exploit the performance
gains of index scans:
select min(x),
max(x),
count(x),
nmiss(x),
range(x),
count(distinct x)
from Bar;
select min(x),
max(x),
count(x),
nmiss(x),
range(x),
count(distinct x)
from Bar
where x between 5 and 10;
select min(x),
max(x),
count(x),
nmiss(x),
range(x),
count(distinct x)
from Bar
where x gt 100;
select min(x),
min(y),
count(x),
count(y)
from Bar;
If any one function in a statement does not meet the index scan criteria, all functions
in that statement
revert to being resolved by table scan instead of index scan. Suppose the server
table Oops has indexes on numeric columns X and Y. Column Z is not indexed. The following
server SQL statement is entirely evaluated by table scan. Index scanning is not performed
on any of the functions.
select min(x),
min(y),
count(x),
count(y),
count(z)
from Oops;
To take advantage of
index scans, you could resubmit the previous statement in the following
way:
select min(x),
min(y),
count(x),
count(y)
from Oops;
select count(y)
from Bar;
The functions MIN(x), MIN(y), COUNT(x), and COUNT(y) are evaluated using index scan
metadata to exploit the performance gains. The function COUNT(y) continues to be evaluated
by table scan. You can combine the COUNT(*) function
with other functions and benefit from index scan performance gains. For the server
table Oops, with indexes on numeric columns X and Y, the following server SQL statement
benefits from index scan performance:
select min(x),
range(y),
count(x),
count(*)
from Oops;The server index scan
is an extension of the server Parallel Group-By facility. The query
must first be accepted by the Parallel Group-By facility to be evaluated
for an index scan. For more information, see
Understanding the Parallel Group-By Facility . When
SPD Server uses the index scan optimization, the following message is printed to the SAS log:
SPDS_NOTE: Metascan used to resolve this query.