Oracle View Ignoring External WHERE Clause

Question

Good day,

This one has me stumped. I have a rather nasty developer query that I would like to store in a non-materialized Oracle view. The text for the view itself is a bit long to list here, but it compiles just fine and generates the correct result set. The view query itself uses a CTE as well as two manual unpivots (the UNPIVOT operator is not available in Oracle 10) and quite a few UNIONS. The idea is to use each of the CTE 'intermediate' queries repeatedly to extract various aggregations that would otherwise not be reasonably possible in a single query (poor DB design, unfortunately out of my control). There are many aggregations required, and this is the best solution I could come up with in terms of speed, maintainability, and readability / self-documenting code.

Now, this view works perfectly when I do a simple

SELECT *
  FROM myView;

However, whenever I try to filter the results, the WHERE clause appears to be ignored. Something along the lines of

SELECT *
  FROM myView
 WHERE DATA_TYPE = 3;  --  <== There is no DATA_TYPE = 3 in the result set

Still returns all rows, while it should return nothing. Other predicates involving any of the other columns also appear to be ignored. Any idea what could possibly be causing this? The SELECT above executes just fine, and no errors are given.

For reference, the DBMS is Oracle 10g.

Thank you in advance for any assistance.

EDIT:

A trimmed down version of the view query that still demonstrates the same behavior is as follows (there are a lot more queries on both the inner and the outer portion of the CTE, I only included two inner and one outer for compactness):

CREATE OR REPLACE VIEW FSA.FSA_V_DB_TOTALS
(
    DATA_CATEGORY, DATA_TYPE, HUMAN_STRING, ELEMENT_NAME, ELEMENT_VAL,
    CLASS_CD, REGION, CNTY, DIST, TRA, FRAN, PROP
)
AS
WITH
--Queries to generate intermediate result sets
--Distribution TRA
DIST_TRA AS (
SELECT 'DISTRIBUTION - TRA' AS DATA_CATEGORY,
       1 AS DATA_TYPE,
       CLASS_CD AS CLASS_CD,
       NULL AS REGION,
       CNTY_CD AS CNTY,
       NULL AS DIST,
       TRA_CD AS TRA,
       NULL AS FRAN,
       NULL AS PROP,
       TEXT AS HUMAN_STRING,
       DECODE(UNPIVOT_ROW, 1, 'CIRCUIT MILEAGE',
                           2, 'WIRE MILEAGE',
                           3, 'DUCT MILEAGE',
                           'N/A') AS ELEMENT_NAME,
       DECODE(UNPIVOT_ROW, 1, CIRCT_FT,
                           2, WIRE_FT,
                           3, DUCT_FT,
                           'N/A') AS ELEMENT_VAL
  FROM (
       SELECT T1.CLASS_CD AS CLASS_CD,
              T1.CNTY_CD AS CNTY_CD,
              T2.TRA_CD AS TRA_CD,
              DECODE(COALESCE(T2.TRA_CD,
                              T1.CNTY_CD,
                              T1.CLASS_CD), NULL, 'DISTRIBUTION TRA TOTAL',
                                            T2.TRA_CD, 'TRA ' || T2.TRA_CD || ' TOTAL',
                                            T1.CNTY_CD, 'COUNTY ' || T1.CNTY_CD || ' TOTAL',
                                            T1.CLASS_CD, 'CLASS ' || T1.CLASS_CD || ' TOTAL') AS TEXT,
              SUM(T2.CIRCT_FT_QTY) AS CIRCT_FT,
              SUM(T2.WIRE_FT_QTY) AS WIRE_FT,
              SUM(T2.DUCT_FT_QTY) AS DUCT_FT
         FROM FSA.FSA001_DISTRIBUT T1
              INNER JOIN
              FSA.FSA002_DIST_TRA T2 ON T1.SERIAL_NO = T2.SERIAL_NO
        GROUP BY GROUPING SETS ((T1.CLASS_CD, T1.CNTY_CD, T2.TRA_CD),
                                (T1.CLASS_CD, T1.CNTY_CD),
                                (T1.CLASS_CD),
                                ())
        ),(
        SELECT level AS UNPIVOT_ROW FROM DUAL CONNECT BY level <= 3
        )
),

