Complex View Merging Transformation and ORA-00979 in Oracle 12c

With this Post I am trying to explain Oracle Query Transformation feature called Complex View Merging. How this feature failed our SQLs in Oracle Database 12c, which is running fine in Oracle Database 11g R2 and what we did to fix this issue. Before going directly to the issue let us read some definitions (from Oracle Documentation) of little heavy words which some of the reader might not know:

Query Transformations: For some statements, the Oracle Database Optimizer (CBO) determines whether it is advantageous to rewrite the original SQL statement into a semantically equivalent SQL statement with a lower cost. When a viable alternative exists, the Oracle Database Optimizer (CBO) calculates the cost of the alternatives separately and chooses the lowest-cost alternative. This process is called Query Transformations.

Complex View Merging: In complex view merging, the optimizer merges views containing GROUP BY and DISTINCT views. Like simple view merging, complex merging enables the optimizer to consider additional join orders and access paths. The optimizer can delay evaluation of GROUP BY or DISTINCT operations until after it has evaluated the joins. Delaying these operations can improve or worsen performance depending on the data characteristics. If the joins use filters, then delaying the operation until after joins can reduce the data set on which the operation is to be performed. Evaluating the operation early can reduce the amount of data to be processed by subsequent joins, or the joins could increase the amount of data to be processed by the operation. The optimizer uses cost to evaluate view merging and merges the view only when it is the lower cost option.

Now we know when is Query Transformations and Complex View Merging, lets looks at the issue which we faced on Oracle 12c.

nimish@garg> SELECT DISTINCT
  2        Employee_Code Integration_Id,
  3        Employee_Code Leaf_Node_Integration_Id,
  4        Fst_Name,
  5        Last_Name,
  6        Wid Emp_Wid,
  7        Wid_Skey Emp_Wid_Skey,
  8        Job_Title,
  9        Department_Name,
 10        Level_Depth,
 11        Role_Nm,
 12        Level1_Depth,
 13        CASE
 14            WHEN Level1_Depth = 1
 15            THEN
 16                (SELECT Res_Static.Integration_Id
 17                   FROM Wc_Res_Static_D Res_Static
 18                  WHERE Res_Static.R_Type = 'LEVEL_DEPTH'
 19                    AND Res_Static.Level_Depth = Level1_Depth)
 20        END Level1_Role_Nm,
 21        Emp_Hire_Dt
 22    FROM (  SELECT Employee_Code,
 23                   MAX (Fst_Name) Fst_Name,
 24                   MAX (Last_Name) Last_Name,
 25                   MAX (Wid) Wid,
 26                   MAX (Wid_Skey) Wid_Skey,
 27                   MAX (Job_Title) Job_Title,
 28                   MAX (Department_Name) Department_Name,
 29                   MAX (Level_Depth) Level_Depth,
 30                   MAX (Job_Role_Nm) Role_Nm,
 31                   MAX (Emp_Hire_Dt) Emp_Hire_Dt,
 32                   1 Level1_Depth
 33                   FROM (    SELECT Employee_Code,
 34                                    Wid,
 35                                    Wid_Skey,
 36                                    Job_Title,
 37                                    Department_Name,
 38                                    Fst_Name,
 39                                    Last_Name,
 40                                    Level_Depth,
 41                                    Job_Role_Nm,
 42                                    Emp_Hire_Dt
 43                               FROM (    SELECT Employee_Code,
 44                                                Level_Depth,
 45                                                Emp_Wid Wid,
 46                                                Emp_Wid_Skey Wid_Skey,
 47                                                Job_Title Job_Title,
 48                                                Department_Name Department_Name,
 49                                                Fst_Name Fst_Name,
 50                                                Last_Name Last_Name,
 51                                                Job_Role_Nm Job_Role_Nm,
 52                                                Emp_Hire_Dt,
 53                                                LTRIM (SYS_CONNECT_BY_PATH (Employee_Code,'*'),'*') Emp_Hierarchy
 54                                           FROM Res_Emp_Hier ResearchTemp
 55                                          WHERE Employee_Code = 64539
 56                                          START WITH Manager_Code IS NULL
 57                                          CONNECT BY NOCYCLE PRIOR Employee_Code = Manager_Code
 58                                    )
 59                                CONNECT BY LEVEL <= REGEXP_COUNT (Emp_Hierarchy,'[^*]+',1)
 60                                ORDER BY LEVEL
 61                        )
 62                  GROUP BY Employee_Code
 63           );
                     AND Res_Static.Level_Depth = Level1_Depth)
                                                  *
ERROR at line 19:
ORA-00979: not a GROUP BY expression

As we can see in above example, SQLPLUS is throwing ORA-00979 exception at line 19, which is at outer level of our GROUP BY SQL, which means Oracle 12c R1 is performing VIEW MERGE MERGING - QUERY TRANSFORMATION and making the SQL invalid. I also tested the same SQL in ORACLE 11g R2 server, where it was working fine.

So to fix the issue I added "NO_QUERY_TRANSFORMATION" hint in the SQL. The NO_QUERY_TRANSFORMATION hint instructs the optimizer to skip all query transformations, including but not limited to OR-expansion, view merging, subquery unnesting, star transformation, and materialized view rewrite. After the fix I executed the SQL and It started working fine.

I hope you have enjoyed reading this article. You might have learnt something new or may have some suggestions. Please do post you feedback and suggestions in comment-box


Related Links:
- Top 15 new features of Oracle Database 12.2 for developers
- Oracle Database 12c New Features for Developers
- JSON in Oracle Database 12c with Examples
- VIEW PUSHED PREDICATE - A Powerful Optimizer Transformation feature
- SQL Tuning: Partition Pruning and HASH JOIN - Real Life Example
- Tune Complete Refresh of Materialized View by atomic_refresh=>false parameter of dbms_mview.refresh