SQL Functions and Statements
This section contains information on updates to SQL functions and statements for Vertica Analytic Database 8.1.x:
MERGE Support for Subqueries and Views as Source Data
MERGE
functionality has been extended to support specifying views and subqueries as merge source data. The MERGE
statement 's USING
clause can now:
- Specify a view in the same way as a table. Vertica expands the view name to the query that it encapsulates, and uses the result set as the merge source data.
- Specify a subquery. Vertica executes the query and uses the result set as the merge source data.
For details, see MERGE Source Options in the Administrator's Guide.
Shortcut Constraint DEFAULT USING
Before this release, a column was required to specify separate subqueries for DEFAULT
and SET USING
constraints, even if both constraints specified the same subquery. Now, you can combine the two constraints and specify their subquery once, as follows:
column-name data-type DEFAULT USING (subquery)
For example, the following two statements are equivalent:
=> ALTER TABLE T1 ADD yy varchar(20) DEFAULT (SELECT y FROM T2 WHERE (T1.c1 = T2.c1)) SET USING (SELECT y FROM T2 WHERE (T1.c1 = T2.c1));
=> ALTER TABLE T1 ADD yy varchar(20) DEFAULT USING (SELECT y FROM T2 WHERE (T1.c1 = T2.c1));
Note: When you use DEFAULT USING
, the DDL that Vertica generates for the table specifies separate DEFAULT
and SET USING
constraints.
Forcing Early Materialization
You can qualify one or more tables in a query with the hint EARLY_MATERIALIZATION
. This hint can be useful in cases where late materialization of join inputs precludes other optimizations—for example, pushing aggregation down the joins or using live aggregate projections.
For More Information
See SQL Reference Manual.