--Distribution Fran
DIST_FRAN AS (
SELECT 'DISTRIBUTION - FRAN' AS DATA_CATEGORY,
       2 AS DATA_TYPE,
       CLASS_CD AS CLASS_CD,
       NULL AS REGION,
       CNTY_CD AS CNTY,
       DIST_CD AS DIST,
       NULL AS TRA,
       FRAN_CD AS FRAN,
       PROP_CD AS PROP,
       TEXT AS HUMAN_STRING,
       DECODE(UNPIVOT_ROW, 1, 'POLE MILEAGE',
                           2, 'ST LIGHT MILEAGE',
                           'N/A') AS ELEMENT_NAME,
       DECODE(UNPIVOT_ROW, 1, POLE_FT,
                           2, SL_FT,
                           'N/A') AS ELEMENT_VAL
  FROM (
       SELECT T1.CLASS_CD AS CLASS_CD,
              T1.CNTY_CD AS CNTY_CD,
              T1.DIST_CD AS DIST_CD,
              T3.FRNCHSE_CD AS FRAN_CD,
              T3.PROPRTY_CD AS PROP_CD,
              DECODE(COALESCE(T3.PROPRTY_CD,
                              T3.FRNCHSE_CD,
                              T1.DIST_CD,
                              T1.CNTY_CD,
                              T1.CLASS_CD), NULL, 'DISTRIBUTION FRAN TOTAL',
                                            T3.PROPRTY_CD, 'PROPERTY ' || T3.PROPRTY_CD     || ' TOTAL',
                                            T3.FRNCHSE_CD, 'FRAN ' || T3.FRNCHSE_CD || ' TOTAL',
                                            T1.DIST_CD, 'DISTRICT ' || T1.DIST_CD || ' TOTAL',
                                            T1.CNTY_CD, 'COUNTY ' || T1.CNTY_CD || ' TOTAL',
                                            T1.CLASS_CD, 'CLASS ' || T1.CLASS_CD || ' TOTAL') AS TEXT,
              SUM(T3.POLE_FT_QTY) AS POLE_FT,
              SUM(T3.ST_LIGHT_FT_QTY) AS SL_FT
         FROM FSA.FSA001_DISTRIBUT T1
              INNER JOIN
              FSA.FSA003_DIST_FRAN T3 ON T1.SERIAL_NO = T3.SERIAL_NO
        GROUP BY GROUPING SETS ((T1.CLASS_CD, T1.CNTY_CD, T1.DIST_CD, T3.FRNCHSE_CD,     T3.PROPRTY_CD),
                                (T1.CLASS_CD, T1.CNTY_CD, T1.DIST_CD, T3.FRNCHSE_CD),
                                (T1.CLASS_CD, T1.CNTY_CD, T1.DIST_CD),
                                (T1.CLASS_CD, T1.CNTY_CD),
                                (T1.CLASS_CD),
                                ())
        ),(
        SELECT level AS UNPIVOT_ROW FROM DUAL CONNECT BY level <= 2
        )
)

--Queries to generate final result set based on CTE intermediate queries
--Subtotals / System totals for Dist-TRA, Dist-Fran, Trans-TRA, Trans-Fran
SELECT DATA_CATEGORY, DATA_TYPE, HUMAN_STRING, ELEMENT_NAME, ELEMENT_VAL,
       CLASS_CD, REGION, CNTY, DIST, TRA, FRAN, PROP
  FROM (SELECT * FROM DIST_TRA
        UNION ALL
        SELECT * FROM DIST_FRAN)

 ORDER BY DATA_CATEGORY, CLASS_CD, REGION, CNTY, DIST, NVL2(TRA, TRA, FRAN), ELEMENT_NAME, HUMAN_STRING
/

Edit 2:

The complete explain plan for selection from the view with a predicate. Note that the DBMS does not show any predicates on the top level SELECT.

SQL> SELECT * FROM FSA.FSA_V_DB_TOTALS WHERE DATA_TYPE = 3;

Execution Plan
----------------------------------------------------------
Plan hash value: 3417880006

