About Query Execution
When you submit a query, the initiator quickly chooses the projections to use, optimizes and plans the query execution, and logs the SQL statement to its log. This planning results in an Explain Plan. The Explain Plan maps out the steps the query performs. You can view it in the Management Console.
The optimizer breaks down the Explain Plan into smaller plans distributed to Executor Node
In the final stages of query plan execution, the initiator node does the following:
- Combines results in a grouping operation
- Merges multiple sorted partial result sets from all the executors
- Formats the results to return to the client
For detailed information about writing and executing queries, see Queries in Analyzing Data.
Backup Isolation Mode
Vertica can run any SQL query in snapshot isolation mode in order to obtain the fastest possible execution. To be precise, snapshot isolation mode is actually a form of a historical query. The syntax is:
AT EPOCH LATEST SELECT...
The command queries all data in the database up to but not including the current epoch without holding a lock or blocking write operations, which could cause the query to miss rows loaded by other users up to (but no more than) a specific number of minutes before execution.
Historical Queries
Vertica can run a query from a snapshot of the database taken at a specific date and time or at a specific epoch. The syntax is:
AT TIME 'timestamp' SELECT...
AT EPOCH epoch_number SELECT...
AT EPOCH LATEST SELECT...
The command queries all data in the database up to and including the specified epoch or the epoch representing the specified date and time, without holding a lock or blocking write operations. The specified TIMESTAMP and epoch_number values must be greater than or equal to the Ancient History Mark epoch.
Historical queries are useful because they access data in past epochs only. Historical queries do not need to hold table locks or block write operations because they do not return the absolute latest data. Their content is private to the transaction and valid only for the length of the transaction.
Historical queries behave in the same manner regardless of transaction isolation level. Historical queries observe only committed data, even excluding updates made by the current transaction, unless those updates are to a temporary table.
Be aware that there is only one backup of the logical schema. This means that any changes you make to the schema are reflected across all epochs. If, for example, you add a new column to a table and you specify a default value for the column, all historical epochs display the new column and its default value.
The DELETE command in Vertica does not actually delete data; it marks records as deleted. (The UPDATE command is actually a combined INSERT and a DELETE.) Thus, you can control how much deleted data is stored on disk. For more information, see Managing Disk Space in the Administrator's Guide.