SELECT
The SELECT command returns a set of records from one or more tables.
query:
| WITH withItem [ , withItem ]* query
| {
select
}
[ ORDER BY orderItem [, orderItem ]* ]
[ LIMIT [ start, ] { count | ALL } ]
[ OFFSET start { ROW | ROWS } ]
withItem:
name
[ '(' column [, column ]* ')' ]
AS '(' query ')'
orderItem:
expression [ ASC | DESC ] [ NULLS FIRST | NULLS LAST ]
select:
SELECT [ DISTINCT ] [/*+ hints */]
{ * | projectItem [, projectItem ]* }
FROM tableExpression
[ WHERE booleanExpression ]
[ GROUP BY { groupItem [, groupItem ]* } ]
[ HAVING booleanExpression ]
[ WINDOW window_name AS ( window_definition ) [, ...] ]
projectItem:
expression [ [ AS ] columnAlias ]
| tableAlias . *
tableExpression:
tableReference [, tableReference ]*
| tableExpression [ ( LEFT ) [ OUTER ] ] JOIN tableExpression [ joinCondition ]
joinCondition:
ON booleanExpression
| USING '(' column [, column ]* ')'
tableReference:
tablePrimary
[ [ AS ] alias ]
tablePrimary:
[ catalogName . ] tableName
| '(' query ')'
groupItem:
expression
| '(' expression [, expression ]* ')'
- Sort order defaults to ascending (ASC).
- Sorts null values after non-null values by default in an ascending sort, before non-null values in a descending sort. For any query, you can use NULLS FIRST to sort null values to the top of the results or NULLS LAST to sort null values to the bottom of the results.
- Allows you to use a positional reference to choose the sort column. For example, the command
SELECT colA,colB FROM table1 ORDER BY 2sorts the results oncolBbecause it is in position 2.
HEAVY.AI provides various query hints for controlling the behavior of the query execution engine.
SELECT /*+ hint */ FROM ...;
SELECT hints must appear first, immediately after the SELECT statement; otherwise, the query fails.
By default, a hint is applied to the query step in which it is defined. If you have multiple SELECT clauses and define a query hint in one of those clauses, the hint is applied only to the specific query step; the rest of the query steps are unaffected. For example, applying the
/* cpu_mode */ hint affects only the SELECT clause in which it exists.You can define a hint to apply to all query steps by prepending
g_ to the query hint. For example, if you define /*+ g_cpu_mode */, CPU execution is applied to all query steps.HEAVY.AI supports the following query hints.
Marker
Key-Value pair
The marker hint type represents a Boolean flag.
Hint | Details | Example |
|---|---|---|
allow_loop_join | Enable loop joins. | SELECT /+* allow_loop_join */ ... |
cpu_mode | Force CPU execution mode. | SELECT /*+ cpu_mode */ ... |
columnar_output | Enable columnar output for the input query. | SELECT /+* columnar_output */ ... |
disable_loop_join | Disable loop joins. | SELECT /+* disable_loop_join */ ... |
dynamic_watchdog | Enable dynamic watchdog. | SELECT /+* dynamic_watchdog */ ... |
dynamic_watchdog_off | Disable dynamic watchdog. | SELECT /+* dynamic_watchdog_off */ ... |
force_baseline_hash_join | Use the baseline hash join scheme by skipping the perfect hash join scheme, which is used by default. | SELECT /+* force_baseline_hash_join */ ... |
force_one_to_many_hash_join | Deploy a one-to-many hash join by skipping one-to-one hash join, which is used by default. | SELECT /+* force_one_to_many_hash_join */ ... |
keep_result | Add result set of the input query to the result set cache. | SELECT /+* keep_result */ ... |
keep_table_function_result | Add result set of the table function query to the result set cache. | SELECT /+* keep_table_function_result */ ... |
overlaps_allow_gpu_build | Use GPU (if available) to build an overlaps join hash table. (CPU is used by default.) | SELECT /+* overlaps_allow_gpu_build */ ... |
overlaps_no_cache | Skip adding an overlaps join hash table to the hash table cache. | SELECT /+* overlaps_no_cache */ ... |
rowwise_output | Enable row-wise output for the input query. | SELECT /+* rowwise_output */ ... |
watchdog | Enable watchdog. | SELECT /+* watchdog */ ... |
watchdog_off | Disable watchdog. | SELECT /+* watchdog_off */ ... |
The key-value pair type is a hint name and its value.
Hint | Details | Example |
|---|---|---|
aggregate_tree_fanout | Defines a fan out of a tree used to compute window aggregation over frame. Depending on the frame size, the tree fanout affects the performance of aggregation and the tree construction for each window function with a frame clause.
| SELECT /+* aggregate_tree_fanout(32) */ SUM(y) OVER (ORDER BY x ROWS BETWEEN ...) ... |
loop_join_inner_table_max_num_rows | Set the maximum number of rows available for a loop join.
| Set the maximum number of rows to 100:
SELECT /+* loop_join_inner_table_max_num_rows(100) */ ... |
max_join_hash_table_size | Set the maximum size of the hash table.
| Set the maximum size of the join hash table to 100: SELECT /+* max_join_hash_table_size(100) */ ... |
overlaps_bucket_threshold | Set the overlaps bucket threshold.
| Set the overlaps threshold to 10: SELECT /*+ overlaps_bucket_threshold(10.0) */ ... |
overlaps_max_size | Set the maximum overlaps size.
| Set the maximum overlap to 10:
SELECT /*+ overlaps_max_size(10.0) */ ... |
overlaps_keys_per_bin | Set the number of overlaps keys per bin.
| SELECT /+* overlaps_keys_per_bin(0.1) */ ... |
query_time_limit | Set the maximum time for the query to run.
| SELECT /+* query_time_limit(1000) */ ... |
In Release 6.4 and higher, you can run SELECT queries across tables in different databases on the same HEAVY.AI cluster without having to first connect to those databases. This enables more efficient storage and memory utilization by eliminating the need for table duplication across databases, and simplifies access to shared data and tables.
To execute queries against another database, you must have ACCESS privilege on that database, as well as SELECT privilege.
Execute a join query involving a table in the current database and another table in the
my_other_db database:SELECT name, saleamt, saledate FROM my_other_db.customers AS c, sales AS s
WHERE c.id = s.customerid;
Last modified 5mo ago