ORA-600 [QCTCTE1] On Query Rewrite Using Nested Mviews and Window Operation in Execution Plan (Doc ID 733783.1)

Last updated on MARCH 27, 2014

Applies to:

Oracle Database - Enterprise Edition - Version 10.2.0.4 and later
Information in this document applies to any platform.

Symptoms

A query rewrite using nested materialized views and "windowing" sum() over () functions generated an ORA-600 [QCTCTE1] error in 10.2.0.4.   The first mview was a filter on a standard view.   The second mview was based on the first mview and contained the sum() over() functions.

The following shows the use of the sum() over() "windowing" functions in the second mview:

CREATE MATERIALIZED VIEW mview_2
BUILD IMMEDIATE 
USING INDEX 
REFRESH ON DEMAND
ENABLE QUERY REWRITE
AS
select entity_id, end_effective_date
, exp(
2.302585092994045684017991454684364207 -- ln(10)
+ sum(ln(1+0.01*nvl(tot_return,0) ))
over (partition by dictionary_id . . . order by end_effective_date)
) index_gross
. . .

 

The desired plan was that a select from the standard view would be rewritten to use the mviews instead of the standard view itself  (NYLIM_PERF_SECT_INDX_MV_SUB2 is the filtering mview based on the standard view):

| Id  | Operation                    |  Name                         | Rows  | Bytes | Cost  |
-----------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT              |                              |  125 | 28500 |    6 |
|  1 |  VIEW                        | NYLIM_PERF_SECT_INDX_V8      |  125 | 28500 |    6 |
|  2 |  WINDOW BUFFER              |                              |  125 |  8625 |    6 |
|*  3 |    TABLE ACCESS BY INDEX ROWID| NYLIM_PERF_SECT_INDX_MV_SUB2  |  125 |  8625 |    6 |
|*  4 |    INDEX FULL SCAN          | NYLIM_PERF_SECT_I_MV_S2_UK1  |  125 |      |    1 |
-----------------------------------------------------------------------------------------------

A testcase was provided and tested on other versions of the database, as well:

In 10.2.0.4, the ORA-600 [QCTCTE1] call stack was as follows:

qctcte <- xtyqbcb <- qctcpqb <- qctcpqb <- opitca <- kksLoadChild <- kxsGetRuntimeLock <- kksfbc <- kkspsc0 <- kksParseCursor <- opiosq0 <- kpooprx <- kpoal8 <- opiodr <- kpoodr <- upirtrc <- kpurcsc <- kpuexecv8 <- kpuexec <- OCIStmtExecute <- qksanAnalyzeSql <- 012 <- kkqsxt <- spefcmpa <- spefmccallstd

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms