SELECT
Retrieves a result set from one or more tables.
Syntax
[{ AT EPOCH {LATEST | epoch-number} | AT TIME 'timestamp'}]
[ AT epoch ] SELECT [ /*+ LABEL(label‑name)*/ ] [ ALL | DISTINCT ] ... { * | expression [ [AS] output-name] }[,…] ... [ INTO TABLE ] ... [ from-clause ] ... [ WHERE condition ] ... [ TIMESERIES slice-time ] ... [ GROUP BY expression[,…] ] ... [ HAVING condition[,…] ] ... [ MATCH ] ... [ UNION { ALL | DISTINCT } ] ... [ EXCEPT ] ... [ INTERSECT ] ... [ ORDER BY expression { ASC | DESC }[,…] ] ... [ LIMIT { count | ALL } ] ... [ OFFSET start ] ... [ FOR UPDATE [ OF table-name[,…] ] ]
Parameters
Note: SELECT
clauses such as INTO
and WHERE
are discussed in sub-sections of this page.
AT epoch
|
Returns data from the specified epoch, where epoch is one of the following:
These options are ignored if used to query temporary tables. For details, see Historical Queries in Analyzing Data. |
/*+LABEL(label‑name)*/
|
Assigns a label to a query so you can identify it for profiling and debugging. In a |
ALL | DISTINCT
|
The |
*
|
Lists all columns in the queried tables. Caution: Selecting all columns from the queried tables can produce a very large wide set, which can adversely affect performance. |
expression |
Forms the output rows of the
|
expression[[AS] output‑name]
|
A table column or column expression to select from the queried tables. You can optionally qualify expression
|
from-clause |
A comma-separated list of data sources to query. |
FOR UPDATE
|
Specifies to obtain an X lock on all tables specified in the query, most often used from
|
Privileges
You must have USAGE privileges on the schemas that contain the queried tables, as well as one, but not both, of the following:
- Owner or user with GRANT OPTION privileges
- SELECT privilege
If the SELECT
statement queries a view, the view owner must have SELECT
privileges on the view's anchor tables or views.
Example
When multiple clients run transactions as in the following example query, deadlocks can occur if FOR UPDATE
is not used. Two transactions acquire an S lock, and when both attempt to upgrade to an X lock, they encounter deadlocks:
=> SELECT balance FROM accounts WHERE account_id=3476 FOR UPDATE; ... => UPDATE accounts SET balance = balance+10 WHERE account_id=3476; => COMMIT;