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 | |
| 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 | |
| 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 | ✓ |
| WIDTH_BUCKET | ✓ |
| ASCII | ✓ |
| BIT_LENGTH | ✓ |
| CHR | ✓ |
| COLOGNE_PHONETIC | |
| CONCAT | |
| DUMP | |
| EDIT_DISTANCE | |
| INITCAP | ✓ |
| INSERT | |
| INSTR | |
| LENGTH | ✓ |
| LOCATE | |
| LOWER | ✓ |
| LPAD | ✓ |
| LTRIM | ✓ |
| OCTET_LENGTH | ✓ |
| REGEXP_COUNT | ✓ |
| 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 | |
| DAYOFWEEK | ✓ |
| 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 | ✓ |