5

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>
11
  • does select * from myview where 1=0 return rows? :)
    – Philᵀᴹ
    Jan 29, 2013 at 22:11
  • Interestingly enough, no rows are returned. I am not sure how, but perhaps this is a problem with the data types of the columns returned? Rapid SQL shows the DATA_TYPE column as being of type NUMBER, though, so I am not sure what could be going wrong there...? Jan 29, 2013 at 22:36
  • Hmm. do: select * from all_tables where table_name='MYVIEW', then do the same for ALL_VIEWS. Maybe there's another object you have permission to access with the same name that's causing problems
    – Philᵀᴹ
    Jan 29, 2013 at 22:46
  • 1
    It has to be a bug or something i'm missing. Can you paste the full view definition on pastebin?
    – Philᵀᴹ
    Jan 29, 2013 at 23:44
  • 1
    If you replace myview in SELECT * FROM myView WHERE DATA_TYPE = 3; with the view query as a subquery does it work? Jan 30, 2013 at 15:35

2 Answers 2

2

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)
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.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.