Validate Data Models

When you validate data models, the validation messages help you correct data models, optimize queries, reduce stuck threads, and enhance the reporting performance.

After you create or edit a data model that's created in the current or previous releases, if you click Validate, Publisher:

1. Checks the queries used for datasets, LOVs, and bursting definitions.

2. Generates the explain plan for SQL queries.

3. Displays a list of error and warning messages.

Take the required action based on the validation message. See My Oracle Support Document ID 2800118.1, and the performance recommendation document attached to the MOS note.

Note that if you have upgraded Publisher from a previous release, the existing data models are marked as not validated.

Data Model Validation Messages

This topic lists the data model validation messages for your reference.

Message Types

• Error – You must resolve the data model errors if you want to use the data model to run a report.

• Warning – Make the correction suggested in the warning message. Reporting performance might get affected if you choose to run the report ignoring the warning.

Message Reference

Validation Type	Message Type	Message	Action
Query	Warning	SQL query contains SELECT *. Use of '*' is restricted. Select the specific columns.	Specify the columns in the query.
Query	Warning	Data model contains nested BI JDBC queries. Linking logical queries is restricted. Use OTBI instead of Publisher reports or remove the link between OBIEE datasets.	Use Oracle Transactional Business Intelligence reports instead of Publisher reports or remove the link between the OBIEE datasets.
Query	Warning	SQL query execution plan contains merge cartesian joins. Generate the explain plan for the SQL query and identify the merge cartesian joins. Add the required filters in the SQL query.	Identify the merge cartesian joins in the explain plan for the SQL query. Add the required filters in the SQL query.
Runtime	Warning	Number of bind values per parameter more than the limit of {0} results in poor performance. Reduce the number of bind values.	Reduce the number of bind values per parameter.
Query	Warning	Number of columns in SELECT exceeds the limit of {0}. Select only the required columns and enable pruning. See Publisher Best Practices for SaaS Environments (Doc ID 2145444.1).	Select only the required columns and enable pruning. See Publisher Best Practices for SaaS Environments (Doc ID 2145444.1).
Query	Warning	SQL query contains non-equi joins. Intermediate row spawning can cause performance issues. Replace non-equi joins with equi join or outer join.	Replace the non-equi joins with equi join or outer join.
Query	Warning	Selected column name length exceeds the limit of {0}. Length of the column name must not be more than 15 characters. Use short alias for column names.	Use short alias with less than 15 characters for the column names.
Query	Warning	Number of inline or subquery exceeds the limit of {0}. Remove the additional in-line select queries.	Remove the additional in-line select queries.
Query	Warning	SQL query contains the FROM DUAL clause. SQL query contains too many DUAL tables. Avoid the usage of FROM DUAL clause.	Avoid the usage of FROM DUAL clause.
Query	Warning	Number of LOB columns in SELECT exceeds the limit of {0}. Select only the required columns.	Select only the required columns.
Query	Error	Query contains DDL or DML keywords. Remove the DDL and DML keywords from the SQL query.	Remove the DDL and DML keywords from the SQL query.
Structure	Warning	Number of group breaks on single dataset exceeds the limit of {0}. Remove multiple groups from the dataset.	Remove multiple groups from the dataset.
Structure	Warning	Data model contains group filters. Replace the group filters with the WHERE clause in the SQL query.	Replace the group filters with the WHERE clause in the SQL query.
Runtime	Error	Data model property is invalid or contains invalid values. Specify the correct data model property and check the property value.	Specify the correct data model property in the query and check the property value.
Query	Warning	SQL query execution plan contains full table scans. Provide the required filters on indexed columns in the SQL query.	Provide the required filters on indexed columns in the SQL query.
Query	Warning	SQL query execution plan contains high buffer reads. Buffer reads exceed the limit of 1GB. Add filters in the SQL query to reduce the data fetch volume.	Add filters in the SQL query to reduce the data fetch volume.
Query	Warning	SQL query execution plan contains high CPU cycles. Add the required  filters in the SQL query to reduce the data fetch volume.	Add the required filters in the SQL query to reduce the data fetch volume.
Query	Warning	SQL query execution plan contains function calls on filter columns. Use of SQL function calls on index columns results in poor performance. Remove function calls on filter columns.	Remove the function calls on the filter columns.
Query	Warning	Detected function calls in the WHERE clause predicates.	Avoid applying SQL or PL/SQL functions to the columns in the filter or join expression.
Query	Warning	Detected calls to PL/SQL functions in the SELECT list; such calls may affect performance significantly.	Avoid using custom PL/SQL functions in the SELECT clauses.
Query	Warning	Scalar subqueries are subqueries in the SELECT list. They must return exactly one value. Using ROWNUM or DISTINCT to restrict the output indicates potential performance problem.	Don't use scalar subqueries with DISTINCT or ROWNUM keyword.
Query	Warning	Too many values in the IN-LIST filter might prevent the optimizer from finding a more efficient plan.	Reduce the number of values in the IN-LIST filter.
Runtime	Warning	A data security predicate (DSP) is wrapped inside a subquery, producing unnecessary nesting. A redundant sub-query increases the total parsing time for the query.	Avoid unnecessary nesting of subqueries.
Query	Warning	BI Server has generated too many joins between WITH sub-queries using the unsupported SYS_OP_MAP_NONNULL function. Too many join predicates may cause low cardinality estimates for joins of the respective tables.	Avoid using too many joins between subqueries.
Query	Warning	Outer-joined tables were found in the query that do not have any columns in a SELECT list. This may create additional performance overhead during parse and run time as the optimizer may be unable to eliminate unused joins. If VO pruning is happening, check the OTBI code.	Avoid unused OUTER joined tables.
Query	Warning	Scalar subqueries are present in a SELECT list. A factored subquery inside a scalar subquery will cause progressively degraded performance during execution as the inner-most subquery and its outer parent subquery will be executed for every row produced by the embracing query.	Avoid the WITH clause in scalar subquery.
Query	Warning	Tables that are listed in a query and joined to other tables but are never selected from may potentially be redundant. This will decrease performance due to additional join overhead. Check if this table is joined on its Primary Key column to Foreign Key columns of other tables.	Avoid joining tables that aren't used in the query.
Query	Warning	A column was found that is defined as a scalar correlated subquery. If such a column is later used in a filter or join expression it may cause serious performance degradation.	Avoid redundant inline view with scalar subquery in its SELECT list.
Query	Warning	Predicates that use bind variables in a non-trivial way, e.g. (:JCODE IS NULL OR mcd.JCODE LIKE :JCODE), are discouraged. In addition, the use of OR in filter predicates that are selective, whether or not a bind value is passed, is discouraged as there are better methods for handling such cases.	Avoid the use of OR in the filter predicates that are selective.
Query	Warning	A CASE expression that contains more than 10 complex expressions (WHEN ... THEN) is CPU-intensive, especially when used in a WHERE clause.	Avoid too many complex WHEN ... THEN expressions in the CASE expression.
Query	Warning	A column defined in a subquery as a literal constant was later referenced in a join predicate elsewhere in the main query. There are better methods for handling such cases prior to executing the main SQL query.	Avoid joining on columns that are defined as constants (literals).
Query	Warning	A subquery was found with more than 10 UNION branches. Each branch of a UNION is executed separately, thus significantly increasing the query's run time. In most cases, a UNION-heavy query can be reworked into a much simpler query by factoring out some 'common denominator' subqueries, and then reusing them as per functional requirements.	Reduce the number of UNION branches in the subquery.
Query	Warning	Remove the unnecessary table reference: the columns from this table can be retrieved from another. When a redundant table is in a query, the database optimizer may not be able to eliminate it during parsing and optimization.	Remove the unnecessary table references.
Structure	Warning	Converting data from CLOB to XML using the XMLTYPE function is slow. Use the data type XMLTYPE for storing XML documents in the database.	Instead of storing data in CLOB and converting to XML, use the XMLTYPE data type for storing the XML documents in the database.
Query	Warning	A FROM clause was found with more than 10 row sources (tables, dictionary views, or inline views). Having too many row sources may cause serious performance degradation for multiple reasons.	Reduce the number of row sources (tables, dictionary views, or inline views) in the FROM clause.
Query	Warning	An inline view is joined to the same FROM clause table elsewhere in the query. This creates redundancy in lookups usage (the same table is used both as a dimension and a lookup).	Make sure the inline view isn't joined to the same FROM clause table anywhere else in the query.
Query	Warning	A lookup table was used as a dimension and joined to itself as a lookup elsewhere in the query. This creates redundancy in lookups usage (the same table is used both as a dimension and a lookup).	Make sure the lookup table isn't joined to the same FROM clause table anywhere else in the query.
Query	Warning	A lookup table is joined to itself being used as a dimension. This creates redundancy in lookups usage (the same table is used both as a dimension and a lookup).	Make sure the lookup table isn't joined to the same FROM clause table anywhere else in the query.
Query	Warning	A subquery was found using columns of a table from the top-most query block. Using a redundant table in a subquery increases the parse and execution times.	Avoid using the same columns of the table in the parent query and in the subquery with WHERE clause.
Query	Warning	A SELECT subquery block is redundant because the same conditions (tables and WHERE clause) exist in parent query block. This is a performance problem because correlated scalar subqueries are not mergeable, and they must be executed once for each row produced by the query that contains them.	Avoid using redundant WHERE clause conditions in subquery.
Query	Warning	Identical or almost identical query blocks in SET operations (UNION, INTERSECT, etc.) may cause many expensive and redundant operations	Avoid using identical queries in the UNION and SET operations.
Query	Warning	A missing join in a query can produce a Cartesian product, cause significant performance issues, and can indicate a functional bug. If the missing join is among tables with a large number of rows, the performance result can be disastrous.	Include joins between tables.
Query	Warning	Lack of filters (meaningful WHERE clause conditions to limit the number of rows returned) may cause performance problems depending on the amount of data in the table. Performance will worsen when multiple tables are joined with no filters.	Include filters in the query to limit the rows returned.
Query	Warning	A GROUP BY clause was found with more than 20 columns. A large number of columns in the GROUP BY result set may cause high CPU time to perform the sorting and grouping operations.	Reduce the number of columns in the GROUP BY clause.
Query	Warning	A table was joined but not selected from, while containing at least one join condition to the rest of the query. This potential issue increases the query runtime as the number of joins in the query increases due to the redundant table.	Remove the redundant bridge tables in the query.
Query	Warning	In some cases, the use of SUBSTR functions (SUBSTR, SUBSTRB, SUBSTRC, SUBSTR2, SUBSTR4) can be safely replaced by a LIKE condition to facilitate the use of index access paths.	Replace SUBSTR in the query with LIKE condition.
Query	Warning	This is a potential issue specific to Oracle Business Intelligence "_TL" suffixed tables where the LANGUAGE filter needs to be applied. When the LANGUAGE filter is missing, the table returns more rows than needed.	Include a filter on the Language column in the query.
Query	Warning	A leading wildcard on a LIKE condition will perform poorly as the database will not be able to use an index path.	Avoid using a leading wildcard in the LIKE condition.
Query	Warning	Hierarchical queries are slow due to their recursive nature. Such structures can be optimized by materializing the block.	Avoid recursive queries in subqueries.
Query	Warning	This is a potential issue specific to Oracle Fusion views with "_VL" suffixed in the name. The table version of these views performs better.	Avoid outer joins to _VL views.
Query	Warning	ORDER BY operations on columns from multiple physical tables can cause expensive sorts, thereby spiking CPU usage.	Avoid sorting using columns from different tables.
Query	Warning	In most cases, an analytic in-line view performs better than a WHERE block aggregate of the same logic. Convert the WHERE block subquery to an analytic in-line view.	Use analytics in inline view instead of aggregate in the subqueries.
Query	Warning	Removing unused columns, known as SQL column pruning, in a query block will improve performance by eliminating their associated resource needs during the execution process. Pruning can make the SQL more lightweight.	Drop unused columns and redundant attributes from the query.
Query	Warning	When an aggregate function in a scalar subquery is executed, it runs the same function for every row returned in the main query. Each execution is returning the same value each time, over and over again. Such subqueries should be rewritten.	Avoid using aggregate functions in scalar subqueries.
Query	Warning	DECODE statements that are deeply nested can cause incorrect cardinality estimations by the database. Performance can improve by simplifying the DECODE statements or by pre-calculating attributes.	Avoid deep nested decode statements in the query.
Query	Warning	This check identifies an attribute which can act as a reason for a late filter. Filters applied at a late point in the SQL structure can prevent optimal filtering and cause unnecessary processing overhead. Applying filters at lower levels will improve performance.	Apply filter at the initial level of the query.
Query	Warning	Join conditions should be written on the same side in the WHERE clause when several EXIST and IN clauses are used.	Use join conditions on the same side of the WHERE clause when you use many EXIST and IN clauses in the query.
Query	Warning	Certain expressions can be rewritten to help the database optimizer choose a better plan while maintaining the same functionally. If concatenation is in the expression, a database bug may arise. See Sub-optimal CONCATENATION in Execution Plans in Technote 2800118.1 for work-arounds and solutions.	Rewrite expressions to help the database optimizer choose a better plan.
Query	Warning	Both OTBI and BIP reports use Fusion View Objects (VOs) that implement user-level security by adding complex semi-joins (EXISTS conditions) to correlated inline views. Very complex structure of security clause may cause optimizer to pick up inefficient join methods. Correlation with external tables may also lead to multiple executions of such security predicates.	Avoid complex EXISTS predicates in subqueries.
Query	Warning	Correlated subqueries are expensive because they are executed once for every row extracted from the external table they are joined to. Aggregation inside correlated subqueries is also expensive as it presumes no or weak filtering.	Avoid scalar subquery with aggregation operations.
Query	Warning	A subquery wrapped inside an IN condition contains aggregation (DISTINCT and/or GROUP BY) or sorting (ORDER BY) operators. Neither of these operators has an effect on the semantics of the IN condition. However they may introduce expensive operations, and noticeably affect performance.	Remove DISTINCT, GROUP BY, or ORDER BY Clauses from the IN_CONDITION in subqueries.
Query	Warning	Nearly identical subqueries in FROM clauses should be avoided as this pattern leads to multiple redundant accesses to base tables, multiple redundant join and filter operations.	Remove repeating instances of subqueries with similar content and structure in the query and subqueries.
Query	Warning	Two tables equi-joined on columns with a low number of distinct values may potentially produce a very large intermediate row source.	Use appropriate filters and conditions in the query to access specific data.
Query	Warning	Filter condition on a table is inefficient and may produce large number of rows.	Use appropriate filters in the query.