SELECT
Retrieves a result set from one or more tables.
Syntax
[{ AT EPOCH {LATEST | epoch-number} | AT TIME 'timestamp'}]
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 LATEST
|
Queries all data in the database up to but not including the current epoch without holding a lock or blocking write operations. See Snapshot Isolation for more information. By default, queries run under the
|
epoch‑number | Queries all data in the database up to and including the current epoch without holding a lock or blocking write operations. |
AT TIME 'timestamp'
|
Specifies an historical query, where all data in the database is queried, up to and including the epoch representing the specified date and time. This query holds no locks and blocks no write operations.
|
/*+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;