Capabilities tell the Exasol which SQL features / keywords a Virtual Schema adapter supports. If the Virtual Schema does not support a certain capability, Exasol rewrites the query without that feature. In case a Virtual Schema adapter has no capabilities at all, Exasol will rewrite all queries to SELECT * FROM table. That means, that it will always load the whole remote table, even if only a single row is requested.So, for optimizing your performance, make sure that at least all functions that you use in the WHERE clause of your queries are supported by the Virtual Schema adapter.
Capability
Supported
SELECTLIST_PROJECTION
✓
SELECTLIST_EXPRESSIONS
✓
FILTER_EXPRESSIONS
✓
AGGREGATE_SINGLE_GROUP
✓
AGGREGATE_GROUP_BY_COLUMN
✓
AGGREGATE_GROUP_BY_EXPRESSION
✓
AGGREGATE_GROUP_BY_TUPLE
✓
AGGREGATE_HAVING
✓
ORDER_BY_COLUMN
✓
ORDER_BY_EXPRESSION
✓
LIMIT
✓
LIMIT_WITH_OFFSET
✓
JOIN
✓
JOIN_TYPE_INNER
✓
JOIN_TYPE_LEFT_OUTER
✓
JOIN_TYPE_RIGHT_OUTER
✓
JOIN_TYPE_FULL_OUTER
✓
JOIN_CONDITION_EQUI
✓
JOIN_CONDITION_ALL
Literal
Supported
NULL
✓
BOOL
✓
DATE
✓
TIMESTAMP
✓
TIMESTAMP_UTC
✓
DOUBLE
✓
EXACTNUMERIC
✓
STRING
✓
INTERVAL
Predicate
Supported
AND
✓
OR
✓
NOT
✓
EQUAL
✓
NOTEQUAL
✓
LESS
✓
LESSEQUAL
✓
LIKE
✓
LIKE_ESCAPE
✓
REGEXP_LIKE
✓
BETWEEN
✓
IN_CONSTLIST
✓
IS_NULL
✓
IS_NOT_NULL
✓
IS_JSON
IS_NOT_JSON
Supported Aggregate Functions
Aggregate Function
Supported
COUNT
✓
COUNT_STAR
✓
COUNT_DISTINCT
✓
COUNT_TUPLE
SUM
✓
SUM_DISTINCT
✓
MIN
✓
MAX
✓
AVG
✓
AVG_DISTINCT
✓
MEDIAN
✓
FIRST_VALUE
✓
LAST_VALUE
✓
STDDEV
✓
STDDEV_DISTINCT
✓
STDDEV_POP
✓
STDDEV_POP_DISTINCT
✓
STDDEV_SAMP
✓
STDDEV_SAMP_DISTINCT
✓
VARIANCE
✓
VARIANCE_DISTINCT
✓
VAR_POP
✓
VAR_POP_DISTINCT
✓
VAR_SAMP
✓
VAR_SAMP_DISTINCT
✓
GROUP_CONCAT
✓
GROUP_CONCAT_DISTINCT
GROUP_CONCAT_SEPARATOR
GROUP_CONCAT_ORDER_BY
GEO_INTERSECTION_AGGREGATE
GEO_UNION_AGGREGATE
ST_INTERSECTION
ST_UNION
APPROXIMATE_COUNT_DISTINCT
MUL
MUL_DISTINCT
EVERY
SOME
LISTAGG
LISTAGG_DISTINCT
LISTAGG_SEPARATOR
LISTAGG_ON_OVERFLOW_ERROR
LISTAGG_ON_OVERFLOW_TRUNCATE
LISTAGG_ORDER_BY
Supported Scalar Functions
Scalar Function
Supported
ADD
✓
SUB
✓
MULT
✓
FLOAT_DIV
✓
NEG
✓
ABS
✓
ACOS
✓
ASIN
✓
ATAN
✓
ATAN2
✓
CEIL
✓
COS
✓
COSH
✓
COT
✓
DEGREES
✓
DIV
✓
EXP
✓
FLOOR
✓
GREATEST
✓
LEAST
✓
LN
✓
LOG
✓
MOD
✓
POWER
✓
RADIANS
✓
RAND
✓
ROUND
SIGN
✓
SIN
✓
SINH
✓
SQRT
✓
TAN
✓
TANH
✓
TRUNC
✓
ASCII
✓
BIT_LENGTH
✓
CHR
✓
COLOGNE_PHONETIC
CONCAT
DUMP
EDIT_DISTANCE
INITCAP
✓
INSERT
INSTR
LENGTH
✓
LOCATE
LOWER
✓
LPAD
✓
LTRIM
✓
OCTET_LENGTH
✓
REGEXP_INSTR
REGEXP_REPLACE
✓
REGEXP_SUBSTR
REPEAT
✓
REPLACE
✓
REVERSE
✓
RIGHT
✓
RPAD
✓
RTRIM
✓
SOUNDEX
SPACE
SUBSTR
✓
TRANSLATE
✓
TRIM
✓
UNICODE
UNICODECHR
UPPER
✓
ADD_DAYS
✓
ADD_HOURS
✓
ADD_MINUTES
✓
ADD_MONTHS
✓
ADD_SECONDS
✓
ADD_WEEKS
✓
ADD_YEARS
✓
CONVERT_TZ
CURRENT_DATE
✓
CURRENT_TIMESTAMP
✓
DATE_TRUNC
✓
DAY
✓
DAYS_BETWEEN
DBTIMEZONE
EXTRACT
✓
FROM_POSIX_TIME
HOUR
HOURS_BETWEEN
LOCALTIMESTAMP
✓
MINUTE
✓
MINUTES_BETWEEN
MONTH
✓
MONTHS_BETWEEN
NUMTODSINTERVAL
NUMTOYMINTERVAL
POSIX_TIME
SECOND
SECONDS_BETWEEN
SESSIONTIMEZONE
SYSDATE
SYSTIMESTAMP
WEEK
✓
YEAR
✓
YEARS_BETWEEN
ST_X
ST_Y
ST_ENDPOINT
ST_ISCLOSED
ST_ISRING
ST_LENGTH
ST_NUMPOINTS
ST_POINTN
ST_STARTPOINT
ST_AREA
ST_EXTERIORRING
ST_INTERIORRINGN
ST_NUMINTERIORRINGS
ST_GEOMETRYN
ST_NUMGEOMETRIES
ST_BOUNDARY
ST_BUFFER
ST_CENTROID
ST_CONTAINS
ST_CONVEXHULL
ST_CROSSES
ST_DIFFERENCE
ST_DIMENSION
ST_DISJOINT
ST_DISTANCE
ST_ENVELOPE
ST_EQUALS
ST_FORCE2D
ST_GEOMETRYTYPE
ST_INTERSECTION
ST_INTERSECTS
ST_ISEMPTY
ST_ISSIMPLE
ST_OVERLAPS
ST_SETSRID
ST_SYMDIFFERENCE
ST_TOUCHES
ST_TRANSFORM
ST_UNION
ST_WITHIN
CAST
IS_NUMBER
IS_BOOLEAN
IS_DATE
IS_DSINTERVAL
IS_YMINTERVAL
IS_TIMESTAMP
TO_CHAR
TO_DATE
TO_DSINTERVAL
TO_YMINTERVAL
TO_NUMBER
TO_TIMESTAMP
BIT_AND
BIT_CHECK
BIT_LROTATE
BIT_LSHIFT
BIT_NOT
BIT_OR
BIT_RROTATE
BIT_RSHIFT
BIT_SET
BIT_TO_NUM
BIT_XOR
CASE
✓
CURRENT_SCHEMA
✓
CURRENT_SESSION
CURRENT_STATEMENT
CURRENT_USER
HASH_MD5
✓
HASHTYPE_MD5
HASH_SHA1
HASHTYPE_SHA1
HASH_SHA256
HASHTYPE_SHA256
HASH_SHA512
HASHTYPE_SHA512
HASH_TIGER
HASHTYPE_TIGER
NULLIFZERO
SYS_GUID
ZEROIFNULL
JSON_VALUE
SESSION_PARAMETER
✓
MIN_SCALE
TYPEOF
✓
CURRENT_CLUSTER
✓