How the CBO Evaluates the UNION and UNION ALL Operators → using UNION ALL. When the Optimizer Uses UNION or UNION ALL The optimizer uses UNION or UNION ALL … when the SQL statement contains UNION or UNION ALL clauses. Example 2-30 shows a query without a UNION … clause. The query finds customers who are new
The UNION [ALL], INTERSECT, MINUS Operators → You can combine multiple queries using the set operators UNION, UNION ALL, INTERSECT, and MINUS … valid on columns of type BLOB, CLOB, BFILE, VARRAY, or nested table. The UNION, INTERSECT, and MINUS … results with each of the set operators. UNION Example The following statement combines the results with … the
Tips for Fast Refresh with UNION ALL → You can use fast refresh for materialized views that use the UNION ALL operator by providing a … UNION ALL operator such as the following: CREATE MATERIALIZED VIEW union_all_mv AS SELECT x.rowid AS … r1, y.rowid AS r2, a, b, c FROM x, y WHERE x.a = y.b UNION ALL SELECT p.rowid, r.rowid, a, c, d … fast_rf_union_all_mv AS SELECT
SDO_AGGR_UNION Returns a geometry object that is the topological union OR operation of the specified… → See the usage information about spatial aggregate functions in Section 1.9. Most of these aggregate functions accept a parameter of type MDSYS.SDOAGGRTYPE, which is described in Section 1.9.1.
QSM-02179 MV with UNION ALL requires compatibility >= 9.2.0 → Cause: The setting of the COMPATIBLE initialization parameter is less than 9.2.0. Action: Set COMPATIBLE to 9.2.0 or higher.
QSM-02170 tables must be identical across the UNION operator → Cause: A materialized view using the UNION operator was specified and the tables on each side of … the UNION operator do not match. Fast refresh is not supported in this context. Action: Restructure … the materialized view query such that the respective sides of the UNION operator have the same table.
PCC-02338 structure contains a nested struct or union → Cause: A structure used as a host variable may not have structures or unions nested within it. Action: Rewrite the variable declaration.
PCC-02339 host variables cannot be of union types → Cause: A C union as a host variable cannot be used. Action: This error message is followed by … another message that gives the line and column of the reference to the illegal union. Recode, using a struct or individual scalar host variables.
QSM-02177 MV with UNION ALL in a view must match the view's SELECT list → Cause: If a materialized view has a view whose definition involves a UNION ALL query, then the … SELECT * when defining a materialized view with a UNION ALL in a view.
QSM-02174 the materialized view does not have a UNION ALL marker column → Cause: For a materialized view with UNION ALL to be fast refreshable, it must have a UNION ALL … distinct values in each query block. Action: Add a UNION ALL marker column to each query block in the UNION ALL query defining the materialized view.
QSM-02123 subquery and UNION materialized views must be primary key materialized views → materialized view and uses subqueries or the union operator in this context. Action: Re-create the materialized view as a primary key materialized view.
QSM-02178 MV has a view with UNION ALL that is not fast refreshable → Cause: The materialized view has a view whose definition involves a UNION ALL query that does not … referenced in the materialized view satisfies conditions for fast refresh of a UNION ALL materialized view.
QSM-02175 some query block in UNION ALL MV is not fast refreshable → Cause: For a materialized view with UNION ALL to be fast refreshable, each query block must conform … query block in the UNION ALL to ensure it is either a materialized view with joins or aggregates.
QSM-02176 MV with UNION ALL in a view is too complex → Cause: If a materialized view has a view whose definition involves a UNION ALL query, then the
PLS-00398 wrong number of columns in UNION, INTERSECT, or MINUS expression → Cause: The SELECT clauses to the left and right of a UNION, INTERSECT, or MINUS expression do not
PLS-00399 different types of columns in UNION, INTERSECT, or MINUS expression → Cause: The select lists to the left and right of a UNION, INTERSECT, or MINUS expression select at
QSM-02171 the materialized view has a join operation in the outer UNION block → Cause: Fast refresh is not supported if a materialized view having the UNION operator contains a
QSM-02150 select lists must be identical across the UNION operator → Cause: You have specified a materialized view using the UNION operator and the respective queries … on each side of the UNION operator do not have the same select list. The RELATED_NAME column shows … such that the respective sides of the UNION operator have identical select lists.
PLS-01705 table specified by a cursor not updatable if cursor specification has a UNION or ORDER_BY → UNION or ORDER_BY. Action: Refer to ANSI document ANSI X3.135-1992 for details.