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:

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.