---------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name             | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |                  | 88095 |  6194K|       |  5232   (3)| 00:01:03 |
|   1 |  VIEW                              | FSA_V_DB_TOTALS  | 88095 |  6194K|       |  5232   (3)| 00:01:03 |
|   2 |   SORT ORDER BY                    |                  | 88095 |  6194K|    17M|  5232   (3)| 00:01:03 |
|   3 |    VIEW                            |                  | 88095 |  6194K|       |  3721   (4)| 00:00:45 |
|   4 |     UNION-ALL                      |                  |       |       |       |            |          |
|   5 |      MERGE JOIN CARTESIAN          |                  |  1995 |   150K|       |  1492   (6)| 00:00:18 |
|   6 |       VIEW                         |                  |     1 |    13 |       |     2   (0)| 00:00:01 |
|*  7 |        CONNECT BY WITHOUT FILTERING|                  |       |       |       |            |          |
|   8 |         FAST DUAL                  |                  |     1 |       |       |     2   (0)| 00:00:01 |
|   9 |       BUFFER SORT                  |                  |  1995 |   124K|       |  1492   (6)| 00:00:18 |
|  10 |        VIEW                        |                  |  1995 |   124K|       |  1490   (6)| 00:00:18 |
|  11 |         SORT GROUP BY ROLLUP       |                  |  1995 | 59850 |       |  1490   (6)| 00:00:18 |
|* 12 |          HASH JOIN                 |                  |   408K|    11M|  3280K|  1447   (3)| 00:00:18 |
|  13 |           TABLE ACCESS FULL        | FSA001_DISTRIBUT |   152K|  1488K|       |   292   (2)| 00:00:04 |
|  14 |           TABLE ACCESS FULL        | FSA002_DIST_TRA  |   408K|  7980K|       |   362   (5)| 00:00:05 |
|  15 |      MERGE JOIN CARTESIAN          |                  | 86100 |  5969K|       |  2229   (3)| 00:00:27 |
|  16 |       VIEW                         |                  |     1 |    13 |       |     2   (0)| 00:00:01 |
|* 17 |        CONNECT BY WITHOUT FILTERING|                  |       |       |       |            |          |
|  18 |         FAST DUAL                  |                  |     1 |       |       |     2   (0)| 00:00:01 |
|  19 |       BUFFER SORT                  |                  | 86100 |  4876K|       |  2229   (3)| 00:00:27 |
|  20 |        VIEW                        |                  | 86100 |  4876K|       |  2227   (3)| 00:00:27 |
|  21 |         SORT GROUP BY ROLLUP       |                  | 86100 |  2438K|    14M|  2227   (3)| 00:00:27 |
|* 22 |          HASH JOIN                 |                  |   267K|  7582K|  3728K|  1006   (3)| 00:00:13 |
|  23 |           TABLE ACCESS FULL        | FSA001_DISTRIBUT |   152K|  1935K|       |   293   (3)| 00:00:04 |
|  24 |           TABLE ACCESS FULL        | FSA003_DIST_FRAN |   267K|  4183K|       |   166   (5)| 00:00:02 |
---------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   7 - filter(LEVEL<=3)
  12 - access("T2"."SERIAL_NO"="T1"."SERIAL_NO")
  17 - filter(LEVEL<=2)
  22 - access("T1"."SERIAL_NO"="T3"."SERIAL_NO")

SQL>

Answer 1

With a bit of trial and error, I was able to spot the culprit. Turns out, it has nothing to do with the view itself. Attempting to manually push the where clause directly into the query also gave the same bad results.

As it turns out, the problem was cause by the CONNECT BY clause in the inner queries. My suspicion (which may be completely wrong) is that it has to do with the fact that Oracle 10g cannot handle recursive CTE's. If anyone has any more information about why this might have happened, I am all ears.

I was able to work around the issue by moving the

SELECT level AS UNPIVOT_ROW FROM DUAL CONNECT BY level <= 3

subquery to its own CTE block and selecting from that as normal. Below I have included the (condensed) working version of the above broken query.

CREATE OR REPLACE VIEW FSA.FSA_V_DB_TOTALS_2
(...)
AS
WITH
--Queries to generate intermediate result sets

--'NUMBERS' CTE block to work around Oracle 10g limitation wherein WHERE
--  clause is ignored if CONNECY BY is directly written into inner queries.
--  NOTE: the size limitation (currently 10) only needs to be larger than
--        or equal to the largest value needed.
NUMBERS AS (
SELECT level AS UNPIVOT_ROW
  FROM DUAL CONNECT BY level <=10
),

--Distribution TRA
DIST_TRA AS (
SELECT ...
  FROM (
       SELECT ...
         FROM FSA.FSA001_DISTRIBUT T1
              INNER JOIN
              FSA.FSA002_DIST_TRA T2 ON T1.SERIAL_NO = T2.SERIAL_NO
        GROUP BY GROUPING SETS (...)
        ),(
        SELECT UNPIVOT_ROW FROM NUMBERS WHERE UNPIVOT_ROW <= 3      -- <==  THIS
        )
),

--Distribution Fran
DIST_FRAN AS (
SELECT ...
  FROM (
       SELECT ...
         FROM FSA.FSA001_DISTRIBUT T1
              INNER JOIN
              FSA.FSA003_DIST_FRAN T3 ON T1.SERIAL_NO = T3.SERIAL_NO
        GROUP BY GROUPING SETS (...)
        ),(
        SELECT UNPIVOT_ROW FROM NUMBERS WHERE UNPIVOT_ROW <= 2      -- <==  THIS AS WELL
        )
)

--Queries to generate final result set based on CTE intermediate queries
--Subtotals / System totals for Dist-TRA, Dist-Fran, Trans-TRA, Trans-Fran
SELECT ...
  FROM (SELECT * FROM DIST_TRA
        UNION ALL
        SELECT * FROM DIST_FRAN)

Answer 2

Maybe you should open a SR on Metalink. This can be a bug. I recall I saw similar behavior on 10g. Some complex query using multiple CONNECT BY was returning incorrect results. I was fixed by patching to terminal patchset.